[MDEV-27214] Import with disabled keys corrupts meta-data like rows, indexes, ... - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Duplicate
Affects Version/s: 10.6.5, 10.6.7, 10.6.8, 10.6.9, 10.6.10
Fix Version/s: N/A
Component/s: Backup, Data Manipulation - Insert, Storage Engine - InnoDB
Labels:
None
Environment:
CentOS 7

Description

When importing a dump created by mysqldump with default options meta-data like Rows and Avg_row_length are 0 and Data_length is 16 KB and indexes are not available anymore which leads to long running or locking queries. Only after executing an optimize table afterwards corrects this or whem using the option skip-disable-keys for creating dump.

In the following example the Data_length is always 16 KB, because of the small table:

Test Table
mysql -e 'CREATE DATABASE testdb;'
mysql testdb -e 'CREATE TABLE testtable ( id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB;'
mysql testdb -e 'INSERT INTO testtable VALUES (1,2),(2,3),(3,4);'

Import with default dump
mysqldump testdb testtable > dump1.sql
mysql -e 'DROP DATABASE IF EXISTS testdb;CREATE DATABASE testdb;'
mysql testdb < dump1.sql
mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G'

Rows: 0
Avg_row_length: 0

Recreate Table
mysql testdb -e 'OPTIMIZE TABLE testtable;'
mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G'

Rows: 3
Avg_row_length: 5461

Import with dump without disabled keys
mysqldump --skip-disable-keys testdb testtable > dump2.sql
mysql -e 'DROP DATABASE IF EXISTS testdb;CREATE DATABASE testdb;'
mysql testdb < dump2.sql
mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G'

Rows: 3
Avg_row_length: 5461

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

metadata-test.txt
3 kB
2022-10-28 12:43
metadata-test2.txt
2 kB
2022-11-17 15:12

Issue Links

duplicates

MDEV-28327 InnoDB persistent statistics fail to update after bulk insert

Closed

relates to

MDEV-515 innodb bulk insert

Closed

MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time

Closed

MDEV-28327 InnoDB persistent statistics fail to update after bulk insert

Closed

MDEV-30959 Import with disabled keys corrupts meta-data like rows, indexes, ...

Closed

Activity

Ascending order - Click to sort in descending order

Alex added a comment - 2022-03-01 10:42

Same behaviour on 10.6.7

Alex added a comment - 2022-03-01 10:42 Same behaviour on 10.6.7

Alex added a comment - 2022-10-28 12:44 - edited

Reproduceable also with 10.6.8, 10.6.9 and latest 10.6.10. However, the behavior is different: After a restart it is working for some time, once it fails at 10.6.7 every further request will fail, but with 10.6.8, 10.6.9, 10.6.10 further requests will work again but it will fail again afterwards.

Tested with the following shell-script:

#!/bin/bash

systemctl restart mariadb

mysql -V

while :

do

  mysql -e 'DROP DATABASE IF EXISTS testdb;CREATE DATABASE testdb;'

  mysql testdb < dump1.sql

  echo `date` - `mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G' | grep Rows`

  sleep 10

done

Attached output of 10.6.7, .8, .9 and latest .10: metadata-test.txt

Alex added a comment - 2022-10-28 12:44 - edited Reproduceable also with 10.6.8, 10.6.9 and latest 10.6.10. However, the behavior is different: After a restart it is working for some time, once it fails at 10.6.7 every further request will fail, but with 10.6.8, 10.6.9, 10.6.10 further requests will work again but it will fail again afterwards. Tested with the following shell-script: #!/bin/bash systemctl restart mariadb mysql -V while : do mysql -e 'DROP DATABASE IF EXISTS testdb;CREATE DATABASE testdb;' mysql testdb < dump1.sql echo ` date ` - `mysql testdb -e 'SHOW TABLE STATUS LIKE "testtable"\G' | grep Rows` sleep 10 done Attached output of 10.6.7, .8, .9 and latest .10: metadata-test.txt

Alexander Barkov added a comment - 2022-11-11 06:14 - edited

A similar problem is repeatable with 10.7.6:

The row statistics is empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 0:

SET UNIQUE_CHECKS=0;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

  `id` int(11) NOT NULL,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `t1` VALUES (1,2),(2,3),(3,4);

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';

+------------+----------------+

| TABLE_ROWS | AVG_ROW_LENGTH |

+------------+----------------+

|          0 |              0 |

+------------+----------------+

The row statistics is not empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 1:

SET UNIQUE_CHECKS=1;

SET FOREIGN_KEY_CHECKS=1;

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

  `id` int(11) NOT NULL,

  `a` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `t1` VALUES (1,2),(2,3),(3,4);

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';

+------------+----------------+

| TABLE_ROWS | AVG_ROW_LENGTH |

+------------+----------------+

|          3 |           5461 |

+------------+----------------+

Alexander Barkov added a comment - 2022-11-11 06:14 - edited A similar problem is repeatable with 10.7.6: The row statistics is empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 0: SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int (11) NOT NULL , `a` int (11) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE =latin1_swedish_ci; INSERT INTO `t1` VALUES (1,2),(2,3),(3,4); SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 't1' AND TABLE_SCHEMA= 'test' ; +------------+----------------+ | TABLE_ROWS | AVG_ROW_LENGTH | +------------+----------------+ | 0 | 0 | +------------+----------------+ The row statistics is not empty if I set UNIQUE_CHECKS and FOREIGN_KEY_CHECKS to 1: SET UNIQUE_CHECKS=1; SET FOREIGN_KEY_CHECKS=1; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int (11) NOT NULL , `a` int (11) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE =latin1_swedish_ci; INSERT INTO `t1` VALUES (1,2),(2,3),(3,4); SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 't1' AND TABLE_SCHEMA= 'test' ; +------------+----------------+ | TABLE_ROWS | AVG_ROW_LENGTH | +------------+----------------+ | 3 | 5461 | +------------+----------------+

Alexander Barkov added a comment - 2022-11-11 07:19 - edited

Repeatable with 10.6.10 and 10.7.6 with the following MTR test:

--source include/have_innodb.inc

CREATE DATABASE db1;

CREATE TABLE db1.t1 (id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB;

INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4);

--let $file = $MYSQLTEST_VARDIR/tmp/dump.sql

--exec $MYSQL_DUMP db1 t1 >$file

DROP DATABASE IF EXISTS db1;

CREATE DATABASE db1;

--exec $MYSQL db1  < $file

--remove_file $file

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';

OPTIMIZE TABLE db1.t1;

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';

DROP DATABASE db1;

This is the output:

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

CREATE DATABASE db1;

CREATE TABLE db1.t1 (id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB;

INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4);

DROP DATABASE IF EXISTS db1;

CREATE DATABASE db1;

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';

TABLE_ROWS	AVG_ROW_LENGTH

0	0

OPTIMIZE TABLE db1.t1;

Table	Op	Msg_type	Msg_text

db1.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead

db1.t1	optimize	status	OK

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1';

TABLE_ROWS	AVG_ROW_LENGTH

3	5461

DROP DATABASE db1;

main.AAA 'innodb'                        [ pass ]     82

Alexander Barkov added a comment - 2022-11-11 07:19 - edited Repeatable with 10.6.10 and 10.7.6 with the following MTR test: --source include/have_innodb.inc CREATE DATABASE db1; CREATE TABLE db1.t1 (id int , a int , PRIMARY KEY (id)) ENGINE=InnoDB; INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4); --let $file = $MYSQLTEST_VARDIR/tmp/dump.sql --exec $MYSQL_DUMP db1 t1 >$file DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; --exec $MYSQL db1 < $file --remove_file $file SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 't1' AND TABLE_SCHEMA= 'db1' ; OPTIMIZE TABLE db1.t1; SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 't1' AND TABLE_SCHEMA= 'db1' ; DROP DATABASE db1; This is the output: worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 CREATE DATABASE db1; CREATE TABLE db1.t1 (id int, a int,PRIMARY KEY (id)) ENGINE=InnoDB; INSERT INTO db1.t1 VALUES (1,2),(2,3),(3,4); DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1'; TABLE_ROWS AVG_ROW_LENGTH 0 0 OPTIMIZE TABLE db1.t1; Table Op Msg_type Msg_text db1.t1 optimize note Table does not support optimize, doing recreate + analyze instead db1.t1 optimize status OK SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='db1'; TABLE_ROWS AVG_ROW_LENGTH 3 5461 DROP DATABASE db1; main.AAA 'innodb' [ pass ] 82

Alexander Barkov added a comment - 2022-11-11 08:33 - edited

The problem is not repeatable in 10.6.11 after this patch:

commit f70960c3482073d2edd4a809899adee56c94ec24 (HEAD -> 10.6)

Author: Thirunarayanan Balathandayuthapani <thiru@mariadb.com>

Date:   Tue Oct 25 12:12:33 2022 +0530

    MDEV-28327      InnoDB persistent statistics fail to update after bulk insert

So it appeared that this problem duplicates ~~MDEV-28327~~.

Alexander Barkov added a comment - 2022-11-11 08:33 - edited The problem is not repeatable in 10.6.11 after this patch: commit f70960c3482073d2edd4a809899adee56c94ec24 (HEAD -> 10.6) Author: Thirunarayanan Balathandayuthapani <thiru@mariadb.com> Date: Tue Oct 25 12:12:33 2022 +0530 MDEV-28327 InnoDB persistent statistics fail to update after bulk insert So it appeared that this problem duplicates MDEV-28327 .

Alexander Barkov added a comment - 2022-11-11 08:34

Hi marko, can you please review a patch?

https://github.com/MariaDB/server/commit/aa1a9e21895b675c01133a74493c3abaa3b6dee1

Thanks.

Alexander Barkov added a comment - 2022-11-11 08:34 Hi marko , can you please review a patch? https://github.com/MariaDB/server/commit/aa1a9e21895b675c01133a74493c3abaa3b6dee1 Thanks.

Marko Mäkelä added a comment - 2022-11-11 08:45

OK to push after addressing my review comments.

Marko Mäkelä added a comment - 2022-11-11 08:45 OK to push after addressing my review comments.

Alexander Barkov added a comment - 2022-11-11 09:12

Here's an updated version:
https://github.com/MariaDB/server/commit/14fdd571ad59c51d9ab00a6d6ec249afd1c70761

Alexander Barkov added a comment - 2022-11-11 09:12 Here's an updated version: https://github.com/MariaDB/server/commit/14fdd571ad59c51d9ab00a6d6ec249afd1c70761

Marko Mäkelä added a comment - 2022-11-11 09:18

Thank you! I think that it was good to extend the test to cover all innodb_page_size.

Marko Mäkelä added a comment - 2022-11-11 09:18 Thank you! I think that it was good to extend the test to cover all innodb_page_size .

Alexander Barkov added a comment - 2022-11-11 13:37

It appeared that this issues was fixed earlier. Added tests only.

Alexander Barkov added a comment - 2022-11-11 13:37 It appeared that this issues was fixed earlier. Added tests only.

Alex added a comment - 2022-11-17 15:13

It looks like ~~MDEV-28327~~ is very similar to this issue. However, I can reproduce this issue also with the latest version 10.6.11.
I have also tried the test in ~~MDEV-28327~~ which is reproduceable in 10.6.10 but not in 10.6.11, as it has been fixed.

@bar You wrote that it is not reproduceable with 10.6.11, have you tried my shell-script?

Attached is a new test-report with 10.6.10 and 10.6.11, where it can be seen that the issue is still existing.
So can you please reopen it.

metadata-test2.txt

Alex added a comment - 2022-11-17 15:13 It looks like MDEV-28327 is very similar to this issue. However, I can reproduce this issue also with the latest version 10.6.11. I have also tried the test in MDEV-28327 which is reproduceable in 10.6.10 but not in 10.6.11, as it has been fixed. @bar You wrote that it is not reproduceable with 10.6.11, have you tried my shell-script? Attached is a new test-report with 10.6.10 and 10.6.11, where it can be seen that the issue is still existing. So can you please reopen it. metadata-test2.txt

People

Assignee:: Alexander Barkov

Reporter:: Alex

Votes:: 4 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2021-12-09 18:02

Updated:: 2024-07-07 21:01

Resolved:: 2022-11-11 13:37

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration