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` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
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` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
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` FOREIGN KEY (`fk`) REFERENCES `test`.`t1` (`pk`))
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 b INT, c INT, d INT, e INT, f INT, g INT, h INT, i INT, j INT, k INT,
52 l INT, m INT, n INT, o INT, p INT, q INT, r INT, s INT, t INT, u INT,
53 KEY(b),KEY(c),KEY(d),KEY(e),KEY(f),KEY(g),KEY(h),KEY(i),KEY(j),KEY(k),
54 KEY(l),KEY(m),KEY(n),KEY(o),KEY(p),KEY(q),KEY(r),KEY(s),KEY(t),KEY(u),
55 KEY(c,b),KEY(d,b),KEY(e,b),KEY(f,b),KEY(g,b),KEY(h,b),KEY(i,b),KEY(j,b),
56 KEY(k,b),KEY(l,b),KEY(m,b),KEY(n,b),KEY(o,b),KEY(p,b),KEY(q,b),KEY(r,b),
57 KEY(s,b),KEY(t,b),KEY(u,b),
58 KEY(d,c),KEY(e,c),KEY(f,c),KEY(g,c),KEY(h,c),KEY(i,c),KEY(j,c),
59 KEY(k,c),KEY(l,c),KEY(m,c),KEY(n,c),KEY(o,c),KEY(p,c),KEY(q,c),KEY(r,c),
60 KEY(s,c),KEY(t,c),KEY(u,c),
61 KEY(e,d),KEY(f,d),KEY(g,d),KEY(h,d),KEY(i,d),KEY(j,d)
62 ) ENGINE=InnoDB;
63 INSERT INTO t1 () VALUES (), ();
64 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
65 AUTO_INCREMENT
66 3
67 SELECT a FROM t1 ORDER BY a;
68 a
69 1
70 2
71 TRUNCATE TABLE t1;
72 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
73 AUTO_INCREMENT
74 1
75 INSERT INTO t1 () VALUES (), ();
76 SELECT a FROM t1 ORDER BY a;
77 a
78 1
79 2
80 DROP TABLE t1;
81 call mtr.add_suppression('InnoDB: in RENAME TABLE table `test`.`t3`');
82 SET FOREIGN_KEY_CHECKS= OFF;
83 CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB;
84 SET FOREIGN_KEY_CHECKS= ON;
85 CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB;
86 CREATE TABLE t3 (a INT) ENGINE=InnoDB;
87 ERROR HY000: Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed")
88 ALTER TABLE t1 RENAME TO t3;
89 ALTER TABLE t3 FORCE;
90 TRUNCATE TABLE t3;
91 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `test`.`t3` (`f2`))
92 DROP TABLE t2, t3;
93 #
94 # MDEV-24861 Assertion `trx->rsegs.m_redo.rseg' failed
95 # in innodb_prepare_commit_versioned
96 #
97 CREATE TABLE t1 (id INT PRIMARY KEY, f TEXT UNIQUE,
98 s BIGINT UNSIGNED AS ROW START, e BIGINT UNSIGNED AS ROW END,
99 PERIOD FOR SYSTEM_TIME(s,e))
100 ENGINE=InnoDB WITH SYSTEM VERSIONING;
101 CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=InnoDB;
102 ALTER TABLE t1 FORCE;
103 TRUNCATE TABLE t2;
104 DROP TABLE t1, t2;
105 # End of 10.6 tests