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

[14 Sep 2007 15:04] Paul Dubois
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
[14 Sep 2007 15:56] MySQL Verification Team
Thank you for the bug report. Verified as described.
[27 Aug 2008 2:29] Justin Swanhart
I am a customer and this bug also affects 5.1.

Please fix this long standing regression before 5.1 GA.
[28 Aug 2008 15:44] Timothy Smith
Workaround: drop & add in 2 separate alter statements.
[1 Oct 2008 23:10] Konstantin Osipov
a) This is a regression
b) ALTER can be very slow. The workaround of 2 alter statements is not viable.
[5 Dec 2008 13:01] Bugs System
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).
[12 Dec 2008 9:46] Bugs System
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.
[12 Dec 2008 12:28] Bugs System
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.
[16 Jan 2009 7:26] Bugs System
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)
[29 Jan 2009 20:50] Paul Dubois
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.
[9 Oct 2009 13:06] Bugs System
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.
[9 Oct 2009 13:06] Magne Mæhre
Pushed into 5.5.0
[12 Oct 2009 15:42] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:17] Bugs System
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)
[3 Nov 2009 15:16] Paul Dubois
Already fixed in earlier 6.0.x release.
[12 Nov 2009 8:22] Bugs System
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)