Aurora MySQL table rename not atomic?

Question

We migrated one of our larger databases to Aurora on Monday, and since then we’ve been getting intermittent errors in our bug tracker related to missing tables, particularly during times of high load. In all cases, the tables in question are regularly swapped out with staging tables using `RENAME TABLE x TO old_x, new_x TO x;`

In “real” MySQL this operation is atomic, however the intermittent query errors reporting non-existent tables suggest that this is not the case in Aurora MySQL. To clarify, the failures occur when other parts of the app logic try to query the table being swapped out. For example, following the above example, `SELECT y FROM x` will sometimes fail with `Table {db_name}.x doesn’t exist`. The rename operation itself completes without issue.

Can anyone confirm this one way or the other? I’m struggling to find any supporting documentation or even anyone reporting the same issue. For context, we are using Aurora MySQL 2.10.2.

Answer

Hi mpbarlow,

I understand from you are getting an intermittent error related to missing tables when running queries especially in times of high load. Please correct me if I misunderstood.

As you may know that MySQL(before 8.0) uses two Data Dictionaries (MySQL's own dictionary and InnoDB's dictionary), this issue might be due to both MySQL and InnoDB dictionaries not being in a consistent state (with each other) for a SQL statement to be able to access a table.
You may disable the foreign_key_checks parameter. As the MySQL documentation[1] clearly warns of the possibility of dictionary inconsistency: 
"With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an inconsistent state and causes the foreign key check that occurs at table load to fail."

The consequence of DDLs run while with foreign_key_checks disabled is that because one of the tables in the FK relationship now has different structure than the other, MySQL can't handle the inconsistency and both tables will go missing.

Reference:

[1] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks

Aurora MySQL table rename not atomic?

Relevant content