Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_partition.inc
3 --source include/have_sequence.inc
4
5 SET @default_stats_persistent= @@global.innodb_stats_persistent;
6 SET GLOBAL innodb_stats_persistent= 0;
7 CREATE TABLE t1(f1 CHAR(200), f2 INT NOT NULL)engine=InnoDB;
8 INSERT INTO t1 SELECT repeat('a', 200), seq FROM seq_1_to_2;
9 # Buffer fits in the memory
10 ALTER TABLE t1 ALGORITHM=COPY, FORCE;
11
12 # Insert more entries
13 INSERT INTO t1 SELECT repeat('b', 200), seq FROM seq_3_to_65536;
14 # Alter should use temporary file for sorting
15 ALTER TABLE t1 ALGORITHM=COPY, ADD INDEX(f2);
16
17 # Error while buffering the insert operation
18 --error ER_DUP_ENTRY
19 ALTER TABLE t1 ALGORITHM=COPY, ADD PRIMARY KEY(f1(2));
20
21 INSERT INTO t1 VALUES(repeat('a', 200), 1);
22 # Error while applying the bulk insert operation
23 --error ER_DUP_ENTRY
24 ALTER TABLE t1 ALGORITHM=COPY, ADD UNIQUE KEY(f2);
25
26 # Ignore shouldn't go through bulk operation
27 ALTER IGNORE TABLE t1 MODIFY f1 CHAR(200) NOT NULL;
28
29 CREATE TABLE t2(f1 INT NOT NULL,
30 FOREIGN KEY(f1) REFERENCES t1(f2))ENGINE=InnoDB;
31 INSERT INTO t2 VALUES(1);
32 # Bulk operation shouldn't happen because of foreign key constraints
33 ALTER TABLE t2 ALGORITHM=COPY, FORCE;
34 DROP TABLE t2, t1;
35
36 CREATE TABLE t1 (f1 INT, f2 INT) ENGINE=InnoDB PARTITION BY HASH(f1) PARTITIONS 2;
37 INSERT INTO t1 VALUES(1, 1);
38 INSERT INTO t1 SELECT seq, seq * 2 FROM seq_1_to_2;
39 # Buffer fits in the memory
40 ALTER TABLE t1 ALGORITHM=COPY, FORCE;
41 # Insert more entries
42 INSERT INTO t1 SELECT seq, seq * 2 FROM seq_3_to_65536;
43 # Alter should use temporary file for sorting
44 ALTER TABLE t1 ALGORITHM=COPY, ADD INDEX(f2);
45 DROP TABLE t1;
46
47 --echo #
48 --echo # MDEV-34756 Validation of new foreign key skipped
49 --echo # if innodb_alter_copy_bulk=ON
50 --echo #
51 CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY,
52 f2 INT NOT NULL)ENGINE=InnoDB;
53 CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
54 f2 INT NOT NULL)ENGINE=InnoDB;
55 --enable_info
56 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(f1) REFERENCES t1(f1);
57 --disable_info
58 INSERT INTO t1 VALUES (1, 1);
59 INSERT INTO t2 VALUES (1, 2);
60 --replace_regex /#sql-alter-[0-9a-f-]*/#sql-alter/
61 --error ER_NO_REFERENCED_ROW_2
62 ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(f2) REFERENCES t1(f1);
63 INSERT INTO t1 VALUES(3, 1);
64 --enable_info
65 SET STATEMENT foreign_key_checks=0 FOR
66 ALTER TABLE t2 ALGORITHM=COPY, ADD CONSTRAINT FOREIGN KEY(f2) REFERENCES t1(f1);
67 ALTER TABLE t1 ALGORITHM=COPY, FORCE;
68 ALTER TABLE t2 ALGORITHM=COPY, FORCE;
69 --disable_info
70 DROP TABLE t2, t1;
71
72 --echo #
73 --echo # MDEV-35237 Bulk insert fails to apply buffered
74 --echo # operation during copy alter
75 --echo #
76 CREATE TABLE t1 (f1 int NOT NULL, f2 tinyint(1) NOT NULL,
77 f3 varchar(80) NOT NULL, PRIMARY KEY(f1),
78 KEY(f2), KEY(f3))ENGINE=InnoDB;
79 INSERT INTO t1 VALUES(1,1,''),(2,0,''), (4,1,'e');
80 CREATE TABLE t2 (f1 int NOT NULL, f2 int NOT NULL,KEY(f1))ENGINE=InnoDB;
81 INSERT INTO t2 VALUES (1,0),(1,0);
82 CREATE TABLE t engine=innodb
83 SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1 ;
84 SELECT COUNT(*) FROM t;
85 DROP TABLE t1, t2, t;
86
87 --echo #
88 --echo # MDEV-35475 Assertion `!rec_offs_nth_extern(offsets1, n)'
89 --echo # failed in cmp_rec_rec_simple_field
90 --echo #
91 CREATE TABLE t1(a BLOB, b VARCHAR(2048), PRIMARY KEY (b)) ENGINE=InnoDB
92 CHARACTER SET latin1 COLLATE latin1_swedish_ci;
93 INSERT INTO t1 VALUES
94 (REPEAT('x',4805),'a'), (REPEAT('x',16111),'b'),
95 (REPEAT('x',65535),'c'), (REPEAT('x',11312),'d'),
96 (REPEAT('x',35177),'e'), (REPEAT('x',65535),'f'),
97 (REPEAT('x',1988),'g'), (NULL,REPEAT('x',2048)),
98 (REPEAT('x',2503),'h'), (REPEAT('x',33152),'i'),
99 (REPEAT('x',65535),'j'), (REPEAT('x',1988),'k'),
100 (REPEAT('x',65535),'l'), (REPEAT('x',65535),'m'),
101 (REPEAT('x',65535),'n'), (REPEAT('x',65535),'o'),
102 (REPEAT('x',1988),'p'), (REPEAT('x',2503),'q'),
103 (REPEAT('x',65535),'r'), (REPEAT('x',65535),'s'),
104 (REPEAT('x',65535),'t'), (REPEAT('x',3169),'u'),
105 (REPEAT('x',7071),'v'), (REPEAT('x',16111),'w'),
106 (REPEAT('x',2325),'x'), (REPEAT('x',33152),'y'),
107 (REPEAT('x',65535),'z'), (REPEAT('x',65535),'aa'),
108 (REPEAT('x',16111),'bb'), (REPEAT('x',4805),'cc'),
109 (REPEAT('x',65535),'dd');
110 ALTER TABLE t1 FORCE, ALGORITHM=COPY;
111 DROP TABLE t1;
112 SET GLOBAL innodb_stats_persistent=@default_stats_persistent;
113
114 --echo #
115 --echo # MDEV-36504 Memory leak after insert into empty table
116 --echo #
117 CREATE TABLE t1 (k INT PRIMARY KEY)ENGINE=InnoDB;
118 INSERT INTO t1 SET k= 1;
119 START TRANSACTION;
120 INSERT INTO t1 SET k= 2;
121 SELECT COUNT(*) > 0 FROM mysql.innodb_index_stats LOCK IN SHARE MODE;
122
123 connect(con1,localhost,root,,,);
124 SET innodb_lock_wait_timeout=0;
125 --error ER_LOCK_WAIT_TIMEOUT
126 CREATE TABLE t2(f1 INT DEFAULT 1 PRIMARY KEY)
127 STATS_PERSISTENT= 1 ENGINE=InnoDB as SELECT k FROM t1;
128 disconnect con1;
129 connection default;
130 SET innodb_lock_wait_timeout=default;
131 DROP TABLE t1;
132 DROP TABLE IF EXISTS t2;
133 --source include/restart_mysqld.inc
134
135 --echo #
136 --echo # MDEV-37296 ALTER TABLE allows adding unique hash key with duplicate values
137 --echo #
138 CREATE TABLE t (pk INT PRIMARY KEY, a INT, b TEXT) ENGINE=InnoDB;
139 INSERT INTO t VALUES (5,3,'foo'), (1,NULL,'bar'), (3,3,'foo');
140 --error ER_DUP_ENTRY
141 ALTER TABLE t ADD UNIQUE (a,b);
142 SHOW CREATE TABLE t;
143 SELECT * FROM t;
144 DROP TABLE t;
145
146 --echo #
147 --echo # MDEV-36787 Error 153: No savepoint with that name upon ROLLBACK TO SAVEPOINT, assertion failure
148 --echo #
149 CREATE TABLE t (a INT) ENGINE=InnoDB;
150 START TRANSACTION;
151 INSERT t VALUES (0),(1);
152 SAVEPOINT A;
153 select @@in_transaction;
154 --error ER_WARN_DATA_OUT_OF_RANGE
155 CREATE TEMPORARY TABLE tmp (a TINYINT) ENGINE=InnoDB AS SELECT 256 AS a;
156 select @@in_transaction;
157 ROLLBACK TO SAVEPOINT A;
158 COMMIT;
159 SELECT * FROM t;
160 DROP TABLE t;
161
162 --echo # End of 10.11 tests