path: root/mysql-test/suite/innodb/r/insert_into_empty.result

SET foreign_key_checks=0, unique_checks=0;
#
# MDEV-24715 Assertion !node->table->skip_alter_undo
#
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;
a	b
1	3
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;
a	b
1	3
DROP TEMPORARY TABLE t;
#
# MDEV-24720 AHI removal during bulk index rollback
#
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;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
#
# MDEV-24832 Root page AHI Removal fails fails during
#		bulk index rollback
#
BEGIN;
INSERT INTO t1 SELECT * FROM seq_1_to_500;
ROLLBACK;
DROP TABLE t1;
SET GLOBAL innodb_adaptive_hash_index = @save_ahi;
#
# MDEV-24951 Assertion m.first->second.valid(trx->undo_no) failed
# in trx_undo_report_row_operation
#
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);
COMMIT;
INSERT INTO t VALUES(0);
DROP TEMPORARY TABLE t,t2;
ERROR 25006: Cannot execute statement in a READ ONLY transaction
SET tx_read_only=0;
DROP TEMPORARY TABLE t,t2;
#
# MDEV-24818 Optimize multiple INSERT into empty table
#
CREATE TABLE t1(f1 INT PRIMARY KEY) ENGINE=InnoDB;
BEGIN;
INSERT INTO t1 VALUES (5),(6),(7);
INSERT INTO t1 VALUES (4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
COMMIT;
SELECT * FROM t1;
f1
BEGIN;
INSERT INTO t1 VALUES (5),(6),(7);
SAVEPOINT a;
INSERT INTO t1 VALUES (4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
ROLLBACK TO SAVEPOINT a;
COMMIT;
SELECT * FROM t1;
f1
5
6
7
DROP TABLE t1;
SET foreign_key_checks=1;
CREATE TABLE t1(f1 INT PRIMARY KEY) ENGINE=InnoDB;
BEGIN;
INSERT INTO t1 VALUES (5),(6),(7);
INSERT INTO t1 VALUES (4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
COMMIT;
SELECT * FROM t1;
f1
5
6
7
BEGIN;
INSERT INTO t1 VALUES (5),(6),(7);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
SAVEPOINT a;
INSERT INTO t1 VALUES (4),(5),(6);
ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
ROLLBACK TO SAVEPOINT a;
COMMIT;
SELECT * FROM t1;
f1
5
6
7
DROP TABLE t1;
SET foreign_key_checks=0;
#
# MDEV-25315 Crash in SHOW ENGINE INNODB STATUS
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1);
BEGIN;
INSERT INTO t1 VALUES(1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES(2), (2);
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
SHOW ENGINE InnoDB STATUS;
COMMIT;
DROP TABLE t1,t2;
#
# MDEV-25297 Assertion: trx->roll_limit <= trx->undo_no
# in ROLLBACK TO SAVEPOINT
#
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);
INSERT INTO t1 VALUES(0);
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
ROLLBACK TO SAVEPOINT x;
ERROR HY000: Got error 153 "No savepoint with that name" during ROLLBACK
COMMIT;
SELECT * FROM t1;
c
SELECT * FROM t2;
c
DROP TABLE t1,t2;
#
# MDEV-25487 Assertion failed in lock_rec_move
#
CREATE TABLE t1 (a INT KEY) ENGINE=InnoDB;
SET @save_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug;
SET GLOBAL innodb_limit_optimistic_insert_debug = 2;
BEGIN;
SELECT * FROM t1 LOCK IN SHARE MODE;
a
INSERT INTO t1 VALUES (0),(1),(2);
INSERT INTO t1 VALUES (0,1);
ERROR 21S01: Column count doesn't match value count at row 1
INSERT INTO t1 VALUES (2);
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
COMMIT;
SET GLOBAL innodb_limit_optimistic_insert_debug = @save_limit;
SELECT * FROM t1;
a
0
1
2
DROP TABLE t1;
#
# MDEV-25534 Assertion lock_table_has...LOCK_IX
#
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;
#
# MDEV-25496 Assertion 'trx->bulk_insert' failed
# in trx_undo_report_row_operation on INSERT
#
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;
#
# MDEV-26947 UNIQUE column checks fail in InnoDB resulting
#	in table corruption
#
CREATE TABLE t (c1 INT KEY,c2 INT UNIQUE) ENGINE=InnoDB;
BEGIN;
INSERT INTO t VALUES (1,0),(2,0);
ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
CHECK TABLE t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
COMMIT;
DROP TABLE t;
CREATE TABLE t (i INT UNIQUE)ENGINE=InnoDB;
INSERT INTO t VALUES (0),(0);
ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
CHECK TABLE t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
DROP TABLE t;
CREATE TABLE t (c INT PRIMARY KEY,c2 CHAR(1) UNIQUE)ENGINE=InnoDB;
BEGIN;
INSERT INTO t VALUES(1, ''),(2, '');
ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
SELECT * FROM t;
c	c2
DELETE FROM t;
COMMIT;
DROP TABLE t;
#
#  MDEV-26956 LeakSanitizer/Valgrind errors in
#     trx_mod_table_time_t::start_bulk_insert
#	upon adding system versioning
#
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;
#
#  MDEV-27316  Assertion `!(index)->is_spatial()' failed.
#
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));
COUNT(*)
1
DROP TABLE t;
#
#  MDEV-27318	Assertion data_size < srv_sort_buf_size failed in row_merge_bulk_buf_add
#
CREATE TABLE t1(f1 MEDIUMTEXT)ENGINE=InnoDB;
INSERT INTO t1 VALUES(REPEAT(1, 8459264));
SELECT length(f1) FROM t1;
length(f1)
8459264
DROP TABLE t1;
#
#  MDEV-27858  Assertion `page_dir_get_n_heap(new_page) == 2U' failed in PageBulk::init
#
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;
#
#  MDEV-28138  MariaDB Assertion Failed in mtr_buf_t::has_space
#
CREATE TABLE t1(
f1 SERIAL,
f2 LINESTRING NOT NULL DEFAULT LineFromText('LINESTRING(1 1,2 2,3 3)'),
SPATIAL INDEX(f2))ENGINE=InnoDB;
INSERT INTO t1(f1) VALUES(0), (1), (2);
ERROR 23000: Duplicate entry '1' for key 'f1'
DROP TABLE t1;
#
#  MDEV-28237  Assertion `0' failed in row_upd_sec_index_entry on DELETE
#
CREATE TABLE t1 (c0 YEAR UNIQUE) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(0),(0);
ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
DELETE FROM t1;
DROP TABLE t1;
#
#  MDEV-28037 Assertion `trx->bulk_insert' failed in innodb_prepare_commit_versioned
#
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;
c
0
SELECT * FROM t2;
c
DROP TABLE t2, t1;
#
#  MDEV-28242  Assertion `!check_foreigns' failed in
#                trx_t::check_bulk_buffer
#
CREATE TABLE t1 (c INT)ENGINE=InnoDB;
BEGIN;
INSERT INTO t1 VALUES(1), (1);
SET SESSION foreign_key_checks=TRUE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
CREATE TABLE t2(d INT)ENGINE=InnoDB;
DROP TABLE t2, t1;
#
#  MDEV-28400 Leak in trx_mod_time_t::start_bulk_insert
#
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;
DROP TABLE t1;