#4518 (ok 4.2.8) Export to SQL: CREATE TABLE option AUTO_INCREMENT ignored

[Alexander Kamp]

(Problem does also exists version 4.2.7.1)

I want a SQL export of table structure only without having the 'AUTO_INCREMENT'. In older versions this was no problem, but since SQL table creation has been split into fields, indices and auto_increments, the AUTO_INCREMENT queries are attached always at the end of the export.

Checkbox 'AUTO_INCREMENT' (name: 'sql_auto_increment') under 'CREATE TABLE options' is now ignored. In ExportSQL.class.php, some checkes are to name 'sql_auto_increments'. Maybe the problem originates there.

[Alexander Kamp]

(I rewrote my message because of further investigation so I can narrow the place where it bugs.)

The table has multiple fields. First column has a PRIMARY KEY and AUTO_INCREMENT. Very classic structure.

First part: structure of table
Checked AUTO_INCREMENT: CREATE TABLE ... (...) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=#####;
Unchecked AUTO_INCREMENT: CREATE TABLE ... (...) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Output in both situations is correct, so not affected in bug.

Second part: keys. Not affected in bug.

Third part: AUTO_INCREMENT values for table
The behavior of this depends on having records in table. When table is empty, it doesn't attach the AUTO_INCREMENT value when checked and unchecked. Lookes like it's okay.
When having records in table, scenario checked and unchecked delivers:
ALTER TABLE ... MODIFY ... bigint(20) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=#####;
In both situations the value for AUTO_INCREMENT is appended. This is only allowed in the checked situation. The unchecked scenario should be faulty.

[Smita Kumari]

Hi Alexk

In your third scenario if I'm not wrong , did you mean the part "AUTO_INCREMENT=#####;" should not be there in case when user have unchecked AUTO_INCREMENT checkbox?

[Alexander Kamp]

Hi Smita,

That's right. In older versions (like 4.0.10), the unchecked AUTO_INCREMENT option did not attach the AUTO_INCREMENT=##### after CREATE TABLE statement.

If you need all scenarios written out here, please let me know. It is easy to reproduce.

Grtz Alexander
(Fyi: I'm running PMA on PHP 5.5.14 - MySQL 5.6.16)

[Smita Kumari]

Hi AlexK

Thanks for confirming. I'm looking into it. and yeah it would be really nice if you can provide with other scenarios you'r having the issue with.

Smita

[Alexander Kamp]

I made a simple test for a database having 2 tables: test1 (having one record) and test2 (empty). Select the database (f.e. test) and import the SQL queries.

After importing you can go to tab 'Export':
- under 'Method': 'Extended (view all options)'
- both test tables selected
- under 'Output': select 'View output as text'
- under 'Object creation options': uncheck/check the 'AUTO_INCREMENT'.

After clicking 'Start' - and see the output - you can click 'Back' to change AUTO_INCREMENT setting.

During exporting I often uncheck AUTO_INCREMENT because I want to export table structures only. Therefore, the AUTO_INCREMENT value is unwanted.

In unchecked AUTO_INCREMENT situation you see the AUTO_INCREMENT=##### at table test1.

[Smita Kumari]

Thank you AlexK for the detailed description.
Here I have opened a pull request with a patch to fix this issue, https://github.com/phpmyadmin/phpmyadmin/pull/1340 .
Let me know if it works as expected.

[Marc Delisle]

Fix accepted, thanks. If Alexander Kamp can confirm, it would be better.

[Alexander Kamp]

I applied the patch. Tested both checked and unchecked scenario.
Results like expected.

Thanks for the great work!