| 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 | |
[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.
Description: Table data is lost after the engine is changed: mysql> SELECT * FROM (SELECT `col_double_key_signed` FROM table_10_undef_undef) AS t WHERE `col_double_key_signed`; +-----------------------+ | col_double_key_signed | +-----------------------+ | 1 | | 1 | | 1.009 | | 1.009 | | 17.1175 | | 44 | | 100 | +-----------------------+ 7 rows in set (0.00 sec) mysql> ALTER TABLE table_10_undef_undef ENGINE = MRG_MYISAM; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM (SELECT `col_double_key_signed` FROM table_10_undef_undef) AS t WHERE `col_double_key_signed`; Empty set (0.00 sec -- no rows in the table mysql> SELECT * FROM table_10_undef_undef; Empty set (0.00 sec) How to repeat: create table table_10_undef_undef ( `pk` int primary key, `col_bigint_undef_signed` bigint , `col_bigint_key_signed` bigint , `col_float_undef_signed` float , `col_float_key_signed` float , `col_double_undef_signed` double , `col_double_key_signed` double , `col_char(20)_undef_signed` char(20) , `col_char(20)_key_signed` char(20) , `col_varchar(20)_undef_signed` varchar(20) , `col_varchar(20)_key_signed` varchar(20) , `col_tinyint_undef_signed` tinyint , `col_tinyint_key_signed` tinyint , `col_smallint_undef_signed` smallint , `col_smallint_key_signed` smallint , key (`col_bigint_key_signed`), key (`col_float_key_signed`), key (`col_double_key_signed`), key (`col_char(20)_key_signed`), key (`col_varchar(20)_key_signed`), key (`col_tinyint_key_signed`), key (`col_smallint_key_signed`) ); insert into table_10_undef_undef values (0,-9.183,12.991,-0,-0,6,44,'c',"but",null,"have","but","I'll","yeah",null),(1,-1,0,12.991,100,100,1.009,null,null,'a',"her",null,"I'll",'b',null),(2,18,0,12.991,19,0,1.009,'p',null,'n',null,'b',"I'm",'v',"be"),(3,18.0266,-1,-9.183,3743,3659,null,"don't",null,null,'r','w',null,null,'j'),(4,-9.183,100,-9.183,-0,-0,0,"how",null,'a','e',null,'s',null,'r'),(5,-9.183,-0,22066,126,1.009,1,'b',"was",'d','d',null,"and",null,null),(6,0,1,-9.183,0,100,17.1175,'m',null,'c',"was",null,null,"can't",'i'),(7,-0,-1,-23573,-1,1.009,100,'i',"want",null,null,'c','n','w',null),(8,12.991,1,null,12.991,1.009,1,null,"in","out",'f',null,"here",null,'j'),(9,-1,12.991,1,null,100,0,"out","for",null,'v',null,"oh",null,null); SELECT * FROM (SELECT `col_double_key_signed` FROM table_10_undef_undef) AS t WHERE `col_double_key_signed`; ALTER TABLE table_10_undef_undef ENGINE = MRG_MYISAM; -- incorrect SELECT * FROM (SELECT `col_double_key_signed` FROM table_10_undef_undef) AS t WHERE `col_double_key_signed`; -- incorrect, rows are lost SELECT * FROM table_10_undef_undef; More information: mysql> show create table table_10_undef_undef\G *************************** 1. row *************************** Table: table_10_undef_undef Create Table: CREATE TABLE `table_10_undef_undef` ( `pk` int(11) NOT NULL, `col_bigint_undef_signed` bigint(20) DEFAULT NULL, `col_bigint_key_signed` bigint(20) DEFAULT NULL, `col_float_undef_signed` float DEFAULT NULL, `col_float_key_signed` float DEFAULT NULL, `col_double_undef_signed` double DEFAULT NULL, `col_double_key_signed` double DEFAULT NULL, `col_char(20)_undef_signed` char(20) DEFAULT NULL, `col_char(20)_key_signed` char(20) DEFAULT NULL, `col_varchar(20)_undef_signed` varchar(20) DEFAULT NULL, `col_varchar(20)_key_signed` varchar(20) DEFAULT NULL, `col_tinyint_undef_signed` tinyint(4) DEFAULT NULL, `col_tinyint_key_signed` tinyint(4) DEFAULT NULL, `col_smallint_undef_signed` smallint(6) DEFAULT NULL, `col_smallint_key_signed` smallint(6) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_bigint_key_signed` (`col_bigint_key_signed`), KEY `col_float_key_signed` (`col_float_key_signed`), KEY `col_double_key_signed` (`col_double_key_signed`), KEY `col_char(20)_key_signed` (`col_char(20)_key_signed`), KEY `col_varchar(20)_key_signed` (`col_varchar(20)_key_signed`), KEY `col_tinyint_key_signed` (`col_tinyint_key_signed`), KEY `col_smallint_key_signed` (`col_smallint_key_signed`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec)