Answers.Body: <ol>
  <li><p>Yes. If you see any errors coming from core code it would be good to <a href="https://docs.civicrm.org/dev/en/latest/tools/git/#pr" rel="nofollow noreferrer">create a PR</a> or <a href="https://lab.civicrm.org/groups/dev/-/issues" rel="nofollow noreferrer">file an issue</a>.</p>
  </li>
  <li><p>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 <a href="https://github.com/civicrm/cv" rel="nofollow noreferrer">cv</a> from the command line might be better: <code>cv api3 System.utf8conversion</code>. Some extensions have tables that don't start with <code>civicrm_</code> so you may need to run it a second time and specify the tables using the <code>pattern</code> parameter, e.g. <code>cv api3 System.utf8conversion patterns=&quot;cividiscount%&quot;</code></p>
  </li>
  </ol>
  <p><a href="https://i.sstatic.net/llNLn.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/llNLn.png" alt="enter image description here" /></a></p>
  <ol start="3">
  <li>It might be coming from your <a href="https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting" rel="nofollow noreferrer">global sql_mode</a> in mysql so you'll want to update that, then rebuild triggers using <code>cv flush -T</code> or using <a href="https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild" rel="nofollow noreferrer">menu rebuild</a> <code>http://example.org/civicrm/menu/rebuild?reset=1&amp;triggerRebuild=1</code></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T09:48:02.890Z
Answers.Text: 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.
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T15:21:33.800Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T15:32:15.730Z
Answers.Text: Could you provide a screenshot regarding how to use the CiviCRM API? I haven't used this API before, and having a visual reference would greatly help me understand its functionality.
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T15:37:33.207Z
Answers.Text: Ok I'll add a screenshot in a minute. See also https://docs.civicrm.org/dev/en/latest/api/#api-explorer. But for `cv` it is just a command line tool like `grep` or `ls`.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T21:24:27.900Z
Answers.Text: 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.
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-05T18:37:31.357Z
Answers.Text: I removed the 'NO_AUTO_CREATE_USER' content from the 'sql_mode' option for issue 3. Then, I executed the query 'cv api System.flush triggers=1' from the CiviCRM API, and the problem was resolved. Thanks again, @Demerit.
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-05T18:39:41.913Z
Answers.Text: Right now, only the first issue remains. Regarding that, I created an RP as you suggested, but either what I wrote wasn't understood or I didn't grasp the answer.
  
  https://lab.civicrm.org/dev/core/-/issues/4825
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-05T19:54:46.487Z
Answers.Text: If you go to your site, do you see any errors about `grouping`? If not, then you don't need to create an issue.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-05T20:56:56.083Z
Answers.Text: I'm currently using the MySQL Upgrade Checker via MySQLShell to identify any issues or recommendations that might arise during the upgrade process. Since we rely on this system daily, I'm concerned about potential errors post-upgrade that could disrupt our daily operations. I want to ensure a seamless upgrade without any errors that could potentially disrupt or compromise our daily workflow.
Answers.UserDisplayName: Fatih ATEŞ
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-05T21:57:49.853Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-30T01:01:21.020Z
Answers.LastActivityDate: 2023-11-30T15:43:21.230Z
Answers.LastEditDate: 2023-11-30T15:43:21.230Z
Answers.UserDisplayName: Demerit
Body: <p>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:</p>
  <ol>
  <li><strong>Usage of db objects with names conflicting with new reserved keywords</strong> Warning: The following objects have names that conflict with new reserved keywords. Ensure queries sent by your applications use <strong>quotes</strong> when referring to them or they will result in errors.</li>
  </ol>
  <p>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?</p>
  <p>Sample Tables:</p>
  <ul>
  <li>xxx_xx.civicrm_mapping_field.grouping - Column name</li>
  <li>xxx_xx.civicrm_option_value.grouping - Column name</li>
  <li>xxx_xx.log_civicrm_mapping_field.grouping - Column name</li>
  <li>xxx_xx.log_civicrm_option_value.grouping - Column name</li>
  </ul>
  <p>Proposed Solution:</p>
  <pre><code>SELECT `grouping` FROM xxx_cagri.civicrm_mapping_field;
  </code></pre>
  <ol start="2">
  <li><strong>Usage of utf8mb3 charset</strong> The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 instead, for improved Unicode support.</li>
  </ol>
  <p>Do I need to fix the mentioned 2250 tables (sample tables listed below)? Will the approach I provided be effective in this case?</p>
  <p>Sample Tables:</p>
  <ul>
  <li>xxx_xx.alode_akeeba_common.key - column's default character set: utf8</li>
  <li>xxx_xx.civicrm_acl.name - column's default character set: utf8</li>
  <li>xxx_xx.civicrm_acl.entity_table - column's default character set: utf8</li>
  <li>xxx_xx.civicrm_acl.operation - column's default character set: utf8</li>
  </ul>
  <p>Proposed Solution:</p>
  <pre><code>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;
  </code></pre>
  <ol start="3">
  <li><strong>Usage of obsolete sql_mode flags</strong> The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0.</li>
  </ol>
  <p>Do I need to fix the mentioned 376 tables (sample tables listed below)? Will the provided approach be effective in this scenario?</p>
  <p>Sample Tables:</p>
  <ul>
  <li>xxx_xx.civicrm_strip_non_numeric - FUNCTION uses obsolete</li>
  <li>xxx_xx.civicrm_acl_after_insert - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode</li>
  <li>xxx_xx.civicrm_acl_after_update - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode</li>
  <li>xxx_xx.civicrm_acl_after_delete - TRIGGER uses obsolete NO_AUTO_CREATE_USER sql_mode</li>
  </ul>
  <p>Proposed Solution:</p>
  <pre><code>ALTER FUNCTION xxx_cagri.civicrm_strip_non_numeric SET @@sql_mode = 'NEW_SQL_MODE_FLAGS';
  </code></pre>
  <p>What are your experiences and recommendations regarding these warnings and proposed solutions? Thank you in advance for your assistance.</p>
  
Tags: <sql><mysql-8.0><utf8mb4>
Title: Issues with CiviCRM Tables During MySQL 8.0 Upgrade
ThreadText: # Issues with CiviCRM Tables During MySQL 8.0 Upgrade
  
  (Asked by: username_0 on 2023-11-29)
  
  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?
  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;
  
  
  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;
  
  
  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.
  
  **Tags:** sql, mysql-8.0, utf8mb4
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-11-30. Score: 2
  
  1. Yes. If you see any errors coming from core code it would be good to [create a PR](https://docs.civicrm.org/dev/en/latest/tools/git/#pr) or [file an issue](https://lab.civicrm.org/groups/dev/-/issues).
  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](https://github.com/civicrm/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](https://i.sstatic.net/llNLn.png)](https://i.sstatic.net/llNLn.png)
  
  3. It might be coming from your [global sql\_mode](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-setting) in mysql so you'll want to update that, then rebuild triggers using `cv flush -T` or using [menu rebuild](https://docs.civicrm.org/sysadmin/en/latest/troubleshooting/#trigger-rebuild) `http://example.org/civicrm/menu/rebuild?reset=1&triggerRebuild=1`
  
  > **Comments:**
  > **username_0**: Hello username_1, 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.  
  > **username_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.  
  > **username_0**: Could you provide a screenshot regarding how to use the CiviCRM API? I haven't used this API before, and having a visual reference would greatly help me understand its functionality.  
  > **username_1**: Ok I'll add a screenshot in a minute. See also https://docs.civicrm.org/dev/en/latest/api/#api-explorer. But for `cv` it is just a command line tool like `grep` or `ls`.  
  > **username_0**: 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.  
  > **username_0**: I removed the 'NO_AUTO_CREATE_USER' content from the 'sql_mode' option for issue 3. Then, I executed the query 'cv api System.flush triggers=1' from the CiviCRM API, and the problem was resolved. Thanks again, @username_1.  
  > **username_0**: Right now, only the first issue remains. Regarding that, I created an RP as you suggested, but either what I wrote wasn't understood or I didn't grasp the answer.  https://lab.civicrm.org/dev/core/-/issues/4825  
  > **username_1**: If you go to your site, do you see any errors about `grouping`? If not, then you don't need to create an issue.  
  > **username_0**: I'm currently using the MySQL Upgrade Checker via MySQLShell to identify any issues or recommendations that might arise during the upgrade process. Since we rely on this system daily, I'm concerned about potential errors post-upgrade that could disrupt our daily operations. I want to ensure a seamless upgrade without any errors that could potentially disrupt or compromise our daily workflow.  
  > **username_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.  
  
  ---
  
  