Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 let $datadir=`select @@datadir`;
3 --echo #
4 --echo # TRUNCATE TABLE
5 --echo #
6 --echo # Truncating is disallowed for parent tables unless such table
7 --echo # participates in self-referencing foreign keys only.
8 --echo #
9 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
10 CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
11 --error ER_TRUNCATE_ILLEGAL_FK
12 TRUNCATE TABLE t1;
13 --echo # Truncation of child should succeed.
14 TRUNCATE TABLE t2;
15 DROP TABLE t2;
16 DROP TABLE t1;
17 CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT,
18 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
19 --echo # Truncation of self-referencing table should succeed.
20 TRUNCATE TABLE t1;
21 DROP TABLE t1;
22
23 --echo #
24 --echo # Also, truncating such tables is allowed if foreign key
25 --echo # checks are disabled.
26 --echo #
27
28 SET @old_foreign_key_checks = @@SESSION.foreign_key_checks;
29 CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=INNODB;
30 CREATE TABLE t2 (fk INT NOT NULL, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
31 CREATE TABLE t3 (pk INT PRIMARY KEY, fk INT,
32 FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=INNODB;
33 SET @@SESSION.foreign_key_checks = 0;
34 TRUNCATE TABLE t1;
35 TRUNCATE TABLE t2;
36 TRUNCATE TABLE t3;
37 SET @@SESSION.foreign_key_checks = 1;
38 --error ER_TRUNCATE_ILLEGAL_FK
39 TRUNCATE TABLE t1;
40 TRUNCATE TABLE t2;
41 TRUNCATE TABLE t3;
42 LOCK TABLES t1 WRITE;
43 SET @@SESSION.foreign_key_checks = 0;
44 TRUNCATE TABLE t1;
45 SET @@SESSION.foreign_key_checks = 1;
46 --error ER_TRUNCATE_ILLEGAL_FK
47 TRUNCATE TABLE t1;
48 UNLOCK TABLES;
49 DROP TABLE t3,t2,t1;
50 SET @@SESSION.foreign_key_checks = @old_foreign_key_checks;
51
52 --echo #
53 --echo # Test that TRUNCATE resets auto-increment.
54 --echo #
55
56 CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
57 b INT, c INT, d INT, e INT, f INT, g INT, h INT, i INT, j INT, k INT,
58 l INT, m INT, n INT, o INT, p INT, q INT, r INT, s INT, t INT, u INT,
59 KEY(b),KEY(c),KEY(d),KEY(e),KEY(f),KEY(g),KEY(h),KEY(i),KEY(j),KEY(k),
60 KEY(l),KEY(m),KEY(n),KEY(o),KEY(p),KEY(q),KEY(r),KEY(s),KEY(t),KEY(u),
61 KEY(c,b),KEY(d,b),KEY(e,b),KEY(f,b),KEY(g,b),KEY(h,b),KEY(i,b),KEY(j,b),
62 KEY(k,b),KEY(l,b),KEY(m,b),KEY(n,b),KEY(o,b),KEY(p,b),KEY(q,b),KEY(r,b),
63 KEY(s,b),KEY(t,b),KEY(u,b),
64 KEY(d,c),KEY(e,c),KEY(f,c),KEY(g,c),KEY(h,c),KEY(i,c),KEY(j,c),
65 KEY(k,c),KEY(l,c),KEY(m,c),KEY(n,c),KEY(o,c),KEY(p,c),KEY(q,c),KEY(r,c),
66 KEY(s,c),KEY(t,c),KEY(u,c),
67 KEY(e,d),KEY(f,d),KEY(g,d),KEY(h,d),KEY(i,d),KEY(j,d)
68 ) ENGINE=InnoDB;
69 INSERT INTO t1 () VALUES (), ();
70 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
71 SELECT a FROM t1 ORDER BY a;
72 TRUNCATE TABLE t1;
73 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1';
74 INSERT INTO t1 () VALUES (), ();
75 SELECT a FROM t1 ORDER BY a;
76 DROP TABLE t1;
77
78 #
79 # MDEV-18923 Assertion `!lex_string_cmp(system_charset_info, fk_info->referenced_table, &table->s->table_name)' failed in fk_truncate_illegal_if_parent
80 #
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 --error ER_CANT_CREATE_TABLE
87 CREATE TABLE t3 (a INT) ENGINE=InnoDB;
88 ALTER TABLE t1 RENAME TO t3;
89 ALTER TABLE t3 FORCE;
90 --error ER_TRUNCATE_ILLEGAL_FK
91 TRUNCATE TABLE t3;
92 DROP TABLE t2, t3;
93
94 --echo #
95 --echo # MDEV-24861 Assertion `trx->rsegs.m_redo.rseg' failed
96 --echo # in innodb_prepare_commit_versioned
97 --echo #
98
99 CREATE TABLE t1 (id INT PRIMARY KEY, f TEXT UNIQUE,
100 s BIGINT UNSIGNED AS ROW START, e BIGINT UNSIGNED AS ROW END,
101 PERIOD FOR SYSTEM_TIME(s,e))
102 ENGINE=InnoDB WITH SYSTEM VERSIONING;
103 CREATE TABLE t2 (id INT PRIMARY KEY) ENGINE=InnoDB;
104 ALTER TABLE t1 FORCE;
105 TRUNCATE TABLE t2;
106 DROP TABLE t1, t2;
107
108 --echo # End of 10.6 tests