1SET @auto= @@global.autocommit; 2SET @@global.autocommit= off; 3SET @iso= @@global.tx_isolation; 4Warnings: 5Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 6SET @@global.tx_isolation= 'serializable'; 7Warnings: 8Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 9SET @completion= @@global.completion_type; 10SET @@global.completion_type= 'no_chain'; 11SET @lock_timeout= @@global.lock_wait_timeout; 12SET @@global.lock_wait_timeout= 1; 13SELECT @@global.lock_wait_timeout; 14@@global.lock_wait_timeout 151 16SET @innodb_lock_timeout= @@global.innodb_lock_wait_timeout; 17SET @@global.innodb_lock_wait_timeout= 1; 18SELECT @@global.innodb_lock_wait_timeout; 19@@global.innodb_lock_wait_timeout 201 21# Additional tests for WL#5217 by QA, testplan 1.1 22CREATE TABLE t1 23(a INT NOT NULL, 24b varchar (64), 25INDEX ind_t1 (b,a), 26PRIMARY KEY (a)) 27ENGINE = InnoDB 28PARTITION BY RANGE (a) 29SUBPARTITION BY HASH (a) SUBPARTITIONS 3 30(PARTITION pNeg VALUES LESS THAN (0) 31(SUBPARTITION subp0 , 32SUBPARTITION subp1 , 33SUBPARTITION subp2 ), 34PARTITION `p0-29` VALUES LESS THAN (30) 35(SUBPARTITION subp3 , 36SUBPARTITION subp4 , 37SUBPARTITION subp5 ), 38PARTITION `p30-299` VALUES LESS THAN (300) 39(SUBPARTITION subp6 , 40SUBPARTITION subp7 , 41SUBPARTITION subp8 ), 42PARTITION `p300-2999` VALUES LESS THAN (3000) 43(SUBPARTITION subp9 , 44SUBPARTITION subp10 , 45SUBPARTITION subp11 ), 46PARTITION `p3000-299999` VALUES LESS THAN (300000) 47(SUBPARTITION subp12 , 48SUBPARTITION subp13 , 49SUBPARTITION subp14 )); 50INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-4, '(pNeg-)subp0'); 51INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, '(pNeg-)subp0'); 52INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, '(pNeg-)subp0'); 53INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-1, '(pNeg-)subp0'); 54INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (4, '(p0-29-)subp3'); 55INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (3, '(p0-29-)subp3'); 56INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (2, '(p0-29-)subp3'); 57INSERT INTO t1 PARTITION (`p0-29`, subp3) VALUES (1, '(p0-29-)subp3'); 58INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (24, '(p0-29-)subp5'); 59INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (23, '(p0-29-)subp5'); 60INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (22, '(p0-29-)subp5'); 61INSERT INTO t1 PARTITION (`p0-29`, subp5) VALUES (21, '(p0-29-)subp5'); 62INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (34, '(p30-299-)subp6'); 63INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (33, '(p30-299-)subp6'); 64INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (32, '(p30-299-)subp6'); 65INSERT INTO t1 PARTITION (`p30-299`, subp9) VALUES (31, '(p30-299-)subp6'); 66INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (234, '(p30-299-)subp8'); 67INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (233, '(p30-299-)subp8'); 68INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (232, '(p30-299-)subp8'); 69INSERT INTO t1 PARTITION (`p30-299`, subp8) VALUES (231, '(p30-299-)subp8'); 70INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (304, '(p300-2999-)subp8'); 71INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (303, '(p300-2999-)subp8'); 72INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (302, '(p300-2999-)subp8'); 73INSERT INTO t1 PARTITION (`p300-2999`, subp8) VALUES (301, '(p300-2999-)subp8'); 74INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3004, '(p3000-299999-)subp12'); 75INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3003, '(p3000-299999-)subp12'); 76INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3002, '(p3000-299999-)subp12'); 77INSERT INTO t1 PARTITION (`p3000-299999`, subp12) VALUES (3001, '(p3000-299999-)subp12'); 78INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299996, '(p3000-299999-)subp14'); 79INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299997, '(p3000-299999-)subp14'); 80INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299998, '(p3000-299999-)subp14'); 81INSERT INTO t1 PARTITION (`p3000-299999`, subp14) VALUES (299999, '(p3000-299999-)subp14'); 82CREATE USER test_user_1 IDENTIFIED BY 'testpw'; 83CREATE USER test_user_2 IDENTIFIED BY 'testpw'; 84GRANT ALL PRIVILEGES ON test.* TO test_user_1; 85GRANT ALL PRIVILEGES ON test.* TO test_user_2; 86connect session1, localhost, test_user_1,'testpw',test; 87UPDATE t1 PARTITION (`p0-29`) SET b='p0-29-upd-1' WHERE a BETWEEN 0 AND 9; 88SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`); 89a b 901 p0-29-upd-1 912 p0-29-upd-1 9221 (p0-29-)subp5 9322 (p0-29-)subp5 9423 (p0-29-)subp5 95231 (p30-299-)subp8 96232 (p30-299-)subp8 97233 (p30-299-)subp8 98234 (p30-299-)subp8 9924 (p0-29-)subp5 1003 p0-29-upd-1 10131 (p30-299-)subp6 10232 (p30-299-)subp6 10333 (p30-299-)subp6 10434 (p30-299-)subp6 1054 p0-29-upd-1 106connect session2, localhost, test_user_1,'testpw',test; 107####### expect p30-299 is readable, p0-29 is locked 108SELECT * FROM t1 PARTITION (`p30-299`); 109a b 110231 (p30-299-)subp8 111232 (p30-299-)subp8 112233 (p30-299-)subp8 113234 (p30-299-)subp8 11431 (p30-299-)subp6 11532 (p30-299-)subp6 11633 (p30-299-)subp6 11734 (p30-299-)subp6 118SELECT * FROM t1 PARTITION (`p0-29`); 119ERROR HY000: Lock wait timeout exceeded; try restarting transaction 120connection session1; 121COMMIT WORK; 122connection session2; 123####### expect p0-29 and p30-299 are readable 124SELECT * FROM t1 PARTITION (`p0-29`,`p30-299`); 125a b 1261 p0-29-upd-1 1272 p0-29-upd-1 12821 (p0-29-)subp5 12922 (p0-29-)subp5 13023 (p0-29-)subp5 131231 (p30-299-)subp8 132232 (p30-299-)subp8 133233 (p30-299-)subp8 134234 (p30-299-)subp8 13524 (p0-29-)subp5 1363 p0-29-upd-1 13731 (p30-299-)subp6 13832 (p30-299-)subp6 13933 (p30-299-)subp6 14034 (p30-299-)subp6 1414 p0-29-upd-1 142disconnect session1; 143disconnect session2; 144connection default; 145DROP TABLE t1; 146DROP USER test_user_1; 147DROP USER test_user_2; 148SET @@global.autocommit= @auto; 149SET @@global.tx_isolation= @iso; 150Warnings: 151Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead 152SET @@global.completion_type= @completion; 153SET @@global.lock_wait_timeout= @lock_timeout; 154SET @@global.innodb_lock_wait_timeout= @innodb_lock_timeout; 155