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

            alex2 Alex created issue -
            alex2 Alex made changes -
            Field Original Value New Value
            Affects Version/s 10.6.7 [ 26812 ]
            Ali.maria Alasdair Haswell made changes -
            Labels CS0387305
            julien.fritsch Julien Fritsch made changes -
            Labels CS0387305
            julien.fritsch Julien Fritsch made changes -
            Assignee Alexander Barkov [ bar ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.6 [ 24028 ]
            julien.fritsch Julien Fritsch made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            alex2 Alex made changes -
            Attachment metadata-test.txt [ 66238 ]
            alex2 Alex made changes -
            Affects Version/s 10.6.10 [ 28407 ]
            Affects Version/s 10.6.9 [ 27507 ]
            Affects Version/s 10.6.8 [ 27506 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Alexander Barkov [ bar ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2022-11-11 13:37:44.0 2022-11-11 13:37:44.542
            bar Alexander Barkov made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Duplicate [ 3 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            alex2 Alex made changes -
            Attachment metadata-test-1.txt [ 66907 ]
            alex2 Alex made changes -
            Attachment metadata-test2.txt [ 66908 ]
            alex2 Alex made changes -
            Attachment metadata-test-1.txt [ 66907 ]
            alex2 Alex made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 199393

            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.