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