| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
*** Prepare tables and data *** |
| 7 |
CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=innodb; |
| 8 |
CREATE TABLE t2 (a INT) ENGINE=innodb; |
| 9 |
CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=innodb; |
| 10 |
SHOW CREATE TABLE t1; |
| 11 |
Table Create Table |
| 12 |
t1 CREATE TABLE `t1` ( |
| 13 |
`a` int NOT NULL, |
| 14 |
KEY `a` (`a`) |
| 15 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 16 |
SHOW CREATE TABLE t2; |
| 17 |
Table Create Table |
| 18 |
t2 CREATE TABLE `t2` ( |
| 19 |
`a` int DEFAULT NULL |
| 20 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 21 |
SHOW CREATE TABLE t3; |
| 22 |
Table Create Table |
| 23 |
t3 CREATE TABLE `t3` ( |
| 24 |
`a` int NOT NULL, |
| 25 |
KEY `a` (`a`) |
| 26 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 27 |
SELECT @@GLOBAL.replica_transaction_retries; |
| 28 |
@@GLOBAL.replica_transaction_retries |
| 29 |
2 |
| 30 |
SELECT @@GLOBAL.innodb_lock_wait_timeout; |
| 31 |
@@GLOBAL.innodb_lock_wait_timeout |
| 32 |
4 |
| 33 |
include/stop_slave.inc |
| 34 |
BEGIN; |
| 35 |
INSERT INTO t1 VALUES (1); |
| 36 |
INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2); |
| 37 |
INSERT INTO t3 VALUES (3); |
| 38 |
COMMIT; |
| 39 |
|
| 40 |
*** Test deadlock *** |
| 41 |
BEGIN; |
| 42 |
SELECT * FROM t1 FOR UPDATE; |
| 43 |
a |
| 44 |
include/start_slave.inc |
| 45 |
SELECT COUNT(*) FROM t2; |
| 46 |
COUNT(*) |
| 47 |
0 |
| 48 |
COMMIT; |
| 49 |
|
| 50 |
# Test that the performance schema column shows > 0 values. |
| 51 |
|
| 52 |
include/assert.inc [current number of retries should be more than the value saved before deadlock.] |
| 53 |
include/check_slave_is_running.inc |
| 54 |
SELECT * FROM t1; |
| 55 |
a |
| 56 |
1 |
| 57 |
SELECT * FROM t3; |
| 58 |
a |
| 59 |
3 |
| 60 |
|
| 61 |
*** Test lock wait timeout *** |
| 62 |
include/stop_slave.inc |
| 63 |
DELETE FROM t2; |
| 64 |
RESET MASTER; |
| 65 |
CHANGE REPLICATION SOURCE TO SOURCE_LOG_POS=MASTER_POS_BEGIN; |
| 66 |
BEGIN; |
| 67 |
SELECT * FROM t1 FOR UPDATE; |
| 68 |
a |
| 69 |
1 |
| 70 |
START SLAVE; |
| 71 |
Warnings: |
| 72 |
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead |
| 73 |
include/wait_for_slave_sql_error.inc [errno=1205] |
| 74 |
SELECT COUNT(*) FROM t2; |
| 75 |
COUNT(*) |
| 76 |
0 |
| 77 |
COMMIT; |
| 78 |
include/start_slave.inc |
| 79 |
SELECT * FROM t1; |
| 80 |
a |
| 81 |
1 |
| 82 |
1 |
| 83 |
SELECT * FROM t3; |
| 84 |
a |
| 85 |
3 |
| 86 |
3 |
| 87 |
include/check_slave_is_running.inc |
| 88 |
|
| 89 |
*** Test lock wait timeout and purged relay logs *** |
| 90 |
SET @my_max_relay_log_size= @@global.max_relay_log_size; |
| 91 |
SET global max_relay_log_size=0; |
| 92 |
include/stop_slave.inc |
| 93 |
DELETE FROM t2; |
| 94 |
RESET MASTER; |
| 95 |
CHANGE REPLICATION SOURCE TO SOURCE_LOG_POS=MASTER_POS_BEGIN; |
| 96 |
BEGIN; |
| 97 |
SELECT * FROM t1 FOR UPDATE; |
| 98 |
a |
| 99 |
1 |
| 100 |
1 |
| 101 |
START SLAVE; |
| 102 |
Warnings: |
| 103 |
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead |
| 104 |
include/wait_for_slave_sql_error.inc [errno=1205] |
| 105 |
SELECT COUNT(*) FROM t2; |
| 106 |
COUNT(*) |
| 107 |
0 |
| 108 |
COMMIT; |
| 109 |
include/start_slave.inc |
| 110 |
SELECT * FROM t1; |
| 111 |
a |
| 112 |
1 |
| 113 |
1 |
| 114 |
1 |
| 115 |
SELECT * FROM t3; |
| 116 |
a |
| 117 |
3 |
| 118 |
3 |
| 119 |
3 |
| 120 |
include/check_slave_is_running.inc |
| 121 |
|
| 122 |
*** Test the deadlock warning to be escalated into the error *** |
| 123 |
delete from t1; |
| 124 |
delete from t2; |
| 125 |
delete from t3; |
| 126 |
delete from t1; |
| 127 |
delete from t2; |
| 128 |
delete from t3; |
| 129 |
set @save.replica_transaction_retries= @@global.replica_transaction_retries; |
| 130 |
set @@global.replica_transaction_retries= 0; |
| 131 |
include/stop_slave.inc |
| 132 |
BEGIN; |
| 133 |
INSERT INTO t1 VALUES (1); |
| 134 |
INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2); |
| 135 |
INSERT INTO t3 VALUES (3); |
| 136 |
COMMIT; |
| 137 |
BEGIN; |
| 138 |
SELECT count(*) as zero FROM t1 FOR UPDATE; |
| 139 |
zero |
| 140 |
0 |
| 141 |
START SLAVE; |
| 142 |
Warnings: |
| 143 |
Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead |
| 144 |
*** Now the slave must be stopped due to timeout *** |
| 145 |
include/wait_for_slave_sql_error.inc [errno=1205] |
| 146 |
rollback; |
| 147 |
set @@global.replica_transaction_retries= @save.replica_transaction_retries; |
| 148 |
include/start_slave.inc |
| 149 |
*** Clean up *** |
| 150 |
DROP TABLE t1,t2,t3; |
| 151 |
SET global max_relay_log_size= @my_max_relay_log_size; |
| 152 |
CALL mtr.add_suppression(".*worker thread retried transaction.*"); |
| 153 |
CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*"); |
| 154 |
End of 5.1 tests |
| 155 |
include/rpl_end.inc |