Bug #19011 Fast (no copying) alter table doesn't work all tables (field types)
Submitted: 11 Apr 2006 14:58 Modified: 27 Apr 2006 15:17
Reporter: Martin Skold Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1-new OS:
Assigned to: Martin Skold CPU Architecture:Any

[11 Apr 2006 14:58] Martin Skold
Description:
Fast (no copying) alter tables (such as renaming a field, changing the field to the 
same type, but new name) doesn't work for all tables (depends on the field types).
This is a flagship feature (and part of fast create index for cluster) and is thus
high prio and a showstopper.

How to repeat:
It is hard to verify directly without looking inside a particular trace what is
happening since the outcome of the alter table is the same. The following
test script uses a command line call to check the internal table id inside
ndbcluster to determine if a table has been copied or not.

-------------------% cut here %-------------------
-- source include/have_ndb.inc
-- source include/not_embedded.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (
  auto int(5) unsigned NOT NULL auto_increment,
  string char(10),
  vstring varchar(10),
  bin binary(2),
  vbin varbinary(7),    
  tiny tinyint(4) DEFAULT '0' NOT NULL ,
  short smallint(6) DEFAULT '1' NOT NULL ,
  medium mediumint(8) DEFAULT '0' NOT NULL,
  long_int int(11) DEFAULT '0' NOT NULL,
  longlong bigint(13) DEFAULT '0' NOT NULL,
  real_float float(13,1) DEFAULT 0.0 NOT NULL,
  real_double double(16,4),
  real_decimal decimal(16,4),
  utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
  ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
  umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
  ulong int(11) unsigned DEFAULT '0' NOT NULL,
  ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
  bits bit(3),
  options enum('zero','one','two','three','four') not null,
  flags set('zero','one','two','three','four') not null,
  date_field date,
  year_field year,
  time_field time,      
  date_time datetime,
  time_stamp timestamp,
  PRIMARY KEY (auto)
) engine=ndb;

CREATE TEMPORARY TABLE ndb_show_tables (id INT, type VARCHAR(20), state VARCHAR(20), logging VARCHAR(20), _database VARCHAR(255), _schema VARCHAR(20), name VARCHAR(255));

--disable_warnings
--exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLTEST_VARDIR/master-data/test/tmp.dat
LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables;
--enable_warnings

select id from ndb_show_tables where name like '%t1%';
set @t1_id = (select id from ndb_show_tables where name like '%t1%');
truncate ndb_show_tables;

alter table t1 change tiny new_tiny tinyint(4) DEFAULT '0' NOT NULL;

--disable_warnings
--exec $NDB_TOOLS_DIR/ndb_show_tables --p > $MYSQLTEST_VARDIR/master-data/test/tmp.dat
LOAD DATA INFILE 'tmp.dat' INTO TABLE ndb_show_tables;
--enable_warnings

# Check that table id is the same
select * from  ndb_show_tables;
select 'no_copy' from ndb_show_tables where id = @t1_id and name like '%t1%';

DROP TABLE t1, ndb_show_tables;

Suggested fix:
Fix Field::is_equal methods for all field types.
[21 Apr 2006 6:46] Martin Skold
Pushed to 5.1.10
[27 Apr 2006 15:17] Paul DuBois
Noted in 5.1.10 changelog.

Some fast <literal>ALTER TABLE</literal> operations
(requiring no temporary table) did not work for all tables.
(Bug #19011)