| Bug #29813 | replication errors on a unstable network | ||
|---|---|---|---|
| Submitted: | 16 Jul 2007 3:48 | Modified: | 30 Jan 2008 12:40 |
| Reporter: | li pickup | ||
| Status: | Duplicate | ||
| Category: | Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.24 | OS: | Linux (Linux 2.6.16-xen) |
| Assigned to: | Target Version: | ||
| Tags: | replication, network, mysqlbinlog | ||
[16 Jul 2007 4: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 13: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 2: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 8:38]
Sveta Smirnova
Thank you for the update. We will wait feedback from you.
[14 Aug 2007 5: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 3: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 12:40]
Valeriy Kravchuk
I think this can be treated as a duplicate of bug report/feature request pointed out by James.
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