[#MDEV-9790] MyISAM allows AUTO_INCREMENT columns with no index

[MDEV-9790] MyISAM allows AUTO_INCREMENT columns with no index Created: 2016-03-25 Updated: 2016-03-25 Resolved: 2016-03-25
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - MyISAM
Affects Version/s:	5.5, 10.0, 10.1, 10.2
Fix Version/s:	N/A

Type:

Bug

Priority:

Major

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Not a Bug

Votes:

Labels:

None

Description

This script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a CHAR(20) NOT NULL, b INT NOT NULL AUTO_INCREMENT, INDEX(a,b)) ENGINE=InnoDB;

correctly return an error:

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

If I now change ENGINE to MyISAM, it creates the table with no errors:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a CHAR(20) NOT NULL, b INT NOT NULL AUTO_INCREMENT, INDEX(a,b)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES ('a'),('b'),('c');

SELECT * FROM t1;

but AUTO_INCREMENT does not actually work:

+---+---+

| a | b |

+---+---+

| a | 1 |

| b | 1 |

| c | 1 |

+---+---+

The problem seems to be introduced by:

Author: unknown <monty@mashka.mysql.fi>  2003-03-07 14:36:52

Committer: unknown <monty@mashka.mysql.fi>  2003-03-07 14:36:52

    Fixed AUTO_INCREMENT handling in MyISAM (last auto_increment patch broke things)

    Some after merge fixes

Comments

Comment by Alexander Barkov [ 2016-03-25 ]

Note, If I change the order of column in the index:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a CHAR(20) NOT NULL, b INT NOT NULL AUTO_INCREMENT, INDEX(b,a)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES ('a'),('b'),('c');

SELECT * FROM t1;

AUTO_INCREMENT starts to work:

+---+---+

| a | b |

+---+---+

| a | 1 |

| b | 2 |

| c | 3 |

+---+---+

Comment by Alexander Barkov [ 2016-03-25 ]

Generated at Thu Feb 08 07:37:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.