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

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

    XMLWordPrintable

    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

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            alex2 Alex
            Votes:
            4 Vote for this issue
            Watchers:
            7 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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