diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2017-04-26 15:31:16 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2017-04-26 23:03:34 +0300 |
commit | 7fc93fd60adc4253a28a5de9ca59a5988bcaaad2 (patch) | |
tree | 1e264adaf52cf8e10fac20a14cc290dfe9ff97f0 | |
parent | 14fe6dd23947636caca6743425ad0501f9b73f51 (diff) | |
download | mariadb-git-7fc93fd60adc4253a28a5de9ca59a5988bcaaad2.tar.gz |
Adapt a test from MySQL
-rw-r--r-- | mysql-test/suite/innodb/include/innodb_isolation_selects.inc | 15 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-isolation.result | 1411 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-isolation.test | 354 |
3 files changed, 1780 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/include/innodb_isolation_selects.inc b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc new file mode 100644 index 00000000000..922f5c1c42c --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc @@ -0,0 +1,15 @@ +# These same selects are used many times in innodb_isolation.test + +--echo ########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +SELECT COUNT(*) FROM t1; +SELECT COUNT(c1) FROM t1; +SELECT COUNT(c2) FROM t1; # Uses secondary index k2 +SELECT COUNT(c3) FROM t1; # Uses clustered index +SELECT SUM(c1) FROM t1; # Uses secondary index +SELECT SUM(c2) FROM t1; # Uses secondary index +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; # Uses clustered index +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +--echo ############################################### diff --git a/mysql-test/suite/innodb/r/innodb-isolation.result b/mysql-test/suite/innodb/r/innodb-isolation.result new file mode 100644 index 00000000000..2248d25b39a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-isolation.result @@ -0,0 +1,1411 @@ +# +# WL#6742 - Test the interaction of multiple transactions using +# different isolation levels to make sure that the value returned +# by count(*) always reflects the correct view of the table according +# to the transaction's selected isolation level. +# +# +# Traverse various indexes to get the right counts. +# This especially tests count(*) which is pushed down to InnoDB in WL#6742. +# +CREATE TABLE t1 ( +c1 INT AUTO_INCREMENT PRIMARY KEY, +c2 INT, +c3 INT, +c4 INT, +INDEX k2(c2) +) Engine=InnoDB; +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 (SELECT * FROM t1); +affected rows: 10 +info: Records: 10 Duplicates: 0 Warnings: 0 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Do some DML in the default connection and leave the transaction pending. +# +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 6; +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Start transactions of Repeatable Read, Read Committed, and Read Uncommitted +# +# Connection 1 REPEATABLE READ +# +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 7; +affected rows: 1 +INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Test a lock wait timeout during COUNT(*) +# +SET innodb_lock_wait_timeout = 1; +SELECT COUNT(*) FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# +# Connection 2 READ COMMITTED +# +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 8; +affected rows: 1 +INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +9 1 1 1 +10 1 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +1015 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 12 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +9 1 +10 1 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 3 READ UNCOMMITTED +# +connect con3,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +57 +SELECT SUM(c2) FROM t1; +SUM(c2) +1119 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3333 12 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +9 1 +10 1 +11 100 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1); +affected rows: 1 +DELETE FROM t1 WHERE c1 in(9); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection default REPEATABLE READ +# +connection default; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Commit the 3 extra connections +# +# Connection 1 REPEATABLE READ +# +connection con1; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 2 READ COMMITTED +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +9 1 1 1 +10 1 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +1015 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 12 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +9 1 +10 1 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 3 READ UNCOMMITTED +# +connection con3; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 2 READ COMMITTED +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +10 1 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +63 +SELECT SUM(c2) FROM t1; +SUM(c2) +11024 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +10 1 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +10 1 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +63 +SELECT SUM(c2) FROM t1; +SUM(c2) +11024 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +10 1 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 1 REPEATABLE READ +# +connection con1; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Select the first 5 records FOR UPDATE using count(*) in a subquery. +# The second record is still pending so we get a lock timeout. +# +SET innodb_lock_wait_timeout = 1; +SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT COUNT(*) FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +67 +SELECT SUM(c2) FROM t1; +SUM(c2) +11127 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Show The EXPLAIN output for these queries; +# +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 # +EXPLAIN SELECT COUNT(*) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL 12 Using index +EXPLAIN SELECT COUNT(c1) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL 12 Using index +EXPLAIN SELECT COUNT(c2) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL 12 # +EXPLAIN SELECT COUNT(c3) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 # +EXPLAIN SELECT SUM(c1) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL 12 # +EXPLAIN SELECT SUM(c2) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL 12 # +EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 12 # +EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 8 # +2 SUBQUERY t1 index NULL k2 5 NULL 12 # +EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 8 # +2 SUBQUERY t1 index NULL k2 5 NULL 12 # +EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 7 # +2 SUBQUERY t1 index NULL k2 5 NULL 12 # +# +# Make all indexes in t2 obsolete to the active repeatable read transaction +# in the default connection. +# +ALTER TABLE t2 row_format=redundant; +# +# Connection default REPEATABLE READ +# Do more DML in the default REPEATABLE READ transaction in order to use recently committed records. +# +connection default; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +UPDATE t1 SET c4 = c2 * 10; +affected rows: 9 +info: Rows matched: 9 Changed: 9 Warnings: 0 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +12 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +12 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +12 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +12 +SELECT SUM(c1) FROM t1; +SUM(c1) +85 +SELECT SUM(c2) FROM t1; +SUM(c2) +11131 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +7.0833 13 1 1.0000 111283 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +7 1 +8 1 +9 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +7 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +6 +############################################### +# +# Table t2 has been altered to a new row format. +# The index should not be useable. +# +SELECT COUNT(*) FROM t2; +ERROR HY000: Table definition has changed, please retry transaction +SELECT * FROM t2; +ERROR HY000: Table definition has changed, please retry transaction +COMMIT; +SELECT COUNT(*) FROM t2; +COUNT(*) +10 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 111280 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 2 +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 111280 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Try COUNT(*) on a DISCARDED table. +# +connection default; +CREATE TABLE t4 LIKE t1; +INSERT INTO t4 (SELECT * FROM t1); +SELECT COUNT(*) FROM t4; +COUNT(*) +9 +ALTER TABLE t4 DISCARD TABLESPACE; +SELECT COUNT(*) FROM t4; +ERROR HY000: Tablespace has been discarded for table `t4` +# +# Test the interaction of a repeatable read transaction +# to changes that happen outside its view. +# +CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +UPDATE t5 SET aa=a, bb=b; +CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +UPDATE t6 SET aa=a, bb=b; +CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +UPDATE t7 SET aa=a, bb=b; +BEGIN; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t5; +COUNT(*) +4 +SELECT * FROM t6; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +SELECT * FROM t7; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +4 +# +# Connection 1 +# +connection con1; +INSERT INTO t5(b) VALUES ("inserted by client 2"); +UPDATE t5 SET a = 10 where a = 1; +UPDATE t5 SET b = "updated by client 2" where a = 2; +DELETE FROM t5 WHERE a = 3; +SELECT * FROM t5; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t5; +COUNT(*) +4 +INSERT INTO t6(b) VALUES ("inserted by client 2"); +UPDATE t6 SET a = 10 where a = 1; +UPDATE t6 SET b = "updated by client 2" where a = 2; +DELETE FROM t6 WHERE a = 3; +SELECT * FROM t6; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +INSERT INTO t7(b) VALUES ("inserted by client 2"); +UPDATE t7 SET a = 10 where a = 1; +UPDATE t7 SET b = "updated by client 2" where a = 2; +DELETE FROM t7 WHERE a = 3; +SELECT * FROM t7; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +4 +# +# Connection default +# +connection default; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +INSERT INTO t5(b) VALUES ("inserted by client 1"); +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +11 inserted by client 1 NULL NULL +UPDATE t5 SET a = a + 100; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +102 updated by client 2 2 inserted by client 1 +104 inserted by client 1 4 inserted by client 1 +105 inserted by client 2 NULL NULL +110 inserted by client 1 1 inserted by client 1 +111 inserted by client 1 NULL NULL +SELECT COUNT(*) FROM t5; +COUNT(*) +7 +UPDATE t6 SET b = "updated by client 2"; +SELECT * FROM t6; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 updated by client 2 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +6 +DELETE FROM t7; +SELECT * FROM t7; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +2 +COMMIT; +SELECT * FROM t5; +a b aa bb +102 updated by client 2 2 inserted by client 1 +104 inserted by client 1 4 inserted by client 1 +105 inserted by client 2 NULL NULL +110 inserted by client 1 1 inserted by client 1 +111 inserted by client 1 NULL NULL +SELECT COUNT(*) FROM t5; +COUNT(*) +5 +SELECT * FROM t6; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +SELECT * FROM t7; +a b aa bb +SELECT COUNT(*) FROM t7; +COUNT(*) +0 +# +# Cleanup +# +DROP TABLE t1,t2,t4,t5,t6,t7; +disconnect con1; +disconnect con2; +disconnect con3; +# +# Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*) +# +CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, +c2 INT NOT NULL DEFAULT 1, +c3 char(20) DEFAULT '', +KEY c2_idx (c2)) ENGINE=InnoDB; +INSERT INTO t1(c1) VALUES (1), (2), (3); +INSERT INTO t1(c1) SELECT c1 + 10 FROM t1; +INSERT INTO t1(c1) SELECT c1 + 100 FROM t1; +CREATE TABLE t2 SELECT * FROM t1; +EXPLAIN SELECT COUNT(*) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +EXPLAIN SELECT COUNT(*) FROM t1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/innodb-isolation.test b/mysql-test/suite/innodb/t/innodb-isolation.test new file mode 100644 index 00000000000..56cd668b305 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-isolation.test @@ -0,0 +1,354 @@ + +#Note:- WL6742 has been removed from 5.7 (Bug 23046302), +# but we are keeping this test since it tests +# count(*) correctness for various isolation +# levels. + +--echo # +--echo # WL#6742 - Test the interaction of multiple transactions using +--echo # different isolation levels to make sure that the value returned +--echo # by count(*) always reflects the correct view of the table according +--echo # to the transaction's selected isolation level. +--echo # + +--source include/have_innodb.inc + +--disable_query_log +let $MYSQLD_DATADIR= `select @@datadir`; +let $initial_timeout=`select @@innodb_lock_wait_timeout`; +--enable_query_log + +--echo # +--echo # Traverse various indexes to get the right counts. +--echo # This especially tests count(*) which is pushed down to InnoDB in WL#6742. +--echo # +CREATE TABLE t1 ( + c1 INT AUTO_INCREMENT PRIMARY KEY, + c2 INT, + c3 INT, + c4 INT, + INDEX k2(c2) +) Engine=InnoDB; +let $1=10; +while ($1 > 0) { + INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); + dec $1; +} + +CREATE TABLE t2 LIKE t1; +--enable_info +INSERT INTO t2 (SELECT * FROM t1); +--disable_info + +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Do some DML in the default connection and leave the transaction pending. +--echo # +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +--enable_info +UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1; +DELETE FROM t1 WHERE c1 = 6; +--disable_info +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted +--echo # +--echo # Connection 1 REPEATABLE READ +--echo # +connect (con1,localhost,root,,); +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +--source suite/innodb/include/innodb_isolation_selects.inc +--enable_info +UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2; +DELETE FROM t1 WHERE c1 = 7; +INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1); +--disable_info +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Test a lock wait timeout during COUNT(*) +--echo # +SET innodb_lock_wait_timeout = 1; +--error ER_LOCK_WAIT_TIMEOUT +SELECT COUNT(*) FROM t1 FOR UPDATE; + + +--echo # +--echo # Connection 2 READ COMMITTED +--echo # +connect (con2,localhost,root,,); +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +--source suite/innodb/include/innodb_isolation_selects.inc +--enable_info +UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3; +DELETE FROM t1 WHERE c1 = 8; +INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1); +--disable_info +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 3 READ UNCOMMITTED +--echo # +connect (con3,localhost,root,,); +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +--source suite/innodb/include/innodb_isolation_selects.inc +--enable_info +UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4; +INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1); +DELETE FROM t1 WHERE c1 in(9); +--disable_info +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection default REPEATABLE READ +--echo # +connection default; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Commit the 3 extra connections +--echo # +--echo # Connection 1 REPEATABLE READ +--echo # +connection con1; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 2 READ COMMITTED +--echo # +connection con2; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 3 READ UNCOMMITTED +--echo # +connection con3; +--source suite/innodb/include/innodb_isolation_selects.inc +COMMIT; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 2 READ COMMITTED +--echo # +connection con2; +--source suite/innodb/include/innodb_isolation_selects.inc +COMMIT; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 1 REPEATABLE READ +--echo # +connection con1; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Select the first 5 records FOR UPDATE using count(*) in a subquery. +--echo # The second record is still pending so we get a lock timeout. +--echo # +SET innodb_lock_wait_timeout = 1; +--error ER_LOCK_WAIT_TIMEOUT +SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE; +--error ER_LOCK_WAIT_TIMEOUT +SELECT COUNT(*) FROM t1 FOR UPDATE; +COMMIT; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Show The EXPLAIN output for these queries; +--echo # +# column 10 is the row count provided by handler::info(). In InnoDB, this is +# a statistical estimate. After the multi-transactional changes above, +# Solaris reports 10 rows which is correct, but other OSes report 9. +--replace_column 10 # +EXPLAIN SELECT * FROM t1; +EXPLAIN SELECT COUNT(*) FROM t1; +EXPLAIN SELECT COUNT(c1) FROM t1; +--replace_column 10 # +EXPLAIN SELECT COUNT(c2) FROM t1; +--replace_column 10 # +EXPLAIN SELECT COUNT(c3) FROM t1; +--replace_column 10 # +EXPLAIN SELECT SUM(c1) FROM t1; +--replace_column 10 # +EXPLAIN SELECT SUM(c2) FROM t1; +--replace_column 10 # +EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +--replace_column 10 # +EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +--replace_column 10 # +EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +--replace_column 10 # +EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); + +--echo # +--echo # Make all indexes in t2 obsolete to the active repeatable read transaction +--echo # in the default connection. +--echo # +ALTER TABLE t2 row_format=redundant; + +--echo # +--echo # Connection default REPEATABLE READ +--echo # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records. +--echo # +connection default; +--source suite/innodb/include/innodb_isolation_selects.inc +--enable_info +UPDATE t1 SET c4 = c2 * 10; +--disable_info +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Table t2 has been altered to a new row format. +--echo # The index should not be useable. +--echo # +--error ER_TABLE_DEF_CHANGED +SELECT COUNT(*) FROM t2; +--error ER_TABLE_DEF_CHANGED +SELECT * FROM t2; + +COMMIT; +SELECT COUNT(*) FROM t2; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Connection 2 +--echo # +connection con2; +--source suite/innodb/include/innodb_isolation_selects.inc + +--echo # +--echo # Try COUNT(*) on a DISCARDED table. +--echo # +connection default; +CREATE TABLE t4 LIKE t1; +INSERT INTO t4 (SELECT * FROM t1); +SELECT COUNT(*) FROM t4; +ALTER TABLE t4 DISCARD TABLESPACE; +--error ER_TABLESPACE_DISCARDED +SELECT COUNT(*) FROM t4; + + +--echo # +--echo # Test the interaction of a repeatable read transaction +--echo # to changes that happen outside its view. +--echo # + +CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +UPDATE t5 SET aa=a, bb=b; +CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +UPDATE t6 SET aa=a, bb=b; +CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +UPDATE t7 SET aa=a, bb=b; +BEGIN; +SELECT * FROM t5; +SELECT COUNT(*) FROM t5; +SELECT * FROM t6; +SELECT COUNT(*) FROM t6; +SELECT * FROM t7; +SELECT COUNT(*) FROM t7; + +--echo # +--echo # Connection 1 +--echo # +connection con1; +INSERT INTO t5(b) VALUES ("inserted by client 2"); +UPDATE t5 SET a = 10 where a = 1; +UPDATE t5 SET b = "updated by client 2" where a = 2; +DELETE FROM t5 WHERE a = 3; +SELECT * FROM t5; +SELECT COUNT(*) FROM t5; +INSERT INTO t6(b) VALUES ("inserted by client 2"); +UPDATE t6 SET a = 10 where a = 1; +UPDATE t6 SET b = "updated by client 2" where a = 2; +DELETE FROM t6 WHERE a = 3; +SELECT * FROM t6; +SELECT COUNT(*) FROM t6; +INSERT INTO t7(b) VALUES ("inserted by client 2"); +UPDATE t7 SET a = 10 where a = 1; +UPDATE t7 SET b = "updated by client 2" where a = 2; +DELETE FROM t7 WHERE a = 3; +SELECT * FROM t7; +SELECT COUNT(*) FROM t7; + +--echo # +--echo # Connection default +--echo # +connection default; +SELECT * FROM t5; +INSERT INTO t5(b) VALUES ("inserted by client 1"); +SELECT * FROM t5; +UPDATE t5 SET a = a + 100; +SELECT * FROM t5; +SELECT COUNT(*) FROM t5; + +UPDATE t6 SET b = "updated by client 2"; +SELECT * FROM t6; +SELECT COUNT(*) FROM t6; + +DELETE FROM t7; +SELECT * FROM t7; +SELECT COUNT(*) FROM t7; + +COMMIT; +SELECT * FROM t5; +SELECT COUNT(*) FROM t5; +SELECT * FROM t6; +SELECT COUNT(*) FROM t6; +SELECT * FROM t7; +SELECT COUNT(*) FROM t7; + +--echo # +--echo # Cleanup +--echo # +DROP TABLE t1,t2,t4,t5,t6,t7; +disconnect con1; +disconnect con2; +disconnect con3; +--disable_query_log +eval set global innodb_lock_wait_timeout=$initial_timeout; +--enable_query_log + +--echo # +--echo # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*) +--echo # + +CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, + c2 INT NOT NULL DEFAULT 1, + c3 char(20) DEFAULT '', + KEY c2_idx (c2)) ENGINE=InnoDB; + +INSERT INTO t1(c1) VALUES (1), (2), (3); +INSERT INTO t1(c1) SELECT c1 + 10 FROM t1; +INSERT INTO t1(c1) SELECT c1 + 100 FROM t1; + +CREATE TABLE t2 SELECT * FROM t1; + +let query1= SELECT COUNT(*) FROM t1; +let query2= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx); +let query3= SELECT COUNT(*) FROM t1, t2; +let query4= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2; + +eval EXPLAIN $query1; +eval EXPLAIN $query2; +eval EXPLAIN $query3; +eval EXPLAIN $query4; + +DROP TABLE t1, t2; |