Multi-column primary key rejected based on column order in key definition, but does not produce a meaningful error message

A PK is created with the CREATE TABLE statement. The key contains multiple columns, one of which is auto-increment. If the auto-increment column is not listed first, the CREATE TABLE is rejected with:

ERROR 1075 (42000) at line 2: Incorrect table definition; there can be only one auto column and it must be defined as a key

However, if the auto-increment column is listed first in the list of columns that comprise the primary key, CREATE TABLE succeeds.

Further investigation shows that:

• On stand-alone MariaDB server this happens no matter what the default engine type is (MyISAM, InnoDB) and no matter if ENGINE= is given in the CREATE TABLE statement, but
• On Galera replication (with default engine set to InnoDB in config), if ENGINE=InnoDB is given explicitly in the CREATE TABLE statement, then CREATE TABLE succeeds even if the auto-increment column is not listed first in the list of columns that comprise the PK (but if the same DDL is run without the ENGINE=InnoDB, even though InnoDB is the default engine, DDL still fails).

PoC 1 (always fails on MariaDB, stand-alone & Galera):

DROP TABLE IF EXISTS `pk_multicol_bug`;

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

);

PoC 2 (always passes on MariaDB, stand-alone & Galera):

DROP TABLE IF EXISTS `pk_multicol_bug`;

CREATE TABLE `pk_multicol_bug` (

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

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

  PRIMARY KEY (`id`, `example_int`)

);

PoC 3 (fails on MariaDB stand-alone, but succeeds on Galera):

DROP TABLE IF EXISTS `pk_multicol_bug`;

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=InnoDB;

If fixing this is not trivial, then at least an errata should be issued and the documentation on multi-column keys should explicitly mention this behaviour: https://mariadb.com/kb/en/library/getting-started-with-indexes