1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6############################################################# 7# Author: Martin 8# Date: 2007-07 9# Purpose: basic online alter test 10############################################################## 11# Change Author: Jonathan 12# Date 2006-08-28 13# Purpose: Add more testing for online alter 14############################################################## 15--source include/have_multi_ndb.inc 16 17--disable_warnings 18DROP TABLE IF EXISTS t1; 19--enable_warnings 20 21# Create utiltity table used to hold the output from ndb_show_table 22CREATE TEMPORARY TABLE IF NOT EXISTS ndb_show_tables_results ( 23 id INT, 24 type VARCHAR(20), 25 state VARCHAR(20), 26 logging VARCHAR(20), 27 _database VARCHAR(255), 28 _schema VARCHAR(20), 29 name VARCHAR(255) 30); 31 32###################################### 33# basic online alter tests 34###################################### 35--echo ******************************* 36--echo * basic online alter tests 37--echo ******************************* 38 39CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=DYNAMIC ENGINE NDB; 40INSERT INTO t1 values (1,1); 41 42--source ndb_show_tables_result.inc 43set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 44 45--echo ******************************* 46--echo * Alter Table online add column 47--echo ******************************* 48--echo * Add column c as CHAR 49--echo ******************************* 50 51ALTER TABLE t1 ADD c CHAR(19); 52 53--source ndb_show_tables_result.inc 54select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 55 56INSERT INTO t1 values (2,1,"a"); 57SELECT * FROM t1 ORDER BY a; 58UPDATE t1 SET c='b' where a = 2; 59SELECT * FROM t1 ORDER BY a; 60DROP TABLE t1; 61 62--echo ******************************* 63--echo * Alter Table online add column 64--echo ******************************* 65--echo * Add column c as nullable INT 66--echo ******************************* 67 68CREATE TABLE t1 (a INT UNSIGNED KEY, b VARCHAR(19)) ENGINE NDB; 69INSERT INTO t1 values (1,"a"); 70 71--source ndb_show_tables_result.inc 72set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 73 74ALTER TABLE t1 ADD c INT; 75 76--source ndb_show_tables_result.inc 77select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 78 79INSERT INTO t1 values (2,"a",1); 80SELECT * FROM t1 ORDER BY a; 81UPDATE t1 SET c = 2 where a = 2; 82SELECT * FROM t1 ORDER BY a; 83DROP TABLE t1; 84 85--echo ******************************* 86--echo * Alter Table online add column 87--echo ******************************* 88--echo * Add column c as nullable INT 89--echo ******************************* 90 91CREATE TABLE t1 (a INT UNSIGNED KEY, b INT COLUMN_FORMAT DYNAMIC) ENGINE NDB; 92INSERT INTO t1 values (1,1); 93 94--source ndb_show_tables_result.inc 95set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 96 97ALTER TABLE t1 ADD c INT; 98 99--source ndb_show_tables_result.inc 100select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 101 102INSERT INTO t1 values (2,1,1); 103SELECT * FROM t1 ORDER BY a; 104UPDATE t1 SET c = 2 where a = 2; 105SELECT * FROM t1 ORDER BY a; 106 107--echo ******************************* 108--echo * Create online Index ci 109--echo ******************************* 110 111CREATE INDEX ci on t1(c) algorithm=inplace; 112 113--source ndb_show_tables_result.inc 114select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 115 116--echo ******************************* 117--echo * Create offline Index ci2 118--echo ******************************* 119 120CREATE INDEX ci2 on t1(c) algorithm=copy; 121 122--source ndb_show_tables_result.inc 123select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 124set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 125 126--echo ******************************* 127--echo * Drop online Index ci 128--echo ******************************* 129 130DROP INDEX ci on t1 algorithm=inplace; 131 132--source ndb_show_tables_result.inc 133select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 134 135--echo ******************************* 136--echo * Drop offline Index ci2 137--echo ******************************* 138 139DROP INDEX ci2 on t1 algorithm=copy; 140 141--source ndb_show_tables_result.inc 142select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 143 144DROP TABLE t1; 145 146--echo ******************************* 147--echo * The following ALTER operations are not supported on-line 148--echo ******************************* 149--echo * Not supported Test#1 150--echo ******************************* 151 152CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=FIXED ENGINE NDB; 153INSERT INTO t1 values (1,1); 154 155--source ndb_show_tables_result.inc 156set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 157 158--error ER_ALTER_OPERATION_NOT_SUPPORTED 159ALTER TABLE t1 algorithm=inplace, ADD c CHAR(19); 160--source ndb_show_tables_result.inc 161select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 162 163ALTER TABLE t1 ADD c CHAR(19); 164 165INSERT INTO t1 values (2,1,"a"); 166SELECT * FROM t1 ORDER BY a; 167UPDATE t1 SET c = 'b' where a = 2; 168SELECT * FROM t1 ORDER BY a; 169DROP TABLE t1; 170 171--echo ******************************* 172--echo * Not supported Test#2 173--echo ******************************* 174 175CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=DYNAMIC ENGINE NDB; 176INSERT INTO t1 values (1,1); 177 178--source ndb_show_tables_result.inc 179set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 180 181--error ER_ALTER_OPERATION_NOT_SUPPORTED 182ALTER TABLE t1 algorithm=inplace, ADD c CHAR(19) DEFAULT 17; 183--source ndb_show_tables_result.inc 184select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 185 186ALTER TABLE t1 ADD c CHAR(19) DEFAULT 17; 187 188INSERT INTO t1 values (2,1,"a"); 189SELECT * FROM t1 ORDER BY a; 190UPDATE t1 SET c = 'b' where a = 2; 191SELECT * FROM t1 ORDER BY a; 192--echo ******************************* 193--echo * Not supported Test#3 194--echo ******************************* 195 196--source ndb_show_tables_result.inc 197set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 198 199--error ER_ALTER_OPERATION_NOT_SUPPORTED 200ALTER TABLE t1 algorithm=inplace, ADD d INT AFTER b; 201--source ndb_show_tables_result.inc 202select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 203 204ALTER TABLE t1 ADD d INT AFTER b; 205--source ndb_show_tables_result.inc 206select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 207 208INSERT INTO t1 VALUES(3,1,1,'b'); 209SELECT * FROM t1 ORDER BY a; 210UPDATE t1 SET d = 2 where a = 3; 211SELECT * FROM t1 ORDER BY a; 212 213--echo ******************************* 214--echo * Not supported Test#4 215--echo ******************************* 216 217--source ndb_show_tables_result.inc 218set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 219 220--error ER_ALTER_OPERATION_NOT_SUPPORTED 221ALTER TABLE t1 algorithm=inplace, ENGINE MYISAM; 222--source ndb_show_tables_result.inc 223select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 224 225DROP TABLE t1; 226 227--echo ******************************* 228--echo * Not supported Test#5 229--echo ******************************* 230 231CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=DYNAMIC ENGINE NDB; 232INSERT INTO t1 values (1,1); 233 234--source ndb_show_tables_result.inc 235set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 236 237--error ER_ALTER_OPERATION_NOT_SUPPORTED 238ALTER TABLE t1 algorithm=inplace, ADD c TIMESTAMP; 239--source ndb_show_tables_result.inc 240select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 241 242ALTER TABLE t1 ADD c TIMESTAMP DEFAULT '2014-11-27 22:45:17'; 243 244INSERT INTO t1 values (2,2,'2007-09-19 18:46:02'); 245SELECT * FROM t1 ORDER BY a; 246UPDATE t1 SET c = '2007-10-22 16:35:06' where a = 2; 247SELECT * FROM t1 ORDER BY a; 248DROP TABLE t1; 249 250--echo ******************************* 251--echo * Not supported Test#6 252--echo ******************************* 253 254CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=DYNAMIC ENGINE NDB; 255INSERT INTO t1 values (1,1); 256 257--source ndb_show_tables_result.inc 258set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 259 260--error ER_ALTER_OPERATION_NOT_SUPPORTED 261ALTER TABLE t1 algorithm=inplace, ADD c CHAR(19) NOT NULL; 262--source ndb_show_tables_result.inc 263select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 264 265ALTER TABLE t1 ADD c CHAR(19) NOT NULL; 266 267INSERT INTO t1 values (2,1,"a"); 268SELECT * FROM t1 ORDER BY a; 269UPDATE t1 SET c = 'b' where a = 2; 270SELECT * FROM t1 ORDER BY a; 271DROP TABLE t1; 272 273--echo ******************************* 274--echo * Not supported Test#7 275--echo ******************************* 276 277CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED) ROW_FORMAT=DYNAMIC ENGINE NDB; 278INSERT INTO t1 values (1,1); 279 280--source ndb_show_tables_result.inc 281set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 282 283--error ER_ALTER_OPERATION_NOT_SUPPORTED 284ALTER TABLE t1 algorithm=inplace, ADD c CHAR(19) COLUMN_FORMAT FIXED; 285--source ndb_show_tables_result.inc 286select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 287 288ALTER TABLE t1 ADD c CHAR(19) COLUMN_FORMAT FIXED; 289 290INSERT INTO t1 values (2,1,"a"); 291SELECT * FROM t1 ORDER BY a; 292UPDATE t1 SET c = 'b' WHERE a = 2; 293SELECT * FROM t1 ORDER BY a; 294DROP TABLE t1; 295 296--echo ******************************* 297--echo * Not supported Test#8 298--echo * Ndb doesn't support renaming attributes on-line 299--echo ******************************* 300 301CREATE TABLE t1 ( 302 auto int(5) unsigned NOT NULL auto_increment, 303 string char(10), 304 vstring varchar(10), 305 bin binary(2), 306 vbin varbinary(7), 307 tiny tinyint(4) DEFAULT '0' NOT NULL , 308 short smallint(6) DEFAULT '1' NOT NULL , 309 medium mediumint(8) DEFAULT '0' NOT NULL, 310 long_int int(11) DEFAULT '0' NOT NULL, 311 longlong bigint(13) DEFAULT '0' NOT NULL, 312 real_float float(13,1) DEFAULT 0.0 NOT NULL, 313 real_double double(16,4), 314 real_decimal decimal(16,4), 315 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL, 316 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL, 317 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL, 318 ulong int(11) unsigned DEFAULT '0' NOT NULL, 319 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL, 320 bits bit(3), 321 options enum('zero','one','two','three','four') not null, 322 flags set('zero','one','two','three','four') not null, 323 date_field date, 324 year_field year, 325 time_field time, 326 date_time datetime, 327 time_stamp timestamp, 328 PRIMARY KEY (auto) 329) engine=ndb; 330 331--source ndb_show_tables_result.inc 332set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 333 334--error ER_ALTER_OPERATION_NOT_SUPPORTED 335alter table t1 algorithm=inplace, change tiny new_tiny tinyint(4) DEFAULT '0' NOT NULL; 336--source ndb_show_tables_result.inc 337select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 338 339alter table t1 change tiny new_tiny tinyint(4) DEFAULT '0' NOT NULL; 340 341create index i1 on t1(medium); 342alter table t1 add index i2(new_tiny); 343drop index i1 on t1; 344 345--source ndb_show_tables_result.inc 346select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 347 348DROP TABLE t1; 349 350##################################### 351# Adding dropping primary key 352###################################### 353# Bug:31233 354###################################### 355--echo **************************************** 356--echo * Adding dropping primary key 357--echo **************************************** 358CREATE TABLE t1 (a INT UNSIGNED NOT NULL) ENGINE NDB; 359--source show_primary_keys.inc 360--error ER_ALTER_OPERATION_NOT_SUPPORTED 361ALTER TABLE t1 algorithm=inplace, ADD PRIMARY KEY (a); 362ALTER TABLE t1 algorithm=copy, ADD PRIMARY KEY (a); 363--source show_primary_keys.inc 364--error ER_ALTER_OPERATION_NOT_SUPPORTED 365ALTER TABLE t1 algorithm=inplace, DROP PRIMARY KEY; 366ALTER TABLE t1 algorithm=copy, DROP PRIMARY KEY; 367--source show_primary_keys.inc 368--error ER_ALTER_OPERATION_NOT_SUPPORTED 369CREATE UNIQUE INDEX pk ON t1(a) algorithm=inplace; 370CREATE UNIQUE INDEX pk ON t1(a) algorithm=copy; 371--source show_primary_keys.inc 372--error ER_ALTER_OPERATION_NOT_SUPPORTED 373ALTER TABLE t1 algorithm=inplace, DROP INDEX PK; 374ALTER TABLE t1 algorithm=copy, DROP INDEX PK; 375--source show_primary_keys.inc 376DROP TABLE t1; 377 378CREATE TABLE t1 (a INT UNSIGNED) ENGINE NDB; 379--error ER_ALTER_OPERATION_NOT_SUPPORTED 380ALTER TABLE t1 algorithm=inplace, ADD b INT UNIQUE; 381ALTER TABLE t1 algorithm=copy, ADD b INT UNIQUE; 382--source show_primary_keys.inc 383--error ER_ALTER_OPERATION_NOT_SUPPORTED 384ALTER TABLE t1 algorithm=inplace, ADD c INT NOT NULL UNIQUE; 385ALTER TABLE t1 algorithm=copy, ADD c INT NOT NULL UNIQUE; 386--source show_primary_keys.inc 387DROP TABLE t1; 388 389###################################### 390# Alter dynmaic table, add TEXT column 391###################################### 392# Bug:30205 393###################################### 394--echo **************************************** 395--echo * Add column c as nullable TEXT and BLOB 396--echo **************************************** 397CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT KEY, b INT DEFAULT 2 COLUMN_FORMAT DYNAMIC) ENGINE NDB; 398--source ndb_show_tables_result.inc 399set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 400 401let $v=5; 402disable_query_log; 403while ($v) 404{ 405 INSERT INTO t1 VALUES(NULL, DEFAULT); 406 dec $v; 407} 408enable_query_log; 409--error ER_ALTER_OPERATION_NOT_SUPPORTED 410ALTER TABLE t1 algorithm=inplace, ADD c TEXT; 411--source ndb_show_tables_result.inc 412select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 413 414--error ER_ALTER_OPERATION_NOT_SUPPORTED 415ALTER TABLE t1 algorithm=inplace, ADD d BLOB; 416--source ndb_show_tables_result.inc 417select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 418 419DROP TABLE t1; 420 421###################################### 422# Alter dynmaic table, add column 423###################################### 424 425CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT KEY, b INT COLUMN_FORMAT DYNAMIC) ENGINE NDB; 426 427let $v=5; 428disable_query_log; 429while ($v) 430{ 431 --eval INSERT INTO t1 VALUES(NULL, $v); 432 dec $v; 433} 434enable_query_log; 435 436--source ndb_show_tables_result.inc 437set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); 438 439--echo ******************************* 440--echo * Add column c as nullable FLOAT 441--echo ******************************* 442ALTER TABLE t1 algorithm=inplace, ADD c FLOAT; 443 444let $v=5; 445disable_query_log; 446while ($v) 447{ 448 --eval INSERT INTO t1 VALUES(NULL, $v, -3.402823466E+38); 449 dec $v; 450} 451enable_query_log; 452 453--echo ******************************* 454--echo * Add column d as nullable DOUBLE 455--echo ******************************* 456ALTER TABLE t1 algorithm=inplace, ADD d DOUBLE UNSIGNED; 457 458let $v=5; 459disable_query_log; 460while ($v) 461{ 462 --eval INSERT INTO t1 VALUES(NULL, $v, -3.402823466E+38, 1.7976931348623E+308); 463 dec $v; 464} 465enable_query_log; 466 467--echo ******************************* 468--echo * Add column e as nullable DECIMAL 469--echo ******************************* 470ALTER TABLE t1 algorithm=inplace, ADD e DECIMAL(5,2); 471 472let $v=5; 473disable_query_log; 474while ($v) 475{ 476 --eval INSERT INTO t1 VALUES(NULL, $v, -3.402823466E+38, 1.7976931348623E+308, 345.21); 477 dec $v; 478} 479enable_query_log; 480 481--echo ******************************* 482--echo * Add column f as nullable DATETIME 483--echo ******************************* 484ALTER TABLE t1 algorithm=inplace, ADD f DATETIME; 485 486let $v=5; 487disable_query_log; 488while ($v) 489{ 490 --eval INSERT INTO t1 VALUES(NULL, $v, -3.402823466E+38, 1.7976931348623E+308, 345.21, '1000-01-01 00:00:00'); 491 dec $v; 492} 493enable_query_log; 494 495--echo ******************************* 496--echo * Add column g as nullable BINARY 497--echo ******************************* 498ALTER TABLE t1 ADD g BINARY(4); 499 500let $v=5; 501disable_query_log; 502while ($v) 503{ 504 --eval INSERT INTO t1 VALUES(NULL, $v, -3.402823466E+38, 1.7976931348623E+308, 345.21, '1000-01-01 00:00:00', '0101'); 505 dec $v; 506} 507enable_query_log; 508 509 510--source ndb_show_tables_result.inc 511select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; 512 513SELECT COUNT(*) FROM t1 WHERE c IS NULL; 514SELECT COUNT(*) FROM t1 WHERE d IS NULL; 515SELECT COUNT(*) FROM t1 WHERE e IS NULL; 516SELECT COUNT(*) FROM t1 WHERE f IS NULL; 517SELECT COUNT(*) FROM t1 WHERE g IS NULL; 518 519UPDATE t1 SET c = 3.402823466E+38, d = 1.2686868689898E+308, e = 666.66, f = '2007-10-23 23:23:23', g = '1111' WHERE a = 1; 520SELECT * FROM t1 WHERE a = 1 or a = 10 or a = 20 or a = 30 ORDER BY a; 521 522############################## 523# Backup and restore section # 524############################## 525--echo ********************************* 526--echo * Backup and restore tables w/ new column 527--echo ********************************* 528 529--source include/ndb_backup.inc 530 531DROP TABLE t1; 532 533--exec $NDB_RESTORE --no-defaults -b $the_backup_id -n 1 -m -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 534--exec $NDB_RESTORE --no-defaults -b $the_backup_id -n 2 -r --print --print_meta $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 535 536source show_varpart.inc; 537 538DROP TABLE t1; 539 540################################### 541# Disk Data Error testing section # 542################################### 543--echo ********************************* 544--echo * Disk Data error testing 545--echo ********************************* 546 547set default_storage_engine=ndb; 548 549CREATE LOGFILE GROUP lg1 550ADD UNDOFILE 'undofile.dat' 551INITIAL_SIZE 16M 552UNDO_BUFFER_SIZE = 1M; 553 554CREATE TABLESPACE ts1 555ADD DATAFILE 'datafile.dat' 556USE LOGFILE GROUP lg1 557INITIAL_SIZE 12M 558ENGINE NDB; 559 560CREATE TABLE t1 561(pk1 INT NOT NULL PRIMARY KEY, b INT COLUMN_FORMAT DYNAMIC) 562TABLESPACE ts1 STORAGE DISK 563ENGINE=NDB; 564 565--error ER_ALTER_OPERATION_NOT_SUPPORTED 566ALTER TABLE t1 algorithm=inplace, CHANGE b b_1 INT COLUMN_FORMAT DYNAMIC; 567--error ER_ALTER_OPERATION_NOT_SUPPORTED 568ALTER TABLE t1 algorithm=inplace, ADD COLUMN c INT COLUMN_FORMAT DYNAMIC; 569--error ER_ALTER_OPERATION_NOT_SUPPORTED 570ALTER TABLE t1 algorithm=inplace, ADD COLUMN d FLOAT COLUMN_FORMAT DYNAMIC; 571--error ER_ALTER_OPERATION_NOT_SUPPORTED 572ALTER TABLE t1 algorithm=inplace, ADD COLUMN e DOUBLE COLUMN_FORMAT DYNAMIC; 573--error ER_ALTER_OPERATION_NOT_SUPPORTED 574ALTER TABLE t1 algorithm=inplace, ADD COLUMN f DATETIME COLUMN_FORMAT DYNAMIC; 575--error ER_ALTER_OPERATION_NOT_SUPPORTED 576ALTER TABLE t1 algorithm=inplace, ADD COLUMN g DECIMAL(5,2) COLUMN_FORMAT DYNAMIC; 577--error ER_ALTER_OPERATION_NOT_SUPPORTED 578ALTER TABLE t1 algorithm=inplace, ADD COLUMN h CHAR(20) COLUMN_FORMAT DYNAMIC; 579--error ER_ALTER_OPERATION_NOT_SUPPORTED 580ALTER TABLE t1 algorithm=inplace, ADD COLUMN h VARCHAR(20) COLUMN_FORMAT DYNAMIC; 581--error ER_ALTER_OPERATION_NOT_SUPPORTED 582ALTER TABLE t1 algorithm=inplace, ADD COLUMN h BINARY(20) COLUMN_FORMAT DYNAMIC; 583--error ER_ALTER_OPERATION_NOT_SUPPORTED 584ALTER TABLE t1 algorithm=inplace, ADD COLUMN h VARBINARY(20) COLUMN_FORMAT DYNAMIC; 585DROP TABLE t1; 586 587# 588# bug#42549 589# 590create table t1 (a int primary key, b int) storage disk tablespace ts1 engine = ndb; 591--error ER_ALTER_OPERATION_NOT_SUPPORTED 592alter table t1 algorithm=inplace, add column c0 int null column_format DYNAMIC; 593alter table t1 algorithm=inplace, add column c1 int null column_format DYNAMIC storage memory; 594drop table t1; 595 596create table t1 (a int primary key, b int storage disk) tablespace ts1 engine = ndb; 597alter table t1 algorithm=inplace, add column c0 int null column_format DYNAMIC; 598alter table t1 algorithm=inplace, add column c1 int null column_format DYNAMIC storage memory; 599drop table t1; 600 601ALTER TABLESPACE ts1 602DROP DATAFILE 'datafile.dat' 603ENGINE = NDB; 604 605DROP TABLESPACE ts1 606ENGINE = NDB; 607 608DROP LOGFILE GROUP lg1 609ENGINE =NDB; 610 611############################## 612# ROW_FORMAT testing section # 613############################## 614--echo ******************** 615--echo * ROW_FORMAT testing 616--echo ******************** 617 618# Bug:30276, should issue a warning 619 620CREATE TABLE t1 621(pk1 INT NOT NULL PRIMARY KEY, b INT COLUMN_FORMAT DYNAMIC)ROW_FORMAT=FIXED 622ENGINE=NDB; 623 624source show_attributes.inc; 625 626DROP TABLE t1; 627 628CREATE TABLE t1 629(pk1 INT NOT NULL COLUMN_FORMAT FIXED PRIMARY KEY, 630b INT COLUMN_FORMAT FIXED)ROW_FORMAT=DYNAMIC ENGINE=NDB; 631 632source show_attributes.inc; 633 634DROP TABLE t1; 635 636--echo ******************** 637--echo * bug#44695 ALTER TABLE during START BACKUP crashes mysqld 638--echo ******************** 639# Testing failure of online alter during ongoing backup 640 641CREATE TABLE t1(k INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ROW_FORMAT=DYNAMIC ENGINE=NDB; 642# create some data to slow down backup 643INSERT INTO t1 VALUES (NULL); 644INSERT INTO t1 SELECT NULL FROM t1; 645INSERT INTO t1 SELECT NULL FROM t1; 646INSERT INTO t1 SELECT NULL FROM t1; 647INSERT INTO t1 SELECT NULL FROM t1; 648INSERT INTO t1 SELECT NULL FROM t1; 649INSERT INTO t1 SELECT NULL FROM t1; 650INSERT INTO t1 SELECT NULL FROM t1; 651INSERT INTO t1 SELECT NULL FROM t1; 652INSERT INTO t1 SELECT NULL FROM t1; 653INSERT INTO t1 SELECT NULL FROM t1; 654INSERT INTO t1 SELECT NULL FROM t1; 655INSERT INTO t1 SELECT NULL FROM t1; 656INSERT INTO t1 SELECT NULL FROM t1; 657INSERT INTO t1 SELECT NULL FROM t1; 658SELECT COUNT(*) FROM t1; 659--exec $NDB_MGM --no-defaults --ndb-connectstring="$NDB_CONNECTSTRING" -e "start backup nowait" >> $NDB_TOOLS_OUTPUT 660--disable_warnings 661--error 0,762,1296 662ALTER TABLE t1 algorithm=inplace, ADD b INT; 663# waut for backup to complete 664--sleep 10 665 666--enable_warnings 667 668DROP TABLE t1, ndb_show_tables_results; 669 670# 671# test alter of table with many attributes 672# 673let $i=499; 674let $separator=; 675let $sql=create table t1 (; 676while ($i) 677{ 678 let $sql=$sql$separator c$i int; 679 let $separator=,; 680 dec $i; 681} 682let $sql=$sql, c501 varchar(10000); 683let $sql=$sql, primary key using hash(c1)) engine=ndb; 684eval $sql; # eval the sql and create the table 685 686insert into t1 (c1) values (1), (2), (3); 687alter table t1 algorithm=copy, modify c1 int auto_increment; 688alter table t1 algorithm=inplace, add column c500 bit(1) column_format DYNAMIC; 689--error ER_TOO_BIG_ROWSIZE 690alter table t1 algorithm=copy, add column c502 varchar(2000); 691--error ER_TOO_BIG_ROWSIZE 692alter table t1 algorithm=inplace, add column c502 varchar(2000); 693delete from t1; 694drop table t1; 695 696 697# 698# Bug #13830980 MYSQL COMPLAINS OF NOT SUPPORTING ALTER ONLINE EVEN WHEN VALID OPTIONS ARE USED 699# 700 701create table t1(a int(10) unsigned not null auto_increment, 702 b varchar(20) default 'x', 703 c varchar(20) default null, 704 primary key (a) ) engine=ndbcluster; 705 706--error ER_ALTER_OPERATION_NOT_SUPPORTED 707alter table t1 algorithm=inplace, add e varchar(20) default 'x' column_format dynamic; 708alter table t1 algorithm=inplace, add e varchar(20) default null column_format dynamic; 709drop table t1; 710 711# 712# Bug #12755722 61528: INNODB BACKEND CRASHES ON ALTER TABLE STATEMENT (MYSQL SERVER HAS GONE AWAY 713# 714 715CREATE TABLE categorylinks ( 716 cl_from int(10) unsigned NOT NULL DEFAULT '0', 717 cl_to varbinary(255) NOT NULL DEFAULT '', 718 cl_sortkey varbinary(70) NOT NULL DEFAULT '', 719 cl_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 720CURRENT_TIMESTAMP, 721 UNIQUE KEY cl_from (cl_from,cl_to), 722 KEY cl_sortkey (cl_to,cl_sortkey,cl_from), 723 KEY cl_timestamp (cl_to,cl_timestamp) 724) ENGINE=ndb DEFAULT CHARSET=binary; 725 726ALTER TABLE categorylinks 727 CHANGE COLUMN cl_sortkey cl_sortkey varbinary(230) NOT NULL default 728'', 729 ADD COLUMN cl_sortkey_prefix varchar(255) binary NOT NULL default '', 730 ADD COLUMN cl_collation varbinary(32) NOT NULL default '', 731 ADD COLUMN cl_type ENUM('page', 'subcat', 'file') NOT NULL default 732'page', 733 ADD INDEX (cl_collation), 734 DROP INDEX cl_sortkey, 735 ADD INDEX cl_sortkey (cl_to, cl_type, cl_sortkey, cl_from); 736 737SHOW CREATE TABLE categorylinks; 738 739DROP TABLE categorylinks; 740