Description:
Under relatively random conditions, replication will stop with a obscure conditions. 'SHOW SLAVE STATUS \G' on a slave gives this output:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Host: monk
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Monk-bin.001332
Read_Master_Log_Pos: 932318382
Relay_Log_File: powell-relay-bin.000444
Relay_Log_Pos: 594436683
Relay_Master_Log_File: Monk-bin.001332
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: datastore_3_1
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: %.sessions,%.policycache,bugs.%
Last_Errno: 0
Last_Error: Query caused different errors on master and slave. Error on master: 'Duplicate entry '%-.64s' for key %d' (1062), Error on slave: 'no error' (0). Default database: ''. Query: 'SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=8,COLLATION_SERVER=8'
Skip_Counter: 0
Exec_Master_Log_Pos: 503345477
Relay_Log_Space: 2097156274
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: 57983
1 row in set (0.00 sec)
----
The query that it seems to be complaining about is simply setting character set information for the client connection. This also seems to be "unskippable" if one were to set the SQL_SLAVE_SKIP_COUNTER variable to 1, it would skip that query and then reach the next instance of this error. Resynchronizing the databases between master and slave fixes the problem. For your reference, here are some variable values:
on a slave:
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-standard-4.1.7-pc-linux-i686/share/mysql/charsets/ |
+--------------------------+---------------------------------------------------------------------+
7 rows in set (0.02 sec)
on the master:
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-standard-4.1.7-pc-linux-i686/share/mysql/charsets/ |
+--------------------------+---------------------------------------------------------------------+
7 rows in set (0.00 sec)
How to repeat:
I have not been able to repeat this consistently, but it has happened more than once (5-10 times over 6-8 months). The only solution has been to start the synchronization process again.
Description: Under relatively random conditions, replication will stop with a obscure conditions. 'SHOW SLAVE STATUS \G' on a slave gives this output: mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space Master_Host: monk Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Monk-bin.001332 Read_Master_Log_Pos: 932318382 Relay_Log_File: powell-relay-bin.000444 Relay_Log_Pos: 594436683 Relay_Master_Log_File: Monk-bin.001332 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: datastore_3_1 Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: %.sessions,%.policycache,bugs.% Last_Errno: 0 Last_Error: Query caused different errors on master and slave. Error on master: 'Duplicate entry '%-.64s' for key %d' (1062), Error on slave: 'no error' (0). Default database: ''. Query: 'SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=8,COLLATION_SERVER=8' Skip_Counter: 0 Exec_Master_Log_Pos: 503345477 Relay_Log_Space: 2097156274 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: 57983 1 row in set (0.00 sec) ---- The query that it seems to be complaining about is simply setting character set information for the client connection. This also seems to be "unskippable" if one were to set the SQL_SLAVE_SKIP_COUNTER variable to 1, it would skip that query and then reach the next instance of this error. Resynchronizing the databases between master and slave fixes the problem. For your reference, here are some variable values: on a slave: mysql> show variables like '%char%'; +--------------------------+---------------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-standard-4.1.7-pc-linux-i686/share/mysql/charsets/ | +--------------------------+---------------------------------------------------------------------+ 7 rows in set (0.02 sec) on the master: mysql> show variables like '%char%'; +--------------------------+---------------------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-standard-4.1.7-pc-linux-i686/share/mysql/charsets/ | +--------------------------+---------------------------------------------------------------------+ 7 rows in set (0.00 sec) How to repeat: I have not been able to repeat this consistently, but it has happened more than once (5-10 times over 6-8 months). The only solution has been to start the synchronization process again.