This page describes known issues and incompatibilities that you might encounter when you perform a major version upgrade from Cloud SQL for MySQL 5.7 to Cloud SQL for MySQL 8.0.
For more information about major version upgrade, see Upgrade the major database version in-place and View error logs.
Incompatible SQL changes
This section lists of SQL incompatibilities in Cloud SQL 5.7 and Cloud SQL 8.0 that can arise when you run the pre-check utility and during the upgrade.
Reserved Keywords
The following is an example error message:
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.
Some keywords, such as GROUPS, LEAD, or RANK,
are now classified as reserved in MySQL version 8.0. This means some
words previously used as identifiers may now be considered illegal. To fix
affected statements, use identifier quoting or rename the identifier.
For a complete keyword list, see Keywords and Reserved Words.
Removed ASC/DESC with GROUP BY clause
The following is an example error message:
[ERROR] [MY-013235] [Server] Error in parsing Routine db_name.routine_name during upgrade. You may have an error in your SQL syntax; check the manual that corresponding to your MySQL server version for the right syntax to use near 'some_text'
The following is another example error message:
[ERROR] [MY-013235] [Server] Unknown trigger has an error in its body: 'You have an error in you SQL syntax; [ERROR] [MY-010198] [Server] Error in parsing Triggers from trigger_name.TRG file.
Queries that previously relied on GROUP BY sorting can produce
results that differ from previous MySQL versions. To preserve a given sort order,
provide an ORDER BY clause.
If a stored procedure, trigger, or event definition contains a query
that uses ASC or DESC with the GROUP BY
clause, then that object's query needs an ORDER BY clause.
For more information, see Remove the syntax for GROUP BY ASC and DESC.
Mix of spatial data with other types as key
The following is an example error message:
[ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys [ERROR] [MY-013140] [Server] Too many key parts specified; max 1 parts allowed
In MySQL version 8.0 and later, an index cannot contain a mix of spatial and other data types. You must remove the key and create a new one supported in MySQL version 8.0 or later. For more information, see Spatial Indexes. To identify spatial data indexes, use a query similar to the following:
SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.COLUMN_NAME, s.INDEX_TYPE, c.DATA_TYPE FROM information_schema.STATISTICS s JOIN information_schema.COLUMNS c ON s.TABLE_SCHEMA = c.TABLE_SCHEMA AND s.TABLE_NAME = c.TABLE_NAME AND s.COLUMN_NAME = c.COLUMN_NAME WHERE c.DATA_TYPE IN ( 'geometry', 'point', 'linestring', 'polygon', 'multipoint', 'multilinestring', 'multipolygon', 'geometrycollection' ) AND s.INDEX_TYPE = 'BTREE';
Invalid UTF8 characters
The following is an example error message:
[ERROR] [MY-010765] [Server] Error in Creating DD entry for %s.%s [ERROR] [MY-013140] [Server] Invalid utf8 character string: invalid_string
If a table definition contains invalid UTF8 characters, then converting the table definitions into the data dictionary might fail. To address this issue, either replace the invalid characters with their corresponding UTF8 characters or remove them altogether.
To identify and address invalid characters, you can use a query similar to the following:
SHOW CREATE TABLE table_name; ALTER TABLE table_name MODIFY COLUMN column_name data_type comment=''; // removing invalid utf8 character from comment
Uncommitted XA transactions
The following is an example error message:
[ERROR] [MY-013527] [Server] Upgrade cannot proceed due to an existing prepared XA transactions
If there are uncommitted XA transactions,
the in-place major version upgrade fails. To address this issue, run an
XA RECOVER
statement before completing the upgrade. This statement checks for uncommitted
XA transactions. If a response is returned, either commit the XA transactions by
issuing an XA COMMIT or rollback the XA transactions issuing an
XA ROLLBACK statement. To check existing XA transactions, you can
run a command similar to the following:
mysql> XA RECOVER CONVERT xid; +----------+--------------+--------------+-------------------------- | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+-------------------------- | 787611 | 9 | 9 | 0x787887111212345678812676152F12345678 | +----------+--------------+--------------+-------------------------- 1 row in set (0.00 sec)
In this example, we can see that the values for gtrid and bqual
are provided in hexadecimal format but, erroneously, concatenated. To address
this issue, you must manually construct these values using the following fields:
gtrid = 0x787887111212345678bqual = 0x812676152F12345678
To commit or rollback these XA transactions, you can create an xid
from this information using a command similar to the following:
xid: gtrid [, bqual [, formatID ]] mysql> XA ROLLBACK|COMMIT 0x787887111212345678,0x812676152F12345678,787611;
Exceed max key length
The following is an example error message:
[ERROR] [MY-013140] [Server] Specified key was too long; max key length is [INTEGER] bytes
This issue can be caused by the sql_mode configuration. In MySQL
version 5.7, the absence of strict modes meant that indexes could be created with
restriction on prefix or index length.
However, in MySQL version 8.0, strict modes such as STRICT_ALL_TABLES
or STRICT_TRANS_TABLES were introduced which applied stricter rules
on index length, which causes this error.
To address the issue, update the index prefix length to within the maximum bytes indicated in the error message. With the default protocol of UTFMB4, each character can take up to 4 bytes, meaning that the maximum character count can be determined by dividing the maximum number of bytes by 4.
Mismatched metadata information
The following is an example error message:
[ERROR] [MY-012084] [InnoDB] Num of Indexes in InnoDB doesn't match with Indexes from server [ERROR] [MY-012069] [InnoDB] table: TABLE_NAME has xx columns but InnoDB dictionary has yy columns
The following is another example error message:
[ERROR] [MY-010767] [Server] Error in fixing SE data for db_name.table_name
If you try to upgrade tables with mismatched metadata between the frm file and the InnoDB dictionary, the upgrade fails. In this case, the frm file might be corrupt. To address the issue, you must dump and restore the impacted tables before attempting to upgrade.
For more information, see Attempting to upgrade tables with mismatched metadata.
To dump and restore the impacted tables, you can run a command similar to the following:
mysqldump --databases database_name --host=$host --user=$user --password=$password > database_dump.sql mysql> source database_dump.sql;
Foreign Key name over 64 characters
The following is an example error message:
[ERROR] [MY-012054] [InnoDB] Foreign key name:key_name is too long
This error indicates tables can't have foreign key constraint names longer than 64 characters. To identify tables with constraint names that are too long, you can use a command similar to the following:
SELECT CONSTRAINT_NAME, TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CHAR_LENGTH(CONSTRAINT_NAME) > 64;
If a table contains a constraint name exceeding 64 characters, then use the
ALTER TABLE command to rename the constraint within this character
limit:
ALTER TABLE your_table RENAME CONSTRAINT your_long_constraint_name TO your_new_constraint_name;
Mismatched letter casing in table names
The following is an example error message:
[ERROR] [MY-013521] [Server] Table name 'SCHEMA_NAME.TABLE_NAME' containing upper case characters is not allowed with lower_case_table_names = 1.
If instances on MySQL version 5.7 require lowercase table names (lower_case_table_names=1),
all the table names must be converted to lowercase before upgrading to MySQL
version 8.0.
Alternatively, you can disable the requirement (lower_case_table_names=0)
and then upgrade the instance. Remember, if you change the value of the
lower_case_table_names field from 1 to 0,
you can't change the value back again in MySQL version 8.0.
Tables recognized by InnoDB that belong to a different engine
The following is an example error message:
Error: Following tables are recognized by InnoDB engine while the SQL layer believes they belong to a different engine. Such situation may happen when one removed InnoDB files manually from the disk and creates a table with same name by using different engine.
If there are tables in the database that the InnoDB engine recognizes which the SQL layer doesn't, the upgrade fails.
Find all tables in the database that aren't using the InnoDB storage engine:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE != 'InnoDB'
For each identified table, run an ALTER TABLE command to change its
storage engine to InnoDB.
ALTER TABLE db_name.table_name ENGINE='INNODB';
Unknown storage engine partition
The following is an example error message:
[System] [MY-011012] [Server] Starting upgrade of data directory. [ERROR] [MY-013140] [Server] Unknown storage engine 'partition'
MySQL version 8.0 doesn't allow partitions in the engine other than InnoDB
and ndbcluster. You must check for tables with partitions and whose
engine isn't InnoDB. To identify these tables, run this query:
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';
Any table reported by the query must be updated to use InnoDB or configured to be nonpartitioned. To change a table storage engine to InnoDB, run this statement:
ALTER TABLE db_name.table_name ENGINE = INNODB;
MVU operation running for a longer duration
There are two underlying tasks associated with a major version upgrade:
- Pre-check operation: Returns a timeout error if not finished in three hours.
- Upgrade operation: Returns a timeout error if not finished within six hours.
If the instance has an ongoing MAJOR_VERSION_UPGRADE operation for
a length of time longer than expected, then you can
investigate the MySQL error logs to check whether
it is blocked in a metadata upgrade, or stuck at some pre-check step. The most
common causes of this issue include the following:
- A very large number of tables, views, or indexes
- Insufficient resources such as CPU or memory
- Major transactions blocking the shutdown of databases for the upgrade process to begin. You can use the Google Cloud console to check current processes.
Too many open files in the system
The following is an example error message:
[ERROR] [MY-012592] [InnoDB] Operating system error number 23 in a file operation [ERROR] [MY-012596] [InnoDB] Error number 23 means 'Too many open files in system'
If the instance contains more than 2 million tables, you might receive an error indicating that there are "too many open files in the system". You might need to reduce the number of tables to less than 2 million before upgrading.
Out-of-memory error
When upgrading from MySQL 5.7 to 8.0, additional memory is required to convert old metadata to the new data dictionary. To avoid receiving an "out of memory" error during the major version upgrade, Cloud SQL recommends having at least 100 KB of memory for each table.
To find the number of tables, use the following query:
SELECT table_schema AS 'Database Name', COUNT(*) AS 'Number of Tables' FROM information_schema.tables
To address the issue, before you start the upgrade, you can temporarily increase the memory by changing the machine type.
For shared core instances (for example,
micro or small cores, including
db-f1-micro, db-g1-small, HA db-f1-micro,
HA db-g1-small), upgrade to a dedicated core instance during the
upgrade operation to avoid any potential resource related issues. You can
downgrade it after the upgrade operation finishes.
MySQL shutdown error
The following is an example error message:
[ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported.
Cloud SQL performs a clean shutdown prior to the major version upgrade. Instances with heavy workloads or long-running transactions might experience an extended shutdown process, potentially causing a timeout and the upgrade to fail. To make sure the upgrade is successful, plan the upgrade during a time period of low traffic without long-running transactions.