| 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 |