Debian Codegraph @

mysql-test/suite/ndb_ddl/alter_inplace.test

raw
1 #
2 # Test variations of inplace alter table on all the defined tables
3 #
4 --source setup.inc
5
6
7
8 let $num_tables = `select count(*) from information_schema.tables
9 where TABLE_SCHEMA = 'ndb_ddl_test'`;
10 if (!$num_tables)
11 {
12 die Could not figure out number of tables in ndb_ddl_test database;
13 }
14
15
16 let $counter = 1;
17 while ($counter <= $num_tables)
18 {
19 # Default table name is t$counter, ie. t1, t2, etc
20 let $tx=t$counter;
21
22 # Save table id
23 let $table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
24 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
25
26 ##
27 # Test ALTER TABLE, ADD INDEX
28 #
29 eval ALTER TABLE $tx ALGORITHM=inplace, ADD INDEX a_idx1(a);
30
31 # Show that table id is still the same
32 let $new_table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
33 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
34 if ($table_id != $new_table_id)
35 {
36 die The table was not altered inplace, its table id changed;
37 }
38
39 # Check that table still contains expected number of rows
40 let $tx_count = `SELECT count(*) from $tx`;
41 if ($tx_count != 5)
42 {
43 echo Wrong number of rows, expected 5 got $tx_count;
44 die Wrong number of rows in altered table;
45 }
46
47 if ($counter == 6)
48 {
49 # Disable warnings for t6 where column 'a' is a VARCHAR and reads from the
50 # table causes "Truncated incorrect DOUBLE value: 'value1'" for each row
51 --disable_warnings ER_TRUNCATED_WRONG_VALUE ONCE
52 }
53
54 # Run query presumably using the new index
55 eval SELECT a FROM $tx WHERE a = 1;
56
57
58 ##
59 # Test ALTER TABLE, DROP INDEX
60 #
61 eval ALTER TABLE $tx ALGORITHM=inplace, DROP INDEX a_idx1;
62
63 # Show that table id is still the same
64 let $new_table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
65 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
66 if ($table_id != $new_table_id)
67 {
68 die The table was not altered inplace, its table id changed;
69 }
70
71 # Check that table still contains expected number of rows
72 let $tx_count = `SELECT count(*) from $tx`;
73 if ($tx_count != 5)
74 {
75 echo Wrong number of rows, expected 5 got $tx_count;
76 die Wrong number of rows in altered table;
77 }
78
79 if ($counter == 6)
80 {
81 # Disable warnings for t6 where column 'a' is a VARCHAR and reads from the
82 # table causes "Truncated incorrect DOUBLE value: 'value1'" for each row
83 --disable_warnings ER_TRUNCATED_WRONG_VALUE ONCE
84 }
85
86 # Run query
87 eval SELECT a FROM $tx WHERE a = 4;
88
89
90 ##
91 # Test ALTER TABLE, ADD COLUMN
92 #
93 let $skip_inplace_add_column = 0;
94 if (`select FIND_IN_SET('$tx', 't6,t8,t9')`)
95 {
96 # inplace add column not supported on table without primary key
97 let $skip_inplace_add_column = 1;
98
99 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
100 eval ALTER TABLE $tx ALGORITHM=inplace,
101 ADD COLUMN m VARCHAR(255) DEFAULT NULL;
102 }
103
104 if (!$skip_inplace_add_column)
105 {
106 eval ALTER TABLE $tx ALGORITHM=inplace,
107 ADD COLUMN m VARCHAR(255) COLUMN_FORMAT DYNAMIC DEFAULT NULL;
108
109 # Show that table id is still the same
110 let $new_table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
111 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
112 if ($table_id != $new_table_id)
113 {
114 die The table was not altered inplace, its table id changed;
115 }
116
117 # Check that table still contains expected number of rows
118 let $tx_count = `SELECT count(*) from $tx`;
119 if ($tx_count != 5)
120 {
121 echo Wrong number of rows, expected 5 got $tx_count;
122 die Wrong number of rows in altered table;
123 }
124
125 # Set values for the new column
126 eval UPDATE $tx SET m = CONCAT("row-", a);
127 eval SELECT m FROM $tx WHERE a = 3;
128
129 # Show that adding column with default current_timestamp is not supported
130 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
131 eval ALTER TABLE $tx ALGORITHM=inplace,
132 ADD COLUMN t TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
133 # Show that adding column with auto_increment is not supported
134 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
135 eval ALTER TABLE $tx ALGORITHM=inplace,
136 ADD COLUMN ai INT NOT NULL UNIQUE AUTO_INCREMENT;
137 }
138
139
140 ##
141 # Test ALTER TABLE, RENAME COLUMN
142 #
143 let $skip_inplace_rename_column = 0;
144 if (`select FIND_IN_SET('$tx', 't1,t2,t3,t4,t5,t6')`)
145 {
146 # inplace add column not supported on table without primary key
147 let $skip_inplace_rename_column = 1;
148
149 --error ER_BAD_FIELD_ERROR,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
150 eval ALTER TABLE $tx ALGORITHM=inplace,
151 RENAME COLUMN b TO b_new;
152 }
153
154 if (!$skip_inplace_rename_column)
155 {
156 eval ALTER TABLE $tx ALGORITHM=inplace,
157 RENAME COLUMN b TO b_new;
158 # Show that table id is still the same
159 let $new_table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
160 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
161 if ($table_id != $new_table_id)
162 {
163 die The table was not altered inplace, its table id changed;
164 }
165
166 # Run query
167 eval SELECT b_new FROM $tx WHERE a = 4;
168
169 eval ALTER TABLE $tx ALGORITHM=inplace,
170 RENAME COLUMN b_new TO b;
171 # Show that table id is still the same
172 let $new_table_id = `SELECT id FROM test.ndbinfo_dict_obj_info
173 WHERE fq_name = 'ndb_ddl_test/def/$tx'`;
174 if ($table_id != $new_table_id)
175 {
176 die The table was not altered inplace, its table id changed;
177 }
178 }
179
180 inc $counter;
181 }
182
183 --source verify_mysql_dd.inc
184
185 --source cleanup.inc