HOME/Technical Articles/A problem that should have been resolved in Aurora MySQL 3.04.2 has recurred where the reader cannot see the table during DDL execution.

A problem that should have been resolved in Aurora MySQL 3.04.2 has recurred where the reader cannot see the table during DDL execution.

2024/7/19 9:462024/9/20 8:54

This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.

#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.

This article summarizes cases where the behavior of "tables becoming invisible to the reader during DDL execution" that was supposed to have been resolved in Aurora MySQL 3.04.2 has recurred.

I hope this helps in some way.

Table becomes invisible to readers during online DDL execution Recurring cases Reproduction verification in local environment Creating a replay table Insert approximately 1 million records Execute online DDL on the writer endpoint Execute SELECT on reader endpoint during online DDL execution Execute second online DDL on the writer endpoint Execute the third online DDL on the writer endpoint SELECT execution on reader endpoint during third online DDL execution What about other versions of Aurora MySQL?3.04.3 (MySQL8.0.28 compatible) → Reproduce 3.05.2 (compatible with MySQL 8.0.32) → Reproduce 3.06.1 (compatible with MySQL 8.0.34) → Reproduce 3.07.0 (compatible with MySQL 8.0.36) → Reproduce Conclusion

Table becomes invisible to readers during online DDL execution

I wrote about this in a previous blog post,

ALGORITHM=INPLACE

[Addendum] Pitfalls and Avoidance of Online DDL in Amazon Aurora MySQL Version 3 - CyberAgent SRG #ca_srg

This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters. The #SRG (Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS. This article was written by Ama

https://ca-srg.dev/c786e05ad11045308e1978ac863c8db3

This issue was resolved in Aurora MySQL 3.04.2 or 3.05 and above.

Recurring cases

The following article was posted on freee's blog on July 11, 2024.

Unexpected DDL behavior in Aurora 3.04.2 and Rails solutions - freee Developers Hub

Hello, I'm Shinta, a DBRE (Database Reliability Engineer). Today, I'd like to introduce some unexpected DDL behavior in Aurora 3.04.2. The trigger for this discovery was verification of the following issue in Aurora 3.04.1. (CyberA...

https://developers.freee.co.jp/entry/aurora-3042-ddl-problem

I will quote some of the reproduction conditions below.

1a: The table has not been accessed via a reader since the problematic DDL was executed on the table.
1b: The table has not been accessed via a reader since it was created
2: If either 1a or 1b is true, run the DDL in question on the table.
3: Access the table from the reader during DDL execution →
Table cannot be opened

It seems that if online DDL is executed on a table for which it has already been executed and there is no reader access to it, then the table will become invisible while it is being executed.