A Tale of Two Maxes

A few days ago an odd error message experienced by a SugarCRM user made its way to my desk. After scratching my head for a few minutes, some ideas for its source came to mind and the problem was corrected. 

Soon thereafter, a similar scenario once again made its way to me, but the conditions causing the error seemed a bit different. Fortunately, both scenarios resulted in similar errors and the solution for the prior scenario was equally applicable to the latter.

This is the error, extracted from the sugarcrm.log file:

MySQL error 1439: Display width out of range for column '<some_field_name>' (max = 255)

So what is this error telling us? The error is telling us that the referenced field cannot be created because its definition indicates a desired width larger than 255. In this particular case, it was an integer field, which in turn translates to a field that can hold more than 255 digits. Note that this a reference to the number of individual digits the field can hold, or its width, not the maximum value it can hold. This brings us to the crux of the problem.



When defining an integer field within the Studio tool in SugarCRM, the parameter that controls this width is labeled Max Size. In contrast, the maximum value that a given field can hold is controlled by the parameter in the field labeled Max Value. Please see the image below and note these two parameters.

Sometimes this distinction causes some confusion and the two values are mixed up, resulting in values greater than 255 in the Max Size field. For example, someone might mistakingly enter 1,000,000 to indicate the largest value the field should accept is one million. This in turn exceeds the hard limit of 255 and results in the above referenced error. 

While not always readily apparent in SugarCRM, the problem may manifest itself in a couple of different ways. For one, the field may be listed in the general field list for a module, but may not actually function, as in, one cannot save data within that same field. In other cases, a custom module with an improperly defined integer field will result in a failure during the creation of the table for the module when the custom module is deployed. This in turn will result in the custom module not functioning, although it may appear in the general list of modules and Studio.

Fortunately, the solution to this problem is easy to implement. To correct it, simply set the Max Size value to a value equal or less than 255. In some cases you might need to delete the field and recreate it, with a valid Max Size value defined.