Debian Codegraph @

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

raw
1 #
2 # TRUNCATE TABLE
3 #
4 # Truncating is disallowed for parent tables unless such table
5 # participates in self-referencing foreign keys only.
6 #
7 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
8 CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
9 TRUNCATE TABLE t1;
10 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
11 # Truncation of child should succeed.
12 TRUNCATE TABLE t2;
13 DROP TABLE t2;
14 DROP TABLE t1;
15 CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
16 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
17 # Truncation of self-referencing table should succeed.
18 TRUNCATE TABLE t1;
19 DROP TABLE t1;
20 #
21 # Also, truncating such tables is allowed if foreign key
22 # checks are disabled.
23 #
24 SET @old_foreign_key_checks = @@SESSION.foreign_key_checks;
25 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
26 CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
27 CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT,
28 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
29 SET @@SESSION.foreign_key_checks = 0;
30 TRUNCATE TABLE t1;
31 TRUNCATE TABLE t2;
32 TRUNCATE TABLE t3;
33 SET @@SESSION.foreign_key_checks = 1;
34 TRUNCATE TABLE t1;
35 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
36 TRUNCATE TABLE t2;
37 TRUNCATE TABLE t3;
38 LOCK TABLES t1 WRITE;
39 SET @@SESSION.foreign_key_checks = 0;
40 TRUNCATE TABLE t1;
41 SET @@SESSION.foreign_key_checks = 1;
42 TRUNCATE TABLE t1;
43 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1`)
44 UNLOCK TABLES;
45 DROP TABLE t3,t2,t1;
46 SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
47 #
48 # Test that TRUNCATE resets auto-increment.
49 #
50 CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
51 INSERT INTO t1 VALUES (NULL), (NULL);
52 ANALYZE TABLE t1;
53 Table Op Msg_type Msg_text
54 test.t1 analyze status OK
55 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
56 AUTO_INCREMENT
57 3
58 SELECT * FROM t1 ORDER BY a;
59 a
60 1
61 2
62 TRUNCATE TABLE t1;
63 ANALYZE TABLE t1;
64 Table Op Msg_type Msg_text
65 test.t1 analyze status OK
66 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
67 AUTO_INCREMENT
68 1
69 INSERT INTO t1 VALUES (NULL), (NULL);
70 SELECT * FROM t1 ORDER BY a;
71 a
72 1
73 2
74 DROP TABLE t1;
75 #
76 # Test that TRUNCATE with innodb_default_row_format changed
77 #
78 CREATE TEMPORARY TABLE t1(c1 DOUBLE(1,0)ZEROFILL,c2 BLOB,c3 YEAR)
79 ENGINE=InnoDB;
80 Warnings:
81 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.
82 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
83 ALTER TABLE t1 MODIFY c2 VARCHAR(1024)BINARY CHARACTER SET 'Binary' COLLATE
84 'Binary';
85 Warnings:
86 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
87 ALTER TABLE t1 ADD INDEX(c2),ADD UNIQUE (c2);
88 SET @@GLOBAL.innodb_default_row_format=1;
89 TRUNCATE t1;
90 DROP TABLE t1;
91 SET @@GLOBAL.innodb_default_row_format=default;
92 CREATE TABLE t1(c1 DOUBLE(1,0)ZEROFILL,c2 BLOB,c3 YEAR)
93 ENGINE=InnoDB;
94 Warnings:
95 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.
96 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
97 ALTER TABLE t1 MODIFY c2 VARCHAR(1024)BINARY CHARACTER SET 'Binary' COLLATE
98 'Binary';
99 Warnings:
100 Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
101 ALTER TABLE t1 ADD INDEX(c2),ADD UNIQUE (c2);
102 alter table t1 partition by key (c2) partitions 2;
103 SET @@GLOBAL.innodb_default_row_format=1;
104 TRUNCATE t1;
105 DROP TABLE t1;
106 SET @@GLOBAL.innodb_default_row_format=default;