diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/insert_into_empty.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/insert_into_empty.test | 497 |
1 files changed, 497 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test new file mode 100644 index 00000000..baba5a14 --- /dev/null +++ b/mysql-test/suite/innodb/t/insert_into_empty.test @@ -0,0 +1,497 @@ +--source include/have_innodb.inc +--source include/innodb_page_size.inc +--source include/have_sequence.inc +--source include/maybe_debug.inc +--source include/have_partition.inc + +# Enable MDEV-515 table-level undo logging for insert into empty table +SET foreign_key_checks=0, unique_checks=0; + +--echo # +--echo # MDEV-24715 Assertion !node->table->skip_alter_undo +--echo # +CREATE TABLE t (a INT UNIQUE) ENGINE=InnoDB +REPLACE SELECT 1 AS a, 2 AS b UNION SELECT 1 AS a, 3 AS c; +SELECT * FROM t; +DROP TABLE t; + +CREATE TEMPORARY TABLE t (a INT UNIQUE) ENGINE=InnoDB +REPLACE SELECT 1 AS a, 2 AS b UNION SELECT 1 AS a, 3 AS c; +SELECT * FROM t; +DROP TEMPORARY TABLE t; + +--echo # +--echo # MDEV-24720 AHI removal during bulk index rollback +--echo # +SET @save_ahi = @@global.innodb_adaptive_hash_index; +SET GLOBAL innodb_adaptive_hash_index = 1; +CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT * FROM seq_1_to_65536; +ROLLBACK; +CHECK TABLE t1; +--echo # +--echo # MDEV-24832 Root page AHI Removal fails fails during +--echo # bulk index rollback +--echo # +BEGIN; +INSERT INTO t1 SELECT * FROM seq_1_to_500; +ROLLBACK; +DROP TABLE t1; +SET GLOBAL innodb_adaptive_hash_index = @save_ahi; + +--echo # +--echo # MDEV-24951 Assertion m.first->second.valid(trx->undo_no) failed +--echo # in trx_undo_report_row_operation +--echo # + +CREATE TEMPORARY TABLE t (c INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; +SET tx_read_only=1; +BEGIN; +INSERT INTO t2 VALUES(0); +INSERT INTO t VALUES(0); +ROLLBACK; +BEGIN; +INSERT INTO t2 VALUES(0); +INSERT INTO t VALUES(0); +COMMIT; +INSERT INTO t VALUES(0); + +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +DROP TEMPORARY TABLE t,t2; +SET tx_read_only=0; +DROP TEMPORARY TABLE t,t2; + +--echo # +--echo # MDEV-24818 Optimize multiple INSERT into empty table +--echo # + +CREATE TABLE t1(f1 INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES (5),(6),(7); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4),(5),(6); +COMMIT; +SELECT * FROM t1; +BEGIN; +INSERT INTO t1 VALUES (5),(6),(7); +SAVEPOINT a; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4),(5),(6); +ROLLBACK TO SAVEPOINT a; +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; + +# Repeat the same with the MDEV-515 test disabled +SET foreign_key_checks=1; + +CREATE TABLE t1(f1 INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES (5),(6),(7); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4),(5),(6); +COMMIT; +SELECT * FROM t1; +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (5),(6),(7); +SAVEPOINT a; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4),(5),(6); +ROLLBACK TO SAVEPOINT a; +COMMIT; +SELECT * FROM t1; +DROP TABLE t1; + +SET foreign_key_checks=0; + +--echo # +--echo # MDEV-25315 Crash in SHOW ENGINE INNODB STATUS +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; + +INSERT INTO t1 VALUES(1); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES(1); +INSERT INTO t2 VALUES(0); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES(2), (2); +--disable_result_log +SHOW ENGINE InnoDB STATUS; +--enable_result_log +COMMIT; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-25297 Assertion: trx->roll_limit <= trx->undo_no +--echo # in ROLLBACK TO SAVEPOINT +--echo # + +CREATE TABLE t1 (c INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 (c INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(0); +SAVEPOINT x; +INSERT INTO t2 VALUES(0); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES(0); +--error ER_ERROR_DURING_ROLLBACK +ROLLBACK TO SAVEPOINT x; +COMMIT; +SELECT * FROM t1; +SELECT * FROM t2; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-25487 Assertion failed in lock_rec_move +--echo # + +CREATE TABLE t1 (a INT KEY) ENGINE=InnoDB; + +--error 0,1193 +SET @save_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug; +--error 0,1193 +SET GLOBAL innodb_limit_optimistic_insert_debug = 2; + +BEGIN; +SELECT * FROM t1 LOCK IN SHARE MODE; +INSERT INTO t1 VALUES (0),(1),(2); +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t1 VALUES (0,1); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (2); +COMMIT; + +--error 0,ER_UNKNOWN_SYSTEM_VARIABLE +SET GLOBAL innodb_limit_optimistic_insert_debug = @save_limit; + +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # MDEV-25534 Assertion lock_table_has...LOCK_IX +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +SET autocommit=0; +LOCK TABLE t1 WRITE; +INSERT INTO t1 VALUES (1); +COMMIT; +CREATE TEMPORARY TABLE t0 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t0 VALUES (1); +INSERT INTO t1 VALUES (2); +COMMIT; +SET autocommit=1; +DROP TABLE t1; +DROP TEMPORARY TABLE t0; + +--echo # +--echo # MDEV-25496 Assertion 'trx->bulk_insert' failed +--echo # in trx_undo_report_row_operation on INSERT +--echo # +CREATE TABLE t (i INT) ENGINE=InnoDB PARTITION BY HASH (i) PARTITIONS 2; +INSERT INTO t VALUES (0); +INSERT INTO t VALUES (1),(0),(1); +DROP TABLE t; + +--echo # +--echo # MDEV-28327 InnoDB persistent statistics fail to update +--echo # after bulk insert +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY)ENGINE=InnoDB +STATS_PERSISTENT=1 STATS_AUTO_RECALC=1; +INSERT INTO t1 SELECT * FROM seq_1_to_4096; +--echo # Wait till statistics update after bulk insert operation +let $wait_condition= select n_rows > 100 from mysql.innodb_table_stats +where table_name="t1"; +source include/wait_condition.inc; +# At innodb_page_size=4k this will be only 4075, not 4096. Add some slack. +# This is related to MDEV-24621 and possibly MDEV-26740. +SELECT n_rows>=4000 FROM mysql.innodb_table_stats WHERE TABLE_NAME="t1"; +DROP TABLE t1; + + +--echo # +--echo # MDEV-27214 Import with disabled keys corrupts meta-data like rows, indexes, ... +--echo # + +CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `a` int(11) DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +STATS_PERSISTENT=1 STATS_AUTO_RECALC=1; +INSERT INTO `t1` VALUES (1,2),(2,3),(3,4); +--echo # Wait till statistics update after bulk insert operation +let $wait_condition= select n_rows > 0 from mysql.innodb_table_stats +where database_name='test' and table_name='t1'; +source include/wait_condition.inc; +SELECT TABLE_ROWS, AVG_ROW_LENGTH>0 FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test'; +DROP TABLE t1; + +--echo # +--echo # MDEV-29975 InnoDB fails to release savepoint during bulk insert +--echo # +CREATE TABLE t (c INT KEY) ENGINE=InnoDB; +begin; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES (0,0); +SAVEPOINT a; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t VALUES (0),(0); +SAVEPOINT a; +commit; +SELECT * FROM t; +DROP TABLE t; + +--echo # +--echo # MDEV-29545 InnoDB: Can't find record during replace stmt +--echo # +CREATE TABLE t1(c1 INT PRIMARY KEY)ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(3331); + +connect(con1,localhost,root,,,); +BEGIN; +SELECT c1 FROM t1; + +connection default; +COMMIT; + +connection con1; +REPLACE INTO t1 VALUES(1984), (1984); +COMMIT; +connection default; +disconnect con1; +SELECT * FROM t1; +DROP TABLE t1; +--echo # End of 10.6 tests + +--echo # +--echo # MDEV-26947 UNIQUE column checks fail in InnoDB resulting +--echo # in table corruption +--echo # + +CREATE TABLE t (c1 INT KEY,c2 INT UNIQUE) ENGINE=InnoDB; +BEGIN; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t VALUES (1,0),(2,0); +CHECK TABLE t; +COMMIT; +DROP TABLE t; + +CREATE TABLE t (i INT UNIQUE)ENGINE=InnoDB; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t VALUES (0),(0); +CHECK TABLE t; +DROP TABLE t; + +CREATE TABLE t (c INT PRIMARY KEY,c2 CHAR(1) UNIQUE)ENGINE=InnoDB; +BEGIN; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t VALUES(1, ''),(2, ''); +SELECT * FROM t; +DELETE FROM t; +COMMIT; +DROP TABLE t; + +--echo # +--echo # MDEV-26956 LeakSanitizer/Valgrind errors in +--echo # trx_mod_table_time_t::start_bulk_insert +--echo # upon adding system versioning +--echo # +CREATE TABLE t1(id INT, s DATE, e DATE, PERIOD FOR p(s,e), + PRIMARY KEY(id, p WITHOUT OVERLAPS)) ENGINE=InnoDB; +ALTER TABLE t1 ADD COLUMN row_start BIGINT UNSIGNED AS ROW START, + ADD COLUMN row_end BIGINT UNSIGNED AS ROW END, + ADD PERIOD FOR SYSTEM_TIME(row_start,row_end), WITH SYSTEM VERSIONING; +DROP TABLE t1; + +--echo # +--echo # MDEV-27316 Assertion `!(index)->is_spatial()' failed. +--echo # +CREATE TABLE t (c POINT NOT NULL, SPATIAL INDEX(c)) ENGINE=InnoDB; +INSERT INTO t VALUES (POINT(1, 1)); +SELECT COUNT(*) FROM t WHERE MBRWithin(t.c, POINT(1,1)); +DROP TABLE t; + +--echo # +--echo # MDEV-27318 Assertion data_size < srv_sort_buf_size failed in row_merge_bulk_buf_add +--echo # +CREATE TABLE t1(f1 MEDIUMTEXT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(REPEAT(1, 8459264)); +SELECT length(f1) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-27858 Assertion `page_dir_get_n_heap(new_page) == 2U' failed in PageBulk::init +--echo # +CREATE TABLE t1 (c INT) ENGINE=InnoDB; +CREATE TABLE t2 (c INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1); +LOCK TABLES t1 WRITE,t2 WRITE; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); +DROP TABLE t2, t1; + +--echo # +--echo # MDEV-28138 MariaDB Assertion Failed in mtr_buf_t::has_space +--echo # +CREATE TABLE t1( + f1 SERIAL, + f2 LINESTRING NOT NULL DEFAULT LineFromText('LINESTRING(1 1,2 2,3 3)'), +SPATIAL INDEX(f2))ENGINE=InnoDB; +--error ER_DUP_ENTRY +INSERT INTO t1(f1) VALUES(0), (1), (2); +DROP TABLE t1; + +--echo # +--echo # MDEV-28237 Assertion `0' failed in row_upd_sec_index_entry on DELETE +--echo # +CREATE TABLE t1 (c0 YEAR UNIQUE) ENGINE=InnoDB; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(0),(0); +DELETE FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-28037 Assertion `trx->bulk_insert' failed in innodb_prepare_commit_versioned +--echo # +CREATE TABLE t1 (c INT) ENGINE=InnoDB; +CREATE TABLE t2 (c INT) ENGINE=InnoDB; +CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW UPDATE t2 SET c=1; +INSERT INTO t1 VALUES (0); +SELECT * FROM t1; +SELECT * FROM t2; +DROP TABLE t2, t1; + +--echo # +--echo # MDEV-28242 Assertion `!check_foreigns' failed in +--echo # trx_t::check_bulk_buffer +--echo # +CREATE TABLE t1 (c INT)ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(1), (1); +SET SESSION foreign_key_checks=TRUE; +SHOW CREATE TABLE t1; +CREATE TABLE t2(d INT)ENGINE=InnoDB; +DROP TABLE t2, t1; + +--echo # +--echo # MDEV-28400 Leak in trx_mod_time_t::start_bulk_insert +--echo # + +SET SESSION foreign_key_checks=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB PARTITION BY HASH(a) PARTITIONS 2; +INSERT INTO t1 VALUES (1),(2); +ALTER TABLE t1 REBUILD PARTITION p0; +# Cleanup +DROP TABLE t1; + +--echo # +--echo # MDEV-29570 InnoDB fails to clean bulk buffer when server +--echo # does rollback operation +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB + PARTITION BY RANGE (pk) ( + PARTITION pn VALUES LESS THAN (20)); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO t1 VALUES (1),(21); +# Cleanup +DROP TABLE t1; + +--echo # +--echo # MDEV-29761 Bulk insert fails to rollback +--echo # during insert..select +--echo # +CREATE TABLE t1 (f1 INT)ENGINE=InnoDB; +CREATE TABLE t2 (f1 INT, UNIQUE INDEX(f1)) ENGINE=InnoDB; +CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(1); +BEGIN; +INSERT t1 SELECT 1 FROM seq_1_to_2; +COMMIT; +SELECT * FROM t1; +SELECT * FROM t2; +CHECK TABLE t1; +CHECK TABLE t2; +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-29801 Inconsistent ER_TOO_BIG_ROWSIZE during bulk +--echo # insert operation +--echo # +call mtr.add_suppression("InnoDB: Cannot add field `(c0[36]|c11)` in table"); + +SET @format= @@innodb_default_row_format; +CREATE TABLE t1 (pk int primary key, c01 text, c02 text, c03 text, + c04 text, c05 text, c06 text, c07 text, c08 text, + c09 text, c10 text, c11 text, c12 text) ENGINE=InnoDB; +SET GLOBAL INNODB_DEFAULT_ROW_FORMAT= COMPACT; +--replace_result 1982 8126 4030 8126 +ALTER TABLE t1 FORCE; +let $page_size= `SELECT @@innodb_page_size`; +let $error_code = 0; + +if ($page_size == 4096) { + let $error_code = ER_TOO_BIG_ROWSIZE; +} + +--error $error_code +INSERT IGNORE INTO t1 VALUES +(1, REPEAT('x',4805), REPEAT('t',2211), REPEAT('u',974), REPEAT('e',871), REPEAT('z',224), REPEAT('j',978), REPEAT('n',190), REPEAT('t',888), REPEAT('x',32768), REPEAT('e',968), REPEAT('b',913), REPEAT('x',12107)), +(2, REPEAT('x',4805), REPEAT('t',2211), REPEAT('u',974), REPEAT('e',871), REPEAT('z',224), REPEAT('j',978), REPEAT('n',190), REPEAT('t',888), REPEAT('x',32768), REPEAT('e',968), REPEAT('b',913), REPEAT('x',12107)); +CHECK TABLE t1; +DROP TABLE t1; +SET GLOBAL INNODB_DEFAULT_ROW_FORMAT= @format; + +--echo # +--echo # MDEV-30321 blob data corrupted by row_merge_write_blob_to_tmp_file() +--echo # + +CREATE TABLE t1 ( + `id` int(11) NOT NULL, + `data` LONGBLOB NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (1, REPEAT('X', @@innodb_sort_buffer_size)), + (2, REPEAT('X', @@innodb_sort_buffer_size)); + +SELECT COUNT(*) AS nb_corrupted_rows FROM t1 WHERE data != REPEAT('X', @@innodb_sort_buffer_size); +DROP TABLE t1; + +--echo # End of 10.7 tests + +--echo # +--echo # MDEV-30426 Assertion !rec_offs_nth_extern(offsets2, n) +--echo # during bulk insert +--echo # +CREATE TABLE t1(f1 TEXT NOT NULL, f2 TEXT NOT NULL, + f3 TEXT NOT NULL, f4 TEXT NOT NULL, + f5 TEXT NOT NULL, f6 TEXT NOT NULL, + PRIMARY KEY(f6(10)))ENGINE=InnoDB; +BEGIN; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t1 VALUES + (repeat('a', 200), repeat('b', 200), repeat('c', 200), + repeat('d', 200), repeat('e', 200), repeat('f', 200)), + (repeat('b', 20000), repeat('c', 16000), repeat('d', 12000), + repeat('e', 12000), repeat('f', 12000), repeat('f', 12000)); +COMMIT; +DROP TABLE t1; +--echo # End of 10.8 tests + +--echo # +--echo # MDEV-31537 Bulk insert operation aborts the server +--echo # for redundant table +--echo # +CREATE TABLE t (a CHAR CHARACTER SET utf8) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT t SELECT left(seq,1) FROM seq_1_to_43691; +DROP TABLE t; |