Bug 489681

Summary: mysql-5.0.45 introduced a regression in alter table
Product: Red Hat Enterprise Linux 5 Reporter: Olivier Fourdan <ofourdan>
Component: mysqlAssignee: Tom Lane <tgl>
Status: CLOSED WONTFIX QA Contact: BaseOS QE <qe-baseos-auto>
Severity: medium Docs Contact:
Priority: medium    
Version: 5.3CC: byte, hhorak, kem, patrickm, sghosh, tao
Target Milestone: rcKeywords: Regression
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2009-03-31 00:25:06 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
Reproducer script
none
Proposed patch none

Description Olivier Fourdan 2009-03-11 12:35:45 UTC
Created attachment 334788 [details]
Reproducer script

Description of problem:

Dropping and re-adding an AUTO_INCREMENT column in a single ALTER TABLE statement causes an error when using mysql-5.0.45-7. This statement did not  produce an error under previous mysql-5.0.22-2.2.el5_1.1.

Version-Release number of selected component (if applicable):

mysql-5.0.45-7

How reproducible:

100% repducible

Steps to Reproduce:
1. Run the attached "mysql-alter.sql" within mysql
  
Actual results:

[...]

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

Expected results:

No error 

Additional info:

This is bug #31031 un MySQL upstream:

    http://bugs.mysql.com/bug.php?id=31031

Comment 1 Olivier Fourdan 2009-03-11 12:37:50 UTC
Created attachment 334789 [details]
Proposed patch

This is a backport of the fix from http://lists.mysql.com/commits/61475

This patch fixes the issue in the reproducer:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source mysql-alter.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

+------+---+
| c    | i |
+------+---+
| a    | 2 | 
| b    | 4 | 
| c    | 6 | 
+------+---+
3 rows in set (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

*************************** 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.00 sec)

+------+---+
| c    | i |
+------+---+
| a    | 1 | 
| b    | 2 | 
| c    | 3 | 
+------+---+
3 rows in set (0.00 sec)

Comment 2 RHEL Program Management 2009-03-11 12:43:23 UTC
This bugzilla has Keywords: Regression.  

Since no regressions are allowed between releases, 
it is also being proposed as a blocker for this release.  

Please resolve ASAP.

Comment 3 Tom Lane 2009-03-11 16:44:01 UTC
I've got about ten serious bugs open for mysql 5.0.45, at least three of which are regressions versus 5.0.22, so I see nothing particularly special about this one.  Perusing the upstream changelogs reveals a lot of other problems fixed since then too.  My opinion is that we need to rebase to a later version.  5.0.67 is not the latest but might be a good choice, since it's been shipped in F-10 for awhile with few complaints.  Or we could go to the latest in the 5.0.x series, which is 5.0.77 as of right now, but that's still in fedora-testing so it hasn't got much track record...

Comment 4 Tom Lane 2009-03-30 23:23:12 UTC
After looking at this, I'm fairly disturbed by the fact that upstream hasn't chosen to patch this in 5.0 or 5.1.  This suggests that the patch has problems in those branches, or at least they don't have a lot of confidence in it.  The fact that the first attempt to fix it in 6.0 was wrong (per the commits cited in their bugzilla entry) certainly indicates that it's not as easy as it looks.

I'm leaning to the opinion that applying this patch may create more problems than it fixes.

Comment 5 RHEL Program Management 2009-03-31 00:25:06 UTC
Development Management has reviewed and declined this request.  You may appeal
this decision by reopening this request.