Support "KEY" in table creation

[Cotwa]

Describe the bug

phpMyAdmin 4.9.3: In SQL tab, when creating a table with a key, I get red error warnings from phpMyAdmin that don't seem to be correct.

To Reproduce

1. Open phpMyAdmin

2. Create a new database named misc

3. Select misc and go to the SQL tab

4. Paste in this code:

CREATE TABLE autos (
auto_id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
make VARCHAR(128),
year INTEGER,
mileage INTEGER
);

You will see lines 2, 3 and 4 flagged with a red X on the left.
I think this is valid code for MySQL, and when executed it does work as expected.

The source of the problem seems to be line 2, where the error says "A comma or a closing bracket was expected. (near KEY)". The following two lines have errors also but they appear to be derivative of the first error.

Expected behavior

I expected no errors to be flagged.

Screenshots

Server configuration

• Operating system: macOS 10.14.6

• Web server: Apache/2.2.34 (Unix) mod_wsgi/3.5 Python/2.7.13 PHP/7.4.2 mod_ssl/2.2.34 OpenSSL/1.0.2o DAV/2 mod_fastcgi/mod_fastcgi-SNAP-0910052141 mod_perl/2.0.11 Perl/v5.24.0

• Database version: libmysql - mysqlnd 7.4.2

• PHP version: 7.4.2

• phpMyAdmin version: 4.9.3

Client configuration

• Browser: Chrome Version 81.0.4044.113 (Official Build) (64-bit)

• Operating system: macOS 10.14.6

Additional context

Note: Using MAMP 5.7; web and db servers on same machine as client.

[ibennetch]

I can reproduce this with our parser library, sql parser, and I've confirmed that it definitely doesn't like the KEY keyword. You're correct that the two other errors that follow are generated because of the trouble with KEY.

The documentation at https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-indexes-keys and https://mariadb.com/kb/en/create-table/#primary-key-column-option says that, in this context, KEY is a synonym for PRIMARY KEY.

Anyone who is interested in making a pull request for this should do the fix against https://github.com/phpmyadmin/sql-parser/ and target the QA branch.

[williamdes]

This syntax works on MySQL 5.5.62/8.0.11 and MariaDB 10.10
So let's say everywhere since ever

[williamdes]

Here is two other working variants

CREATE TABLE autos3 (
auto_id INT UNSIGNED NOT NULL  KEY,
make VARCHAR(128),
year INTEGER,
mileage INTEGER
);

CREATE TABLE autos3 (
auto_id INT UNSIGNED ZEROFILL NOT NULL  KEY,
make VARCHAR(128),
year INTEGER,
mileage INTEGER
);