MariaDB

unknown error replication run but no data update after secs

Asked by Irwan on 2014-10-30

i use 10.0.14,
this is three day, how to find error in this replication, im using vpn 2 master 1 slave,
i do full backup in three day, clear connection, create new connection, change server id 102 to 202 but same result,

i have to 2 master 1 slave,
master connection:
- MMXATN serverid 101
- MMXGSR serverid 202
slave :
SVR-MMX serverid 100

in slave i check MMXATN run normally, data is up to date,
but MMXGSR only run few second after that i see in status not update and without error :
Read_Master_Log_Pos: 9132941
Exec_Master_Log_Pos: 9132903

to check replication thread run in master :
MariaDB [(none)]> show processlist;
+-------+-------------+----------------------+--------+-------------+------+----------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+-------------+----------------------+--------+-------------+------+----------------+------------------+----------+
| 13182 | root | 10.10.1.100:1050 | ssd002 | Sleep | 0 | | NULL | 0.000 |
| 13781 | slaveGSR | 10.10.30.200:64298 | NULL | Binlog Dump | 32 | Writing to net | NULL | 0.000 |
| 13794 | root | 10.10.30.111:61754 | NULL | Query | 0 | init | show processlist | 0.000 |
+-------+-------------+----------------------+--------+-------------+------+----------------+------------------+----------+
3 rows in set (0.65 sec)

so i check error log in slave :
141030 21:35:38 [ERROR] Master 'MMXGSR': Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141030 21:35:38 [Note] Master 'MMXGSR': Slave I/O thread: Failed reading log event, reconnecting to retry, log 'MMXGSR-bin.000001' at position 9132941
141030 21:36:49 [ERROR] Master 'MMXGSR': Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141030 21:36:49 [Note] Master 'MMXGSR': Slave I/O thread: Failed reading log event, reconnecting to retry, log 'MMXGSR-bin.000001' at position 9132941

here result in slave server :

MariaDB [(none)]> show all slaves status\G
*************************** 1. row ***************************
              Connection_name: MMXATN
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.30.201
                  Master_User: slaveATN
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: repl-bin.000014
          Read_Master_Log_Pos: 30459112
               Relay_Log_File: mariadb-relay-bin-MMXATN.000307
                Relay_Log_Pos: 61401
        Relay_Master_Log_File: repl-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: temporary,ssd000
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 30459112
              Relay_Log_Space: 746541
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 101
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 104857600
         Executed_log_entries: 87704
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-100-504572
*************************** 2. row ***************************
              Connection_name: MMXGSR
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.30.202
                  Master_User: slaveGSR
                  Master_Port: 3309
                Connect_Retry: 10
              Master_Log_File: MMXGSR-bin.000001
          Read_Master_Log_Pos: 9132941
               Relay_Log_File: mariadb-relay-bin-MMXGSR.000002
                Relay_Log_Pos: 9133195
        Relay_Master_Log_File: MMXGSR-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: temporary,ssd000
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9132903
              Relay_Log_Space: 9162129
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 202
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 104857600
         Executed_log_entries: 89636
         Executed_log_entries: 89636
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 15.000
               Gtid_Slave_Pos: 0-100-504572
2 rows in set (0.73 sec)

and here result mysqlbinlog position 9132941 with long query replace insert :

# at 9132941
#141030 18:15:31 server id 202 end_log_pos 14768492 Query thread_id=8934 exec_time=2 error_code=0
SET TIMESTAMP=1414664131/*!*/;
REPLACE INTO masteroutlet VALUES

( '0', 'NON MEMBER', 'MMX MART DEFAULT CUSTOMER', 'DONT EDIT DONT DELETE', 'NY', '-', '-', '-', '0', '000', '0', '0', '0', '0', '0', '0', '2013/12/04', '04/12/2013 16:27:00', 0, 1, 0, 0, 1, 0, '0', '0', '0', '0', '0', '0', '0', 0, 0, NULL, '0', 'Ak', '0', '0', '0', '0', '0', '0', '0', 0, 0, 0, '0', 0, '0', '0', '0', '0', '-', '0', '0', '0', '0', '0', 2, 'SISTEM', 0, '-', '2013/08/01', '3', '2090/09/04', NULL, '0', '0', NULL, NULL, NULL, NULL, '2013/12/04 11:34:37') ,

is because query too large ?
but why another connection (MMXATN) normally.

global variable on master MMXGSR :
http://pastebin.com/QHRYUKyP

global variable on slave :
http://pastebin.com/HYZpfymd

i check variable :
both max_allowed_packet = 100M
slave_max_allowed_packet = 1G