1connect con1,localhost,root,,; 2# 3# Bug#20837 Apparent change of isolation level 4# during transaction 5# 6# Bug#53343 completion_type=1, COMMIT/ROLLBACK 7# AND CHAIN don't preserve the isolation 8# level 9connection default; 10SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 11CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; 12INSERT INTO t1 VALUES (1),(2); 13COMMIT; 14START TRANSACTION; 15SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 16ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress 17COMMIT; 18SET @@autocommit=0; 19COMMIT; 20SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 21START TRANSACTION; 22SELECT @@tx_isolation; 23@@tx_isolation 24REPEATABLE-READ 25Should be REPEATABLE READ 26SELECT * FROM t1; 27s1 281 292 30SELECT @@tx_isolation; 31@@tx_isolation 32REPEATABLE-READ 33Should be REPEATABLE READ 34INSERT INTO t1 VALUES (-1); 35SELECT @@tx_isolation; 36@@tx_isolation 37REPEATABLE-READ 38Should be REPEATABLE READ 39COMMIT; 40START TRANSACTION; 41SELECT * FROM t1; 42s1 431 442 45-1 46SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 47connection con1; 48START TRANSACTION; 49INSERT INTO t1 VALUES (1000); 50COMMIT; 51connection default; 52We should not be able to read the '1000' 53SELECT * FROM t1; 54s1 551 562 57-1 58COMMIT; 59Now, the '1000' should appear. 60START TRANSACTION; 61SELECT * FROM t1; 62s1 631 642 65-1 661000 67COMMIT; 68SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 69connection default; 70SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 71START TRANSACTION; 72connection con1; 73START TRANSACTION; 74INSERT INTO t1 VALUES (1001); 75COMMIT; 76connection default; 77SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 78COUNT(*) 791 80Should be 1 81COMMIT AND CHAIN; 82connection con1; 83INSERT INTO t1 VALUES (1002); 84COMMIT; 85connection default; 86SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 87COUNT(*) 881 89Should be 1 90COMMIT; 91SELECT * FROM t1; 92s1 931 942 95-1 961000 971001 981002 99DELETE FROM t1 WHERE s1 >= 1000; 100COMMIT; 101connection default; 102SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 103START TRANSACTION; 104connection con1; 105START TRANSACTION; 106INSERT INTO t1 VALUES (1001); 107COMMIT; 108connection default; 109SELECT COUNT(*) FROM t1 WHERE s1 = 1001; 110COUNT(*) 1111 112Should be 1 113ROLLBACK AND CHAIN; 114connection con1; 115INSERT INTO t1 VALUES (1002); 116COMMIT; 117connection default; 118SELECT COUNT(*) FROM t1 WHERE s1 = 1002; 119COUNT(*) 1201 121Should be 1 122COMMIT; 123SELECT * FROM t1; 124s1 1251 1262 127-1 1281001 1291002 130DELETE FROM t1 WHERE s1 >= 1000; 131COMMIT; 132SET @@completion_type=1; 133connection default; 134SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 135START TRANSACTION; 136connection con1; 137START TRANSACTION; 138INSERT INTO t1 VALUES (1001); 139COMMIT; 140connection default; 141SELECT * FROM t1 WHERE s1 >= 1000; 142s1 1431001 144Should see 1001 145COMMIT AND NO CHAIN; 146default transaction is now in REPEATABLE READ 147connection con1; 148INSERT INTO t1 VALUES (1002); 149COMMIT; 150connection default; 151SELECT * FROM t1 WHERE s1 >= 1000; 152s1 1531001 1541002 155Should see 1001 and 1002 156connection con1; 157INSERT INTO t1 VALUES (1003); 158COMMIT; 159connection default; 160SELECT * FROM t1 WHERE s1 >= 1000; 161s1 1621001 1631002 164Should see 1001 and 1002, but NOT 1003 165COMMIT; 166SELECT * FROM t1; 167s1 1681 1692 170-1 1711001 1721002 1731003 174DELETE FROM t1 WHERE s1 >= 1000; 175COMMIT AND NO CHAIN; 176SET @@completion_type=0; 177COMMIT; 178connection default; 179SET @@completion_type=1; 180COMMIT AND NO CHAIN; 181SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 182START TRANSACTION; 183connection con1; 184START TRANSACTION; 185INSERT INTO t1 VALUES (1001); 186COMMIT; 187connection default; 188SELECT * FROM t1 WHERE s1 >= 1000; 189s1 1901001 191Should see 1001 192ROLLBACK AND NO CHAIN; 193default transaction is now in REPEATABLE READ 194connection con1; 195INSERT INTO t1 VALUES (1002); 196COMMIT; 197connection default; 198SELECT * FROM t1 WHERE s1 >= 1000; 199s1 2001001 2011002 202Should see 1001 and 1002 203connection con1; 204INSERT INTO t1 VALUES (1003); 205COMMIT; 206connection default; 207SELECT * FROM t1 WHERE s1 >= 1000; 208s1 2091001 2101002 211Should see 1001 and 1002, but NOT 1003 212COMMIT; 213SELECT * FROM t1; 214s1 2151 2162 217-1 2181001 2191002 2201003 221DELETE FROM t1 WHERE s1 >= 1000; 222COMMIT AND NO CHAIN; 223SET @@completion_type=0; 224COMMIT; 225connection default; 226SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 227SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 228START TRANSACTION; 229SELECT * FROM t1; 230s1 2311 2322 233-1 234connection con1; 235INSERT INTO t1 VALUES (1000); 236COMMIT; 237connection default; 238SELECT * FROM t1; 239s1 2401 2412 242-1 243Should get same result as above (i.e should not read '1000') 244COMMIT; 245DELETE FROM t1 WHERE s1 >= 1000; 246COMMIT; 247SET @@completion_type=1; 248COMMIT AND NO CHAIN; 249SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 250START TRANSACTION; 251TRUNCATE TABLE t1; 252INSERT INTO t1 VALUES (1000); 253SELECT * FROM t1; 254s1 2551000 256Should read '1000' 257connection con1; 258INSERT INTO t1 VALUES (1001); 259COMMIT; 260connection default; 261SELECT * FROM t1; 262s1 2631000 264Should only read the '1000' as this transaction is now in REP READ 265COMMIT AND NO CHAIN; 266SET @@completion_type=0; 267COMMIT AND NO CHAIN; 268SET @@autocommit=1; 269COMMIT; 270disconnect con1; 271DROP TABLE t1; 272# 273# End of test cases for Bug#20837 274# 275# 276# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); 277# 278# 279# Test 1: Check supported syntax 280START TRANSACTION; 281COMMIT; 282START TRANSACTION READ ONLY; 283COMMIT; 284START TRANSACTION READ WRITE; 285COMMIT; 286START TRANSACTION READ ONLY, READ WRITE; 287ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 288START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT; 289COMMIT; 290START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT; 291COMMIT; 292START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY; 293COMMIT; 294START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE; 295COMMIT; 296START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE; 297ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 298SET TRANSACTION READ ONLY; 299SET TRANSACTION READ WRITE; 300SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 301SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE; 302SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED; 303SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED; 304SET TRANSACTION READ ONLY, READ WRITE; 305ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'READ WRITE' at line 1 306COMMIT; 307# 308# Test 2: Check setting of variable. 309SET SESSION TRANSACTION READ WRITE; 310SELECT @@tx_read_only; 311@@tx_read_only 3120 313SET SESSION TRANSACTION READ ONLY; 314SELECT @@tx_read_only; 315@@tx_read_only 3161 317SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 318SELECT @@tx_read_only; 319@@tx_read_only 3200 321SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; 322SELECT @@tx_read_only; 323@@tx_read_only 3241 325START TRANSACTION; 326# Not allowed inside a transaction 327SET TRANSACTION READ ONLY; 328ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress 329# But these are allowed. 330SET SESSION TRANSACTION READ ONLY; 331SET GLOBAL TRANSACTION READ ONLY; 332COMMIT; 333SET SESSION TRANSACTION READ WRITE; 334SET GLOBAL TRANSACTION READ WRITE; 335# 336# Test 3: Test that write operations are properly blocked. 337CREATE TABLE t1(a INT); 338CREATE TEMPORARY TABLE temp_t2(a INT); 339SET SESSION TRANSACTION READ ONLY; 340# 1: DDL should be blocked, also on temporary tables. 341CREATE TABLE t3(a INT); 342ERROR 25006: Cannot execute statement in a READ ONLY transaction 343ALTER TABLE t1 COMMENT "Test"; 344ERROR 25006: Cannot execute statement in a READ ONLY transaction 345DROP TABLE t1; 346ERROR 25006: Cannot execute statement in a READ ONLY transaction 347CREATE TEMPORARY TABLE temp_t3(a INT); 348ERROR 25006: Cannot execute statement in a READ ONLY transaction 349ALTER TABLE temp_t2 COMMENT "Test"; 350ERROR 25006: Cannot execute statement in a READ ONLY transaction 351DROP TEMPORARY TABLE temp_t2; 352ERROR 25006: Cannot execute statement in a READ ONLY transaction 353CREATE FUNCTION f1() RETURNS INT RETURN 1; 354ERROR 25006: Cannot execute statement in a READ ONLY transaction 355DROP FUNCTION f1; 356ERROR 25006: Cannot execute statement in a READ ONLY transaction 357CREATE PROCEDURE p1() BEGIN END; 358ERROR 25006: Cannot execute statement in a READ ONLY transaction 359DROP PROCEDURE p1; 360ERROR 25006: Cannot execute statement in a READ ONLY transaction 361CREATE VIEW v1 AS SELECT 1; 362ERROR 25006: Cannot execute statement in a READ ONLY transaction 363SET SESSION TRANSACTION READ WRITE; 364CREATE VIEW v1 AS SELECT 1; 365SET SESSION TRANSACTION READ ONLY; 366DROP VIEW v1; 367ERROR 25006: Cannot execute statement in a READ ONLY transaction 368SET SESSION TRANSACTION READ WRITE; 369DROP VIEW v1; 370SET SESSION TRANSACTION READ ONLY; 371RENAME TABLE t1 TO t2; 372ERROR 25006: Cannot execute statement in a READ ONLY transaction 373RENAME TABLE temp_t2 TO temp_t3; 374ERROR 25006: Cannot execute statement in a READ ONLY transaction 375TRUNCATE TABLE t1; 376ERROR 25006: Cannot execute statement in a READ ONLY transaction 377CREATE DATABASE db1; 378ERROR 25006: Cannot execute statement in a READ ONLY transaction 379DROP DATABASE db1; 380ERROR 25006: Cannot execute statement in a READ ONLY transaction 381SET SESSION TRANSACTION READ WRITE; 382# 2: DML should be blocked on non-temporary tables. 383START TRANSACTION READ ONLY; 384INSERT INTO t1 VALUES (1), (2); 385ERROR 25006: Cannot execute statement in a READ ONLY transaction 386UPDATE t1 SET a= 3; 387ERROR 25006: Cannot execute statement in a READ ONLY transaction 388DELETE FROM t1; 389ERROR 25006: Cannot execute statement in a READ ONLY transaction 390# 3: DML should be allowed on temporary tables. 391INSERT INTO temp_t2 VALUES (1), (2); 392UPDATE temp_t2 SET a= 3; 393DELETE FROM temp_t2; 394# 4: Queries should not be blocked. 395SELECT * FROM t1; 396a 397SELECT * FROM temp_t2; 398a 399HANDLER t1 OPEN; 400HANDLER t1 READ FIRST; 401a 402HANDLER t1 CLOSE; 403HANDLER temp_t2 OPEN; 404HANDLER temp_t2 READ FIRST; 405a 406HANDLER temp_t2 CLOSE; 407# 5: Prepared statements 408PREPARE stmt FROM "DELETE FROM t1"; 409ERROR 25006: Cannot execute statement in a READ ONLY transaction 410PREPARE stmt FROM "DELETE FROM temp_t2"; 411EXECUTE stmt; 412DEALLOCATE PREPARE stmt; 413COMMIT; 414# 6: Stored routines 415CREATE FUNCTION f1() RETURNS INT 416BEGIN 417DELETE FROM t1; 418RETURN 1; 419END| 420CREATE FUNCTION f2() RETURNS INT 421BEGIN 422DELETE FROM temp_t2; 423RETURN 1; 424END| 425CREATE PROCEDURE p1() DELETE FROM t1; 426CREATE PROCEDURE p2() DELETE FROM temp_t2; 427START TRANSACTION READ ONLY; 428SELECT f1(); 429ERROR 25006: Cannot execute statement in a READ ONLY transaction 430SELECT f2(); 431f2() 4321 433CALL p1(); 434ERROR 25006: Cannot execute statement in a READ ONLY transaction 435CALL p2(); 436COMMIT; 437DROP FUNCTION f1; 438DROP FUNCTION f2; 439DROP PROCEDURE p1; 440DROP PROCEDURE p2; 441# 7: Views 442CREATE VIEW v1 AS SELECT a FROM t1; 443START TRANSACTION READ ONLY; 444INSERT INTO v1 VALUES (1), (2); 445ERROR 25006: Cannot execute statement in a READ ONLY transaction 446SELECT * FROM v1; 447a 448COMMIT; 449DROP VIEW v1; 450# 8: LOCK TABLE 451SET SESSION TRANSACTION READ ONLY; 452LOCK TABLE t1 WRITE; 453ERROR 25006: Cannot execute statement in a READ ONLY transaction 454LOCK TABLE t1 READ; 455UNLOCK TABLES; 456SET SESSION TRANSACTION READ WRITE; 457DROP TABLE temp_t2, t1; 458# 459# Test 4: SET TRANSACTION, CHAINing transactions 460CREATE TABLE t1(a INT); 461SET SESSION TRANSACTION READ ONLY; 462START TRANSACTION; 463DELETE FROM t1; 464ERROR 25006: Cannot execute statement in a READ ONLY transaction 465COMMIT; 466START TRANSACTION READ WRITE; 467DELETE FROM t1; 468COMMIT; 469SET SESSION TRANSACTION READ WRITE; 470SET TRANSACTION READ ONLY; 471START TRANSACTION; 472DELETE FROM t1; 473ERROR 25006: Cannot execute statement in a READ ONLY transaction 474COMMIT; 475START TRANSACTION READ WRITE; 476DELETE FROM t1; 477COMMIT; 478START TRANSACTION READ ONLY; 479SELECT * FROM t1; 480a 481COMMIT AND CHAIN; 482DELETE FROM t1; 483ERROR 25006: Cannot execute statement in a READ ONLY transaction 484COMMIT; 485START TRANSACTION READ ONLY; 486SELECT * FROM t1; 487a 488ROLLBACK AND CHAIN; 489DELETE FROM t1; 490ERROR 25006: Cannot execute statement in a READ ONLY transaction 491COMMIT; 492DROP TABLE t1; 493# 494# Test 5: Test that reserved keyword ONLY is still allowed as 495# identifier - both directly and in SPs. 496SET @only= 1; 497CREATE TABLE t1 (only INT); 498INSERT INTO t1 (only) values (1); 499SELECT only FROM t1 WHERE only = 1; 500only 5011 502DROP TABLE t1; 503CREATE PROCEDURE p1() 504BEGIN 505DECLARE only INT DEFAULT 1; 506END| 507CALL p1(); 508DROP PROCEDURE p1; 509# 510# Test 6: Check that XA transactions obey default access mode. 511CREATE TABLE t1(a INT); 512SET TRANSACTION READ ONLY; 513XA START 'test1'; 514INSERT INTO t1 VALUES (1); 515ERROR 25006: Cannot execute statement in a READ ONLY transaction 516UPDATE t1 SET a=2; 517ERROR 25006: Cannot execute statement in a READ ONLY transaction 518DELETE FROM t1; 519ERROR 25006: Cannot execute statement in a READ ONLY transaction 520XA END 'test1'; 521XA PREPARE 'test1'; 522XA COMMIT 'test1'; 523DROP TABLE t1; 524# 525# Test 7: SET TRANSACTION inside stored routines 526CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; 527CALL p1(); 528SELECT @@tx_read_only; 529@@tx_read_only 5301 531SET SESSION TRANSACTION READ WRITE; 532DROP PROCEDURE p1; 533CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, 534ISOLATION LEVEL SERIALIZABLE; 535CALL p1(); 536SELECT @@tx_read_only; 537@@tx_read_only 5381 539SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; 540DROP PROCEDURE p1; 541CREATE FUNCTION f1() RETURNS INT 542BEGIN 543SET SESSION TRANSACTION READ ONLY; 544RETURN 1; 545END| 546SELECT f1(); 547f1() 5481 549SELECT @@tx_read_only; 550@@tx_read_only 5511 552SET SESSION TRANSACTION READ WRITE; 553DROP FUNCTION f1; 554CREATE FUNCTION f1() RETURNS INT 555BEGIN 556SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; 557RETURN 1; 558END| 559SELECT f1(); 560f1() 5611 562SELECT @@tx_read_only; 563@@tx_read_only 5641 565SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; 566DROP FUNCTION f1; 567# 568# Test 8: SET TRANSACTION and auto-commit 569SELECT @@autocommit; 570@@autocommit 5711 572CREATE TABLE t1(a INT) engine=InnoDB; 573SET TRANSACTION READ ONLY; 574SELECT * FROM t1; 575a 576# This statement should work, since last statement committed. 577INSERT INTO t1 VALUES (1); 578DROP TABLE t1; 579