Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Data will be lost when the table structure is rebuilt.

Description

sql:
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;

load data infile 'data.sql' into table storage_acid_dml_table_000_2;

drop table if exists storage_acid_dml_table_203;
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;

insert into storage_acid_dml_table_203 select * from storage_acid_dml_table_000_2;
delete from storage_acid_dml_table_203 where C_ID%2=0;

  1. if sleep 1s, data will not be lost.

  2. select sleep(1);
    select count from storage_acid_dml_table_203;
    alter table storage_acid_dml_table_203 engine=InnoDB;
    select count from storage_acid_dml_table_203;

Environment

None

Attachments

1
  • 25 Apr 2023, 02:15 AM

Activity

Show:

Dmitry Lenev December 5, 2024 at 1:16 PM

Hello!

From the analysis It looks like a duplicate of https://perconadev.atlassian.net/browse/PS-9144 which was fixed in Percona Server 8.0.39-30 and 8.4.2-2 releases. So I am closing this report as a duplicate.

YuanyueZheng May 17, 2023 at 2:34 AM

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.

Aaditya Dubey May 4, 2023 at 12:48 PM

Hi ,

Thank you for the repeat
Verified as described.

Tested on Following version of MySQL 1. PS 8.0.28 2. PS 8.0.32 [Latest] 3. Upstream 8.0.32 4. Upstream 8.0.33 This issue is repeating on all above mentioned versions: mysql [localhost:8033] {msandbox} ((none)) > source ~/sandboxes/msb_up8_0_32/PS-8737.sql Query OK, 2 rows affected (0.04 sec) Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected, 1 warning (0.05 sec) Query OK, 1000 rows affected (0.41 sec) Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected, 1 warning (0.03 sec) Query OK, 1000 rows affected (0.49 sec) Records: 1000  Duplicates: 0  Warnings: 0 Query OK, 500 rows affected (0.03 sec) +----------+ | count(*) | +----------+ |      500 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (1.40 sec) Records: 0  Duplicates: 0  Warnings: 0 +----------+ | count(*) | +----------+ |      499 | +----------+ 1 row in set (0.02 sec)

sending the concern to engineering for further review and updates.

Duplicate

Smart Checklist

Created April 25, 2023 at 2:16 AM
Updated December 5, 2024 at 1:16 PM
Resolved December 5, 2024 at 1:16 PM