Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30959

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

Details

    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.

      Attachments

        1. results.txt
          6 kB
        2. test.sh
          0.7 kB
        3. test-duration.sh
          1 kB

        Issue Links

          Activity

            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?

            marko Marko Mäkelä added a comment - 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?
            alex2 Alex added a comment -

            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?

            alex2 Alex added a comment - 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?

            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.

            marko Marko Mäkelä added a comment - 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.

            People

              marko Marko Mäkelä
              alex2 Alex
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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