RDS MySQL 5.7 Aurora2 missing table in INNODB_SYS_TABLESPACES

Question

Hi,

While being in progress of updating 5.7 to 8, we are hitting the issue of "Usage of old temporal type" on 1 table -> 1 column
This exact type/config of column is used in various other tables, so that got us digging in the information schema tables.

Turns out, the table with the problem is not listed in table INNODB_SYS_TABLESPACES.

It's a 500GB, ~3 billion record table.

Converting the table so it's ready for 8 is doable with some tricks, but I prefer to first look into the challenge why this is the only table that is missing, is the one complaining.

Google and AI both are pointing towards "panic" and contact AWS :D

Cheers!

Answer

Hello.

When you mention the "Usage of old temporal type," it likely refers to the changes that happened with temporal types like DATETIME and TIMESTAMP between MySQL versions. In MySQL 5.6.4 and later, these types were changed to support fractional seconds. If your MySQL 5.7 installation was upgraded from an older version and the table was created before 5.6.4, then the table might be using the older format. This difference can cause issues when upgrading.

**INNODB_SYS_TABLESPACES**: This InnoDB system table provides metadata about tablespaces. If a table isn't listed here, it's typically a cause for concern. It suggests some form of inconsistency or corruption.

Before making any changes, verify the table for corruption using the CHECK TABLE command:

```
CHECK TABLE your_table_name;
```

If any issues are reported, they might need to be addressed before you continue.

Regards, Andrii

Answer

Lastly, if the problems persist or if you're concerned about potential data loss or corruption, consider seeking assistance from AWS team.

RDS MySQL 5.7 Aurora2 missing table in INNODB_SYS_TABLESPACES

Relevant content