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