#4487 (ok 4.2.8) Export of partitioned table does not import

4.2.7
fixed
Marc Delisle
None
1
2014-08-31
2014-07-10
Bill Karwin
No

phpMyAdmin cannot export and import a table with partitioning and an auto-increment key.

Create a simple table with partitioning.

CREATE TABLE test.test ( id INT AUTO_INCREMENT PRIMARY KEY ) PARTITION BY HASH(id);
INSERT INTO test.test VALUES (326);

Export test database. See that the DDL is formatted with invalid syntax.

CREATE TABLE IF NOT EXISTS `test` (
 `id` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (id) */ AUTO_INCREMENT=327 ;

ALTER TABLE `test`
 ADD PRIMARY KEY (`id`);

Try to import, and get the following error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT=327' at line 14

The same error happens if you try to import by using the mysql client.

Explanation:

The AUTO_INCREMENT=327 is a table option, and all table options must appear before the PARTITION clause.

Discussion

  • Bill Karwin
    Bill Karwin
    2014-07-10

    Tested with phpMyAdmin 4.2.5.

    Apologies for not marking that information in the initial question, I wasn't sure if "milestone" was the version that exhibits the bug, or if developers set the milestone after they triage the bug to a target release.

     
  • Marc Delisle
    Marc Delisle
    2014-07-16

    • Group: 3.3.7 --> 4.2.5
     
  • Smita Kumari
    Smita Kumari
    2014-08-21

    Hi Marc

    During export, to generate the create schema of the table, we do "SHOW CREATE" query and from the resultant create statement, we remove the AUTO_INCREMENT=#old_val# because of outdated number from DRIZZLE and then append the AUTO_INCREMENT=#new_val# in the end of create statement. This issue is because that itself as AUTO_INCREMENT is being appended in the end.
    So I suggest, what if instead of removing AUTO_INCREMENT=#old_val# first and then appending AUTO_INCREMENT=#new_val# in create statement, we just replace the value of AUTO_INCREMENT, that is just replace #old_val# with "#new_val#" at original place in create statement from SHOW CREATE ??

     
  • Marc Delisle
    Marc Delisle
    2014-08-21

    Hi Smita,
    If I understand your intention correctly, I don't believe this would work because we intentionally moved the ALTER TABLE x ADD PRIMARY KEY lower in the export file. Without a PK, we cannot set the auto increment current value.

    Note that according to my tests, the AUTO_INCREMENT=x is still there in the exported CREATE TABLE statement and it does not belong there, as we have a specific section to set it.

     
  • Smita Kumari
    Smita Kumari
    2014-08-21

    No I didn't mean that. let me try to explain:
    If Show create of table gives the result something like:

    CREATE TABLE `test` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=327 DEFAULT CHARSET=latin1
    

    then in addition to removing PRIMARY KEY (id) and "AUTO_INCREMENT" from the line "id int(11) NOT NULL AUTO_INCREMENT" we also remove "AUTO_INCREMENT=327" and again we append AUTO_INCREMENT=327 as value (327 here) might not be correct for DRIZZLE.
    Removing PRIMARY KEY (id) and "AUTO_INCREMENT" from the line "id int(11) NOT NULL AUTO_INCREMENT" is perfectly fine.
    What I meant is instead of removing "AUTO_INCREMENT=327" and again appending in the end to have the updated value for AUTO_INCREMENT(here 327), why not replace the the value 327 with new value (with updated one) in place.
    From the source code:
    the follwing block is removing "AUTO_INCREMENT=327"

    $schema_create = preg_replace(
            '/AUTO_INCREMENT\s*=\s*([0-9])+/',
            '',
            $schema_create
        );
    

    and the next line

    $schema_create .= ($compat != 'MSSQL') ? $auto_increment : '';
    

    we are appending the AUTO_INCREMENT=## in the end of the create statement.
    So my point is instead of removing AUTO_INCREMENT=327, replace just 327 with new value. Don't remove complete string "AUTO_INCREMENT=327" first and then append again in end.

    Hope it is clear :-)

     
    Last edit: Smita Kumari 2014-08-21
  • Marc Delisle
    Marc Delisle
    2014-08-22

    Smita,
    do you mean generating this in export? Here, 3000 is the new value.
    Well, try it: it does not work.

    CREATE TABLE IF NOT EXISTS test (
    id int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3000 ;

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table test
    --
    ALTER TABLE test
    ADD PRIMARY KEY (id);

    --
    -- AUTO_INCREMENT for dumped tables
    --

    --
    -- AUTO_INCREMENT for table test
    --
    ALTER TABLE test
    MODIFY id int(11) NOT NULL AUTO_INCREMENT;

     
  • Bill Karwin
    Bill Karwin
    2014-08-22

    One can declare an AUTO_INCREMENT for any column that is part of a KEY (that is, an index). It doesn't have to be a PRIMARY KEY or UNIQUE KEY, but that's more common.

    One can set the AUTO_INCREMENT=3000 table option even if there is no AUTO_INCREMENT column. The table just ignores it. Subsequently, SHOW CREATE TABLE does not show any AUTO_INCREMENT table option.

    One can also declare a table with partitioning before altering the table to add a PRIMARY KEY constraint. In other words, the following is fine:

    CREATE TABLE IF NOT EXISTS test ( id int(11) NOT NULL )
    ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3000 PARTITION BY HASH(id);
    ALTER TABLE test ADD PRIMARY KEY (id);

    The cause of the error is the position of the AUTO_INCREMENT=3000 in the syntax after PMA modifies the statement. It is not legal syntax to put a table option after the PARTITION clause.

    CREATE TABLE IF NOT EXISTS test ( id int(11) NOT NULL )
    ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY HASH(id) AUTO_INCREMENT=3000; / ERROR /

    If the PMA code were to move the AUTO_INCREMENT table option into the table options section, but still before the PARTITION keyword, that would fix this issue.

     
  • Bill Karwin
    Bill Karwin
    2014-08-22

    Another solution could be to use the SHOW CREATE TABLE as-is, even though it produces the wrong number for Drizzle. Then subsequently ALTER TABLE to correct the auto-increment number for Drizzle.

    CREATE TABLE IF NOT EXISTS test ( id int(11) NOT NULL PRIMARY KEY)
    ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 PARTITION BY HASH(id);
    ALTER TABLE test AUTO_INCREMENT=327;

     
  • Smita Kumari
    Smita Kumari
    2014-08-27

    Thanks Bill for the clarifications.:-)

    Hi Marc
    Since I was not able to explain clearly my approach here so I have opened a pull request with the fix I was suggesting, here is it https://github.com/phpmyadmin/phpmyadmin/pull/1344 . I have tested the possible scenarios and it works for me if I'm not missing something. Please have a look. and Sorry for the delay.

     
    Last edit: Smita Kumari 2014-08-27
  • Marc Delisle
    Marc Delisle
    2014-08-27

    • summary: Export of partitioned table does not import --> (ok 4.2.8) Export of partitioned table does not import
    • status: open --> resolved
    • assigned_to: Marc Delisle
    • Group: 4.2.5 --> 4.2.7
    • Priority: 5 --> 1
     
  • Marc Delisle
    Marc Delisle
    2014-08-27

    Smita: pull request accepted, thanks.

    Bill: a confirmation would be appreciated.

     
  • Bill Karwin
    Bill Karwin
    2014-08-27

    I tested Smita's patch, and it has improved. But unfortunately it still outputs invalid syntax if the table has zero rows, because SHOW CREATE TABLE doesn't have an AUTO_INCREMENT=# table option for a table with zero rows.

     
  • Marc Delisle
    Marc Delisle
    2014-08-27

    • summary: (ok 4.2.8) Export of partitioned table does not import --> Export of partitioned table does not import
    • status: resolved --> open
    • Priority: 1 --> 5
     
  • Marc Delisle
    Marc Delisle
    2014-08-28

    Thanks Bill for the feedback. I have merged another patch from Smita and this time, all cases look covered.

     
  • Marc Delisle
    Marc Delisle
    2014-08-28

    • summary: Export of partitioned table does not import --> (ok 4.2.8) Export of partitioned table does not import
    • status: open --> resolved
    • Priority: 5 --> 1
     
  • Marc Delisle
    Marc Delisle
    2014-08-31

    • Status: resolved --> fixed