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

            Transition Time In Source Status Execution Times
            Julien Fritsch made transition -
            Open Confirmed
            108d 14h 55m 1
            Alexander Barkov made transition -
            Confirmed In Progress
            227d 23h 36m 1
            Alexander Barkov made transition -
            In Progress In Review
            56s 1
            Marko Mäkelä made transition -
            In Review Stalled
            11m 24s 1
            Alexander Barkov made transition -
            Stalled Closed
            4h 51m 1

            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.