[MDEV-30959] Import with disabled keys corrupts meta-data like rows, indexes, ... Created: 2023-03-29  Updated: 2023-08-07  Resolved: 2023-08-07

Status: Closed
Project: MariaDB Server
Component/s: Backup, Data Manipulation - Insert, Storage Engine - InnoDB
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alex Assignee: Marko Mäkelä
Resolution: Incomplete Votes: 0
Labels: None
Environment:

CentOS 7


Attachments: Text File results.txt     File test-duration.sh     File test.sh    
Issue Links:
Relates
relates to MDEV-27214 Import with disabled keys corrupts me... Closed

 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.

Attached test-script to reproduce the problem and output.

Already reported at MDEV-27214, but closed because it "appeared that this problem duplicates MDEV-28327".
However, it the issue still exists with the latest 10.6-version 10.6.12.
Unfortunately, my request to reopen the ticket 4 month ago was ignored.



 Comments   
Comment by Marko Mäkelä [ 2023-03-29 ]

Thank you for the bug report. InnoDB has never supported ALTER TABLE…DISABLE KEYS or ALTER TABLE…ENABLE KEYS. If I understood the contents of the attachments correctly, the claimed problem is that the fix of MDEV-28327 does not work if these statements are being used, which appears to be the default in mariadb-dump a.k.a. mysqldump.
However, compared to our test file mysql-test/suite/innodb/t/insert_into_empty.test, test.sh is missing an important step of waiting that the background task that updates the InnoDB persistent statistics has been run:

--echo # Wait till statistics update after bulk insert operation
let $wait_condition= select n_rows > 0 from mysql.innodb_table_stats
where database_name='test' and table_name='t1';
source include/wait_condition.inc;
SELECT TABLE_ROWS, AVG_ROW_LENGTH>0 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';

Can you please add a similar wait to your test script and see what happens?

Comment by Alex [ 2023-03-29 ]

Thanks for the fast response. I modified the test-script, so that it now measures the duration when the statistics are updated: test-duration.sh
It takes some time to update the statistics independently if --skip-disable-keys is set or not. Maybe SHOW TABLE STATUS is waiting if --skip-disable-keys is set, because I did not get a single failure in my previous tests.

However, this was just a setted up test-case. In reality the problem was that after an import of a bigger database the indexes are not available anymore which leaded to long running or locking queries. How do I check if the indexes are correctly generated?

Comment by Marko Mäkelä [ 2023-07-06 ]

InnoDB should always create all indexes correctly. Do they exist in the output of SHOW CREATE TABLE and does CHECK TABLE report any inconsistency?

Note: a possibly related bug MDEV-28327 was fixed in MariaDB Server 10.6.11.

Generated at Thu Feb 08 10:20:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.