2

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:

  1. 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?

Sample Tables:

  • 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

Proposed Solution:

SELECT `grouping` FROM xxx_cagri.civicrm_mapping_field;
  1. 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?

Sample Tables:

  • 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

Proposed Solution:

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;
  1. 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?

Sample Tables:

  • 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

Proposed Solution:

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.

Improve this question

1 Answer 1

Reset to default
2

  1. Yes. If you see any errors coming from core code it would be good to create a PR or file an issue.

  2. There's an api call you can run instead which will use the correct sql statements and update the tables for you. You can use api explorer (Support menu - Developer - Api3 Explorer) but it might time out on a large database so using cv from the command line might be better: cv api3 System.utf8conversion. Some extensions have tables that don't start with civicrm_ so you may need to run it a second time and specify the tables using the pattern parameter, e.g. cv api3 System.utf8conversion patterns="cividiscount%"

enter image description here

  1. It might be coming from your global sql_mode in mysql so you'll want to update that, then rebuild triggers using cv flush -T or using menu rebuild http://example.org/civicrm/menu/rebuild?reset=1&triggerRebuild=1
Improve this answer
10
  • Hello Demerit, I might not have a good grasp of English, which might be why I couldn't understand the 2nd and 3rd proposed solutions. Can't I solve these problems with the SQL query provided as a recommended solution? I would greatly appreciate it if you could provide a bit more explanation and clarification on this matter.
    – Fatih ATEŞ
    Commented Nov 30, 2023 at 9:48
  • 1
    I'll try to update the answer a bit. You could use sql for the 2nd one, but it's safer and I think easier to use the api since it will run the correct sql for you. For the 3rd one, no you shouldn't use sql to alter the triggers because they are rebuilt automatically sometimes, e.g. on upgrade. So you'll first need to change your sql_mode in my.cnf or whereever it is set on your system.
    – Demerit
    Commented Nov 30, 2023 at 15:21
  • 1
    Ok I'll add a screenshot in a minute. See also docs.civicrm.org/dev/en/latest/api/#api-explorer. But for cv it is just a command line tool like grep or ls.
    – Demerit
    Commented Nov 30, 2023 at 15:37
  • 1
    Thank you so much for the valuable information you provided. With the API call, all updates, except for 11 tables, were completed within a few minutes. I had prepared myself to update this many tables individually. :) Thanks to you, I've been relieved from an incredible workload. I've also manually changed the 11 tables named snap_civicrm_v5_65_xxx using phpMyAdmin. Now, I'm moving on to the issue in the third stage. I'll keep you informed about the progress.
    – Fatih ATEŞ
    Commented Nov 30, 2023 at 21:24
  • 1
    You could try doing a grep grouping * on your entire site and see if any of the results don't have backticks and then look into those. It won't just be civi you'll also have to look at extensions and cms plugins and any other code. Another way is make a copy of your site on mysql 8 and click on most of the things in your daily workflow and see if they still work.
    – Demerit
    Commented Dec 5, 2023 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.