SugarCRM Troubleshooting: Duplicate Column Error

Some months back an interesting problem was brought to my attention. The problem at hand related to an error that occurred during the Quick Rebuild & Repair process and read as follows:

Executing repair query: Query Failed:ALTER TABLE leads_cstm add column my_customField_c char(36) NULL , add column my_customField_c char(36) NULL::MySQL error 1060: Duplicate column name 'my_customField_c'

Based on the error, one could surmise that SugarCRM was attempting to modify the leads_cstm table to add the column my_customField_c, but failed in the process of doing so because the MySQL server believes the column already exists. In short, one cannot add a column to a table if the column we are attempting to add shares the name of an already existing column.

Examination of the table in question revealed that indeed the field already existed, but why did SugarCRM not recognize this fact?

To understand the answer, it is helpful to comprehend the manner in which field definitions work in SugarCRM. Under normal circumstances, SugarCRM would look at the list of defined fields in vardefs.php, vardefs.ext.php, as well as the table fields_meta_data, and then compare that information to the structure of the corresponding table in the database.

For this example, SugarCRM would look at modules/Leads/vardefs.php, custom/Extension/modules/Leads/Ext/Vardefs (any file in this folder would suffice and it is used to build vardefs.ext.php), and finally the fields_meta_data table for any custom field definitions belonging to the Leads module. 

The first file is the default table definition included with SugarCRM. Given that is the case, one could reason that said file contains definitions for columns that will always exist in the corresponding database table, because they are already there from the very beginning. So why do we not get an error for those?

The reason is simple, and leads us to our solution. Part of the rebuild process takes this matter into account during the comparison process and informs SugarCRM that the fields already exist and that there is no need to add them to the table. However, there is a bug in this process and if the name of the field is not in all lowercase, SugarCRM is unable to confirm the existence of the field and attempts to add it, leading to the error.

To correct the problem, examine the files in custom/Extension/modules/Leads/Ext/Vardefs (replacing Leads with the appropriate module name) and confirm that none of the field definitions include names with upper case letters. Adjust to lower case if necessary, making sure to save the changes. In addition, review the records in the fields_meta_data table to check for upper case names. Once everything has been adjusted, the Quick Rebuild & Repair process should complete without error.