#4437 (ok 4.2.3) Problems due to missing enforcement of the minimum supported MySQL version

[tasty_wheat]

System/Software Specifications:
+ MySQL 5.0.27-standard (32 bit)
+ Windows 7 (64 bit)
+ PHPMyAdmin 4.2.2

Steps to reproduce bug:
1. Create a new table (use any name) with a column (use any name) that is a signed INT, auto increment, and a primary key and the table is charset=latin1 and engine=MyISAM.
2. Export the table using PHPMyAdmin defaults.
3. Drop the table from the database.
4. Import the exported SQL.

During the import an error should occur that says, "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key." This happens because the CREATE TABLE statement does not set the primary key, that happens later with a ALTER TABLE statement. I would have thought this to be more of a MySQL bug, however, if I use a SHOW CREATE TABLE statement on this table the primary key is part of the CREATE TABLE statement.

[Shivam Dixit]

I tried to reproduce the bug, but in my case it was working fine. There were no errors generated.

System Specifications
phpMyAdmin 4.2.2
Ubuntu 12.04 (64 bits)
MySQL 5.5.35

[U. Cavus]

@Shivam This error is being reported in many places around the web. Perhaps your version of MySQL does not have this limitation but MySQL 5.0 certainly does, and it renders database exports made with PMA unusable. The only way to fix them is to decompress, manually edit the SQL, then recompress it, Which is completely unnacceptable. This is not anomalous behaviour, it's in the manual. You can't have an AUTO_INCREMENT column in a table definition unless it's also defined as a key.

I understand that keys and constraints are now added afterwards for performance reasons but in the case of auto-incremented columns, this is not a backwards-compatible solution and it needs to be fixed.

[Marc Delisle]

@U.Cavus We have documented on the downloads page and in phpMyAdmin's documentation, that MySQL 5.0 is no longer supported by phpMyAdmin current stable version. Starting with phpMyAdmin 4.1.1, MySQL 5.5 is the minimum supported version.

[U. Cavus]

Added MySQL 5.6 to our dev server specifically to import and recover a database exported with PMA 4.2.2. It fell over on the first statement:

CREATE TABLE `adminnotification_inbox` (
  `notification_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Notification id',
  `severity` smallint(5) unsigned NOT NULL default '0' COMMENT 'Problem type',
  `date_added` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'Create date',
  `title` varchar(255) NOT NULL COMMENT 'Title',
  `description` text COMMENT 'Description',
  `url` varchar(255) default NULL COMMENT 'Url',
  `is_read` smallint(5) unsigned NOT NULL default '0' COMMENT 'Flag if notification read',
  `is_remove` smallint(5) unsigned NOT NULL default '0' COMMENT 'Flag if notification might be removed'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Adminnotification Inbox' AUTO_INCREMENT=88 ;

And PMA halted, just as before with MySQL said: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

[U. Cavus]

Tried a test table export from MySQL 5.6 and the output is clearly different:

CREATE TABLE `test` (
`foo` int(1) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `test`
--
ALTER TABLE `test`
MODIFY `foo` int(1) unsigned NOT NULL AUTO_INCREMENT;

I still consider this a bug. Why can't PMA ensure the AUTO_INCREMENT is removed from the table def and added afterwards? What happens to all the people that have updated PMA, seemingly without issue, and now have broken exports? PMA issues no warnings about MySQL < 5.5 being too old, and does not prevent itself running. What about all those cPanel servers that get a recent version of PMA but may still be on MySQL 5.1? I think it's a recipe for disaster and your response seems to be "we don't care."

[Marc Delisle]

@U.Cavus Please clarify: your example of export for adminnotification_inbox was exported from your MySQL 5.0 server, correct?

[Marc Delisle]

For 4.2.3, added enforcement of the minimum MySQL version:
https://github.com/phpmyadmin/phpmyadmin/commit/9112d7c9303b51b7e156cc98fa79ab8824b50e30

[U. Cavus]

Correct, the faulty export was from MySQL 5.0 with PMA 4.2.2