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

[Bill Karwin]

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.

[Bill Karwin]

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.

[Smita Kumari]

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]

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]

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 :-)

[Marc Delisle]

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]

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]

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]

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.

[Marc Delisle]

Smita: pull request accepted, thanks.

Bill: a confirmation would be appreciated.

[Bill Karwin]

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]

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