MySQL Bugs: #86355: alter table that adds generated column and index crashes and deletes table

Bug #86355	alter table that adds generated column and index crashes and deletes table
Submitted:	17 May 2017 13:02	Modified:	17 Jun 2017 17:00
Reporter:	Marlin Forbes	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.7.16	OS:	Red Hat (RDS backed so the OS is a guess)
Assigned to:		CPU Architecture:	Any
Tags:	aws, RDS

Description:
Firstly let me start off by saying I realize this is a bug report for an older version of MySQL. However, that being said, this specific version is installed across all AWS RDS tiers as the default, so it's definitely a critical issue.

One of our SQL migrations repeatably caused a segfault in an RDS instance of MySQL Server 5.7.16, which resulted in the deletion of the table being operated on.

We experienced this error on the client side once it hit the relevant SQL:

SQLSTATE[HY000] [2002] Connection refused

And on the server-side this logging occurred:

https://pastebin.co.za/5631383682678784

And finally (and fatally), the 'users' table disappeared. The MySQL administrator still lists the table as present, but you cannot select from it or alter it without experiencing this error:

Error Code: 1146: Table 'dbname.users' doesn't exist

How to repeat:
1. Create a table called 'users' with a nullable JSON column called 'properties'.
2. Add some random data into the column (including a property for quantity with a mixture of INT and NULL), ensuring that you have a sizeable volume of records. Our table has around 400k records with varying data in that column.
3. Try to execute the following SQL:

ALTER TABLE users
ADD COLUMN quantity INT GENERATED ALWAYS AS (properties->"$.quantity"),
ADD INDEX users_quantity_idx(quantity)

Suggested fix:
ALTER TABLE users ADD COLUMN quantity INT GENERATED ALWAYS AS (properties->"$.quantity");
ALTER TABLE users ADD INDEX users_quantity_idx(quantity);

Splitting the ALTER into individual statements works, hinting at a race condition between the column and the index creation within one statement.

Thank you for the bug report. Please try current version released 5.7.18, if still the issue happens provide a complete test case not just describing it (create table, insert data, query). Thanks.

The issue does not occur with 5.7.18, so something fixed it recently.

Sorry, a clarification is needed. I have not experienced this locally on my machine with 5.7.18.

However, I also cannot duplicate this bug using 5.7.16 locally either, it only happens on RDS.

So it is entirely possible that this bug could still exist and will exhibit on RDS with most recent version as well. This is not possible to prove currently with RDS stuck at 5.7.16.

Thank you for the feedback. Anyway would be nice you provide the complete test case so we could try too, also to have the my.cnf where it crashes could help to repeat. Thanks.

I will have a look at creating a scriptable test case, it will take a little though.

I have now seen that the latest version of RDS on AWS is 5.6.17, not 5.6.16 as I thought.

Please reopen this bug report when the script test case is ready. Thanks in advance.

Tested this issue against the newer RDS version using 5.7.17. Same issue...

https://pastebin.co.za/5729201025974272

Waiting for script test case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".