During the upgrade to MySQL 8.0, I encountered three different warnings related to CiviCRM tables and I want to take the correct steps in this regard. These warnings are listed below:
- Usage of db objects with names conflicting with new reserved keywords Warning: The following objects have names that conflict with new reserved keywords. Ensure queries sent by your applications use quotes when referring to them or they will result in errors.
Do I need to fix the mentioned 4 tables (sample tables listed below)? Are the commands I provided for fixing these tables correct and valid?
- xxx_xx.civicrm_mapping_field.grouping - Column name
- xxx_xx.civicrm_option_value.grouping - Column name
- xxx_xx.log_civicrm_mapping_field.grouping - Column name
- xxx_xx.log_civicrm_option_value.grouping - Column name
SELECT `grouping` FROM xxx_cagri.civicrm_mapping_field;
- Usage of utf8mb3 charset The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.
Do I need to fix the mentioned 2250 tables (sample tables listed below)? Will the approach I provided be effective in this case?
- xxx_xx.alode_akeeba_common.key - column's default character set: utf8
- xxx_xx.civicrm_acl.name - column's default character set: utf8
- xxx_xx.civicrm_acl.entity_table - column's default character set: utf8
- xxx_xx.civicrm_acl.operation - column's default character set: utf8
ALTER TABLE xxx_cagri.civicrm_acl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE xxx_cagri.civicrm_action_log MODIFY COLUMN message TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- Usage of obsolete sql_mode flags The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0.
Do I need to fix the mentioned 376 tables (sample tables listed below)? Will the provided approach be effective in this scenario?
- xxx_xx.civicrm_strip_non_numeric - FUNCTION uses obsolete
- xxx_xx.civicrm_acl_after_insert - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode
- xxx_xx.civicrm_acl_after_update - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode
- xxx_xx.civicrm_acl_after_delete - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode
ALTER FUNCTION xxx_cagri.civicrm_strip_non_numeric SET @@sql_mode = 'NEW_SQL_MODE_FLAGS';
What are your experiences and recommendations regarding these warnings and proposed solutions? Thank you in advance for your assistance.