MySQL Bugs: #31232: mysql_alter_table() does not roll back .frm on add

Bug #31232	mysql_alter_table() does not roll back .frm on add_index() failure
Submitted:	27 Sep 2007 11:23	Modified:	27 Nov 2007 10:13
Reporter:	Marko Mäkelä	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
In smart ALTER TABLE, when handler::add_index() returns 
ER_PRIMARY_CANT_HAVE_NULL, MySQL does not seem to roll back the changes to the table.

How to repeat:
I have this in our test case of an unpublished InnoDB branch that implements smart ALTER TABLE:

create table t1(a int, aa blob, b blob, c text, d text not null)
engine=innodb default charset = utf8;

insert into t1 values (null,null,null,null,'null');
insert into t1
select a,aa,left(repeat(d,100*a),65535),repeat(d,20*a),d from t2,t3;

--error ER_PRIMARY_CANT_HAVE_NULL
alter table t1 add primary key (a,aa), add key (b(20));
delete from t1 where d='null';
--error ER_DUP_ENTRY
alter table t1 add primary key (a,aa(255)), add key (b(20));

The latter ALTER TABLE does not lead to handler::add_index() being
called, because MySQL thinks that the index definition has changed,
from (a,aa) to (a,aa(255)).  However, t1 should not have a primary key,
because the first ALTER TABLE statement (executed by
handler::add_index()) failed.

If I change the primary key in the first alter table from (a,aa) to
(a,aa(255)), then MySQL will call handler::add_index().  A failed
handler::add_index() should lead into the .frm file being rolled back,
shouldn't it?  I would have expected MySQL to call handler::add_index()
in both cases.

Suggested fix:
Whenever handler::add_index(), handler::prepare_drop_index(), or handler::final_drop_index() return nonzero, roll back the changes to the .frm file.

Can you please create a clone for this to be tested.
The current implementation does rollback as part of
add_index returning an error. This is tested by attempting
to create unique indexes on non-unique data in the
ndb test suite.

Marko, please tell Martin Skold precisely what is wrong.

Apparently, this bug has indeed been fixed meanwhile.