Unable to upgrade MySQL 5.6 to 5.7

0

Hello, trying to upgrade MySQL from 5.6.51 to 5.7.24 using AWS console.
my steps:

  1. restored DB snapshot from production into new instance (TEST_DB)
  2. modified engine from 5.6.51 to 5.7.24
  3. applied immediately
  4. received error in PrePatchCompatibility.log

contents of PrePatchCompatibility.log:

Executing Compatibility Checks for the MySQL server at null.
Source Version: null, Target Version: 5.7.24.

  1. MySQL 5.7 preupgrade check to catch orphan table issues
    For the following tables, either the datadir directory or frm file was removed or corrupted.
    More Information:
    https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

[table_schema, table_name]
xxxxxDb, - Check the server logs or examine datadir to determine the issue and correct it before upgrading.

  1. The DB instance must have enough space to rebuild the largest table that uses an old temporal data format.
    The DB instance must have enough space for the largest table that uses an old temporal data format to rebuild.
    More Information:
    https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.DateTime57

You must rebuild tables that use an old temporal data format, it might take an extended period of time. - Scale storage so that there is enough space for the largest table that uses an old temporal data format, and make sure the storage type is gp2 or io1, if possible.

Errors: 1
Warnings: 1
Database Objects Affected: 2
----------------------- END OF LOG ----------------------

results:
a) i repeated several times - FAIL
b) tried different target engines 5.7.X - FAIL
c) i applied manually ALTER TABLE xxx FORCE to each table, which use datetime-columns (as suggested in docs) - FAIL
d) i started resizing of the storage from 500G to 1000G (used space by tables about 160G) - it works 25 hours already in Modifying-state - so i see/feel it as FAIL
e) server logs in AWS console for TEST_DB doesn't show anything useful
f) created read replica and tried to upgrade it - FAIL

questions:

  1. how to understand real cause of my case including concrete affected database objects?
  2. what i should to do to migrate my database without errors? deadline is here 03 Aug 2021.
  3. why resizing of the database storage stucks during 25 hours in Modifying-state?

please help

longman
asked 3 years ago2006 views
2 Answers
0

resolved after connect with Support.

general baseline:
1. check old date-format tables (and alter it). Docs: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.DateTime57
use query:
SELECT DISTINCT CONCAT('ALTER TABLE ', REPLACE(is_tables.TABLE_SCHEMA, '', ''), '`.`', REPLACE(is_tables.TABLE_NAME, '`', ''), '` FORCE;')
FROM information_schema.TABLES is_tables
INNER JOIN information_schema.COLUMNS col ON col.TABLE_SCHEMA = is_tables.TABLE_SCHEMA
AND col.TABLE_NAME = is_tables.TABLE_NAME
LEFT OUTER JOIN information_schema.INNODB_SYS_TABLES systables ON
SUBSTRING_INDEX(systables.NAME, '#', 1) = CONCAT(is_tables.TABLE_SCHEMA,'/',is_tables.TABLE_NAME)
LEFT OUTER JOIN information_schema.INNODB_SYS_COLUMNS syscolumns ON
syscolumns.TABLE_ID = systables.TABLE_ID AND syscolumns.NAME = col.COLUMN_NAME
WHERE col.COLUMN_TYPE IN ('time','timestamp','datetime')
AND is_tables.TABLE_TYPE = 'BASE TABLE'
AND is_tables.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema')
AND (is_tables.ENGINE = 'InnoDB' AND syscolumns.MTYPE = 6);

2. run mysqlcheck. Docs: https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html#option_mysqlcheck_check
mysqlcheck --host=<scheme>.<region>.rds.amazonaws.com --user=<user> --password=<password> --databases <database_name> --check-upgrade

3. check orphaned tables, which can exists after some failed ALTER-statements in the past. Docs: https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
and remove them using template:
DROP TABLE #mysql50#<table name from previous output>;
for example:
DROP TABLE #mysql50##sql-993_15a14a;

after this steps upgrade was made successfully

longman
answered 3 years ago
0

This doesn't work as described here: https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/ as there is a situation where a file is missing and the DROP TABLE just errors out

The suggested fix would be something like that:

cp t1.frm "#sql-f3db_2.frm"

and then do the DROP TABLE which I obviously cannot do on the managed RDS MySQL instance. What would be the suggested solution to that one then?

mysql> select * from information_schema.innodb_sys_tables where name like '%#sql%';
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                       | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
|      315 | some_db/#sql2-11d5-12 |   33 |     15 |   291 | Barracuda   | Dynamic    |             0 | Single     |
+----------+----------------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)
mysql> use some_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE `#mysql50##sql2-11d5-12`;
ERROR 1051 (42S02): Unknown table 'some_db.#mysql50##sql2-11d5-12'
Simon
answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions