Debian Codegraph @

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

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