Strange Characters ending up in Slave Relay Log !!!

Hi All,

         We are having a very strange problem with two of our slave servers, here is what is happening

mysql> select version();
 5.0.30-Debian_1~bpo.1-log

1. we backup our slave every night, and when we do that we stop the slave, and start a remote mysqldump from the backup machine..
2. within usually the first 5 minutes we get the one or more of the following errors in the error log

070701 11:19:50 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 070701 11:19:50 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.022362' position 1024154 070701 11:19:50 [Note] Slave: connected to master 'replication@xxxxx.prod.xxxxx.com:3306',replication resumed in log 'mysql-bin.022362' at position 1024154

3) and after the backup is done, when the sql_thread is started back, it results in a "SQL Syntax error", thereby stopping replication...

4) and each time this happens the very last statement in the relay log before mysql rotates the log file after reconnecting to the master (see above error) , usually has garbage characters or some sql statements completely messed up like the one below

      INSERT INTO clientsession_events_data
                (clientsession_events_id,  data)
            VALUES
                (xxxxxxx,                        'array (\n
\'memory_stats\' => \n  array (\n    \'native_thread_count\' => 2    VALUES
            (xxxxxx,                 9,              NOW(),
0,                  '')
     /* /catalog/skudb/test3.php */.@ðF¢lB

        INSERT INTO clientsession_eve;
# at 1024291
#070701 11:19:51 server id 1294 end_log_pos 1024343 Rotate to AF001294-relay-bin.007930 pos: 4

5) I can easily reproduce this error and as I was telling usually during the first few minutes of starting a backup or when the slave IO_Thread is behind and is reading a lot of Bin logs from the master ....

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

I have seen this before in one of the posts, but there didn't seem to be a solution , I guess there was also a big that was filed ...

I ran some ping tests to/from this machine and there was no packet loss, and strange thing is if network was a problem it should be happening all the time....

mysql> show global variables like "%timeout%";
+--------------------------+-------+

| Variable_name | Value |
+--------------------------+-------+

| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 120   |
| net_read_timeout         | 5     |
| net_write_timeout        | 5     |
| slave_net_timeout        | 3600  |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 3     |

+--------------------------+-------+

Anybody has any ideas, or have encountered this before .....

-Kishore Jalleda
http://kjalleda.googlepages.com Received on Wed Jul 4 21:50:39 2007