Bug #100443 | Table data lost after changing the engine for a table | ||
---|---|---|---|
Submitted: | 6 Aug 2020 14:46 | Modified: | 7 Aug 2020 12:28 |
Reporter: | Yushan ZHANG | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Storage Engines | Severity: | S3 (Non-critical) |
Version: | 5.7, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[6 Aug 2020 14:46]
Yushan ZHANG
[7 Aug 2020 5:10]
Yushan ZHANG
I found a description in the documentation: ... To prevent inadvertent loss of data, ALTER TABLE cannot be used to change the storage engine of a table to MERGE or BLACKHOLE. ... However, with the setting `sql_mode = "NO_ENGINE_SUBSTITUTION"; `, I could still change the storage engine to MERGE (also MRG_MYISAM); mysql> show variables like "sql_mode"; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | sql_mode | NO_ENGINE_SUBSTITUTION | +---------------+------------------------+ 1 row in set (0.01 sec) mysql> ALTER TABLE table_10_undef_undef ENGINE = MERGE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE table_10_undef_undef ENGINE = MRG_MYISAM; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
[7 Aug 2020 12:28]
MySQL Verification Team
Hi Mr. ZHANG, Thank you for your bug report. I have repeated your test case on both 5.7.31 and (current) 8.0.22 and I have got the identical results: col_double_key_signed 1 1 1.009 1.009 17.1175 44 100 nothing nothing and .... col_double_key_signed 1 1 1.009 1.009 17.1175 44 100 nothing nothing Verified as reported.
[14 Aug 2020 13:46]
jingbo zhao
I have repeated this issues, and I have fixed. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: fix_alter_table_lost_data.txt (text/plain), 1.23 KiB.
[24 Aug 2020 17:16]
Omer Barnir
Hi Mr. Zhao Thanks for your contribution
[25 Aug 2020 11:59]
MySQL Verification Team
Thank you , Mr. Jingbo Zhao on the contribution of your fine patch.
[16 Jun 2021 7:59]
Fengchun Hua
When will this bug fixed? I didn't see it in github repo.
[16 Jun 2021 12:59]
MySQL Verification Team
Hi, We are the Verification Team and we do not have access to the scheduling of the bug fixes.