This page describes known issues and incompatibilitiesthat you might encounter during precheck operations when performing a majorversion upgrade from Cloud SQL for MySQL 5.7 toCloud SQL for MySQL 8.0.

For more information about major version upgrade, seeUpgrade the major database version in-placeandView error logs.

Incompatible SQL changes

This section lists SQL incompatibilities in Cloud SQL 5.7 andCloud SQL 8.0 that you might encounter when running the precheck utilityor during the upgrade and provides suggestions for how to fix each of them.

Reserved Keywords

The following error occurs when you try to use a reserved keyword:

Warning: The following objects have names that conflict with new reservedkeywords. Ensure queries sent by your applications use `quotes` when referringto them or they will result in errors.

This error occurs if you try to use keywords now classified as reserved in MySQLversion 8.0, such as the following:

• GROUPS
• LEAD
• RANK

This means some words previously used as identifiers may now be considered illegal.To resolve this issue, fix impacted statements by using identifier quoting orrename the identifier.

For a complete keyword list, seeKeywords and Reserved Words.

Removed ASC/DESC with GROUP BY clause

The following error occurs when you try to useASC/DESC with theGROUP BY clause:

[ERROR] [MY-013235] [Server] Error in parsing Routine db_name.routine_name duringupgrade. You may have an error in your SQL syntax; check the manual thatcorresponding to your MySQL server version for the right syntax to use near'some_text'

The following is another error message you might receive in this case:

[ERROR] [MY-013235] [Server] Unknown trigger has an error in its body: 'You havean error in you SQL syntax;[ERROR] [MY-010198] [Server] Error in parsing Triggers from trigger_name.TRG file.

This error occurs if you try to sort your query usingGROUP BY.

Queries that previously relied onGROUP BY sorting can produceresults that differ from previous MySQL versions. To preserve a given sort order,provide anORDER BY clause.

To resolve the issue, use theORDER BY clause. For example, if astored procedure, trigger, or event definition contains a querythat usesASC orDESC with theGROUP BYclause, then that object's query needs anORDER BY clause.

For more information, seeRemove the syntax for GROUP BY ASC and DESC.

Mix of spatial data with other types as key

The following error occurs when you use the wrong prefix key:

[ERROR] [MY-013140] [Server] Incorrect prefix key; the used key part isn't astring, the used length is longer than the key part, or the storage engine doesn'tsupport unique prefix keys[ERROR] [MY-013140] [Server] Too many key parts specified; max 1 parts allowed

This error occurs if you try to use a mix of spatial data with other types as key.

In MySQL version 8.0 and later, an index cannot contain a mix of spatial andother data types. You must remove the key and create a new one supported in MySQLversion 8.0 or later. For more information, seeSpatial Indexes.

To resolve this issue, identify spatial data indexes using a query similar to thefollowing:

SELECTs.TABLE_SCHEMA,s.TABLE_NAME,s.INDEX_NAME,s.COLUMN_NAME,s.INDEX_TYPE,c.DATA_TYPEFROMinformation_schema.STATISTICSsJOINinformation_schema.COLUMNScONs.TABLE_SCHEMA=c.TABLE_SCHEMAANDs.TABLE_NAME=c.TABLE_NAMEANDs.COLUMN_NAME=c.COLUMN_NAMEWHEREc.DATA_TYPEIN('geometry','point','linestring','polygon','multipoint','multilinestring','multipolygon','geometrycollection')ANDs.INDEX_TYPE='BTREE';

Invalid UTF8 characters

The following error occurs when you use invalid UTF8 character strings:

[ERROR] [MY-010765] [Server] Error in Creating DD entry for %s.%s[ERROR] [MY-013140] [Server] Invalid utf8 character string: invalid_string

This error occurs if there are invalid UTF8 characters in commands. For example,if a table definition contains invalid UTF8 characters, then converting the tabledefinitions into the data dictionary might fail.

To resolve this issue, either replace the invalid characters with theircorresponding UTF8 characters or remove them altogether.

To identify and address invalid characters, you can use a query similar to thefollowing:

SHOWCREATETABLEtable_name;ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namedata_typecomment='';//removinginvalidutf8characterfromcomment

Uncommitted XA transactions

The following error occurs when there are existing prepared XA transactions:

[ERROR] [MY-013527] [Server] Upgrade cannot proceed due to an existing prepared XA transactions

This error occurs if there are uncommittedXA transactions,causing the in-place major version upgrade to fail.

To resolve this issue, run anXA RECOVERstatement before completing the upgrade. This statement checks for uncommittedXA transactions.

If a response is returned, either commit the XA transactions byissuing anXA COMMIT or rollback the XA transactions issuing anXA ROLLBACK statement.

To check existing XA transactions, you can run a command similar to the following:

mysql>XARECOVERCONVERTxid;+----------+--------------+--------------+--------------------------|formatID|gtrid_length|bqual_length|data|+----------+--------------+--------------+--------------------------|787611|9|9|0x787887111212345678812676152F12345678|+----------+--------------+--------------+--------------------------1rowinset(0.00sec)

In this example, we can see that the values forgtrid andbqualare provided in hexadecimal format but, erroneously, concatenated. To resolvethis issue, you must manually construct these values using the following fields:

• gtrid = 0x787887111212345678
• bqual = 0x812676152F12345678

To commit or rollback these XA transactions, you can create anxidfrom this information using a command similar to the following:

xid:gtrid[,bqual[,formatID]]mysql>XAROLLBACK|COMMIT0x787887111212345678,0x812676152F12345678,787611;

Exceed max key length

The following error occurs when a specified key is too long:

[ERROR] [MY-013140] [Server] Specified key was too long; max key length is [INTEGER] bytes

This error occurs if the provided key length exceeds the allowed limit.

This issue can be caused by thesql_mode configuration. In MySQLversion 5.7, the absence of strict modes meant that indexes could be created withrestriction on prefix or index length.

However, in MySQL version 8.0, strict modes such asSTRICT_ALL_TABLESorSTRICT_TRANS_TABLES were introduced which applied stricter ruleson index length, which causes this error.

To resolve the issue, update the index prefix length to within the maximum bytesindicated in the error message. With the default protocol of UTFMB4, eachcharacter can take up to 4 bytes, meaning that the maximum character count canbe determined by dividing the maximum number of bytes by 4.

Mismatched metadata information

The following error occurs when there is mismatched metadata:

[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 error message you might receive in this case:

[ERROR] [MY-010767] [Server] Error in fixing SE data for db_name.table_name

This error occurs if you try to upgrade tables with mismatched metadata.

For example, if you try to upgrade tables with mismatched metadata between the FRM file andtheInnoDB data dictionary, the upgrade fails. In this case, the FRM file might becorrupt. To resolve this issue, you must dump and restore the impacted tablesbefore attempting to upgrade.

For more information, seeAttempting to upgrade tables with mismatched metadata.

To dump and restore the impacted tables, you can run a command similar to thefollowing:

mysqldump--databases database_name --host=$host --user=$user --password=$password > database_dump.sqlmysql>sourcedatabase_dump.sql;

Foreign Key name over 64 characters

The following error occurs when you try to use a foreign key constraint namethat's too long:

[ERROR] [MY-012054] [InnoDB] Foreign key name:key_name is too long

This error occurs if the foreign key name is longer than 64 characters.

To resolve the issue, identify tables with constraint names that are too longusing a command similar to the following:

SELECTCONSTRAINT_NAME,TABLE_NAMEFROMinformation_schema.REFERENTIAL_CONSTRAINTSWHERECHAR_LENGTH(CONSTRAINT_NAME)>64;

If a table contains a constraint name exceeding 64 characters, then use theALTER TABLE command to rename the constraint within this characterlimit:

ALTERTABLEyour_tableRENAMECONSTRAINTyour_long_constraint_nameTOyour_new_constraint_name;

Orphan tables

Orphan tables can cause multiple issues.

For detailed troubleshooting help, seeOrphan tables.

Mismatched letter casing in table names

The following error occurs when you don't use precise table names:

[ERROR] [MY-013521] [Server] Table name 'SCHEMA_NAME.TABLE_NAME' containing upper case characters is not allowed with lower_case_table_names = 1.

This error occurs if there are mismatched letter casings between table names.

To resolve this issue, if instances on MySQL version 5.7 require lowercase tablenames (lower_case_table_names=1), all the table names must beconverted 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 thelower_case_table_names field from1 to0,you can't change the value back again in MySQL version 8.0.

Tables recognized byInnoDB that belong to a different engine

The following error occurs when a table is recognized byInnoDB butit actually belongs to a different engine:

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.

This error occurs if you delete a table and then create a new table with the samename, using a different engine.

If there are tables in the database that theInnoDB engine recognizeswhich the SQL layer doesn't, the upgrade fails.

To resolve this issue, find all tables in the database that aren't using theInnoDB storage engine:

SELECT*FROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMA='db_name'ANDENGINE!='InnoDB'

For each identified table, run anALTER TABLE command to change itsstorage engine toInnoDB.

ALTERTABLEdb_name.table_nameENGINE='INNODB';

Unknown storage enginepartition

The following error occurs when you try to use an unknown storage engine:

[System] [MY-011012] [Server] Starting upgrade of data directory. [ERROR] [MY-013140] [Server] Unknown storage engine 'partition'

This error occurs if there are unsupported partitions in the engine.

MySQL version 8.0 only allows the following partitions in the storage engine:

• InnoDB
• ndbcluster

To resolve this issue, you must check for tables with partitions and whoseengine isn'tInnoDB.

To identify these tables, use the following query:

SELECTTABLE_SCHEMA,TABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWHEREENGINENOTIN('innodb','ndbcluster')ANDCREATE_OPTIONSLIKE'%partitioned%';

Any table reported by the query must be updated to useInnoDB or configured tobe nonpartitioned. To change a table storage engine toInnoDB, run this statement:

ALTERTABLEdb_name.table_nameENGINE=INNODB;

MVU operation running for a longer duration

There are two underlying tasks associated with a major version upgrade:

• Precheck 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 ongoingMAJOR_VERSION_UPGRADE operation fora length of time longer than expected, then you caninvestigate the MySQL error logs to check whetherit is blocked in a metadata upgrade, or stuck at some precheck step. The mostcommon 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 error occurs when there are too many open files in the system:

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

This error occurs if, for example, the instance contains more than 2 million tables.In this case, you might receive an error indicating that there are "too many openfiles in the system".

To resolve this issue, reduce the number of tables before upgrading.

Out-of-memory error

The following error occurs when you run out of memory:

Out of memory

This error occurs if tables aren't allocated enough memory.

When upgrading from MySQL 5.7 to 8.0, additional memory is required to convertold metadata to the new data dictionary.

To resolve this issue, we recommend you have at least 100 KB of memory foreach table.

To find the number of tables, use the following query:

SELECTtable_schemaAS'Database Name',COUNT(*)AS'Number of Tables'FROMinformation_schema.tables

Before you start the upgrade, you can temporarily increasethe memory bychanging the machine type.

For shared core instances (for example,micro or smallcores, includingdb-f1-micro,db-g1-small,HA db-f1-micro,HA db-g1-small), upgrade to a dedicated core instance during theupgrade operation to avoid any potential resource related issues. You candowngrade it after the upgrade operation finishes.

MySQL shutdown error

The following error occurs when you try to upgrade after a crash:

[ERROR] [MY-012526] [InnoDB] Upgrade after a crash is not supported.

This error occurs if a previous shutdown has taken longer than expected.

Cloud SQL performs a clean shutdown prior to the major versionupgrade. Instances with heavy workloads or long-running transactions mightexperience an extended shutdown process, potentially causing a timeout and theupgrade to fail.

To resolve this issue and to make sure the upgrade is successful, plan the upgradeduring a time period of low traffic without long-running transactions.

What's next

• Upgrade the database major version in-place