1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2DROP TABLE IF EXISTS foo,bar; 3set session tokudb_disable_slow_alter=ON; 4create table foo (a int NOT NULL, b bigint NOT NULL, c tinyint NOT NULL, d int NOT NULL, primary key (b))engine=TokuDB; 5insert into foo values (122,10,1,1000),(1,110,1,0),(0,3234,0,657),(0,0,0,0),(99,99,0,99), (-1,-1,-1,-1); 6create table bar like foo; 7alter table bar engine=MyISAM; 8insert into bar select * from foo; 9include/diff_tables.inc [test.foo, test.bar] 10alter table foo add column e int default NULL first; 11alter table bar add column e int default NULL first; 12select * from foo; 13e a b c d 14NULL -1 -1 -1 -1 15NULL 0 0 0 0 16NULL 122 10 1 1000 17NULL 99 99 0 99 18NULL 1 110 1 0 19NULL 0 3234 0 657 20include/diff_tables.inc [test.foo, test.bar] 21alter table foo drop column e; 22alter table bar drop column e; 23include/diff_tables.inc [test.foo, test.bar] 24alter table foo add column e int default -1 first; 25alter table bar add column e int default -1 first; 26include/diff_tables.inc [test.foo, test.bar] 27alter table foo drop column e; 28alter table bar drop column e; 29include/diff_tables.inc [test.foo, test.bar] 30alter table foo add column e int NOT NULL default -1 first; 31alter table bar add column e int NOT NULL default -1 first; 32include/diff_tables.inc [test.foo, test.bar] 33select * from foo; 34e a b c d 35-1 -1 -1 -1 -1 36-1 0 0 0 0 37-1 122 10 1 1000 38-1 99 99 0 99 39-1 1 110 1 0 40-1 0 3234 0 657 41drop table foo; 42drop table bar; 43create table foo (a int, b int not null, c int, d int not null, e int, primary key (e))engine=TokuDB; 44alter table foo drop column e; 45ERROR 42000: Table 'foo' uses an extension that doesn't exist in this XYZ version 46create table bar like foo; 47alter table bar engine=MyISAM; 48insert into foo values (NULL, -1, NULL, -1,1),(0,0,0,0,0),(NULL,234,234,324,234),(98567,76,NULL,7668,90909); 49insert into bar select * from foo; 50include/diff_tables.inc [test.foo, test.bar] 51alter table foo drop column b; 52alter table bar drop column b; 53include/diff_tables.inc [test.foo, test.bar] 54alter table foo drop column d; 55alter table bar drop column d; 56include/diff_tables.inc [test.foo, test.bar] 57alter table foo drop column a; 58alter table bar drop column a; 59include/diff_tables.inc [test.foo, test.bar] 60drop table foo; 61drop table bar; 62create table foo (a varchar(20), b varchar(20) not null, c varchar(20), d varchar(20) not null, e int, primary key (e))engine=TokuDB; 63alter table foo drop column e; 64ERROR 42000: Table 'foo' uses an extension that doesn't exist in this XYZ version 65create table bar like foo; 66alter table bar engine=MyISAM; 67insert into foo values (NULL, "-1", NULL, "-1",1),("dfg0","0rrr","eee0","qwert",0),(NULL,"234","234","324",234),("98567","76",NULL,"7668","90909"); 68insert into bar select * from foo; 69include/diff_tables.inc [test.foo, test.bar] 70alter table foo drop column b; 71alter table bar drop column b; 72include/diff_tables.inc [test.foo, test.bar] 73alter table foo drop column d; 74alter table bar drop column d; 75include/diff_tables.inc [test.foo, test.bar] 76alter table foo drop column a; 77alter table bar drop column a; 78include/diff_tables.inc [test.foo, test.bar] 79drop table foo; 80drop table bar; 81create table foo( 82a int, 83b int, 84c int, 85d int, 86e int, 87f int, 88g int, 89h int, 90aa int, 91bb int, 92cc int, 93dd int 94)engine = TokuDB; 95create table bar like foo; 96alter table bar engine=MyISAM; 97insert into foo values (NULL,-1,NULL,-1,NULL,-1,NULL,-1,NULL,-1,NULL,-1); 98insert into foo values (-1,NULL,-1,NULL,-1,NULL,-1,NULL,-1,NULL,-1,NULL); 99insert into foo values (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); 100insert into foo values (-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1); 101insert into bar select * from foo; 102include/diff_tables.inc [test.foo, test.bar] 103select * from foo; 104a b c d e f g h aa bb cc dd 105NULL -1 NULL -1 NULL -1 NULL -1 NULL -1 NULL -1 106-1 NULL -1 NULL -1 NULL -1 NULL -1 NULL -1 NULL 107NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 108-1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 109alter table foo drop column a, drop column aa, drop column c, drop column cc, drop column e, drop column f; 110alter table bar drop column a, drop column aa, drop column c, drop column cc, drop column e, drop column f; 111include/diff_tables.inc [test.foo, test.bar] 112select * from foo; 113b d g h bb dd 114-1 -1 NULL -1 -1 -1 115NULL NULL -1 NULL NULL NULL 116NULL NULL NULL NULL NULL NULL 117-1 -1 -1 -1 -1 -1 118alter table foo add column a int default -1 first, add column aa int NOT NULL default -1 after h, add column c int default NULL after b, add column cc int default 0 after bb, add column f int default NULL after d, add column e int NOT NULL default 12345 after d; 119alter table bar add column a int default -1 first, add column aa int NOT NULL default -1 after h, add column c int default NULL after b, add column cc int default 0 after bb, add column f int default NULL after d, add column e int NOT NULL default 12345 after d; 120include/diff_tables.inc [test.foo, test.bar] 121select * from foo; 122a b c d e f g h aa bb cc dd 123-1 -1 NULL -1 12345 NULL NULL -1 -1 -1 0 -1 124-1 NULL NULL NULL 12345 NULL -1 NULL -1 NULL 0 NULL 125-1 NULL NULL NULL 12345 NULL NULL NULL -1 NULL 0 NULL 126-1 -1 NULL -1 12345 NULL -1 -1 -1 -1 0 -1 127drop table foo; 128drop table bar; 129