| Bug #110706 | Data will be lost when the table structure is rebuilt. | ||
|---|---|---|---|
| Submitted: | 17 Apr 2023 2:56 | Modified: | 17 May 2023 2:31 |
| Reporter: | yuanyue Zheng | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 8.0.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[17 Apr 2023 2:56]
yuanyue Zheng
[17 Apr 2023 3:02]
yuanyue Zheng
mtr data
Attachment: mysql-bug-data-110706.zip (application/x-zip-compressed, text), 29.72 KiB.
[17 Apr 2023 3:02]
yuanyue Zheng
mtr data
Attachment: mysql-bug-data-110706.zip (application/x-zip-compressed, text), 29.72 KiB.
[18 Apr 2023 11:25]
MySQL Verification Team
Hello yuanyue Zheng, Thank you for the report and feedback. regards, umesh
[18 Apr 2023 11:26]
MySQL Verification Team
- 8.0.32
rm -rf 110706/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/110706 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/110706 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/110706/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 &
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> drop table if exists storage_acid_dml_table_000_2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `storage_acid_dml_table_000_2` (
-> `C_ID` int NOT NULL,
-> `C_D_ID` int NOT NULL,
-> `C_W_ID` bigint NOT NULL,
-> `C_DOUBLE` double NOT NULL,
-> `C_DECIMAL` decimal(10,0) NOT NULL,
-> `C_FIRST` varchar(64) NOT NULL,
-> `C_MIDDLE` char(2) DEFAULT NULL,
-> `C_LAST` varchar(64) NOT NULL,
-> `C_STREET_1` varchar(20) NOT NULL,
-> `C_STREET_2` varchar(20) DEFAULT NULL,
-> `C_CITY` varchar(64) NOT NULL,
-> `C_STATE` char(2) NOT NULL,
-> `C_ZIP` char(9) NOT NULL,
-> `C_PHONE` char(16) NOT NULL,
-> `C_SINCE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `C_CREDIT` char(2) NOT NULL,
-> `C_CREDIT_LIM` decimal(12,2) DEFAULT NULL,
-> `C_DISCOUNT` decimal(4,4) DEFAULT NULL,
-> `C_BALANCE` decimal(12,2) DEFAULT NULL,
-> `C_YTD_PAYMENT` double NOT NULL,
-> `C_PAYMENT_CNT` float NOT NULL,
-> `C_DELIVERY_CNT` tinyint(1) NOT NULL,
-> `C_END` date NOT NULL,
-> `C_VCHAR` varchar(9000) DEFAULT NULL,
-> `C_DATA` text,
-> `C_TEXT` blob,
-> `C_TINYTEXT` tinytext,
-> `C_MEDIUMBLOB` mediumblob,
-> `C_LONGBLOB` longblob,
-> PRIMARY KEY (`C_ID`,`C_D_ID`,`C_W_ID`),
-> UNIQUE KEY `storage_acid_dml_index_000_2_1` (`C_ID`,`C_D_ID`),
-> KEY `storage_acid_dml_index_000_2_2` (`C_ID`),
-> KEY `storage_acid_dml_index_000_2_3` (`C_CITY`),
-> KEY `storage_acid_dml_index_000_2_4` (`C_FIRST`,`C_STATE`),
-> KEY `storage_acid_dml_index_000_2_5` (`C_ID`,`C_D_ID`,`C_MIDDLE`),
-> KEY `storage_acid_dml_index_000_2_6` (`C_ID`,`C_D_ID`,`C_MIDDLE`,`C_STREET_1`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> load data infile '/export/home/tmp/ushastry/mysql-8.0.32/data.sql' into table storage_acid_dml_table_000_2;
Query OK, 1000 rows affected (0.35 sec)
Records: 1000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> drop table if exists storage_acid_dml_table_203;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `storage_acid_dml_table_203` (
-> `C_ID` int DEFAULT NULL,
-> `C_D_ID` int NOT NULL,
-> `C_W_ID` bigint NOT NULL,
-> `C_DOUBLE` double NOT NULL,
-> `C_DECIMAL` decimal(10,0) NOT NULL,
-> `C_FIRST` varchar(64) NOT NULL,
-> `C_MIDDLE` char(2) DEFAULT NULL,
-> `C_LAST` varchar(64) NOT NULL,
-> `C_STREET_1` varchar(20) NOT NULL,
-> `C_STREET_2` varchar(20) DEFAULT NULL,
-> `C_CITY` varchar(64) NOT NULL,
-> `C_STATE` char(2) NOT NULL,
-> `C_ZIP` char(9) NOT NULL,
-> `C_PHONE` char(16) NOT NULL,
-> `C_SINCE` timestamp NULL DEFAULT NULL,
-> `C_CREDIT` char(2) NOT NULL,
-> `C_CREDIT_LIM` decimal(12,2) DEFAULT NULL,
-> `C_DISCOUNT` decimal(4,4) DEFAULT NULL,
-> `C_BALANCE` decimal(12,2) DEFAULT NULL,
-> `C_YTD_PAYMENT` double NOT NULL,
-> `C_PAYMENT_CNT` float NOT NULL,
-> `C_DELIVERY_CNT` tinyint(1) NOT NULL,
-> `C_END` date NOT NULL,
-> `C_VCHAR` varchar(9000) DEFAULT NULL,
-> `C_DATA` text,
-> `C_TEXT` blob,
-> `C_TINYTEXT` tinytext,
-> `C_MEDIUMBLOB` mediumblob,
-> `C_LONGBLOB` longblob
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>
mysql> insert into storage_acid_dml_table_203 select * from storage_acid_dml_table_000_2;
Query OK, 1000 rows affected (0.28 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> delete from storage_acid_dml_table_203 where C_ID%2=0;
Query OK, 500 rows affected (0.03 sec)
mysql> -- if sleep 1s, data will be lost.
mysql> -- select sleep(1);
mysql> select count(*) from storage_acid_dml_table_203;
+----------+
| count(*) |
+----------+
| 500 |
+----------+
1 row in set (0.00 sec)
mysql> alter table storage_acid_dml_table_203 engine=InnoDB;
Query OK, 0 rows affected (1.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from storage_acid_dml_table_203;
+----------+
| count(*) |
+----------+
| 499 |
+----------+
1 row in set (0.00 sec)
[24 Apr 2023 11:46]
zhijun long
Are there plans to fix it? This is a serious bug.
[17 May 2023 2:31]
yuanyue Zheng
It seems like: MySQL 8.0.27 adds the parallel DDL feature and innodb_ddl_buffer_size variable defines the maximum buffer size for DDL operations. During DDL operations, if the data size exceeds innodb_ddl_buffer_size, key_buffer needs to be flushed to disks when traversing the table data, and persistent cursor will store the cursor position to the previous user record on the page. If the record is marked as deleted, and purge thread reclaims the space, persistent cursor will restore to the wrong record and cause data inconsistency.
