Introduction
I updated MySQL from 5.7 to 8.0. There were some problems when updating. This is just a memo how to solve the problems.
InnoDB deprecated file format parameters
These parameters are deprecated in 8.0.
- innodb_file_format
- innodb_file_format_check
- innodb_file_format_max
- innodb_large_prefix
ref: MySQL :: WL#7704: InnoDB: Remove deprecated file format parameters in 8.0
Query cache parameters are deprecated
These parammeters are deprecated in 8.0.
- query_cache_limit
- query_cache_size
ref: https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
innodb_support_xa
innodb_support_xa is deprecated as well.
ref: MySQL :: WL#8843: Deprecate and remove the parameter innodb_support_xa
Can't create users with GRANT
You can't create users with GRANT operation. For example, you need to change
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON db.* TO 'user'@'%' IDENTIFIED BY 'yourpassword'
like this:
CREATE USER IF NOT EXISTS 'user'@'%' identified by 'yourpassword'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON db.* TO 'user'@'%';
ref: How to grant all privileges to root user in MySQL 8.0 - Stack Overflow
default-authentication-plugin
The default value of default-authentication-plugin is caching_sha2_password. If your MySQL driver doesn't support the authentication method, you'll get an error this authentication plugin is not supported. mysql_native_password is the default value in MySQL 5.7 so you can specify it like this:
default-authentication-plugin = mysql_native_password
Other problems I've faced with
TABLE_NAMES from information_schema
My program executes following query.
SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydb'; +------------------------------------+ | TABLE_NAME | +------------------------------------+ | event_log_email | | following_teacher | | goose_db_version | | lesson | | lesson_status_log | | m_country | +------------------------------------+
The result of the query is TABLE_NAME in 8.0 although it was table_name in 5.7. As a result, I changed my query like this:
SELECT TABLE_NAME AS table_name FROM information_schema.tables WHERE table_schema = 'mydb';
