Bug #29813 replication errors on a unstable network
Submitted: 16 Jul 2007 1:48 Modified: 30 Jan 2008 11:40
Reporter: li pickup (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.24 OS:Linux (Linux 2.6.16-xen)
Assigned to: CPU Architecture:Any
Tags: mysqlbinlog, network, replication

[16 Jul 2007 1:48] li pickup
Description:
There are some errors in MySQL replication on an unstable network. It will log uncorrected sql statement in binlog. I used mysqlbinlog to get the information at master as follows:

[ master ]

# at 13650
#070711 11:02:24 server id 2  end_log_pos 352   Query   thread_id=137801        exec_time=0     error_code=0
SET TIMESTAMP=1184122944;
INSERT INTO meetingrawlog(timestamp, confid, eventtype, refnum1, refnum2, refnum3, refnum4, refnum5, refnum6, refstr1, refstr2, refstr3) VALUES ('2007-07-11 11:02:24', '100010000001970', 'SessJoin', 1, 256, 318869504, 318869506, 0, 0, '', '', '');
# at 13974
#070711 11:02:24 server id 2  end_log_pos 601969        Xid = 33933858
COMMIT;
# at 14001
#070711 11:02:28 server id 2  end_log_pos 28    Intvar
SET INSERT_ID=2473392;
# at 14029
#070711 11:02:28 server id 2  end_log_pos 350   Query   thread_id=137801        exec_time=0     error_code=0
SET TIMESTAMP=1184122948;
INSERT,^@^@^A^@^@^@^@^@^D^A^@^@^@+^@^@^@^@^@^@^@^@^@?ù^H^@^@^@^@^@mysql-bin.000075_^@^@^B^@^Q?<93>F^O^A^@^@^@^^@^@^@^@^@^@^@^@^@^D^5.0.24a-standard-log^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^S8^M^@^H^@^R^@^D^D^D^D^R^@^@K^@^D^ZA^@^@^C^@<95>E<94>F^B^B^@^@^@@^@^@^@?ù^H^@^@^@<8e>^Z^B^@^@^@^@^@^@^@^@^Z^@^@^@@^@^@^A^@^@^@^@^@^@^@^@^C
^@^B^@^D!^@!^@!^@^@BEGIN^]^@^@^D^@hE<94>F^E^B^@^@^@^\^@^@^@^\^@^@^@;
# at 14351
#070713 17:32:50 server id 3  end_log_pos 14404         Rotate to localhost-relay-bin.000003  pos: 4
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

At slave, ‘show slave status\G’ shows that:

[ slave ]

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.0.0.34
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000075
        Read_Master_Log_Pos: 602319
             Relay_Log_File: localhost-relay-bin.000002
              Relay_Log_Pos: 14001
      Relay_Master_Log_File: mysql-bin.000075
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: mysql
         Replicate_Do_Table: 
     Replicate_Ignore_Table: lms_its.its_site_product_version_parameter_value
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1064
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'lms_conference'. Query: 'INSERT,'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 601969
            Relay_Log_Space: 14351
            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: NULL
1 row in set (0.00 sec)

How to repeat:
n/a

Suggested fix:
n/a
[16 Jul 2007 2:09] li pickup
I am sorry I have a mistake in the description.
the binlog information is get from slave relay binlog.
[18 Jul 2007 11:26] Sveta Smirnova
Thank you for the report.

But version 5.0.24 is quite old. Please try with current version 5.0.45 and if problem is still exists describe your unstable network. I mean we need to recreate such unstable network which can broke data sent via protocol TCP.
[19 Jul 2007 0:48] li pickup
Thanks for your reply.

I will test it and give the result here after we update the MySQL server. Thanks.
[19 Jul 2007 6:38] Sveta Smirnova
Thank you for the update.

We will wait feedback from you.
[14 Aug 2007 3:13] li pickup
Dear Sveta :
    Thanks for you support. The replication is througn VPN on internat, also the outbound is not very stable. This is out production env,so it is not easy to update mysql new version always;aslo we has no lab to test it with new mysql versions.
    This phenomenon apears always this days. I think mysql replication is depend on TCP,sine TCP is  reliable connection , it has some policy to verify data's correct. So, in theory,there is  no explanation.
[14 Oct 2007 1:45] James Day
See bug #26489 and bug #25737 for similar cases of trouble with unstable VPN and the proposed checksum solution.
[30 Jan 2008 11:40] Valeriy Kravchuk
I think this can be treated as a duplicate of bug report/feature request pointed out by James.