[MXS-2180] errant transaction breaking replication Created: 2018-11-22  Updated: 2020-08-25  Resolved: 2018-11-28

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: 2.2.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Muhammad Irfan Assignee: markus makela
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

RHEL 7

Sprint: 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.

 Comments   
Comment by Dipti Joshi (Inactive) [ 2018-11-22 ]

What version of MaxScale is this on? What OS is it running on? muhammad.irfan

Comment by Muhammad Irfan [ 2018-11-23 ]

dshjoshi maxscale version 2.2.15 on RHEL 7

Comment by markus makela [ 2018-11-28 ]

Not a MaxScale bug.

Generated at Thu Feb 08 04:12:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.