Server IP : 172.67.216.182 / Your IP : 162.158.88.163 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/rpl/r/ |
Upload File : |
include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); include/sync_slave_sql_with_master.inc include/stop_slave_sql.inc SET @saved_slave_parallel_type = @@GLOBAL.slave_parallel_type; SET @saved_slave_parallel_workers = @@GLOBAL.slave_parallel_workers; SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order; SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; SET @saved_slave_transaction_retries = @@GLOBAL.slave_transaction_retries; SET GLOBAL slave_transaction_retries = 2; SET GLOBAL slave_parallel_type = "LOGICAL_CLOCK"; SET GLOBAL slave_parallel_workers = 3; SET GLOBAL slave_preserve_commit_order = ON; SET GLOBAL innodb_lock_wait_timeout = 1000; # # Case 1: Verify slave can find the deadlock when DELETE is waiting # for its turn to commit # [connection master] INSERT INTO t1 VALUES(10, 10); SET debug = "d,set_commit_parent_100"; INSERT INTO t1 VALUES(11, NULL); DELETE FROM t1 WHERE c2 <= 3; [connection slave] BEGIN; INSERT INTO t1 VALUES(11, 11); [connection slave1] include/start_slave_sql.inc [connection slave] ROLLBACK; include/rpl_diff.inc [connection master] SET debug = ""; TRUNCATE t1; INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); include/sync_slave_sql_with_master.inc include/stop_slave_sql.inc # # Case 2: Verify slave can find the deadlock when it begins to applying # second DELETE statement. # [connection master] INSERT INTO t1 VALUES(20, NULL); SET debug = "d,set_commit_parent_100"; INSERT INTO t1 VALUES(21, NULL); BEGIN; INSERT INTO t1 VALUES(22, 22); DELETE FROM t1 WHERE c2 <= 3; INSERT INTO t1 VALUES(23, 23); INSERT INTO t1 VALUES(24, 24); INSERT INTO t1 VALUES(25, 25); COMMIT; [connection slave] BEGIN; INSERT INTO t1 VALUES(21, 21); [connection server_2_1] BEGIN; INSERT INTO t1 VALUES(23, 23); [connection slave1] include/start_slave_sql.inc [connection slave] ROLLBACK; [connection server_2_1] ROLLBACK; include/rpl_diff.inc [connection master] SET debug = ""; TRUNCATE t1; INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (32, 32); include/sync_slave_sql_with_master.inc include/stop_slave_sql.inc # # Test case 3: Verify the worker can handle it correctly when it is # retrying a transaction. # [connection master] INSERT INTO t1 VALUES(30, NULL); SET debug = "d,set_commit_parent_100"; INSERT INTO t1 VALUES(31, NULL); INSERT INTO t1 VALUES(33, NULL); DELETE FROM t1 WHERE c2 <= 3; [connection slave] BEGIN; INSERT INTO t1 VALUES(31, 31); [connection server_2_1] BEGIN; INSERT INTO t1 VALUES(33, 33); [connection slave1] include/start_slave_sql.inc [connection slave] ROLLBACK; [connection server_2_1] ROLLBACK; include/rpl_diff.inc SET debug = ""; # # Test Case 4: Innodb internal transaction deadlock # [connection master] CREATE TABLE t2 LIKE mysql.innodb_table_stats; include/sync_slave_sql_with_master.inc include/stop_slave_sql.inc CALL mtr.add_suppression(".*InnoDB: Cannot save table statistics for table.*"); LOCK TABLE t1 WRITE; [connection master] TRUNCATE t2; SET debug = "d,set_commit_parent_100"; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK INSERT INTO t2 SELECT * FROM mysql.innodb_table_stats; [connection slave] include/start_slave_sql.inc UNLOCK TABLES; # # Test Case 5: It won't cause transaction rollback if # slave_preserve_commit_order is OFF # [connection master] SET debug = ""; TRUNCATE t1; INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); include/sync_slave_sql_with_master.inc include/stop_slave_sql.inc [connection master] INSERT INTO t1 VALUES(50, 50); SET debug = "d,set_commit_parent_100"; INSERT INTO t1 VALUES(51, NULL); BEGIN; INSERT INTO t1 VALUES(52, 52); DELETE FROM t1 WHERE c2 <= 3; INSERT INTO t1 VALUES(53, 53); INSERT INTO t1 VALUES(54, 54); INSERT INTO t1 VALUES(55, 55); COMMIT; [connection slave] BEGIN; INSERT INTO t1 VALUES(51, 51); [connection server_2_1] BEGIN; INSERT INTO t1 VALUES(53, 53); [connection slave1] SET GLOBAL slave_preserve_commit_order = OFF; SET GLOBAL slave_transaction_retries = 0; include/start_slave_sql.inc [connection slave] ROLLBACK; [connection server_2_1] ROLLBACK; include/rpl_diff.inc # # Deinitialize # [connection master] SET debug = ""; DROP TABLE t1, t2; include/sync_slave_sql_with_master.inc include/stop_slave.inc SET GLOBAL slave_transaction_retries = @saved_slave_transaction_retries; SET GLOBAL slave_parallel_type = @saved_slave_parallel_type; SET GLOBAL slave_parallel_workers = @saved_slave_parallel_workers; SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order; SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; include/rpl_end.inc