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

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.

      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

        Issue Links

          Activity

            OK to push after addressing my review comments.

            marko Marko Mäkelä added a comment - OK to push after addressing my review comments.
            bar Alexander Barkov added a comment - Here's an updated version: https://github.com/MariaDB/server/commit/14fdd571ad59c51d9ab00a6d6ec249afd1c70761

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

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

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

            bar Alexander Barkov added a comment - It appeared that this issues was fixed earlier. Added tests only.
            alex2 Alex added a comment -

            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

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

              bar Alexander Barkov
              alex2 Alex
              Votes:
              4 Vote for this issue
              Watchers:
              9 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.