Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
2.2.15
-
None
-
RHEL 7
-
MXS-SPRINT-71
Description
If you execute function/procedure on slave server and it only contains CREATE/DROP TEMPORARY TABLE statement then DROP TEMPORARY TABLE will execute on slave server and it will create an errant transaction and will break replication.
It's happening probably from maxscale as there is no way to prevent execution of procedure on slave side as maxscale is handling failover and can't drop procedure from any of the server in replication because of failover/switchover requirement.
Let me show you quick test from my side on MariaDB Server version 10.2.14
1- I created below user with same set of privileges as you on MASTER and used your procedure.
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'ipe'@'%' IDENTIFIED BY 'ipe';
|
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `test`.* TO 'ipe'@'%' IDENTIFIED BY 'ipe';
|
GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'ipe'@'%' IDENTIFIED BY 'ipe';
|
|
|
DELIMITER //
|
CREATE PROCEDURE testproc()
|
BEGIN
|
DROP TEMPORARY TABLE IF EXISTS `t1_tmp`;
|
DROP TEMPORARY TABLE IF EXISTS `t2_tmp`;
|
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t1_tmp` (
|
`rxn1` varchar(400) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t2_tmp` (
|
`rxn2` varchar(16) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
END //
|
DELIMITER ;
|
2- As soon as I call that testproc() on MASTER no issues on replication side, but as soon as I execute that proc on SLAVE it creates errant transactions and SLAVE breaks as soon as you execute this proc or any write statement on MASTER and as we aware DROP TEMPORARY TABLE is allowed despite of read_only is enabled.
master [localhost] {ipe} (test) > call testproc();
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
slave1 [localhost] {msandbox} ((none)) > show slave status\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 127.0.0.1
|
Master_User: rsandbox
|
Master_Port: 24044
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.000003
|
Read_Master_Log_Pos: 3544
|
Relay_Log_File: mysql-relay.000002
|
Relay_Log_Pos: 3843
|
Relay_Master_Log_File: mysql-bin.000003
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
.
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 3544
|
Relay_Log_Space: 4148
|
.
|
Master_Server_Id: 1
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Current_Pos
|
Gtid_IO_Pos: 0-1-28
|
.
|
slave1 [localhost] {ipe} (test) > call testproc();
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
master [localhost] {ipe} (test) > call testproc();
|
Query OK, 0 rows affected (0.00 sec)
|
{code:SQL}
|
|
|
Now, check slave status output:
|
|
|
{code:SQL}
|
slave1 [localhost] {msandbox} ((none)) > show slave status\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 127.0.0.1
|
Master_User: rsandbox
|
Master_Port: 24044
|
Connect_Retry: 60
|
Master_Log_File: mysql-bin.000003
|
Read_Master_Log_Pos: 4636
|
Relay_Log_File: mysql-relay.000002
|
Relay_Log_Pos: 3843
|
Relay_Master_Log_File: mysql-bin.000003
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: No
|
.
|
Last_Errno: 1950
|
Last_Error: An attempt was made to binlog GTID 0-1-29 which would create an out-of-order sequence number with existing GTID 0-2-33, and gtid strict mode is enabled
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 3544
|
Relay_Log_Space: 5240
|
.
|
Binlog Entries on slave:
SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
DROP TEMPORARY TABLE IF EXISTS `test`.`t2_tmp`,`test`.`t1_tmp` /* generated by server */
|
/*!*/;
|
# at 3556
|
#181121 17:33:18 server id 2 end_log_pos 3598 CRC32 0x128a9d80 GTID 0-2-29 ddl
|
/*!100001 SET @@session.server_id=2*//*!*/;
|
/*!100001 SET @@session.gtid_seq_no=29*//*!*/;
|
# at 3598
|
#181121 17:33:18 server id 2 end_log_pos 3755 CRC32 0x48a6028b Query thread_id=27 exec_time=0 error_code=0
|
SET TIMESTAMP=1542839598/*!*/;
|
SET @@session.pseudo_thread_id=27/*!*/;
|
/*!\C latin1 *//*!*/;
|
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */
|
/*!*/;
|
# at 3755
|
#181121 17:33:18 server id 2 end_log_pos 3797 CRC32 0xe62ab3e9 GTID 0-2-30 ddl
|
/*!100001 SET @@session.gtid_seq_no=30*//*!*/;
|
# at 3797
|
#181121 17:33:18 server id 2 end_log_pos 3949 CRC32 0xce694ce2 Query thread_id=27 exec_time=0 error_code=0
|
SET TIMESTAMP=1542839598/*!*/;
|
DROP TEMPORARY TABLE IF EXISTS `test`.`t2_tmp` /* generated by server */
|
/*!*/;
|
# at 3949
|
#181121 17:33:18 server id 2 end_log_pos 3991 CRC32 0x9ba6eb32 GTID 0-2-31 ddl
|
/*!100001 SET @@session.gtid_seq_no=31*//*!*/;
|
# at 3991
|
#181121 17:33:18 server id 2 end_log_pos 4205 CRC32 0x40a3afd6 Query thread_id=27 exec_time=0 error_code=0
|
use `test`/*!*/;
|
SET TIMESTAMP=1542839598/*!*/;
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t1_tmp` (
|
`DISCARDED_RSN` varchar(400) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
/*!*/;
|
# at 4205
|
#181121 17:33:18 server id 2 end_log_pos 4247 CRC32 0x71814416 GTID 0-2-32 ddl
|
/*!100001 SET @@session.gtid_seq_no=32*//*!*/;
|
# at 4247
|
#181121 17:33:18 server id 2 end_log_pos 4448 CRC32 0x78662cbd Query thread_id=27 exec_time=0 error_code=0
|
SET TIMESTAMP=1542839598/*!*/;
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t2_tmp` (
|
`RXNTRANSREF` varchar(16) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
/*!*/;
|
# at 4448
|
#181121 17:33:34 server id 2 end_log_pos 4490 CRC32 0xb8de3c28 GTID 0-2-33 ddl
|
/*!100001 SET @@session.gtid_seq_no=33*//*!*/;
|
# at 4490
|
#181121 17:33:34 server id 2 end_log_pos 4648 CRC32 0x4830df28 Query thread_id=27 exec_time=0 error_code=0
|
SET TIMESTAMP=1542839614/*!*/;
|
/*!\C utf8 *//*!*/;
|
SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t2_tmp`,`t1_tmp`
|
/*!*/;
|
To summarise, as soon as procedure executes on slave server directly it creates errant transaction. I would suggest to set sql_log_bin=0 injected by maxscale before such CREATE/DROP TEMPORARY TABLE.