MySQL Quick Hit: Counting Records

Within the context of our technical work, we sometimes just need a simple number to guide us along to help answer our questions.

For example, we may be curious about the number of records that were created per day. Or, perhaps we want to know how many were modified, etc. Filters and reports within Sugar can help us get some answers, but we can also do some nifty things by way of SQL queries. 

Let us take a look at the following query:

select distinct(date(date_entered)), count(*) from opportunities

where deleted = '0'

group by date(date_entered);

The above will generate output similar to the following:

Notice that the results provide us an easy to read snapshot of the data we are seeking. Obtaining similar numbers for other criteria is a simple matter of changing the field being analyzed by the DISTINCT() function and GROUP BY clause. Below is an example of a modified version that would give us the number of records that were modified per day, rather than entered as in the original example.

select distinct(date(date_modified)), count(*) from opportunities

where deleted = '0'

group by date(date_modified);

May this help simplify your work.

SugarCRM: Label Placement

Sometimes things happen. Allow me to illustrate.

For a number of years, there has been a little known option within Sidecar view metadata that allowed one to define the placement of field labels relative to the data. You can see the setting defined in line 173 of the snippet pictured below:

Setting the value to false causes the labels to display to the left of the data, as illustrated in the image that follows:

Why would one consider using this feature?

The option has some limitations, but it is helpful for reducing the amount of white space visible to the user. This, in turn, makes the view more compact and allows for more data to be displayed within a given area of the screen. It is also applicable to all users, by default.

Additionally, it is also possible to set the label placement on a per panel or module basis, allowing more granular control over the placement of labels. An example of a per panel configuration is demonstrated below:

Regardless of the manner in which one wishes to implement this feature, it always requires manual intervention, given that the labelsOnTop attribute could not be set through Studio.

The manner in which label placement is defined changed with the release of Sugar 10. 

MySQL Troubleshooting: Error 1118 Row Size Too Large

A while back I found myself attempting to correct an issue relating to the restore of a MySQL database backup. The problem at hand was the result of a very large table that would halt the process with the following: 

Error 1118 Row Size Too Large.

The source of the error was a table that contained numerous fields, tallying in the hundreds. While the database contained this table with a large row size, it did not present any particular issues executing common operations such as INSERTs, UPDATEs or DELETEs. Problems were only present if one attempted to restore the database.

Following various attempts to correct the problem, the solution eventually revealed itself. As it turns out, in some versions of MySQL, a server parameter that enforces certain rules upon creating tables is enabled by default. This validation examines the row size of each table as it is being created and if it exceeds the row size limit for the server, an error 1118 occurs, causing the restore process to halt.

The server setting in question is:

innodb_strict_mode

The setting is either defined as on or off and only affects tables utilizing the InnoDB engine. 

Setting the parameter to off (innodb_strict_mode = 0) and restarting the server allows the restore process to complete. It is worth noting that turning off the setting merely changes the behavior of the server such that it reports row size violations as warnings instead of errors, but does not alter the ability of the server to support more fields per table. This latter point is important because attempts to add additional columns to a table with a large row size will also cause the referenced error.

More information on the setting is found at the following page:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_strict_mode