oinume journal

Scratchpad of what I learned

Top > MySQL > Problems when updating MySQL from 5.7 to 8.0

Problems when updating MySQL from 5.7 to 8.0

MySQL

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';