[MXS-1161] Binlog Server Madness on regular SQL query Created: 2017-03-04  Updated: 2020-04-01  Resolved: 2017-10-10

Status: Closed
Project: MariaDB MaxScale
Component/s: binlogrouter
Affects Version/s: None
Fix Version/s: 2.0.0

Type: New Feature Priority: Major
Reporter: VAROQUI Stephane Assignee: Massimiliano Pinto (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
PartOf
includes MXS-1155 Define a set of features for better i... Closed
Sprint: 2017-43

 Description   

It happen that replication-manager when connecting to the binlog server do send some SQL command , that make the replication become crazy in infinite loop

2017-03-04 09:17:31 notice : Replication: Request binlog records from mariadb-bin.000009 at position 315 from master server 192.168.0.41:5056
2017-03-04 09:17:31 notice : Replication: identity seen by the master: server_id: 999, uuid: d76b55f8-00b2-11e7-893e-eca86bf4865a
2017-03-04 09:17:31 notice : Replication: identity seen by the slaves: server_id: 5056, hostname: MacBook-Pro-de-Apple-2.local, MySQL version: 10.1.21-MariaDB
2017-03-04 09:17:31 error : Replication: Master connection error 2003 '#HY000 Lost connection to backend server.' in state 'Binlog Dump', attempting reconnect to master 192.168.0.41:5056
2017-03-04 09:17:31 notice : Replication: Master 192.168.0.41 disconnected after 0 seconds. 0 events read.
2017-03-04 09:17:31 notice : Replication: attempting to connect to master server 192.168.0.41:5056, binlog mariadb-bin.000009, pos 315
2017-03-04 09:17:31 notice : Replication: Request binlog records from mariadb-bin.000009 at position 315 from master server 192.168.0.41:5056
2017-03-04 09:17:31 notice : Replication: identity seen by the master: server_id: 999, uuid: d76b55f8-00b2-11e7-893e-eca86bf4865a
2017-03-04 09:17:31 notice : Replication: identity seen by the slaves: server_id: 5056, hostname: MacBook-Pro-de-Apple-2.local, MySQL version: 10.1.21-MariaDB
2017-03-04 09:17:31 error : Replication: Master connection error 2003 '#HY000 Lost connection to backend server.' in state 'Binlog Dump', attempting reconnect to master 192.168.0.41:5056
2017-03-04 09:17:31 notice : Replication: Master 192.168.0.41 disconnected after 0 seconds. 0 events read.
2017-03-04 09:17:31 notice : Replication: attempting to connect to master server 192.168.0.41:5056, binlog mariadb-bin.000009, pos 315
2017-03-04 09:17:31 notice : Replication: Request binlog records from mariadb-bin.000009 at position 315 from master server 192.168.0.41:5056
2017-03-04 09:17:31 notice : Replication: identity seen by the master: server_id: 999, uuid: d76b55f8-00b2-11e7-893e-eca86bf4865a
2017-03-04 09:17:31 notice : Replication: identity seen by the slaves: server_id: 5056, hostname: MacBook-Pro-de-Apple-2.local, MySQL version: 10.1.21-MariaDB
2017-03-04 09:17:31 error : Replication: Master connection error 2003 '#HY000 Lost connection to backend server.' in state 'Binlog Dump', attempting reconnect to master 192.168.0.41:5056

After many tcpdump dowgrade to from 10.2 to 10.1 i figure it out and foudn the offending queries

SELECT @@max_allowed_packet
SELECT MAX(Select_priv) as Select_priv, MAX(Process_priv) as Process_priv, MAX(Super_priv) as Super_priv, MAX(Repl_slave_priv) as Repl_slave_priv, MAX(Repl_client_priv) as Repl_client_priv, MAX(Reload_priv) as Reload_priv FROM mysql.user WHERE user = ? AND host IN(?,?,?,?,?,?,?,?,?)
SELECT MAX(Select_priv) as Select_priv, MAX(Process_priv) as Process_priv, MAX(Super_priv) as Super_priv, MAX(Repl_slave_priv) as Repl_slave_priv, MAX(Repl_client_priv) as Repl_client_priv, MAX(Reload_priv) as Reload_priv FROM mysql.user WHERE user = ? AND host IN(?,?,?,?,?,?,?,?,?)
SELECT MAX(Select_priv) as Select_priv, MAX(Process_priv) as Process_priv, MAX(Super_priv) as Super_priv, MAX(Repl_slave_priv) as Repl_slave_priv, MAX(Repl_client_priv) as Repl_client_priv, MAX(Reload_priv) as Reload_priv FROM mysql.user WHERE user = ? AND host IN(?,?,?,?,?,?,?,?,?)
SELECT MAX(Select_priv) as Select_priv, MAX(Process_priv) as Process_priv, MAX(Super_priv) as Super_priv, MAX(Repl_slave_priv) as Repl_slave_priv, MAX(Repl_client_priv) as Repl_client_priv, MAX(Reload_priv) as Reload_priv FROM mysql.user WHERE user = ? AND host IN(?,?,?,?,?,?,?,?,?)

So i change the logic of replication-manager to consider all nodes maxscale to avoid sending SQL statement and the issue is gone .

I would like to keep the auto discovery logic so if maxscale expose a mysql protocol i should be able to send any syntax in it without disturbing the service .



 Comments   
Comment by Massimiliano Pinto (Inactive) [ 2017-10-10 ]

The MRM / BLR integration is now a work in progress task.

Unsupported queries will be fixed by MXS-1466 and other tasks.

Comment by Massimiliano Pinto (Inactive) [ 2017-10-10 ]

Duplicate of MXS-1466

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