diff options
Diffstat (limited to 'mysql-test/suite/innodb/r')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-index.result | 14 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-lock.result | 17 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 90 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_bug12661768.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_bug59641.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_gis.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result | 10 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_mysql.result | 52 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_replace.result | 77 |
9 files changed, 188 insertions, 78 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index a33099661aa..785270be600 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -1045,6 +1045,20 @@ COMMIT; UPDATE bug12547647 SET c = REPEAT('b',16928); ERROR HY000: Undo log record is too big. DROP TABLE bug12547647; +SET @r=REPEAT('a',500); +CREATE TABLE t1(a INT, +v1 VARCHAR(500), v2 VARCHAR(500), v3 VARCHAR(500), +v4 VARCHAR(500), v5 VARCHAR(500), v6 VARCHAR(500), +v7 VARCHAR(500), v8 VARCHAR(500), v9 VARCHAR(500), +v10 VARCHAR(500), v11 VARCHAR(500), v12 VARCHAR(500), +v13 VARCHAR(500), v14 VARCHAR(500), v15 VARCHAR(500), +v16 VARCHAR(500), v17 VARCHAR(500), v18 VARCHAR(500) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +CREATE INDEX idx1 ON t1(a,v1); +INSERT INTO t1 VALUES(9,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r); +UPDATE t1 SET a=1000; +DELETE FROM t1; +DROP TABLE t1; set global innodb_file_per_table=0; set global innodb_file_format=Antelope; set global innodb_file_format_max=Antelope; diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result index 41f308788a2..439a8d6513c 100644 --- a/mysql-test/suite/innodb/r/innodb-lock.result +++ b/mysql-test/suite/innodb/r/innodb-lock.result @@ -89,3 +89,20 @@ commit; # Connection 'con1'. commit; drop table t1; +# +#Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE +#fixed by re-fixing Bug#7975 +#aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT... +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES(3,1); +BEGIN; +INSERT IGNORE INTO t1 VALUES(3,14); +BEGIN; +INSERT IGNORE INTO t1 VALUES(3,23); +SELECT * FROM t1 FOR UPDATE; +COMMIT; +a b +3 1 +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index efb47f65e6d..ff9f7057622 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1302,6 +1302,20 @@ ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fail update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ERROR 42S22: Unknown column 't1.id' in 'where clause' drop table t3,t2,t1; +CREATE TABLE t1 ( +c1 VARCHAR(8), c2 VARCHAR(8), +PRIMARY KEY (c1, c2) +) ENGINE=InnoDB; +CREATE TABLE t2 ( +c0 INT PRIMARY KEY, +c1 VARCHAR(8) UNIQUE, +FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue'); +INSERT INTO t2 VALUES (10, 'old'), (20, 'other'); +UPDATE t1 SET c1 = 'other' WHERE c1 = 'old'; +ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 2 would lead to a duplicate entry +DROP TABLE t2,t1; create table t1( id int primary key, pid int, @@ -1576,6 +1590,12 @@ ERROR 42S21: Duplicate column name 'c1' alter table t1 add key (c1,c1,c2); ERROR 42S21: Duplicate column name 'c1' drop table t1; +create table t1(a int(1) , b int(1)) engine=innodb; +insert into t1 values ('1111', '3333'); +select distinct concat(a, b) from t1; +concat(a, b) +11113333 +drop table t1; CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB; SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE); ERROR HY000: The used table type doesn't support FULLTEXT indexes @@ -1674,7 +1694,7 @@ variable_value - @innodb_rows_deleted_orig 71 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; variable_value - @innodb_rows_inserted_orig -1066 +1071 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig 866 @@ -1693,74 +1713,7 @@ variable_value - @innodb_row_lock_time_max_orig SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg'; variable_value - @innodb_row_lock_time_avg_orig 0 -SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops; -show variables like "innodb_sync_spin_loops"; -Variable_name Value -innodb_sync_spin_loops 30 -set global innodb_sync_spin_loops=1000; -show variables like "innodb_sync_spin_loops"; -Variable_name Value -innodb_sync_spin_loops 1000 -set global innodb_sync_spin_loops=0; -show variables like "innodb_sync_spin_loops"; -Variable_name Value -innodb_sync_spin_loops 0 -set global innodb_sync_spin_loops=20; -show variables like "innodb_sync_spin_loops"; -Variable_name Value -innodb_sync_spin_loops 20 -set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig; -show variables like "innodb_thread_concurrency"; -Variable_name Value -innodb_thread_concurrency 0 -set global innodb_thread_concurrency=1001; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_concurrency value: '1001' -show variables like "innodb_thread_concurrency"; -Variable_name Value -innodb_thread_concurrency 1000 -set global innodb_thread_concurrency=0; -show variables like "innodb_thread_concurrency"; -Variable_name Value -innodb_thread_concurrency 0 -set global innodb_thread_concurrency=16; -show variables like "innodb_thread_concurrency"; -Variable_name Value -innodb_thread_concurrency 16 -show variables like "innodb_concurrency_tickets"; -Variable_name Value -innodb_concurrency_tickets 500 -set global innodb_concurrency_tickets=1000; -show variables like "innodb_concurrency_tickets"; -Variable_name Value -innodb_concurrency_tickets 1000 -set global innodb_concurrency_tickets=0; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' -show variables like "innodb_concurrency_tickets"; -Variable_name Value -innodb_concurrency_tickets 1 -set global innodb_concurrency_tickets=500; -show variables like "innodb_concurrency_tickets"; -Variable_name Value -innodb_concurrency_tickets 500 -show variables like "innodb_thread_sleep_delay"; -Variable_name Value -innodb_thread_sleep_delay 10000 -set global innodb_thread_sleep_delay=100000; -show variables like "innodb_thread_sleep_delay"; -Variable_name Value -innodb_thread_sleep_delay 100000 -set global innodb_thread_sleep_delay=0; -show variables like "innodb_thread_sleep_delay"; -Variable_name Value -innodb_thread_sleep_delay 0 -set global innodb_thread_sleep_delay=10000; -show variables like "innodb_thread_sleep_delay"; -Variable_name Value -innodb_thread_sleep_delay 10000 set storage_engine=INNODB; -set session old_alter_table=1; drop table if exists t1,t2,t3; --- Testing varchar --- --- Testing varchar --- @@ -2334,7 +2287,6 @@ select * from t1 where a=20 and b is null; a b 20 NULL drop table t1; -set session old_alter_table=0; create table t1 (v varchar(65530), key(v)); Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes diff --git a/mysql-test/suite/innodb/r/innodb_bug12661768.result b/mysql-test/suite/innodb/r/innodb_bug12661768.result new file mode 100644 index 00000000000..1f2401ddd47 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug12661768.result @@ -0,0 +1,2 @@ +SET SESSION foreign_key_checks=0; +ERROR 23000: Upholding foreign key constraints for table 'bug12661768_1', entry '3-bbb', key 2 would lead to a duplicate entry diff --git a/mysql-test/suite/innodb/r/innodb_bug59641.result b/mysql-test/suite/innodb/r/innodb_bug59641.result index c94c2105e98..5062c69558b 100644 --- a/mysql-test/suite/innodb/r/innodb_bug59641.result +++ b/mysql-test/suite/innodb/r/innodb_bug59641.result @@ -1,5 +1,5 @@ call mtr.add_suppression("Found 3 prepared XA transactions"); -FLUSH TABLES; +flush tables; CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); COMMIT; diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index d401093cabc..36b37e4ccd8 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -572,7 +572,7 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref p p 28 const 2 Using where +1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 2 diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index bd8760b8f79..30e58aacff5 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -97,6 +97,8 @@ insert into t1 values (1,1), (2,null), (3,1), (4,1), # Demonstrate that for the SELECT statement # used later in the test JT_EQ_REF access method is used. # +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; @@ -119,7 +121,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL @@ -150,6 +152,7 @@ key_len NULL ref NULL rows NULL Extra +set optimizer_switch=@tmp_optimizer_switch; # # Demonstrate that the reported SELECT statement # no longer produces warnings. @@ -301,6 +304,8 @@ begin; # is retreived and processed first. # # Verify that JT_EQ_REF is used. +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; explain select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; @@ -323,7 +328,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using where +Extra Using index condition; Using where id 2 select_type DERIVED table NULL @@ -354,6 +359,7 @@ key_len NULL ref NULL rows NULL Extra +set optimizer_switch=@tmp_optimizer_switch; # Lock the record. select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 813288fe601..5223c566475 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -316,7 +316,7 @@ INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); DELETE IGNORE FROM t1 WHERE i = 1; Warnings: -Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION) +Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION) SELECT * FROM t1, t2; i i 1 1 @@ -1430,9 +1430,9 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; +set @@optimizer_switch='in_to_exists=on,materialization=off'; explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; +select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found @@ -2685,11 +2685,54 @@ rows 3 Extra Using index DROP TABLE t1; # +# Bug#56862 Execution of a query that uses index merge returns a wrong result +# +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int, +b int, +INDEX idx(a)) +ENGINE=INNODB; +INSERT INTO t1(a,b) VALUES +(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), +(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), +(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), +(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); +SET SESSION sort_buffer_size = 1024*36; +EXPLAIN +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where +SELECT COUNT(*) FROM +(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +COUNT(*) +1537 +SET SESSION sort_buffer_size = DEFAULT; +DROP TABLE t1; +# # ALTER TABLE IGNORE didn't ignore duplicates for unique add index # create table t1 (a int primary key, b int) engine = innodb; insert into t1 values (1,1),(2,1); alter ignore table t1 add unique `main` (b); +select * from t1; +a b +1 1 drop table t1; End of 5.1 tests # @@ -2737,8 +2780,7 @@ SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1536 -2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where +1 SIMPLE t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; diff --git a/mysql-test/suite/innodb/r/innodb_replace.result b/mysql-test/suite/innodb/r/innodb_replace.result new file mode 100644 index 00000000000..30009b8ddc9 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_replace.result @@ -0,0 +1,77 @@ +# +#Bug#11759688 52020: InnoDB can still deadlock +#on just INSERT...ON DUPLICATE KEY +#a.k.a. Bug#7975 deadlock without any locking, simple select and update +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES(3,1); +BEGIN; +SET DEBUG_SYNC='write_row_noreplace SIGNAL insert1 WAIT_FOR select1'; +INSERT INTO t1 VALUES(3,2); +SET DEBUG_SYNC='now WAIT_FOR insert1'; +SELECT * FROM t1 LOCK IN SHARE MODE; +a b +3 1 +SELECT * FROM t1 FOR UPDATE; +SET DEBUG_SYNC='now SIGNAL select1'; +ERROR 23000: Duplicate entry '3' for key 'PRIMARY' +INSERT INTO t1 VALUES(3,3) ON DUPLICATE KEY UPDATE b=b+10; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +COMMIT; +SET DEBUG_SYNC='write_row_replace SIGNAL insert2 WAIT_FOR select2'; +REPLACE INTO t1 VALUES(3,4); +SET DEBUG_SYNC='now WAIT_FOR insert2'; +SELECT * FROM t1; +a b +3 11 +SELECT * FROM t1 LOCK IN SHARE MODE; +SET DEBUG_SYNC='now SIGNAL select2'; +SET DEBUG_SYNC='write_row_replace SIGNAL insert3 WAIT_FOR select3'; +INSERT INTO t1 VALUES(3,5) ON DUPLICATE KEY UPDATE b=b+20; +a b +3 4 +SET DEBUG_SYNC='now WAIT_FOR insert3'; +SELECT b FROM t1 LOCK IN SHARE MODE; +SET DEBUG_SYNC='now SIGNAL select3'; +b +24 +SET DEBUG_SYNC='write_row_noreplace SIGNAL insert4 WAIT_FOR select4'; +LOAD DATA INFILE '../../std_data/loaddata5.dat' INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b); +SET DEBUG_SYNC='now WAIT_FOR insert4'; +SELECT b FROM t1 WHERE a=3 LOCK IN SHARE MODE; +b +24 +SELECT b FROM t1 WHERE a=3 FOR UPDATE; +SET DEBUG_SYNC='now SIGNAL select4'; +b +24 +ERROR 23000: Duplicate entry '3' for key 'PRIMARY' +SET DEBUG_SYNC='write_row_noreplace SIGNAL insert5 WAIT_FOR select5'; +LOAD DATA INFILE '../../std_data/loaddata5.dat' IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b); +SET DEBUG_SYNC='now WAIT_FOR insert5'; +SELECT * FROM t1; +a b +3 24 +SELECT * FROM t1 WHERE a=3 LOCK IN SHARE MODE; +a b +3 24 +SELECT * FROM t1 WHERE a=3 FOR UPDATE; +SET DEBUG_SYNC='now SIGNAL select5'; +a b +3 24 +SET DEBUG_SYNC='write_row_replace SIGNAL insert6 WAIT_FOR select6'; +LOAD DATA INFILE '../../std_data/loaddata5.dat' REPLACE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (a, b); +SET DEBUG_SYNC='now WAIT_FOR insert6'; +SELECT * FROM t1; +a b +1 2 +3 24 +5 6 +SELECT a,b FROM t1 LOCK IN SHARE MODE; +SET DEBUG_SYNC='now SIGNAL select6'; +a b +1 2 +3 4 +5 6 +SET DEBUG_SYNC='RESET'; +DROP TABLE t1; |