0

I am having a problem in SuiteCRM Version 7.11.10 (Sugar Version 6.5.25).

I am trying to phone field. When I put field size like 255, 100 or 50 fails. But if I put a size like 5 it works.

Database failure

The output of sugarcrm.log is the following for the errors:

Fri Feb 28 10:09:52 2020 [41][33f90d60-b3c5-a268-7484-5e54fc96488f][FATAL] Mysqli_query failed.
Fri Feb 28 10:09:52 2020 [41][33f90d60-b3c5-a268-7484-5e54fc96488f][FATAL] Cannot create column Query Failed: ALTER TABLE accounts_cstm add COLUMN phone_c varchar(100)  NULL : MySQL error 1118: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
Fri Feb 28 10:09:52 2020 [41][33f90d60-b3c5-a268-7484-5e54fc96488f][FATAL] Exception handling in /opt/suitecrm/include/MVC/Controller/SugarController.php:400
Fri Feb 28 10:09:52 2020 [41][33f90d60-b3c5-a268-7484-5e54fc96488f][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Fri Feb 28 10:09:52 2020 [41][33f90d60-b3c5-a268-7484-5e54fc96488f][FATAL] backtrace:
#0 /opt/suitecrm/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /opt/suitecrm/include/database/DBManager.php(328): DBManager->registerError('Cannot create c...', 'Cannot create c...', true)
#2 /opt/suitecrm/include/database/MysqliManager.php(179): DBManager->checkError('Cannot create c...', true)
#3 /opt/suitecrm/modules/DynamicFields/DynamicField.php(657): MysqliManager->query('ALTER TABLE acc...', true, 'Cannot create c...')
#4 /opt/suitecrm/modules/DynamicFields/templates/Fields/TemplateField.php(594): DynamicField->addFieldObject(Object(TemplatePhone))
#5 /opt/suitecrm/modules/ModuleBuilder/controller.php(399): TemplateField->save(Object(DynamicField))
#6 /opt/suitecrm/include/MVC/Controller/SugarController.php(525): ModuleBuilderController->action_SaveField()
#7 /opt/suitecrm/include/MVC/Controller/SugarController.php(494): SugarController->do_action()
#8 /opt/suitecrm/include/MVC/Controller/SugarController.php(468): SugarController->handle_action()
#9 /opt/suitecrm/modules/ModuleBuilder/controller.php(101): SugarController->process()
#10 /opt/suitecrm/include/MVC/Controller/SugarController.php(373): ModuleBuilderController->process()
#11 /opt/suitecrm/include/MVC/SugarApplication.php(113): SugarController->execute()
#12 /opt/suitecrm/index.php(52): SugarApplication->execute()
#13 {main}

I understand that SuiteCRM is saving a row that exceeds the max row size of 65535, probably because it is using a lot of columns with fixed sized VARCHAR columns. And when it adds the last columns it does not fit unless you reduce the size. I tried to reduce the size thanks to this post.

The DB seems to be telling me the solution. Change some VARCHAR columns to TEXT or BLOB.

I try to add smaller fields like text or integer fields and have no problems. Unfortunately reducing the field size does not work with dropdowns for example.. Adding dropdowns always fails. So it does look that SuiteCRM columns are too big and there is not enough size for the additional data.

Is there a way to change the configuration avoiding any code changes? Do I need to upgrade SuiteCRM version?

Sebastian D'Agostino
  • 1,575
  • 2
  • 27
  • 44

1 Answers1

0

You will need to manually update all the custom fields to use only the needed size.

By default text fields (varchars) default to 255 which add up very quickly.

Go through all the fields and set them to the right size, its the only way to make room as its a hard limit.

Optionally, you can add the fields by code using the main table (instead of the _cstm table). This example adds roof_type field to opportunity table.

$dictionary['Opportunity']['fields']['roof_type'] = array(
'name' => 'roof_type',
'vname' => 'LBL_ROOF_TYPE',
'type' => 'varchar',
'len' => '255',
);
mrbarletta
  • 902
  • 11
  • 17