Debian Codegraph @

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

raw
1 #############################################################
2 # Author: Martin
3 # Date: 2018-06-27
4 # Purpose: basic online alter column test
5 ##############################################################
6 --source include/have_multi_ndb.inc
7 --source suite/ndb/include/backup_restore_setup.inc
8
9 # Create view which displays all user tables.
10 # NOTE! Uses a format which is backward compatible with the output
11 # from "ndb_show_tables" previously used by this test - for example names
12 # have surrounding quotes.
13 --disable_query_log ONCE
14 CREATE VIEW ndb_show_tables_results AS
15 SELECT
16 id,
17 /* Hardcoded since only user tables are selected */
18 'UserTable' AS type,
19 /* Table name is last part of fq_name */
20 CONCAT("'", CONCAT(SUBSTRING_INDEX(fq_name, "/", -1), "'")) AS name
21 FROM ndbinfo.dict_obj_info WHERE type = 2 /* User table */;
22
23 ######################################
24 # online alter column
25 ######################################
26 --echo *******************************
27 --echo * online alter column tests
28 --echo *******************************
29
30 CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED, e INT UNIQUE, f TEXT) ROW_FORMAT=DYNAMIC ENGINE NDB;
31 INSERT INTO t1 values (1,1,1,"One");
32
33 set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%');
34
35 --echo ***************************************
36 --echo * Alter Table online change column name
37 --echo ***************************************
38
39 ALTER TABLE t1 CHANGE b c INT UNSIGNED ;
40
41 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%';
42
43 --source show_attributes.inc
44
45 INSERT INTO t1 values (2,2,2,"Two");
46 SELECT * FROM t1 ORDER BY a;
47
48 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE c D INT UNSIGNED;
49
50 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%';
51
52 --source show_attributes.inc
53
54 INSERT INTO t1 values (3,3,3,"Three");
55 SELECT * FROM t1 ORDER BY a;
56
57 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE D d INT UNSIGNED;
58
59 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%';
60
61 --source show_attributes.inc
62
63 INSERT INTO t1 values (4,4,4,"Four");
64 SELECT * FROM t1 ORDER BY a;
65
66 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE f t TEXT;
67
68 select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%';
69
70 --source show_attributes.inc
71
72 INSERT INTO t1 values (5,5,5,"Five");
73 SELECT * FROM t1 ORDER BY a;
74
75 ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra1 VARCHAR(10);
76 ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra2 VARCHAR(10);
77 UPDATE t1 set extra1="XXXX", extra2="YYYY";
78 SELECT * FROM t1 ORDER BY a;
79 # Swap column names
80 ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN extra1 TO extra2, RENAME COLUMN extra2 TO extra1;
81 SELECT * FROM t1 ORDER BY a;
82
83 # Restart cluster nodes
84 --exec $NDB_MGM -e "all restart" >> $NDB_TOOLS_OUTPUT
85 # Wait for all nodes to start
86 --exec $NDB_WAITER
87
88 #
89 # Wait until the connection to the
90 # cluster has been restored or timeout occurs
91 #
92 connection default;
93 --disable_result_log
94 --disable_query_log
95 --source include/ndb_not_readonly.inc
96 --enable_result_log
97 --enable_query_log
98
99 SELECT * FROM t1 ORDER BY a;
100
101 ##############################
102 # Backup and restore section #
103 ##############################
104 --echo *********************************
105 --echo * Backup and restore tables w/ changed column
106 --echo *********************************
107
108 --source include/ndb_backup.inc
109
110 DROP TABLE t1;
111
112 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
113 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
114 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
115
116 SELECT * FROM t1 ORDER BY a;
117
118 --let $ndb_describe_table= t1
119 --let $ndb_describe_indexes= 1
120 --let $ndb_describe_blobs= 1
121 --source suite/ndb/include/ndb_describe_table.inc
122
123 --echo *********************************
124 --echo * Error tests
125 --echo *********************************
126
127 # Duplicate name
128 --error ER_DUP_FIELDNAME
129 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE d a INT UNSIGNED;
130
131 # Cannot rename primary key column
132 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
133 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE a pk INT UNSIGNED;
134
135 # Cannot rename indexed column
136 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
137 ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE e ue INT;
138
139 # Cannot rename column and add column
140 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
141 ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN D TO D2, ADD t2 CHAR(10);
142 DROP TABLE t1;
143
144 --source suite/ndb/include/backup_restore_cleanup.inc
145 --remove_file $NDB_TOOLS_OUTPUT
146
147 --disable_query_log ONCE
148 DROP VIEW ndb_show_tables_results;