Debian Codegraph @

mysql-test/suite/innodb/r/create_tablespace_32k.result

raw
1 #
2 # CREATE TABLESPACE related tests for 32k page sizes.
3 #
4 SET DEFAULT_STORAGE_ENGINE=InnoDB;
5 # Strict-mode has no effect on CREATE TABLESPACE. But it does affect
6 # whether an invalid KEY_BLOCK_SIZE is rejected or adjusted.
7 SHOW VARIABLES LIKE 'innodb_strict_mode';
8 Variable_name Value
9 innodb_strict_mode ON
10 SHOW VARIABLES LIKE 'innodb_file_per_table';
11 Variable_name Value
12 innodb_file_per_table ON
13 #
14 # Create a tablespace with compressed page sizes that can match
15 # innodb-page-size.
16 #
17 CREATE TABLESPACE s_1k ADD DATAFILE 's_1k.ibd' FILE_BLOCK_SIZE=1k;
18 ERROR HY000: InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
19 SHOW WARNINGS;
20 Level Code Message
21 Error 1478 InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
22 Error 1528 Failed to create TABLESPACE s_1k
23 Error 1031 Table storage engine for 's_1k' doesn't have this option
24 CREATE TABLESPACE s_2k ADD DATAFILE 's_2k.ibd' FILE_BLOCK_SIZE=2k;
25 ERROR HY000: InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
26 SHOW WARNINGS;
27 Level Code Message
28 Error 1478 InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
29 Error 1528 Failed to create TABLESPACE s_2k
30 Error 1031 Table storage engine for 's_2k' doesn't have this option
31 CREATE TABLESPACE s_4k ADD DATAFILE 's_4k.ibd' FILE_BLOCK_SIZE=4k;
32 ERROR HY000: InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
33 SHOW WARNINGS;
34 Level Code Message
35 Error 1478 InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
36 Error 1528 Failed to create TABLESPACE s_4k
37 Error 1031 Table storage engine for 's_4k' doesn't have this option
38 CREATE TABLESPACE s_8k ADD DATAFILE 's_8k.ibd' FILE_BLOCK_SIZE=8k;
39 ERROR HY000: InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
40 SHOW WARNINGS;
41 Level Code Message
42 Error 1478 InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
43 Error 1528 Failed to create TABLESPACE s_8k
44 Error 1031 Table storage engine for 's_8k' doesn't have this option
45 CREATE TABLESPACE s_16k ADD DATAFILE 's_16k.ibd' FILE_BLOCK_SIZE=16k;
46 ERROR HY000: InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
47 SHOW WARNINGS;
48 Level Code Message
49 Error 1478 InnoDB: Cannot create a COMPRESSED tablespace when innodb_page_size > 16k.
50 Error 1528 Failed to create TABLESPACE s_16k
51 Error 1031 Table storage engine for 's_16k' doesn't have this option
52 CREATE TABLESPACE s_32k ADD DATAFILE 's_32k.ibd' FILE_BLOCK_SIZE=32k;
53 CREATE TABLESPACE s_64k ADD DATAFILE 's_64k.ibd' FILE_BLOCK_SIZE=64k;
54 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=32768.
55 SHOW WARNINGS;
56 Level Code Message
57 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=65536 because INNODB_PAGE_SIZE=32768.
58 Error 1528 Failed to create TABLESPACE s_64k
59 Error 1031 Table storage engine for 's_64k' doesn't have this option
60 === information_schema.innodb_tablespaces and innodb_datafiles ===
61 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
62 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
63 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
64 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
65 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
66 s_32k General DEFAULT 0 1 1 Any s_32k.ibd
67 === information_schema.files ===
68 Space_Name File_Type Engine Status Tablespace_Name Path
69 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
70 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
71 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
72 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
73 s_32k TABLESPACE InnoDB NORMAL s_32k MYSQLD_DATADIR/s_32k.ibd
74 #
75 # Add tables to the tablespaces.
76 #
77 CREATE TABLE t_zip1k_in_321k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=1 TABLESPACE s_32k;
78 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
79 SHOW WARNINGS;
80 Level Code Message
81 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
82 Error 1031 Table storage engine for 't_zip1k_in_321k' doesn't have this option
83 CREATE TABLE t_zip2k_in_32k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2 TABLESPACE s_32k;
84 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
85 SHOW WARNINGS;
86 Level Code Message
87 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
88 Error 1031 Table storage engine for 't_zip2k_in_32k' doesn't have this option
89 CREATE TABLE t_zip4k_in_32k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 TABLESPACE s_32k;
90 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
91 SHOW WARNINGS;
92 Level Code Message
93 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
94 Error 1031 Table storage engine for 't_zip4k_in_32k' doesn't have this option
95 CREATE TABLE t_zip8k_in_32k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8 TABLESPACE s_32k;
96 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
97 SHOW WARNINGS;
98 Level Code Message
99 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
100 Error 1031 Table storage engine for 't_zip8k_in_32k' doesn't have this option
101 CREATE TABLE t_zip16k_in_32k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=16 TABLESPACE s_32k;
102 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
103 SHOW WARNINGS;
104 Level Code Message
105 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
106 Error 1031 Table storage engine for 't_zip16k_in_32k' doesn't have this option
107 CREATE TABLE t_zip32k_in_32k (a int, b text) ROW_FORMAT=Compressed KEY_BLOCK_SIZE=32 TABLESPACE s_32k;
108 ERROR HY000: InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
109 SHOW WARNINGS;
110 Level Code Message
111 Error 1478 InnoDB: Tablespace `s_32k` cannot contain a COMPRESSED table
112 Error 1031 Table storage engine for 't_zip32k_in_32k' doesn't have this option
113 CREATE TABLE t_red_in_32k (a int, b text) ROW_FORMAT=redundant TABLESPACE s_32k;
114 CREATE TABLE t_com_in_32k (a int, b text) ROW_FORMAT=compact TABLESPACE s_32k;
115 CREATE TABLE t_dyn_in_32k (a int, b text) ROW_FORMAT=dynamic TABLESPACE s_32k;
116 # Add data to the existing Tables
117 INSERT INTO t_red_in_32k VALUES (1,'a'),(2,'b'),(3,'c');
118 INSERT INTO t_com_in_32k VALUES (1,'a'),(2,'b'),(3,'c');
119 INSERT INTO t_dyn_in_32k VALUES (1,'a'),(2,'b'),(3,'c');
120 # Restart mysqld
121 # restart
122 #
123 # Try to drop a tablespace which is not empty
124 #
125 DROP TABLESPACE s_32k;
126 ERROR HY000: Tablespace `s_32k` is not empty.
127 #
128 # Add more data to the existing Tables
129 #
130 INSERT INTO t_red_in_32k VALUES (4,'d');
131 INSERT INTO t_com_in_32k VALUES (4,'d');
132 INSERT INTO t_dyn_in_32k VALUES (4,'d');
133 #
134 # Restart the server and make sure that everything is OK.
135 #
136 # restart
137 === information_schema.innodb_tablespaces and innodb_datafiles ===
138 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
139 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
140 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
141 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
142 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
143 s_32k General DEFAULT 0 1 1 Any s_32k.ibd
144 === information_schema.files ===
145 Space_Name File_Type Engine Status Tablespace_Name Path
146 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
147 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
148 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
149 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
150 s_32k TABLESPACE InnoDB NORMAL s_32k MYSQLD_DATADIR/s_32k.ibd
151 === information_schema.innodb_tables and innodb_tablespaces ===
152 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
153 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
154 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
155 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
156 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
157 test/t_com_in_32k s_32k 129 5 Compact 0 General
158 test/t_dyn_in_32k s_32k 161 5 Dynamic 0 General
159 test/t_red_in_32k s_32k 128 5 Redundant 0 General
160 # Directory of MYSQLD_DATADIR/
161 mysql.ibd
162 s_32k.ibd
163 # Directory of MYSQLD_DATADIR/test/
164 #
165 # Clean-up.
166 #
167 CHECK TABLE t_red_in_32k;
168 Table Op Msg_type Msg_text
169 test.t_red_in_32k check status OK
170 CHECK TABLE t_com_in_32k;
171 Table Op Msg_type Msg_text
172 test.t_com_in_32k check status OK
173 CHECK TABLE t_dyn_in_32k;
174 Table Op Msg_type Msg_text
175 test.t_dyn_in_32k check status OK
176 DROP TABLE t_red_in_32k;
177 DROP TABLE t_com_in_32k;
178 DROP TABLE t_dyn_in_32k;
179 DROP TABLESPACE s_32k;