MySQL Bugs: #6236: Incomplete ALTER TABLE breaks MERGE compatibility

Bug #6236	Incomplete ALTER TABLE breaks MERGE compatibility
Submitted:	24 Oct 2004 22:14	Modified:	9 May 2005 11:33
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.22	OS:
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
An incomplete ALTER TABLE statement (which is accepted as valid by mysqld) miscalculates the number of nullable columns in the table.

This is probably true for any storange engine, but this causes problems specifically for MERGE tables, where the base tables appear to be completely identical but you receive errno 143 because of this internal difference.

Verified against 4.0.22, 4.1.7, Linux and Windows.

How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 ( a VARCHAR(10) NOT NULL PRIMARY KEY ) TYPE=MyISAM;
CREATE TABLE t2 ( a VARCHAR(10) NOT NULL PRIMARY KEY ) TYPE=MERGE UNION=(t1);
FLUSH TABLES;
ALTER TABLE t1 MODIFY a VARCHAR(10);
SHOW CREATE TABLE t2;
FLUSH TABLES;
ALTER TABLE t1 MODIFY a VARCHAR(10) NOT NULL;
SHOW CREATE TABLE t2;
DROP TABLE IF EXISTS t1, t2;

Can also use myisamchk -dvv (record length and field structure) to see the differences caused by the incomplete ALTER.

Suggested fix:
The ALTER TABLE statement should probably be rejected in this specific case (column participates in PRIMARY KEY thus must be NOT NULL), but in any case the ALTER TABLE should not generate a different table definition than the CREATE TABLE produces.

I take this review though I'm still not in the reviewers list.

After changing tabs to blanks, it is OK to push.

Patch approved by Sergei, Apr 27

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix in 4.0.25

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/7159