Tested on Linux only, but likely all

Hi Assen,

It is mentioned in the documentation here https://mariadb.com/kb/en/library/auto_increment/ "If the key consists of multiple columns, the AUTO_INCREMENT column must be the first one, unless the storage engine is Aria or MyISAM."

MariaDB [test]> CREATE TABLE `pk_multicol_bug` (

    -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

    -> `example_int` int(11) NOT NULL DEFAULT '0',

    -> PRIMARY KEY (`example_int`, `id`)

    -> ) ENGINE=Myisam;

Query OK, 0 rows affected (0.02 sec)

Good to know it is mentioned at least somewhere. Still:

• The document I referenced in KB has no notion of this restriction - would be good to have a link there at least. Since I found this article via Google when searching for MariaDB multi-column primary key, them others will stumble on it too.
• The error message given by MySQL server is far and away from the real cause.
• Would be nice if mysqldump could take care to write the autoincrement column first.
• This does not explain the oddity with Galera, which accepts the non-autoincrement column first if ENGINE=InnoDB is given explicitly (even though this is the config default for Galera).

The document I referenced in KB has no notion of this restriction - would be good to have a link there at least. Since I found this article via Google when searching for MariaDB multi-column primary key, them others will stumble on it too.

I agree, this point is very suitable to the page "Getting started with indexes", or at least the page should link to the auto-increment page mentioning that there are tricks that need to be learnt.

The error message given by MySQL server is far and away from the real cause.

I agree, the message is awful for this case.

Would be nice if mysqldump could take care to write the autoincrement column first.

I don't quite understand what you mean by that, if you suggest that mysqldump must re-order columns in the index, it's a big no, mysqldump must not change the definition of a structure. If you meant something else, please elaborate.

This does not explain the oddity with Galera, which accepts the non-autoincrement column first if ENGINE=InnoDB is given explicitly (even though this is the config default for Galera).

Indeed it doesn't explain the oddity, only I can't reproduce it. For starters, can you paste the full unabridged output from the command line client:

CREATE TABLE ... <your table definition including the engine>;

SHOW CREATE TABLE <table name>;

and paste or attach the output of SHOW VARIABLES.

Would be nice if mysqldump could take care to write the autoincrement column first means that whatever I dump via mysqldump should be importable via mysql CLI. With this but it is not the case, because mysqldump creates a dump which fails to import with mysql command-line client. From ops perspective, this is quite unpleasant.

I'll check my notes for the Galera case when I get the time, but fixing the previous one is of much more use.

Can you provide an example where mysqldump creates a dump which fails to import?
Aside from that Galera oddity which is still in question, I don't see how it's possible within the scope of the problem that you described. If the definition of the table violates the auto-increment limitation, it's just not created, and thus is not dumped by mysqldump, so there is no problem restoring from the dump. So, what's the use case?

I'll need a few days to get access to the actual machine and obtain a sample (the dump was well over a gigabyte). But from what I recall it was a dump made on MariaDB 10.0 on Ubuntu 16.04 which failed to import on MariaDB 10.2 on RHEL 7 (both are vendor builds). It could be that the database originally started as MySQL 5.5 before being upgraded in-place to MariaDB 10.0.

The limitation on auto-increment is very old and, to my knowledge, affects all mentioned versions. So, if there was indeed a dump that failed to load due to it (and wasn't modified manually before that), we would need to see the relevant portion of it. The dump will also contain the server version which it was taken from.

So, after resurrecting the original machine from backup it turns the tables were indeed MyISAM, which explains the ordering - and the import was attempted into Galera, which explains the error.

If you don't want to touch the column ordering by default, mysqldump could provide this as some command-line option or --compat option (e.g., we make a good use of "no_table_options" when moving between engine types - may be even included in this one).

Alternatively, mysqldump could print a warning to STDERR when writing such a key.

And I still believe import should give a proper error message about column ordering.

I confirm it for the error message part and documentation.

I'll leave it to serg to decide upon mysqldump request, I personally don't see any use in that. If the engine allows such a key, it's a perfectly valid ordering already and mysqldump doesn't need to (and mustn't) interfere. If the engine doesn't allow it, but somehow it appears upon SHOW CREATE TABLE which the tool executes, it means we have a much bigger problem, somehow an invalid structure appeared in the server, and it's not for mysqldump to try to fix it.

Agree about MyISAM.

Documentation: "unless the storage engine is Aria or MyISAM" is not strictly correct. Engines that claim to support auto-increment column being not the first key part are: Blackhole, Federated, FederatedX, Aria, MyISAM, MERGE, Spider, TokuDB.

https://mariadb.com/kb/en/library/getting-started-with-indexes/and https://mariadb.com/kb/en/library/auto_increment/ have been updated.