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:
- YOVAASTON serverid 101
- YOVAKRJATI serverid 202
slave :
SVR-YOVA serverid 100

in slave i check YOVAASTON run normally, data is up to date,
but YOVAKRJATI 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

i check bin log in master :
MariaDB [(none)]> show master status;
+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| YOVAKRJATI-bin.000001 | 26018774 | ssd002 | |
+-----------------------+----------+--------------+------------------+
1 row in set (0.65 sec)

to check replication thread run in master :
MariaDB [(none)]> show processlist;
+-------+-------------+----------------------+--------+-------------+------+----------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+-------------+----------------------+--------+-------------+------+----------------+------------------+----------+
| 13182 | root | 192.168.1.100:1050 | ssd002 | Sleep | 0 | | NULL | 0.000 |
| 13781 | slavekrjati | 192.168.30.200:64298 | NULL | Binlog Dump | 32 | Writing to net | NULL | 0.000 |
| 13794 | root | 192.168.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 'YOVAKRJATI': Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141030 21:35:38 [Note] Master 'YOVAKRJATI': Slave I/O thread: Failed reading log event, reconnecting to retry, log 'YOVAKRJATI-bin.000001' at position 9132941
141030 21:36:49 [ERROR] Master 'YOVAKRJATI': Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141030 21:36:49 [Note] Master 'YOVAKRJATI': Slave I/O thread: Failed reading log event, reconnecting to retry, log 'YOVAKRJATI-bin.000001' at position 9132941

here result in slave server :

MariaDB [(none)]> show all slaves status\G
*************************** 1. row ***************************
              Connection_name: YOVAASTON
              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: 192.168.30.201
                  Master_User: slaveaston
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: repl-bin.000014
          Read_Master_Log_Pos: 30459112
               Relay_Log_File: mariadb-relay-bin-yovaaston.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: YOVAKRJATI
              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: 192.168.30.202
                  Master_User: slavekrjati
                  Master_Port: 3309
                Connect_Retry: 10
              Master_Log_File: YOVAKRJATI-bin.000001
          Read_Master_Log_Pos: 9132941
               Relay_Log_File: mariadb-relay-bin-yovakrjati.000002
                Relay_Log_Pos: 9133195
        Relay_Master_Log_File: YOVAKRJATI-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)

my sqlbinlog here :
http://irwanto.net/download/YOVAKRJATI-bin.7z

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', 'YOVA MART DEFAULT CUSTOMER', 'DONT EDIT DONT DELETE', 'BALIKPAPAN', '-', '-', '-', '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') ,

( '100', 'IRWANTO', 'JL. KARANG JAWA DALAM', '-', 'BALIKPAPAN', '-', '-', '-', '0', '000', '0', '0', '0', '0', '0', '0', '2014/10/28', '02/09/2013 13:39:23', 0, 1, 0, 0, 1, 0, '0', '0', '0', '0', '0', '0', '0', 0, 0, NULL, 'LL', 'Ak', '0', '0', '0', '0', '0', '0', '0', 0, 0, 0, '0', 0, '0', '', '0', '<email address hidden>', '081253278278', '0', 'irwan_forum', '140', '11', '0', 4, NULL, 216, 'BALIKPAPAN', '1982/02/02', '3', '2015/09/04', '647104.020282.0003', '0', '0', NULL, NULL, NULL, '76123', '2014/10/28 16:41:05') ,

( '10004', 'MICHAEL ANGGA*', 'PONDOK ARUM', '-', 'BALIKPAPAN', '0', '0', '', '0', '000', '0', '0', '0', '0', '0', '0', '2014/10/28', '02/09/2013 13:39:23', 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', '085210558556', '0', '0', '13', '0', '14', 2, NULL, 433, 'BALIKPAPAN', '1990/01/29', '3', '2015/09/04', NULL, '0', '0', NULL, NULL, NULL, NULL, '2014/10/28 21:23:09') ,

( '10005', 'VERONICA TUKAK.S', 'JL. MT HARYONO KM.4', '-', 'BALIKPAPAN', '0', '0', '', '0', '000', '0', '0', '0', '0', '0', '0', '2013/12/04', '02/09/2013 14:01:34', 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', '08134756771', '0', '0', '0', '0', '0', 0, NULL, 62, 'BALIKPAPAN', '1990/01/01', '3', '2015/09/04', NULL, '0', '0', NULL, NULL, NULL, NULL, '2013/12/04 11:34:37') ,

...
...
...

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

global variable on master yovakrjati :
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

Question information

Language:
English Edit question
Status:
Solved
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Solved by:
Irwan
Solved:
2014-11-12
Last query:
2014-11-12
Last reply:
Irwan (irwan-forum) said : #1

solved,,

i set global slave_compressed_protocol=ON;

slave now run ...