Debian Codegraph @

mysql-test/suite/innodb/t/mysqldump_max_recordsize.test

raw
1 # *********************************************************
2 # page size 32K ----> page size 16K
3 # start the DB with page size 32K
4 # create tables with record length = 16k
5 # take the dump wl5757.sql
6 # case 1 : start the DB with page size 16K
7 # load/import the dump file
8 # Check ERROR 1118 (42000) at line 106: Row size too large
9 # During the load time for the page size 16k
10 # case 2 : page size 32K ----> page size 64K
11 # start the DB with page size 64K
12 # Check no errors are expected during load time for page
13 # size 64K, because record length is same for 32k and 64k
14 # *********************************************************
15 --source include/not_valgrind.inc
16
17 let $MYSQLD_BASEDIR= `select @@basedir`;
18
19 # Create datadir path,rathan than mtr defaults path
20 --mkdir $MYSQL_TMP_DIR/datadir1
21
22 # Set different paths for --datadir
23 let $MYSQLD_DATADIR1 = $MYSQL_TMP_DIR/datadir1/data;
24
25 call mtr.add_suppression("ERROR 1118 \\(42000\\) at line 106: Row size too large \\(> 8126\\)");
26
27 # Stop the MTR default datadir, as it was started with default 16k page size
28 --echo # Stop DB server which was created by MTR default
29 --source include/shutdown_mysqld.inc
30
31 let BOOTSTRAP_SQL=$MYSQL_TMP_DIR/boot.sql;
32
33 --echo # create bootstrap file
34 write_file $BOOTSTRAP_SQL;
35 CREATE DATABASE test DEFAULT CHARACTER SET latin1;
36 EOF
37
38 # Set the bootstrap parameter page size 32k for datadir1
39 let NEW_CMD = $MYSQLD --no-defaults --innodb_dedicated_server=OFF --initialize-insecure --lc_messages_dir=$MYSQL_SHAREDIR --innodb_page_size=32K --basedir=$MYSQLD_BASEDIR --datadir=$MYSQLD_DATADIR1 --init-file=$BOOTSTRAP_SQL --secure-file-priv="" </dev/null>>$MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1;
40
41 --echo # Run the bootstrap command with page size 32k
42 --exec $NEW_CMD
43
44 --echo # Start the DB server with page size 32k
45 --replace_result $MYSQLD_DATADIR1 MYSQLD_DATADIR1
46 --let $restart_parameters="restart: --innodb_page_size=32K --datadir=$MYSQLD_DATADIR1"
47 # Wait for 300 seconds instead of the default 100
48 --let $wait_counter= 3000
49 --source include/start_mysqld.inc
50
51 USE test;
52
53 # page size 32K
54 SELECT @@innodb_page_size;
55
56 # Trying to create a table with record length < 16K ( 63 columns * 255 = 16065 )
57 CREATE TABLE tab1(col1 CHAR (255), col2 CHAR (255), col3 CHAR(255),col4 CHAR(255), col5 CHAR(255),
58 col6 CHAR(255), col7 CHAR(255), col8 CHAR(255), col9 CHAR(255),col10 CHAR(255), col11 CHAR(255),
59 col12 CHAR(255), col13 CHAR(255),col14 CHAR(255),col15 CHAR(255),col16 CHAR(255), col17 CHAR(255),
60 col18 CHAR(255),col19 CHAR(255),col20 CHAR(255),col21 CHAR(255),col22 CHAR(255), col23 CHAR(255),
61 col24 CHAR(255),col25 CHAR(255),col26 CHAR(255),col27 CHAR(255),col28 CHAR(255), col29 CHAR(255),
62 col30 CHAR(255),col31 CHAR(255),col32 CHAR(255),col33 CHAR(255),col34 CHAR(255), col35 CHAR(255),
63 col36 CHAR(255),col37 CHAR(255),col38 CHAR(255),col39 CHAR(255),col40 CHAR(255), col41 CHAR(255),
64 col42 CHAR(255),col43 CHAR(255),col44 CHAR(255),col45 CHAR(255),col46 CHAR(255), col47 CHAR(255),
65 col48 CHAR(255),col49 CHAR(255),col50 CHAR(255),col51 CHAR(255),col52 CHAR(255), col53 CHAR(255),
66 col54 CHAR(255),col55 CHAR(255),col56 CHAR(255),col57 CHAR(255),col58 CHAR(255), col59 CHAR(255),
67 col60 CHAR(255),col61 CHAR(255),col62 CHAR(255),col63 CHAR(255)) ENGINE = innodb ;
68
69 # Load data
70 INSERT INTO tab1 VALUES(repeat('a1',127),repeat('a2',127),repeat('a3',127),repeat('a4',127),
71 repeat('a5',127),repeat('a6',127),repeat('a7',127),repeat('a8',127),repeat('a9',127),repeat('a10',62),
72 repeat('a11',62),repeat('a12',62),repeat('a13',62),repeat('a14',62),repeat('a15',62),repeat('a16',62),
73 repeat('a17',62),repeat('a18',62),repeat('a19',62),repeat('a20',62),repeat('a21',62),
74 repeat('a22',62),repeat('a23',62),repeat('a24',62),repeat('a25',62),repeat('a26',62),
75 repeat('a27',62),repeat('a28',62),repeat('a29',62),repeat('a30',62),repeat('a31',62),
76 repeat('a32',62),repeat('a33',62),repeat('a34',62),repeat('a35',62),repeat('a36',62),
77 repeat('a37',62),repeat('a38',62),repeat('a39',62),repeat('a40',62),repeat('a41',62),
78 repeat('a42',62),repeat('a43',62),repeat('a44',62),repeat('a45',62),repeat('a46',62),
79 repeat('a47',62),repeat('a48',62),repeat('a49',62),repeat('a50',62),repeat('a51',62),
80 repeat('a52',62),repeat('a53',62),repeat('a54',62),repeat('a55',62),repeat('a56',62),
81 repeat('a57',62),repeat('a58',62),repeat('a59',62),repeat('a60',62),repeat('a61',62),
82 repeat('a62',62),repeat('a63',62));
83
84 # Trying to create a table with record length=16K ( 64 columns * 255 = 16384 )
85 SET innodb_strict_mode=OFF;
86 CREATE TABLE tab2(col1 CHAR (255), col2 CHAR (255), col3 CHAR(255),col4 CHAR(255), col5 CHAR(255),
87 col6 CHAR(255), col7 CHAR(255), col8 CHAR(255), col9 CHAR(255),col10 CHAR(255), col11 CHAR(255),
88 col12 CHAR(255), col13 CHAR(255),col14 CHAR(255),col15 CHAR(255),col16 CHAR(255), col17 CHAR(255),
89 col18 CHAR(255),col19 CHAR(255),col20 CHAR(255),col21 CHAR(255),col22 CHAR(255), col23 CHAR(255),
90 col24 CHAR(255),col25 CHAR(255),col26 CHAR(255),col27 CHAR(255),col28 CHAR(255), col29 CHAR(255),
91 col30 CHAR(255),col31 CHAR(255),col32 CHAR(255),col33 CHAR(255),col34 CHAR(255), col35 CHAR(255),
92 col36 CHAR(255),col37 CHAR(255),col38 CHAR(255),col39 CHAR(255),col40 CHAR(255), col41 CHAR(255),
93 col42 CHAR(255),col43 CHAR(255),col44 CHAR(255),col45 CHAR(255),col46 CHAR(255), col47 CHAR(255),
94 col48 CHAR(255),col49 CHAR(255),col50 CHAR(255),col51 CHAR(255),col52 CHAR(255), col53 CHAR(255),
95 col54 CHAR(255),col55 CHAR(255),col56 CHAR(255),col57 CHAR(255),col58 CHAR(255), col59 CHAR(255),
96 col60 CHAR(255),col61 CHAR(255),col62 CHAR(255),col63 CHAR(255),col64 CHAR(255)) ENGINE=innodb ROW_FORMAT=COMPACT;
97
98 # Load data , hit with an ERROR 1118 (42000): Row size too large (> 16318).
99 --error ER_TOO_BIG_ROWSIZE
100 INSERT INTO tab2 VALUES(repeat('a1',127),repeat('a2',127),repeat('a3',127),repeat('a4',127),
101 repeat('a5',127),repeat('a6',127),repeat('a7',127),repeat('a8',127),repeat('a9',127),repeat('a10',62),
102 repeat('a11',62),repeat('a12',62),repeat('a13',62),repeat('a14',62),repeat('a15',62),repeat('a16',62),
103 repeat('a17',62),repeat('a18',62),repeat('a19',62),repeat('a20',62),repeat('a21',62),
104 repeat('a22',62),repeat('a23',62),repeat('a24',62),repeat('a25',62),repeat('a26',62),
105 repeat('a27',62),repeat('a28',62),repeat('a29',62),repeat('a30',62),repeat('a31',62),
106 repeat('a32',62),repeat('a33',62),repeat('a34',62),repeat('a35',62),repeat('a36',62),
107 repeat('a37',62),repeat('a38',62),repeat('a39',62),repeat('a40',62),repeat('a41',62),
108 repeat('a42',62),repeat('a43',62),repeat('a44',62),repeat('a45',62),repeat('a46',62),
109 repeat('a47',62),repeat('a48',62),repeat('a49',62),repeat('a50',62),repeat('a51',62),
110 repeat('a52',62),repeat('a53',62),repeat('a54',62),repeat('a55',62),repeat('a56',62),
111 repeat('a57',62),repeat('a58',62),repeat('a59',62),repeat('a60',62),repeat('a61',62),
112 repeat('a62',62),repeat('a63',63),repeat('a64',63));
113
114 # Check for compressed tables
115 # ERROR 1118 (42000): Row size too large (> 16383).
116 CREATE TABLE tab3(col1 CHAR (255), col2 CHAR (255), col3 CHAR(255),col4 CHAR(255), col5 CHAR(255),
117 col6 CHAR(255), col7 CHAR(255), col8 CHAR(255), col9 CHAR(255),col10 CHAR(255), col11 CHAR(255),
118 col12 CHAR(255), col13 CHAR(255),col14 CHAR(255),col15 CHAR(255),col16 CHAR(255), col17 CHAR(255),
119 col18 CHAR(255),col19 CHAR(255),col20 CHAR(255),col21 CHAR(255),col22 CHAR(255), col23 CHAR(255),
120 col24 CHAR(255),col25 CHAR(255),col26 CHAR(255),col27 CHAR(255),col28 CHAR(255), col29 CHAR(255),
121 col30 CHAR(255),col31 CHAR(255),col32 CHAR(255),col33 CHAR(255),col34 CHAR(255), col35 CHAR(255),
122 col36 CHAR(255),col37 CHAR(255),col38 CHAR(255),col39 CHAR(255),col40 CHAR(255), col41 CHAR(255),
123 col42 CHAR(255),col43 CHAR(255),col44 CHAR(255),col45 CHAR(255),col46 CHAR(255), col47 CHAR(255),
124 col48 CHAR(255),col49 CHAR(255),col50 CHAR(255),col51 CHAR(255),col52 CHAR(255), col53 CHAR(255),
125 col54 CHAR(255),col55 CHAR(255),col56 CHAR(255),col57 CHAR(255),col58 CHAR(255), col59 CHAR(255),
126 col60 CHAR(255),col61 CHAR(255),col62 CHAR(255),col63 CHAR(255)) ENGINE = innodb ROW_FORMAT=COMPRESSED;
127
128 INSERT INTO tab3 VALUES(repeat('a1',127),repeat('a2',127),repeat('a3',127),repeat('a4',127),
129 repeat('a5',127),repeat('a6',127),repeat('a7',127),repeat('a8',127),repeat('a9',127),repeat('a10',62),
130 repeat('a11',62),repeat('a12',62),repeat('a13',62),repeat('a14',62),repeat('a15',62),repeat('a16',62),
131 repeat('a17',62),repeat('a18',62),repeat('a19',62),repeat('a20',62),repeat('a21',62),
132 repeat('a22',62),repeat('a23',62),repeat('a24',62),repeat('a25',62),repeat('a26',62),
133 repeat('a27',62),repeat('a28',62),repeat('a29',62),repeat('a30',62),repeat('a31',62),
134 repeat('a32',62),repeat('a33',62),repeat('a34',62),repeat('a35',62),repeat('a36',62),
135 repeat('a37',62),repeat('a38',62),repeat('a39',62),repeat('a40',62),repeat('a41',62),
136 repeat('a42',62),repeat('a43',62),repeat('a44',62),repeat('a45',62),repeat('a46',62),
137 repeat('a47',62),repeat('a48',62),repeat('a49',62),repeat('a50',62),repeat('a51',62),
138 repeat('a52',62),repeat('a53',62),repeat('a54',62),repeat('a55',62),repeat('a56',62),
139 repeat('a57',62),repeat('a58',62),repeat('a59',62),repeat('a60',62),repeat('a61',62),
140 repeat('a62',62),repeat('a63',62));
141
142 # Check record count 1
143 SELECT count(*) FROM tab1;
144
145 # Check record count 0
146 SELECT count(*) FROM tab2;
147
148 # Check record count 1
149 SELECT count(*) FROM tab3;
150
151 # take the dump when page size is 32K
152 --exec $MYSQL_DUMP --databases test > $MYSQLTEST_VARDIR/tmp/wl5757.sql
153
154 DROP DATABASE test;
155
156 # Stop the server which was started with page size 32K
157 --source include/shutdown_mysqld.inc
158
159 # Remove residue files and data folder of page size 32K
160 --force-rmdir $MYSQL_TMP_DIR/datadir1/data
161
162 # Create datadir path,rathan than mtr defaults path
163 --mkdir $MYSQL_TMP_DIR/datadir1/data
164
165 # Set the bootstrap parameter page size 16k for datadir1
166 let NEW_CMD = $MYSQLD --no-defaults --innodb_dedicated_server=OFF --initialize-insecure --lc_messages_dir=$MYSQL_SHAREDIR --innodb_page_size=16K --basedir=$MYSQLD_BASEDIR --datadir=$MYSQLD_DATADIR1 --init-file=$BOOTSTRAP_SQL --secure-file-priv="" </dev/null>>$MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1;
167
168 --echo # Run the bootstrap command with page size 16k
169 --exec $NEW_CMD
170
171 --echo # Start the DB server with page size 16k
172 --replace_result $MYSQLD_DATADIR1 MYSQLD_DATADIR1
173 --let $restart_parameters="restart: --innodb_page_size=16K --datadir=$MYSQLD_DATADIR1"
174 # Wait for 300 seconds instead of the default 100
175 --let $wait_counter= 3000
176 --source include/start_mysqld.inc
177
178 # page size 16K
179 SELECT @@innodb_page_size;
180
181 # check strict mode ON
182 SELECT @@innodb_strict_mode;
183
184 # Here load fails with ERROR 1118 (42000) at line 106: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC.
185 # Hence running the Load command with force operation, hence added same in above add_suppression function.
186 --exec $MYSQL --force < $MYSQLTEST_VARDIR/tmp/wl5757.sql
187
188 # Check "Cannot create the table `test`.`tab1` because the record size will exceed the maximum allowed size for a record."
189 let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
190 let SEARCH_PATTERN = \\[ERROR\\] .*MY-\\d+.* Cannot create the table `test`.`tab1` because the record size will exceed the maximum allowed size for a record.;
191 --source include/search_pattern.inc
192
193 # Check "Cannot create the table `test`.`tab2` because the record size will exceed the maximum allowed size for a record."
194 let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
195 let SEARCH_PATTERN = \\[ERROR\\] .*MY-\\d+.* Cannot create the table `test`.`tab2` because the record size will exceed the maximum allowed size for a record.;
196 --source include/search_pattern.inc
197
198 USE test;
199
200 # Check tables should exist.
201 SHOW TABLES;
202
203 # Check table does not exist
204 --error ER_NO_SUCH_TABLE
205 SELECT count(*) FROM tab1;
206
207 # Check table does not exist
208 --error ER_NO_SUCH_TABLE
209 SELECT count(*) FROM tab2;
210
211 # Check table does not exist
212 --error ER_NO_SUCH_TABLE
213 SELECT count(*) FROM tab3;
214
215 # cleanup
216 DROP DATABASE test;
217
218 # Stop the server which was started with page size 16K
219 --source include/shutdown_mysqld.inc
220
221 # Remove residue files and data folder of page size 16K
222 --force-rmdir $MYSQL_TMP_DIR/datadir1/data
223
224 # Create datadir path,rathan than mtr defaults path
225 --mkdir $MYSQL_TMP_DIR/datadir1/data
226
227 # Set the bootstrap parameter page size 64k for datadir1
228 let NEW_CMD = $MYSQLD --no-defaults --innodb_dedicated_server=OFF --initialize-insecure --lc_messages_dir=$MYSQL_SHAREDIR --innodb_page_size=64K --basedir=$MYSQLD_BASEDIR --datadir=$MYSQLD_DATADIR1 --init-file=$BOOTSTRAP_SQL --secure-file-priv="" </dev/null>>$MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1;
229
230 --echo # Run the bootstrap command with page size 64k
231 --exec $NEW_CMD
232
233 --echo # Start the DB server with page size 64k
234 --replace_result $MYSQLD_DATADIR1 MYSQLD_DATADIR1
235 --let restart_parameters="restart: --innodb_page_size=64K --datadir=$MYSQLD_DATADIR1"
236 # Wait for 300 seconds instead of the default 100
237 --let $wait_counter= 3000
238 --source include/start_mysqld.inc
239
240 # page size 64K
241 SELECT @@innodb_page_size;
242
243 # check strict mode ON
244 SELECT @@innodb_strict_mode;
245
246 # No errors are expected as for 32k/64k the max record length is same, hence compatible
247 --exec $MYSQL --force < $MYSQLTEST_VARDIR/tmp/wl5757.sql
248
249 USE test;
250
251 # Check tables should exist.
252 SHOW TABLES;
253
254 # Check record count 1
255 SELECT count(*) FROM tab1;
256
257 # Check record count 0
258 SELECT count(*) FROM tab2;
259
260 # Check record count 1
261 SELECT count(*) FROM tab3;
262
263 SET innodb_strict_mode=Default;
264
265 --let $restart_parameters=
266 # Wait for 300 seconds instead of the default 100
267 --let $wait_counter= 3000
268 --source include/restart_mysqld.inc
269
270 --disable_query_log
271 call mtr.add_suppression("Cannot create the table .* because the record size will exceed the maximum allowed size for a record.");
272 --enable_query_log
273 --remove_file $BOOTSTRAP_SQL
274 # Remove residue files and data folder of page size 32K
275 --force-rmdir $MYSQL_TMP_DIR/datadir1
276 --remove_file $MYSQLTEST_VARDIR/tmp/wl5757.sql
277 --remove_file $MYSQLTEST_VARDIR/tmp/bootstrap.log