About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
⚠️⚠️⚠️ STG SRG Portal ⚠️⚠️⚠️/Technical article/In some cases, the issue where the table becomes invisible to the reader during DDL execution, which was supposed to be resolved in Aurora MySQL 3.04.2, recurs.

In some cases, the issue where the table becomes invisible to the reader during DDL execution, which was supposed to be resolved in Aurora MySQL 3.04.2, recurs.

This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article summarizes cases where the "table becomes invisible to the reader during DDL execution" behavior, which was supposed to have been resolved in Aurora MySQL 3.04.2, recurs.
I hope this is of some help.
 

The table becomes invisible to the leader during online DDL execution.

I wrote about this in a previous blog post,
ALGORITHM=INPLACE
 
This issue was resolved in Aurora MySQL 3.04.2 or later, or 3.05 or later.

Cases of recurrence

The following article was posted on freee's blog on July 11, 2024.
 
I will quote a portion of the section regarding the reproduction conditions.
1a: The table in question has not been accessed via a reader since the DDL statement was executed on that table.
1b: The table has not been accessed via a reader since it was created.
2: Execute the DDL in question onto the table when either 1a or 1b is satisfied.
3: Access the table from the reader while DDL is being executed →
An issue occurred where the table could not be opened.
 
It seems that if you run the online DDL again on a table that has already had an online DDL executed on it, and the reader hasn't accessed it since, the table will become invisible during the execution.

Reproducibility verification in my local environment

Create a reproduction table

Insert approximately 1 million records randomly.

Execute online DDL on the writer endpoint.

 

SELECT statement executed on leader endpoint during online DDL execution

At this point, no problems occur, as confirmed in previous tests.
 

Second online DDL execution on the writer endpoint

After completion, immediately execute the third time without selecting from the leader.
 

Third online DDL execution on the writer endpoint

 

SELECT statement executed on the leader endpoint during the third online DDL execution.

The table has disappeared!
Of course, once the online DDL is complete, it will become visible again.
 
In other words, to safely execute online DDL in an existing environment,
  • Do not execute online DDL consecutively on the same table.
    • Either leave some time between readings or intentionally include a reading from the leader.
You need to be aware of this.

How about other versions of Aurora MySQL?

[Conclusion] This issue is reproducible in all versions.

3.04.3 (MySQL 8.0.28 compatible) → Reproducible

Although the Japanese version of the release notes has not yet been published, the latest minor version of the LTS, 3.04.3, was released on June 26, 2024.
This issue was also reproduced in this version.
 

3.05.2 (MySQL 8.0.32 compatible) → Reproducible

3.06.1 (MySQL 8.0.34 compatible) → Reproducible

 

3.07.0 (MySQL 8.0.36 compatible) → Reproducible

In conclusion

It was a sad story about how an issue that we thought had been resolved actually remained unresolved.
Thank you to freee for featuring this issue on your blog!
 
We have informed AWS about this issue, and I believe they are investigating it, so I hope they will resolve it in the future.
 
SRG is looking for new team members. If you are interested, please contact us here.