Debian Codegraph @

mysql-test/suite/ndb/t/ndb_alter_table_online.test

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