Aurora MySQL v2 (MySQL 5.7 互換) の EOL が 2024/10/31 ということで、そろそろソワソワし始めている方も多いのではないでしょうか。
Upgrade Checker Utility
MySQL Shell には、MySQL 5.7 から 8.0 へアップグレードする際に問題になりそうな点を洗い出してくれる、便利なユーティリティがあります。 例えば、予約語や廃止される機能の利用の有無をチェックしてくれます。
Aurora でも動く？
Upgrade Checker Utility は Vanilla MySQL を想定して作られているものです。Auroraでどこまで動くかわかりませんが、「予約語のチェックだけでも動いてくれればラッキー」ぐらいの感覚で試してみました。
MySQL Shell のオプションに --mc を指定して、 Classic Protocol 使うようにしましょう。
指定しなくても接続できましたが、Aurora がサポートしていない X Protocol での接続がまず試みられるため、接続に時間がかかりました。
$ mysqlsh --mc admin@database-1.cluster-cm3tklt7o4tn-ro.ap-northeast-1.rds.amazonaws.com Please provide the password for 'admin@database-1.cluster-cm3tklt7o4tn-ro.ap-northeast-1.rds.amazonaws.com':
MySQL 8.0 の予約語 を使ったテーブル(ADIMN)を作って試してみます。Upgrade Checker で問題点として検知されるはず。
mysql> create table ADMIN (pk int auto_increment primary key, v int); Query OK, 0 rows affected (0.06 sec)
実行結果
途中でエラーで止まるのでは。。。と予想していたのですが、ちゃんと結果が出ました。 予約語が使われているテーブルが検知できてます 👍
3) 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.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html
t.ADMIN - Table name
一部、Aurora 固有のプロシージャが問題扱いになってますが、Aurora 固有のものはスルーで問題ないでしょう。
mysql.lambda_async - at line 4,12: unexpected token '('
mysql.rds_import_binlog_ssl_material - at line 12,14: unexpected token
'ebr_clear_ssl_material'
mysql.rds_remove_binlog_ssl_material - at line 2,16: unexpected token
'ebr_remove_ssl_material'
mysql.rds_set_external_master - at line 40,20: unexpected token
'ebr_export_ssl_material'
mysql.rds_set_external_master_with_auto_position - at line 38,20: unexpected
token 'ebr_export_ssl_material'
mysql.rds_heartbeat2 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES
table but missing from TABLES table
意外にちゃんと動いてくれそうなので、Auroraでも、Upgrade Checker を流してみて損はなさそう。
JS > util.checkForServerUpgrade()
The MySQL server at database-1.cluster-cm3tklt7o4tn.ap-northeast-1.rds.amazonaws.com:3306, version
5.7.12 - MySQL Community Server (GPL), will now be checked for compatibility
issues for upgrade to MySQL 8.0.33...
1) Usage of old temporal type
No issues found
2) MySQL 8.0 syntax check for routine-like objects
The following objects did not pass a syntax check with the latest MySQL 8.0
grammar. A common reason is that they reference names that conflict with new
reserved keywords. You must update these routine definitions and `quote` any
such references before upgrading.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html
mysql.lambda_async - at line 4,12: unexpected token '('
mysql.rds_import_binlog_ssl_material - at line 12,14: unexpected token
'ebr_clear_ssl_material'
mysql.rds_remove_binlog_ssl_material - at line 2,16: unexpected token
'ebr_remove_ssl_material'
mysql.rds_set_external_master - at line 40,20: unexpected token
'ebr_export_ssl_material'
mysql.rds_set_external_master_with_auto_position - at line 38,20: unexpected
token 'ebr_export_ssl_material'
3) 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.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html
t.ADMIN - Table name
4) Usage of utf8mb3 charset
No issues found
5) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
6) Partitioned tables using engines with non native partitioning
No issues found
7) Foreign key constraint names longer than 64 characters
No issues found
8) Usage of obsolete MAXDB sql_mode flag
No issues found
9) Usage of obsolete sql_mode flags
No issues found
10) ENUM/SET column definitions containing elements longer than 255 characters
No issues found
11) Usage of partitioned tables in shared tablespaces
No issues found
12) Circular directory references in tablespace data file paths
No issues found
13) Usage of removed functions
No issues found
14) Usage of removed GROUP BY ASC/DESC syntax
No issues found
15) Removed system variables for error logging to the system log configuration
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
16) Removed system variables
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
17) System variables with new default values
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
18) Zero Date, Datetime, and Timestamp values
Warning: By default zero date/datetime/timestamp values are no longer allowed
in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
SQL_MODE by default. These modes should be used with strict mode as they will
be merged with strict mode in a future release. If you do not include these
modes in your SQL_MODE setting, you are able to insert
date/datetime/timestamp values that contain zeros. It is strongly advised to
replace zero values with valid ones, as they may not work correctly in the
future.
More information:
https://lefred.be/content/mysql-8-0-and-wrong-dates/
global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
which allows insertion of zero dates
19) Schema inconsistencies resulting from file removal or corruption
Error: Following tables show signs that either table datadir directory or frm
file was removed/corrupted. Please check server logs, examine datadir to
detect the issue and fix it before upgrade
mysql.rds_heartbeat2 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES
table but missing from TABLES table
20) Tables recognized by InnoDB that belong to a different engine
No issues found
21) Issues reported by 'check table x for upgrade' command
No issues found
22) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:
[mysqld]
default_authentication_plugin=mysql_native_password
However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication
23) Columns which cannot have default values
No issues found
24) Check for invalid table names and schema names used in 5.7
No issues found
25) Check for orphaned routines in 5.7
No issues found
26) Check for deprecated usage of single dollar signs in object names
No issues found
27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
No issues found
Errors: 6
Warnings: 3
Notices: 0
ERROR: 6 errors were found. Please correct these issues before upgrading to avoid compatibility issues.
