MySQL Bugs: #31031: ALTER TABLE regression in 5.0

Bug #31031	ALTER TABLE regression in 5.0
Submitted:	14 Sep 2007 15:04	Modified:	12 Nov 2009 18:59
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0/5.1/6.0	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any

Description:
Before MySQL 5.0, you could drop and re-add an AUTO_INCREMENT
column in a single ALTER TABLE statement. (The effect is to
resequence the column values.) In 5.0, the same ALTER TABLE
statement produces an error.

I suppose it's possible that this shouldn't be consider an error,
but in that case, I would be interested to know the underlying
basis for why the operation no longer works. Thanks.

How to repeat:
Test script:

DROP TABLE IF EXISTS t;
CREATE TABLE t
  (c CHAR(10), i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
SHOW CREATE TABLE t\G
INSERT INTO t VALUES('a',2),('b',4),('c',6);
SELECT * FROM t;
ALTER TABLE t
  DROP i,
  ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
  AUTO_INCREMENT = 1;
SHOW CREATE TABLE t\G
SELECT * FROM t;

In 4.1.24, the result looks like this:

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t
    ->   (c CHAR(10), i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` char(10) default NULL,
  `i` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO t VALUES('a',2),('b',4),('c',6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+---+
| c    | i |
+------+---+
| a    | 2 |
| b    | 4 |
| c    | 6 |
+------+---+
3 rows in set (0.01 sec)

mysql> ALTER TABLE t
    ->   DROP i,
    ->   ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   AUTO_INCREMENT = 1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` char(10) default NULL,
  `i` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

mysql> SELECT * FROM t;
+------+---+
| c    | i |
+------+---+
| a    | 1 |
| b    | 2 |
| c    | 3 |
+------+---+
3 rows in set (0.00 sec)

In 5.0.50 (and 5.1), the ALTER TABLE statement fails:

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t
    ->   (c CHAR(10), i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` char(10) default NULL,
  `i` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> INSERT INTO t VALUES('a',2),('b',4),('c',6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+---+
| c    | i |
+------+---+
| a    | 2 | 
| b    | 4 | 
| c    | 6 | 
+------+---+
3 rows in set (0.00 sec)

mysql> ALTER TABLE t
    ->   DROP i,
    ->   ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   AUTO_INCREMENT = 1;
ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys

Thank you for the bug report. Verified as described.

I am a customer and this bug also affects 5.1.

Please fix this long standing regression before 5.1 GA.

Workaround: drop & add in 2 separate alter statements.

a) This is a regression
b) ALTER can be very slow. The workaround of 2 alter statements is not viable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/60737

2766 Magne Mahre	2008-12-05
      Bug #31031  	ALTER TABLE regression in 5.0
      
      Certain ALTER TABLE statements failed with Error 1089,
      with no appearant reason.  In particular if you combined
      a DROP and an ADD in the same statement.
      
      The problem was in a check introduced to fix 26794, which
      was slightly wrong (a logical OR that should have been a
      logical AND).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61452

2766 Magne Mahre	2008-12-12
      Bug #31031        ALTER TABLE regression in 5.0
      
      An ALTER TABLE statement which added a column and added
      a non-partial index on it failed with:
      
      "ERROR 1089 (HY000): Incorrect sub part key; the used
      key part isn't a string, the used length is longer than
      the key part, or the storage engine doesn't support unique
      sub keys"
      
      In a check introduced to fix an earlier bug (no. 26794),
      to allow for indices on spatial type columns, the
      test expression was flawed (a logical OR was used instead
      of a logical AND), which led to this regression.
      
      The code in question does a sanity check on the key, and
      the flawed code mistakenly classified any index created
      in the way specified above as a partial index.  Since
      many data types does not allow partial indices, the
      statement would fail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61475

2781 Magne Mahre	2008-12-12
      Bug #31031        ALTER TABLE regression in 5.0
            
      An ALTER TABLE statement which added a column and added
      a non-partial index on it failed with:
            
      "ERROR 1089 (HY000): Incorrect sub part key; the used
      key part isn't a string, the used length is longer than
      the key part, or the storage engine doesn't support unique
      sub keys"
            
      In a check introduced to fix an earlier bug (no. 26794),
      to allow for indices on spatial type columns, the
      test expression was flawed (a logical OR was used instead
      of a logical AND), which led to this regression.
            
      The code in question does a sanity check on the key, and
      the flawed code mistakenly classified any index created
      in the way specified above as a partial index.  Since
      many data types does not allow partial indices, the
      statement would fail.

Pushed into 6.0.10-alpha (revid:davi.arnaut@sun.com-20090114101000-u02ac63f9ee13x84) (version source revid:magne.mahre@sun.com-20081212122756-3ycdpvn0yo8qil4z) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 6.0.10 changelog.

ALTER TABLE statements that added a column and added a non-partial
index on the column failed to add the index.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86353

2895 Magne Mahre	2009-10-09
      Bug #31031  ALTER TABLE regression in 5.0
      
      An ALTER TABLE statement which added a column and added
      a non-partial index on it failed with:
                  
      "ERROR 1089 (HY000): Incorrect sub part key; the used
      key part isn't a string, the used length is longer than
      the key part, or the storage engine doesn't support unique
      sub keys"
                  
      In a check introduced to fix an earlier bug (no. 26794),
      to allow for indices on spatial type columns, the
      test expression was flawed (a logical OR was used instead
      of a logical AND), which led to this regression.
                  
      The code in question does a sanity check on the key, and
      the flawed code mistakenly classified any index created
      in the way specified above as a partial index.  Since
      many data types does not allow partial indices, the
      statement would fail.

Pushed into 5.5.0

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:dlenev@mysql.com-20091009152620-weybanb3xbbxxs6h) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in earlier 6.0.x release.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)