Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1161

Binlog Server Madness on regular SQL query

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • None
    • 2.0.0
    • binlogrouter
    • None
    • 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 .

      Attachments

        Issue Links

          Activity

            People

              Massimiliano Pinto Massimiliano Pinto (Inactive)
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.