MariaDB – DatabaseSolutions

Replication Broken on slave having different database version and character set with master. Below are table format on master and slave.

Master Details

Database version: mysql 5.5.20
Table character Set : latin1
Table Structure :
- CREATE TABLE `abc` (
  `a` varchar(100) DEFAULT NULL,
  `b` varchar(100) DEFAULT NULL,
  `c` varchar(100) DEFAULT NULL,
  `d` varchar(100) DEFAULT NULL,
  `e` varchar(100) DEFAULT NULL,
  `f` varchar(100) DEFAULT NULL,
  `g` varchar(100) DEFAULT NULL,
  `h` varchar(100) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Slave Details

Database version: MariaDB 10.2.14
Table character Set : utf8mb4
- CREATE TABLE `abc ` (
  `a` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `b` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `c` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `d` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `e` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `f` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `g` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `h` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Replication on slave broken with below error :

MariaDB [xxx]> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XYZ
Master_User: XYZ

Last_Errno: 1677
Last_Error: Column 0 of table ‘xxx.xxx’ cannot be converted from type ‘varchar(100)’ to type ‘varchar(100)’

SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:

To resolve the same, changed variable “slave_type_conversions” to “ALL_NON_LOSSY” and restarted replication.

MariaDB [xxx]> stop slave; start slave;

MariaDB [xxx]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XYZ
Master_User: XYZ
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000163
Read_Master_Log_Pos: 451597492
Relay_Log_File: relay-log.000050
Relay_Log_Pos: 402
Relay_Master_Log_File: mysql-bin.000163
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 451597492
Relay_Log_Space: 2411116
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 34103
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

Note : To know more about variable refer below links.

Issue related to variable :

https://mariadb.com/kb/en/library/setting-up-replication/#comment_3650

Category: MariaDB

MariaDB Replication Errno: 1677 Last_Error: Column 0 of table ‘xxx.xxx’ cannot be converted from type