| Bug #71111 | strange replication broken on slave | ||
|---|---|---|---|
| Submitted: | 9 Dec 2013 2:45 | Modified: | 11 Dec 2013 5:49 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Dec 2013 2:56]
zhai weixiang
The value of character_set_database is different between master and slave. But even so , the slave should create the table as the same as it was defined on master.
[9 Dec 2013 18:26]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please send us configuration files of both master and slave.
[10 Dec 2013 2:11]
zhai weixiang
same configure file on master and slave. [client] socket = /u01/m56/run/mysql.sock [mysql] auto-rehash [mysqld] core-file #### Baes dir #### basedir = /u01/my56 datadir = /u01/m56/data tmpdir = /u01/m56/tmp socket = /u01/m56/run/mysql.sock #### Base configure info #### port = 12316 skip-name-resolve old_passwords = 0 lower_case_table_names = 1 open_files_limit = 65535 max_allowed_packet = 160M max_connect_errors = 50000 max_connections = 12100 max_user_connections = 12000 thread_cache_size=256 table_open_cache_instances=5 metadata_locks_hash_instances = 64 metadata_locks_cache_size=10240 #innodb_use_native_aio = 1 innodb_sync_array_size=256 table_open_cache_instances=20 table_open_cache=20000 innodb_data_file_path=ibdata1:5G;ibdata2:5G:autoextend innodb_log_buffer_size=200M innodb_log_file_size=1000M innodb_log_files_in_group=4 #innodb_stats_auto_recalc = 0 #innodb_adaptive_hash_index_partitions = 8 innodb_file_format=Barracuda bind-address = 0.0.0.0 innodb_buffer_pool_dump_at_shutdown=1 innodb_buffer_pool_load_at_startup = 1 innodb_print_all_deadlocks = 1 innodb_buffer_pool_instances=8 innodb_online_alter_log_max_size=2G innodb_lru_scan_depth=2048 table_definition_cache = 10240 innodb_file_io_threads=4 innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_sync_spin_loops=30 innodb_spin_wait_delay=6 transaction_isolation=READ-COMMITTED sql_mode=STRICT_ALL_TABLES,no_zero_date,no_zero_in_date binlog_format=ROW #### Log info #### log-error = /u01/m56/log/alert.log log-queries-not-using-indexes long_query_time = 1 #### Binary log && Relay log #### log-bin = /u01/m56/log/mysql-bin log-slave-updates = 1 relay-log = /u01/m56/log/mysqld-relay-bin relay-log-index = /u01/m56/log/mysqld-relay-bin.index master-info-file = /u01/m56/log/master.info relay-log-info-file = /u01/m56/log/relay-log.info #expire_logs_days=1 max_binlog_size = 20M max_binlog_cache_size = 2G sync_binlog = 1 ### Server id #### server-id= 4136783306 binlog_rows_query_log_events = 1 #### query cache #### query_cache_size = 0 query_cache_limit = 2048 query_cache_min_res_unit = 1K query_cache_type=0 #rpl_semi_sync_master_enabled= 1 #rpl_semi_sync_master_timeout = 1000000 #rpl_semi_sync_master_wait_point = AFTER_COMMIT #myisam concurrent_insert=2 sort_buffer_size=400k join_buffer_size=100K read_buffer_size=1M read_rnd_buffer_size=5M myisam_sort_buffer_size=100M #innodb default-storage-engine = INNODB innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 innodb_buffer_pool_size = 16G innodb_max_dirty_pages_pct = 60 innodb_thread_concurrency=64 innodb_purge_threads = 4 #innodb_io_capacity=1000 #innodb_flush_method = O_DIRECT_NO_FSYNC innodb_flush_method = O_DIRECT innodb_flush_neighbors=0 innodb_read_ahead_threshold=0 innodb_sort_buffer_size=2M #innodb_read_only = 1 performance_schema_instrument = '%=on' performance_schema=ON #innodb_page_hash_locks=256 innodb_io_capacity_max = 6000 innodb_io_capacity = 3000 #innodb_doublewrite_batch_delay=100 #relay_log_info_repository='TABLE' #master_info_repository='TABLE' enforce_gtid_consistency = ON gtid_mode = ON explicit_defaults_for_timestamp=ON #memcache #innodb_api_enable_binlog = 1 #daemon_memcached_option="-p11222" #daemon_memcached_r_batch_size=1 #daemon_memcached_w_batch_size=1 thread_stack = 524288 [mysqld_safe] pid-file = /u01/m56/run/mysql.pid
[10 Dec 2013 17:33]
Sveta Smirnova
Thank you for the feedback. I still can not repeat described behavior. Did you create the table in version 5.6.15?
[11 Dec 2013 3:01]
zhai weixiang
Yes, I am using MySQL5.6.15. Please make sure the DEFAULT CHARSET of database is different between master and slave.
[11 Dec 2013 5:49]
MySQL Verification Team
Hello Weixiang, Thank you for the report and feedback. Verified as described. Thanks, Umesh
[11 Dec 2013 5:50]
MySQL Verification Team
How to repeat:
// Setup simple master/slave replication ( with/without gtid)
On Master:
drop database if exists test;
create database test DEFAULT CHARSET=latin1;
On Slave:
alter database test DEFAULT CHARACTER SET=gbk;
On Master:
use test;
CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` char(20) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB;
set @id:=0;
insert into `t1` values (@id:=@id+1,LEFT(UUID(), 20)),(@id:=@id+1,LEFT(UUID(), 20)),(@id:=@id+1,LEFT(UUID(), 20)),(@id:=@id+1,LEFT(UUID(), 20));
insert into `t1`(`a`,`b`) select @id:=@id+1,LEFT(UUID(), 20) from `t1` k1, `t1` k2, `t1` k3, `t1` k4,`t1` k5,`t1` k6, `t1` k7, `t1` k8, `t1` k9,`t1` k0,`t1` ka, `t1` kb, `t1` kc, `t1` kd limit 1000000;
On Slave: Replication breaks with Error 1677
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.000003
Read_Master_Log_Pos: 233017750
Relay_Log_File: cluster-repo-relay-bin.000002
Relay_Log_Pos: 233016907
Relay_Master_Log_File: master.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 1 of table 'test.t1' cannot be converted from type 'char(10)' to type 'char(20)'
Skip_Counter: 0
Exec_Master_Log_Pos: 233017460
Relay_Log_Space: 233017377
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 1 of table 'test.t1' cannot be converted from type 'char(10)' to type 'char(20)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: e11ddfec-623d-11e3-837a-00163e03b799
Master_Info_File: /tmp/slave/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 131212 09:17:39
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
[25 Mar 2014 6:54]
Paul Namuag
For such case, statement-based replication is not affected on this. As a workaround, you can do, SET SESSION binlog_format='STATEMENT'; then execute you query after the statement. Other than this, but may out of the subject, just of course change your character set names only at master, if planned charset is from the slave but be sure to turn off binary log as such, SET sql_log_bin = 0; YOUR STATEMENT HERE SET sql_log_bin = 1;
Description: The replication was broken and error message (from SHOW SLAVE STATUS) Last_SQL_Errno: 1677 Last_SQL_Error: Column 1 of table 'test.t1' cannot be converted from type 'char(10)' to type 'char(20)' on slave; root@test 10:31:01>show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` char(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.00 sec) on master; mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` char(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) DEFAULT CHARSET between master and slave is different. And t1 on slave is created by normal replication. and quoted output from mysqlbinlog: # at 833 #131208 18:36:40 server id 4134341436 end_log_pos 954 CRC32 0x92b71690 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1386556600/*!*/; create table t1 (a int primary key, b char(20)) /*!*/; # at 954 #131208 18:37:24 server id 4134341436 end_log_pos 1002 CRC32 0xe396a1b3 GTID [commit=yes] SET @@SESSION.GTID_NEXT= '2e5e3375-6077-11e3-b7e4-90b11c540089:5'/*!*/; # at 1002 #131208 18:37:24 server id 4134341436 end_log_pos 1074 CRC32 0x8fc76618 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1386556644/*!*/; BEGIN /*!*/; # at 1074 #131208 18:37:24 server id 4134341436 end_log_pos 1132 CRC32 0x055e2b2b Rows_query # insert into t1 values (1, 'hello') # at 1132 #131208 18:37:24 server id 4134341436 end_log_pos 1180 CRC32 0x596c887c Table_map: `test`.`t1` mapped to number 73 # at 1180 #131208 18:37:24 server id 4134341436 end_log_pos 1226 CRC32 0x41a8c922 Write_rows: table id 73 flags: STMT_END_F BINLOG ' 5CylUh08C232OgAAAGwEAACAACJpbnNlcnQgaW50byB0MSB2YWx1ZXMgKDEsICdoZWxsbycpKyte BQ== 5CylUhM8C232MAAAAJwEAAAAAEkAAAAAAAEABHRlc3QAAnQxAAID/gL+FAJ8iGxZ 5CylUh48C232LgAAAMoEAAAAAEkAAAAAAAEAAgAC//wBAAAABWhlbGxvIsmoQQ== '/*!*/; ### INSERT INTO `test`.`t1` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='hello' /* STRING(20) meta=65044 nullable=1 is_null=0 */ # at 1226 #131208 18:37:24 server id 4134341436 end_log_pos 1257 CRC32 0xc26d7c08 Xid = 93 COMMIT/*!*/; How to repeat: always repeat on my environment ( using MySQL5.6.15 ) Suggested fix: N/A