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:
None 
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
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)
[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.