Debian Codegraph @

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

raw
1 # create bootstrap file
2 # Stop the MTR default DB server
3 # Run the bootstrap command
4 # restart: --datadir=MYSQLD_DATADIR --innodb_redo_log_capacity=LOG_CAPACITY
5 CREATE TABLE t1 (a INT);
6 CREATE TABLE t2 (a INT PRIMARY KEY, b BLOB);
7 CREATE TABLE t3 (a INT PRIMARY KEY, b SMALLINT, c TEXT, d JSON, UNIQUE KEY k1(b), KEY k2(b, c(20)));
8 CREATE TABLE t4 (a INT PRIMARY KEY, b TINYINT, c MEDIUMINT, d BIGINT);
9 CREATE TABLE t5 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a,b), c DECIMAL(5,2), d FLOAT(7,4), e DOUBLE);
10 Warnings:
11 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
12 create table `t6` (
13 `c0291` char(140) not null,
14 `c0292` int(10) unsigned zerofill not null,
15 `c0293` time(3) default null,
16 `c0294` int(11) not null,
17 `c0295` binary(1) not null,
18 `c0296` datetime(6) not null,
19 `c0297` date default null,
20 `c0298` mediumtext not null,
21 `c0299` int(10) unsigned zerofill not null,
22 `c0300` decimal(54,6) not null,
23 primary key (`c0296`,`c0298`(186)),
24 unique key `i0002` (`c0294`),
25 unique key `i0005` (`c0298`(102)),
26 unique key `i0007` (`c0299`,`c0292`,`c0297`),
27 unique key `i0008` (`c0296`,`c0293`),
28 unique key `i0001` (`c0297`,`c0298`(240),`c0296`,`c0293`,`c0294`),
29 unique key `i0004` (`c0296`,`c0295`,`c0293`,`c0291`(95),`c0298`(215)),
30 key `i0003` (`c0296`,`c0291`(26)),
31 key `i0006` (`c0297`,`c0296`,`c0293`,`c0294`),
32 key `i0009` (`c0299`,`c0292`,`c0300`) using btree
33 ) engine=innodb default charset=latin1 row_format=compact;
34 Warnings:
35 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
36 Warning 1681 Integer display width is deprecated and will be removed in a future release.
37 Warning 1681 Integer display width is deprecated and will be removed in a future release.
38 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
39 Warning 1681 Integer display width is deprecated and will be removed in a future release.
40 CREATE TABLE t7(
41 a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
42 b INT NOT NULL DEFAULT '123',
43 c TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00'
44 ) ROW_FORMAT=REDUNDANT ENGINE=InnoDB;
45 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
46 CREATE TABLE t8(fld1 INT NOT NULL PRIMARY KEY) TABLESPACE=ts1 ENGINE=InnoDB;
47 CREATE TABLE t9(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
48 FOREIGN KEY(fld1) REFERENCES t8(fld1) ON UPDATE CASCADE) TABLESPACE=ts1 ENGINE=InnoDB;
49 CREATE TABLE t10(a INT PRIMARY KEY, b JSON, c BLOB, d LONGBLOB, e MEDIUMBLOB, f TINYBLOB);
50 CREATE TABLE t11 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10),
51 j INT, m INT GENERATED ALWAYS AS(b + x), n VARCHAR(10),
52 p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, y)),
53 x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)) ENGINE=InnoDB;
54 CREATE TABLE t12 (
55 id INT NOT NULL,
56 store_id INT NOT NULL,
57 x INT GENERATED ALWAYS AS (id + store_id)
58 ) ENGINE=InnoDB
59 PARTITION BY RANGE (store_id) (
60 PARTITION p0 VALUES LESS THAN (6),
61 PARTITION p1 VALUES LESS THAN (11),
62 PARTITION p2 VALUES LESS THAN (16),
63 PARTITION p3 VALUES LESS THAN (21)
64 );
65 CREATE TABLE t13 (
66 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
67 id int(10) not null ,
68 first_name varchar(50) NOT NULL,
69 last_name varchar(50) NOT NULL,
70 PRIMARY KEY (FTS_DOC_ID),
71 UNIQUE KEY idx_1 (first_name, last_name),
72 FULLTEXT KEY `idx_2` (first_name)
73 ) ENGINE=InnoDB;
74 Warnings:
75 Warning 1681 Integer display width is deprecated and will be removed in a future release.
76 Warning 1681 Integer display width is deprecated and will be removed in a future release.
77 CREATE TABLE t14 (
78 i int(10) unsigned not null auto_increment primary key,
79 a varchar(255) not null,
80 FULLTEXT KEY (a)
81 ) ENGINE = INNODB;
82 Warnings:
83 Warning 1681 Integer display width is deprecated and will be removed in a future release.
84 create table t15 (c1 int, c2 geometry not null SRID 0, spatial index (c2)) engine=innodb;
85 CREATE TABLE t16 (
86 a INT,
87 b INT,
88 c INT,
89 d INT,
90 e INT,
91 f INT,
92 PRIMARY KEY (a),
93 INDEX i1 (b),
94 INDEX i2 (c),
95 INDEX i3 (d),
96 INDEX i4 (e)
97 ) ENGINE=INNODB TABLESPACE=`innodb_system`;
98 SET GLOBAL innodb_file_per_table=OFF;
99 CREATE TABLE t17(
100 c1 iNT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
101 SET GLOBAL innodb_file_per_table=default;
102 SET NAMES UTF8;
103 Warnings:
104 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
105 CREATE TABLE t18(హి INT, శరెెనిక BLOB);
106 #
107 # Bug#29020745: INNODB TABLESPACES WITH SUBPARTITIONS DO NOT
108 # CONTAIN TABLE SDI OBJECT
109 #
110 # Bug#30326020: SUBPARTITIONING NOT REFLECTED IN SDI
111 #
112 CREATE TABLE t18_ (a INT NOT NULL, b INT)
113 PARTITION BY RANGE (a) PARTITIONS 3 SUBPARTITION BY KEY (b) (
114 PARTITION p1 VALUES LESS THAN (200) (
115 SUBPARTITION p11,
116 SUBPARTITION p12,
117 SUBPARTITION p13),
118 PARTITION p2 VALUES LESS THAN (600) (
119 SUBPARTITION p21,
120 SUBPARTITION p22,
121 SUBPARTITION p23),
122 PARTITION p3 VALUES LESS THAN (1800) (
123 SUBPARTITION p31,
124 SUBPARTITION p32,
125 SUBPARTITION p33));
126 # Check SDI from mysql.ibd
127 # Check SDI from t1.ibd
128 # Check SDI from t2.ibd
129 # Check SDI from t3.ibd
130 # Check SDI from t4.ibd
131 # Check SDI from t5.ibd
132 # Check SDI from t6.ibd
133 # Check SDI from t7.ibd
134 # Check SDI from ts1.ibd(holds t8 & t9)
135 # Check SDI from t10.ibd
136 # Check SDI from t11.ibd
137 # Check SDI from partition tables t12.ibd
138 # Check SDI from t13.ibd
139 # Check SDI from t14.ibd
140 # Check SDI from t15.ibd
141 # Check SDI from system tablespace(has 16,17)
142 # Check SDI from t18.ibd
143 # Check SDI from t18_#p#p1#sp#p11.ibd
144 # Should have both tablespace and table SDIs
145 # Check SDI from t18_#p#p1#sp#p12.ibd
146 # Should only have tablespace SDI
147 # restart: --datadir=MYSQLD_DATADIR --innodb_redo_log_capacity=LOG_CAPACITY
148 # ALTER TABLE t1, ADD COLUMN. Verify SDI shows the newly added column
149 ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT;
150 # ALTER TABLE t2, ADD INDEX, Verify SDI shows the newly added index
151 ALTER TABLE t2 ADD INDEX k1(b(10));
152 # ALTER TABLE t3, DROP INDEX, Verify SDI doesn't show dropped index
153 ALTER TABLE t3 DROP INDEX k2;
154 # ALTER TABLE t4, DROP column, Verify SDI doesn't show dropped column
155 ALTER TABLE t4 DROP COLUMN d, ALGORITHM=INSTANT;
156 # ALTER TABLE RENAME column
157 ALTER TABLE t10 CHANGE a pk INT;
158 # Drop virtual column, verify SDI is updated
159 ALTER TABLE t11 DROP COLUMN m;
160 # Create and drop tables in general tablespace
161 CREATE TABLE t19(a INT) TABLESPACE=`ts1`;
162 CREATE TABLE t20(a INT, b INT) TABLESPACE=`ts1`;
163 DROP TABLE t19;
164 DROP TABLE t20;
165 # ALTER TABLE t1, ADD COLUMN. Verify SDI shows the newly added column 'b'
166 # Check SDI from t1.ibd
167 # ALTER TABLE t2, ADD INDEX, Verify SDI shows the newly added index 'k1'
168 # Check SDI from t2.ibd
169 # ALTER TABLE t3, DROP INDEX, Verify SDI doesn't show dropped index 'k2'
170 # Check SDI from t3.ibd
171 # ALTER TABLE t4, DROP column, Verify SDI doesn't show dropped column 'd'
172 # Check SDI from t4.ibd
173 # ALTER TABLE t10 RENAME column from 'a' to 'pk'. Verify column name is 'pk'
174 # Check SDI from t10.ibd
175 # Drop virtual column of t11, verify SDI doesn't show clumn 'm'
176 # Check SDI from t11.ibd
177 # Create and drop tables in general tablespace. Shouldn't show t19, t20
178 # Remove json and other test files
179 # restart
180 #
181 # Bug#27151163 - INNODB: ASSERTION FAILURE: DICT0SDI.CC:.*
182 #
183 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
184 CREATE DATABASE sdi;
185 CREATE TABLE sdi.t1(i int) TABLESPACE ts1;
186 # Shutdown the server
187 # Remove ts1.ibd file
188 # restart
189 ALTER TABLE sdi.t1 ADD COLUMN j INT;
190 ERROR HY000: Tablespace is missing for table `sdi`.`t1`.
191 ALTER TABLE sdi.t1 RENAME COLUMN i to j;
192 ERROR HY000: Tablespace is missing for table `sdi`.`t1`.
193 ALTER TABLESPACE ts1 RENAME to ts2;
194 ERROR HY000: Failed to alter: TABLESPACE ts1
195 SHOW WARNINGS;
196 Level Code Message
197 Error 1533 Failed to alter: TABLESPACE ts1
198 Error 1812 Tablespace is missing for table ts1.
199 DROP DATABASE sdi;
200 DROP TABLESPACE ts1;