Need help in upgrading the aurora MYSQL 5.7 to MYSQL 8 [URGENT]

5

we are facing an issue in upgrading our aurora MySQL 5.7 instance to MYSQL 8 instance.

The procedure we follow is as follows.

  • We created a snapshot of Mysql aurora instance through the AWS console.
  • Then we restore this snapshot to a new Aurora MYSQL 8 instance.
  • In this process, we are getting the following errors that we are not able to resolve and these are from the internal table structure of the Aurora.
    • mysql.rds_monitor table present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table
    • mysql.general_log_backup recognized by the InnoDB engine but belongs to CSV.

Thanks in advance

  • Maybe you were using custom cluster or instance-level parameter groups for the 5.7 cluster and you did not add these during the restore. Make sure you create new custom parameter groups for Aurora MySQL 3 and select them in the Database options under Additional configuration on the Aurora console on the Restore DB cluster configuration page. https://aws.amazon.com/blogs/database/amazon-aurora-mysql-3-with-mysql-8-0-compatibility-is-now-generally-available/

  • FWIW - I'm running into this issue as well. I tried what @JaccoPK suggested: created a non-default cluster parameter group and enabled slow/general logs, but that didn't help. You can see the issue if you look at both of those tables:

    select * from information_schema.innodb_sys_tables where name like '%general_log_template'; select table_schema,table_name,engine,row_format,update_time from information_schema.tables where table_schema like '%general_log_template';

    You can see it exists in the information_schema.innodb_sys_tables, but not in information_schema.tables. I'm trying to find a way around it, but doing a simple CREATE TABLE IF NOT EXISTS mysql.general_log_template LIKE mysql.general_log; errors out with a permissions issue on mysql. I'm still trying to find a workaround. I'll post an answer if I find one.

  • Heads up - I think that second query is wrong. table_schema should be table_name. But, I'm still unsure if it's a valid check or not.

  • Hi @JaccoPK, Thanks for the quick response. I tried the same but still got the same errors. Any further suggestions will be helpfull

asked 2 years ago3944 views
3 Answers
1

I have a problem very similar to the one reported in this topic. I am upgrading from Aurora 2 for MySQL to Aurora 3 and during the Aurora 2 snapshot restore process with 5.7.x the errors reported in upgrade-prechecks.log and also util.checkForServerUpgrade() ; which are preventing the upgrade process.

mysql.rds_heartbeat2 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table

And also Orphan Intermediate Tables. I tried to do the process mentioned in the documentation but I was not successful. Aurora for MySQL is database managed and for that reason I don't have access to physical files. If there is any way to solve this problem I would be very grateful. I have many database instances needing to update and we have this pending.

Regards,

answered 2 years ago
0

I'm also having this issue when attempting to upgrade from Aurora 2 for MySQL to Aurora 3. These must be remnants of past migrations since the entries in information_schema.innodb_sys_tables were also present in Aurora 1, but didn't prevent migrating to Aurora 2.

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 mysql.table_migration_info - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table

answered 2 years ago
0

I'm having the same issue here. Is there a way to address this issue so that I can upgrade from Aurora 2 to 3?

"description": "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", "detectedProblems": [ { "level": "Error", "dbObject": "db.table", "description": "present in INFORMATION_SCHEMA's INNODB_SYS_TABLES table but missing from TABLES table" } ]

wcb_aws
answered 20 days 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