UpgradeWizard not working in 4.5.1e

[rturnbull1977]

Hello to all,
I have been trying to do some upgrade / customizations in Sugar. I feel the best way to accomplish this is doing the customizations in a module to be loaded against sugarcrm. However I ran into a big problem today with the loader. I have been working on this for a couple of days, and found out it is a problem with the upgradeWizard. I HOPE THIS GETS FIXED!!!!!

Anyways, here is a detailed explaination of what I was doing.

I a manifest.php file I have created, I'm creating custom fields. I'm following SugarCRM's documentation in the developer wiki on this subject. http://www.sugarcrm.com/wiki/index.p...est_definition

Now, here is the problem. It looks like for custom fields, the data_types are not recognized properly by the UpgradeWizard and are causing some major problems. Here are two fields that I'm defining.

array ( //18 <---- Gets created in error. no entry in fields_meta_data
'name' => 'deactivation_date',
'label' => 'Deactivation Date',
'module' => 'Accounts',
'data_type' => 'date',
'max_size' => NULL,
'required_option' => 'optional',
'default_value' => NULL,
'audited' => '0',
'ext1' => NULL,
'ext2' => NULL,
'ext3' => NULL,
),
array ( //19 <---- Gets created in error. no entry in fields_meta_data
'name' => 'status',
'label' => 'Account Status',
'module' => 'Accounts',
'data_type' => 'enum',
'max_size' => NULL,
'required_option' => 'optional',
'default_value' => NULL,
'audited' => '0',
'ext1' => 'status',
'ext2' => NULL,
'ext3' => NULL,
),

Nothing too special here. However the real story comes in the logfile. Make sure you turn up the verbosity of your logger in the log4php.properties changing it to debug.

Inside the log I see this:

Mon Aug 20 15:56:11 2007,173 [986] INFO SugarCRM - Insert: INSERT into fields_meta_data set id='Accountsdeactivation_date_c', name='deactivation_date_c', l
abel='deactivation_date_c', help=null, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_
merge='0', ext4=null
Mon Aug 20 15:56:11 2007,173 [986] INFO SugarCRM - Save: INSERT into fields_meta_data set id='Accountsdeactivation_date_c', name='deactivation_date_c', lab
el='deactivation_date_c', help=null, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_me
rge='0', ext4=null
Mon Aug 20 15:56:11 2007,174 [986] INFO SugarCRM - Query:INSERT into fields_meta_data set id='Accountsdeactivation_date_c', name='deactivation_date_c', lab
el='deactivation_date_c', help=null, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_me
rge='0', ext4=null
Mon Aug 20 15:56:11 2007,174 [986] INFO SugarCRM - Query Execution Time:0.000333
Mon Aug 20 15:56:11 2007,175 [986] INFO SugarCRM - Query:SELECT * FROM fields_meta_data WHERE custom_module='Accounts' AND deleted = 0
Mon Aug 20 15:56:11 2007,176 [986] INFO SugarCRM - Query Execution Time:0.00051700000000002
Mon Aug 20 15:56:11 2007,183 [986] INFO SugarCRM - Query:ALTER TABLE accounts_cstm ADD deactivation_date_c varchar()
Mon Aug 20 15:56:11 2007,183 [986] INFO SugarCRM - Query Execution Time:0.00017999999999999
Mon Aug 20 15:56:11 2007,184 [986] ERROR SugarCRM - MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ')' at line 1
Mon Aug 20 15:56:11 2007,186 [986] INFO SugarCRM - Found extended language file: en_us.lang.ext.php
Mon Aug 20 15:56:11 2007,191 [986] INFO SugarCRM - Insert: INSERT into fields_meta_data set id='Accountsstatus_c', name='status_c', label='status_c', help=
null, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_merge='0', ext1='status', ext4=nu
ll
Mon Aug 20 15:56:11 2007,192 [986] INFO SugarCRM - Save: INSERT into fields_meta_data set id='Accountsstatus_c', name='status_c', label='status_c', help=nu
ll, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_merge='0', ext1='status', ext4=null
Mon Aug 20 15:56:11 2007,192 [986] INFO SugarCRM - Query:INSERT into fields_meta_data set id='Accountsstatus_c', name='status_c', label='status_c', help=nu
ll, custom_module='Accounts', date_modified='2007-08-20 21:56:11', deleted='0', audited='0', mass_update='0', duplicate_merge='0', ext1='status', ext4=null
Mon Aug 20 15:56:11 2007,193 [986] INFO SugarCRM - Query Execution Time:0.00034099999999998
Mon Aug 20 15:56:11 2007,193 [986] INFO SugarCRM - Query:SELECT * FROM fields_meta_data WHERE custom_module='Accounts' AND deleted = 0
Mon Aug 20 15:56:11 2007,194 [986] INFO SugarCRM - Query Execution Time:0.000525
Mon Aug 20 15:56:11 2007,201 [986] INFO SugarCRM - Query:ALTER TABLE accounts_cstm ADD status_c varchar()
Mon Aug 20 15:56:11 2007,202 [986] INFO SugarCRM - Query Execution Time:0.00018300000000002
Mon Aug 20 15:56:11 2007,202 [986] ERROR SugarCRM - MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ')' at line 1

the datatype that I specified for deactivation_date and status is WRONG. deactivation_date is a type of DATE as you can see in the custom field definition, and status is a enum. However the UpgradeWizard wants to create a varchar for each. Because the max_size is NOT defined for these fields, when the upgrade wizard does insert fields it can't because its trying to insert a varchar with NO SIZE, THUS A MYSQL ERROR.

Has anyone come across this? Is there a fix for this? Is there going to be a fix for this? Please let me know. If your not convinced, try it yourself.

Thanks

[susiewilliams]

Hi there,

Thank you for the detailed explanation of what you're seeing. To answer your questions regarding whether this is a bug that has a targeted release fix, the easiest way to find that out is to go to bugs.sugarcrm.com. There you can search on bugs, as well as enter one (which I would highly recommend... that's really the fastest way to ensure that the development team is aware of the issue; they don't regularly read the forums). When you enter a bug, you can then track its progress as it's verified, assigned to a release, and fixed.

Hope this helps,

-Susie

[rturnbull1977]

Thanks, did that yesterday. The bug number is 14757
Hope to see some response there soon. So far, none.

Thanks
Ryan

[rturnbull1977]

I have posted this to the bugs.sugarcrm.com as well. Here is my digging into getting this resolved. Please read and respond with a email message.

Hello to all,
I believe I have found the problem. The problem starts in the file DynamicField.php, and is in the function addField. When this function is executed, it executes the function get_db_add_alter_table to add the custom fields after an initial table create for the custom fields. In my case the table accounts_cstm is created. Then from there a function is called that is named get_db_add_alter_table. This function is from the TemplateField.php script. Now here is the real problem. The function get_db_add_alter_table calls $this->get_db_type. Apparently from my study on the Template*.php files, depending on the data_type specfied in the custom field definition, certain operations will take place. For example if I specify a custom field of date, the TemplateDate.php file function get_db_type is used. Within these functions are the specific calls (if any) for the differnt databases that SugarCRM supports.

However if I specifiy date as the data_type, the get_db_type function in TemplateDate.php is not used, the get_db_type function within TemplateField.php is used..... NOT GOOD.

Once this function is called, the return is always "varchar"!!!!!!!!!

I don't know if this is happening with any other data_types, however THIS IS VERY VERY SERIOUS. If this is not working in opensource, fine, you can let it slide, but because Professional and Enterprise are simular in framework to opensource, I HAVE TO ASSUME THIS IS BROKEN THERE AS WELL!!! Have your Professional/Enterprise customers try this on their onsite versions and let the support calls begin!!!

As a test, try and load a module with custom_fields specified in the manifest.php file. I AM ALMOST CERTAIN ALL FIELDS ARE GOING TO BE CREATED AS VARCHAR!!! even if date, enum, int, etc are specified in the manifest.php file section for custom_fields. PLEASE ESCULATE THE PRIORITY FOR THIS!!! As this is pretty important for anyone loading modules (ie MOST OF US).

Thanks