Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test

raw
1 -- source include/mysql_upgrade_preparation.inc
2 call mtr.add_suppression("Table rebuild required");
3
4 let $MYSQLD_DATADIR= `select @@datadir`;
5
6 --enable_prepare_warnings
7
8 --echo #
9 --echo # Opening a Maria-10.2.26 table with a stored VARCHAR column
10 --echo #
11
12 --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
13 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm
14 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD
15 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI
16
17 SHOW CREATE TABLE t1;
18 SHOW CREATE TABLE t1;
19 FLUSH TABLES;
20 SHOW CREATE TABLE t1;
21
22 FLUSH TABLES;
23 SELECT * FROM t1;
24 SELECT * FROM t1;
25 FLUSH TABLES;
26 SELECT * FROM t1;
27
28 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
29 CREATE TABLE t2 LIKE t1;
30 FLUSH TABLES;
31 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
32 CREATE TABLE t2 LIKE t1;
33
34 SHOW CREATE TABLE t1;
35 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
36 ALTER TABLE t1 ADD b INT DEFAULT a;
37 SHOW CREATE TABLE t1;
38 SELECT * FROM t1;
39 FLUSH TABLES;
40 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
41 ALTER TABLE t1 ADD c INT DEFAULT a;
42 SELECT * FROM t1;
43 DROP TABLE t1;
44
45
46 --echo #
47 --echo # Fixing a Maria-10.2.26 table with a stored VARCHAR column
48 --echo #
49
50 --echo # Fixing by dropping the generated stored column
51 --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
52 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm
53 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD
54 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI
55
56 ALTER TABLE t1 DROP v;
57 SELECT * FROM t1;
58 DROP TABLE t1;
59
60 --echo # Fixing by altering the generation expression of the stored column
61 --echo # Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR
62 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm
63 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD
64 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI
65
66 SHOW CREATE TABLE t1;
67 ALTER TABLE t1 MODIFY v VARCHAR(5) AS (RTRIM(a)) PERSISTENT;
68 SHOW CREATE TABLE t1;
69 DROP TABLE t1;
70
71
72
73 --echo #
74 --echo # Opening a Maria-10.2.26 table with a virtual VARCHAR column
75 --echo #
76
77 --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
78 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
79 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
80 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
81
82 SHOW CREATE TABLE t1;
83 SHOW CREATE TABLE t1;
84 FLUSH TABLES;
85 SHOW CREATE TABLE t1;
86
87 FLUSH TABLES;
88 SELECT * FROM t1;
89 SELECT * FROM t1;
90 FLUSH TABLES;
91 SELECT * FROM t1;
92
93 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
94 CREATE TABLE t2 LIKE t1;
95 FLUSH TABLES;
96 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
97 CREATE TABLE t2 LIKE t1;
98
99
100 SHOW CREATE TABLE t1;
101 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
102 ALTER TABLE t1 ADD b INT DEFAULT a;
103 FLUSH TABLES;
104 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
105 ALTER TABLE t1 ADD c INT DEFAULT a;
106 SELECT * FROM t1;
107 DROP TABLE t1;
108
109
110 --echo #
111 --echo # Fixing a Maria-10.2.26 table with a virtual VARCHAR column
112 --echo #
113
114 --echo # Fixing by dropping the virtual column
115 --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
116 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
117 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
118 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
119
120 SHOW CREATE TABLE t1;
121 ALTER TABLE t1 DROP v;
122 SELECT * FROM t1;
123 DROP TABLE t1;
124
125 --echo # Fixing by dropping a key on a virtual column, using ALTER TABLE
126 --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
127 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
128 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
129 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
130
131 SHOW CREATE TABLE t1;
132 ALTER TABLE t1 DROP KEY v;
133 SHOW CREATE TABLE t1;
134 SELECT * FROM t1;
135 DROP TABLE t1;
136
137
138 --echo # Fixing by dropping a key on a virtual column, using DROP INDEX
139 --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
140 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
141 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
142 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
143
144 DROP INDEX v ON t1;
145 SHOW CREATE TABLE t1;
146 SELECT * FROM t1;
147 DROP TABLE t1;
148
149
150 --echo # Fixing by altering the generation expression of a virtual column
151 --echo # Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR
152 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
153 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
154 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
155
156 SHOW CREATE TABLE t1;
157 ALTER TABLE t1 MODIFY v VARCHAR(5) AS(RTRIM(a)) VIRTUAL;
158 SHOW CREATE TABLE t1;
159 SELECT * FROM t1;
160 CREATE TABLE t2 LIKE t1;
161 SHOW CREATE TABLE t2;
162 DROP TABLE t2;
163 DROP TABLE t1;
164
165
166 --echo #
167 --echo # Upgrading a Maria-10.2.26 table with a stored column
168 --echo #
169
170 --echo # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR
171 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.frm $MYSQLD_DATADIR/test/t1.frm
172 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYD $MYSQLD_DATADIR/test/t1.MYD
173 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_stored.MYI $MYSQLD_DATADIR/test/t1.MYI
174
175 CHECK TABLE t1 FOR UPGRADE;
176 FLUSH TABLES;
177 CHECK TABLE t1 FOR UPGRADE;
178 DROP TABLE t1;
179
180
181 --echo #
182 --echo # Upgrading a Maria-10.2.26 table with a virtual column
183 --echo #
184
185 --echo # Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR
186 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.frm $MYSQLD_DATADIR/test/t1.frm
187 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYD $MYSQLD_DATADIR/test/t1.MYD
188 --copy_file std_data/vcol_upgrade/maria100226_char_to_vchar_virtual.MYI $MYSQLD_DATADIR/test/t1.MYI
189
190 CHECK TABLE t1 FOR UPGRADE;
191 FLUSH TABLES;
192 CHECK TABLE t1 FOR UPGRADE;
193 DROP TABLE t1;