Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1912

LP #989253: mysqldump --innodb-optimize-keys --no-data results in no keys

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Bill Karwin last update 22-05-2013 11:08:58

      Tested with mysqldump from 5.1.61-rel13.2-log Percona Server (GPL), 13.2, Revision 430
      Linux: CentOS release 6.2 2.6.32-220.13.1.el6.x86_64

      When using mysqldump --innodb-optimize-keys --no-data, all secondary key definitions are lost.

      Steps to reproduce:

      1. Create an ordinary table with a secondary key and one sample row.

      mysql> create table test.foo (id serial primary key, c int, key(c));
      mysql> insert into test.foo values (1, 1234);

      2. Dump table with standard key optimization.
      The keys are defined inside the CREATE TABLE statement.
      There is a block of statements for "Dumping data for table `foo`", including LOCK TABLES and INSERT and UNLOCK TABLES.

      $ mysqldump test foo > test-foo-1.sql

      3. Dump table with standard key optimization, but no data.
      The keys are defined inside the CREATE TABLE statement.
      The block for "Dumping data" is omitted. No LOCK TABLES, no INSERTs, no UNLOCK TABLES.

      $ mysqldump --no-data test foo > test-foo-2.sql

      4. Dump table with Percona key optimization, with data.
      The secondary key definition is deferred to the "Dumping data" section, between INSERT and UNLOCK TABLES.

      $ mysqldump --innodb-optimize-keys test foo > test-foo-3.sql

      5. Dump table with Percona key optimization, with no data.
      All the secondary key definitions are missing, because they are part of the omitted block for "Dumping data".

      $ mysqldump --innodb-optimize-keys --no-data test foo > test-foo-4.sql

      You could argue that there is no reason to separate secondary key definition when using --no-data, because it takes virtually no time to create the indexes on empty tables anyway.

      But if I want to create empty tables, then use mysqlimport to load data from flat files, then create secondary indexes on the tables to benefit from fast index creation. It would be useful if mysqldump --innodb-optimize-keys --no-data were to output the ALTER TABLE ADD KEY statements, so I could grep them out and run them after importing the data.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist