Upgrading from MySQL 5.7 to MySQL 8.0: Difference between revisions
From Convention Master Documentation
(Created page with "== Incompatible SQL modes == For MySQL 5.7, the installation instructions guided you to create <code>/etc/mysql/mysql.conf.d/convention_master.cnf</code> with the following contents: <code>[mysqld]</code> <code>sql-mode="NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"</code> These may also have been placed directly within <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code> or another configuration file; your installation may...") |
No edit summary |
||
| Line 5: | Line 5: | ||
<code>[mysqld]</code> | <code>[mysqld]</code> | ||
<code>sql-mode="NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"</code> | <code>sql-mode="NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"</code> | ||
These may also have been placed directly within <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code> or another configuration file; your installation may vary. | These may also have been placed directly within <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code> or another configuration file; your installation may vary. | ||
Revision as of 08:58, 22 March 2023
Incompatible SQL modes
For MySQL 5.7, the installation instructions guided you to create /etc/mysql/mysql.conf.d/convention_master.cnf with the following contents:
[mysqld]sql-mode="NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
These may also have been placed directly within /etc/mysql/mysql.conf.d/mysqld.cnf or another configuration file; your installation may vary.
BEFORE beginning the MySQL upgrade process!!!
You must remove NO_AUTO_CREATE_USER from the list of SQL modes. This is not a valid mode in MySQL 8.0, and leaving it in place will cause the upgrade process to break! You should now have
[mysqld]sql-mode="NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
HELP! I didn't read the instructions and my installation is now broken!
Find and remove the incompatible SQL mode as mentioned above, then run apt-get --fix-broken install.
Set the required options
AFTER the upgrade to MySQL 8.0 is finished, log into MySQL as the root user (you may try sudo mysql, or you may also try mysql -u root -p) and run the following query:
SET GLOBAL log_bin_trust_function_creators = 1;
You should now be ready to run with MySQL 8.0.