Answers.Body: <p>Sounds like you've overcome the obvious ones already.</p>
  <p>We've done some limited testing with the latest Craft 2 release on MySQL 8+ and no other red flags have been raised from our experience.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-20T11:56:04.937Z
Answers.Text: Thanks for your input Brad, that's good to know!
Answers.UserDisplayName: James White
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-01-20T00:24:48.477Z
Answers.LastActivityDate: 2021-01-20T00:24:48.477Z
Answers.UserDisplayName: Brad Bell
Body: <p>Yes, that's not a typo. This is for a Craft 2 site (long story). Because MySQL 5.6 is EOL in a few weeks, I am validating how feasible it can be to run under MySQL 8.0, there's not really any option to go 5.7, as our upstream provider provisions MySQL 8 by default now.</p>
  <p>I'm asking mainly for anyone else who may have looked at this in addition to validating my own testing and what I have found. I provisioned a NGINX, PHP 7.3 FPM, MySQL 8.0 environment and did initially find several problems with MySQL 8 and Craft 2 that mean you couldn't get past the &quot;cannot connect to DB error&quot; even though the DB credentials were fine. Confirming that it wasn't me being dumb with wrong .env credentials I downgraded my DB container back to MySQL 5.7 and everything immediately worked, so it confirmed that MySQL 8 was the issue.</p>
  <p>I eventually wrote a simple PDO MySQL test outside of Craft and found some errors being thrown which we not visible when going to /admin/install (I may have got more clues looking at the logs, but sometimes, you need to sanity check yourself!). Which thankfully did then explain why things were failing and there were several issues in the end.</p>
  <h3>Default authentication plugin changes</h3>
  <p>I found Craft 2 couldn't even connect with PDO because of the default authentication plugin change for new MySQL 8 servers. Because this environment was provisioned with docker the default setting was <code>caching_sha2_password</code>. Older databases probably won't hit this.</p>
  <p><a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin" rel="noreferrer">https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin</a></p>
  <p>This required the following change on the DB user however:</p>
  <pre><code>ALTER USER 'db_user' IDENTIFIED WITH mysql_native_password BY 'password';
  FLUSH PRIVILEGES;
  </code></pre>
  <ul>
  <li>db_user - MySQL database user</li>
  <li>password - MySQL database user password</li>
  </ul>
  <h3>SQL mode</h3>
  <p>SQL mode requires the following modification, because it references a removed option in MySQL 8 which will break the DB connection as well. From the original topic on Craft 2 on MySQL 5.7.5+: <a href="https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106">Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by</a></p>
  <p>SQL mode needs to be set to:</p>
  <pre><code>sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  </code></pre>
  <p><code>NO_AUTO_CREATE_USER</code> needs to be removed from initSQLs or any .cnf configuration for MySQL 8.0+.</p>
  <p>After that, this appears to allow Craft 2 to connect properly.</p>
  <h3>General issues/problems?</h3>
  <p>So once I'd figured out why Craft 2 was having a hard time connecting to the DB backend, I pulled a DB export from our currently live environment using MySQL 5.6 and imported it into MySQL, aside from a minor issue with row sizes on the <code>craft_content</code> table (not MySQL 8 specific another issue) the DB seems to be OK and running under MySQL 8 fine. I haven't extensively tested all functionality yet, but for the most part it does seem to be running OK, no server errors when performing some save actions and such.</p>
  <p>Are there any major considerations or issues specifically for Craft 2 and MySQL 8 that should be monitored?</p>
  
Tags: <craft2><mysql>
Title: Running Craft CMS 2 on MySQL 8.0+ (Any major considerations?)
ThreadText: # Running Craft CMS 2 on MySQL 8.0+ (Any major considerations?)
  
  (Asked by: username_0 on 2021-01-19)
  
  Yes, that's not a typo. This is for a Craft 2 site (long story). Because MySQL 5.6 is EOL in a few weeks, I am validating how feasible it can be to run under MySQL 8.0, there's not really any option to go 5.7, as our upstream provider provisions MySQL 8 by default now.
  
  I'm asking mainly for anyone else who may have looked at this in addition to validating my own testing and what I have found. I provisioned a NGINX, PHP 7.3 FPM, MySQL 8.0 environment and did initially find several problems with MySQL 8 and Craft 2 that mean you couldn't get past the "cannot connect to DB error" even though the DB credentials were fine. Confirming that it wasn't me being dumb with wrong .env credentials I downgraded my DB container back to MySQL 5.7 and everything immediately worked, so it confirmed that MySQL 8 was the issue.
  
  I eventually wrote a simple PDO MySQL test outside of Craft and found some errors being thrown which we not visible when going to /admin/install (I may have got more clues looking at the logs, but sometimes, you need to sanity check yourself!). Which thankfully did then explain why things were failing and there were several issues in the end.
  
  ### Default authentication plugin changes
  
  I found Craft 2 couldn't even connect with PDO because of the default authentication plugin change for new MySQL 8 servers. Because this environment was provisioned with docker the default setting was `caching_sha2_password`. Older databases probably won't hit this.
  
  <https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin>
  
  This required the following change on the DB user however:
  
  ```
  ALTER USER 'db_user' IDENTIFIED WITH mysql_native_password BY 'password';
  FLUSH PRIVILEGES;
  
  ```
  
  * db\_user - MySQL database user
  * password - MySQL database user password
  
  ### SQL mode
  
  SQL mode requires the following modification, because it references a removed option in MySQL 8 which will break the DB connection as well. From the original topic on Craft 2 on MySQL 5.7.5+: [Getting this SQL Error: GROUP BY incompatible with sql\_mode=only\_full\_group\_by](https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106)
  
  SQL mode needs to be set to:
  
  ```
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  
  ```
  
  `NO_AUTO_CREATE_USER` needs to be removed from initSQLs or any .cnf configuration for MySQL 8.0+.
  
  After that, this appears to allow Craft 2 to connect properly.
  
  ### General issues/problems?
  
  So once I'd figured out why Craft 2 was having a hard time connecting to the DB backend, I pulled a DB export from our currently live environment using MySQL 5.6 and imported it into MySQL, aside from a minor issue with row sizes on the `craft_content` table (not MySQL 8 specific another issue) the DB seems to be OK and running under MySQL 8 fine. I haven't extensively tested all functionality yet, but for the most part it does seem to be running OK, no server errors when performing some save actions and such.
  
  Are there any major considerations or issues specifically for Craft 2 and MySQL 8 that should be monitored?
  
  **Tags:** craft2, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-01-20. Score: 1
  
  Sounds like you've overcome the obvious ones already.
  
  We've done some limited testing with the latest Craft 2 release on MySQL 8+ and no other red flags have been raised from our experience.
  
  > **Comments:**
  > **username_0**: Thanks for your input username_1, that's good to know!  
  
  ---
  
  