Bug #60784 Last_SQL_Errno: 1677
Submitted: 6 Apr 2011 22:13 Modified: 8 Apr 2011 3:26
Reporter: steven tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.5.8-log MySQL Communit OS:Linux
Assigned to: CPU Architecture:Any

[6 Apr 2011 22:13] steven tang
Description:
It has been a standard practice that when upgrade MySQL database, we upgrade the schema of slave server first to avoid long downtime. 

In MySQL 5.1.41, we can change a slave table test:

mysql> desc test;  ==> both Master and slave
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| val   | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

to

mysql> desc test; ==> only slave changed.
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| val   | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

and keep the master untouched and the replication would be just fine. 

However we noticed that in Server version: 5.5.8-log MySQL Community Server (GPL), the replication failed and we got following error:

       Last_SQL_Error: Column 1 of table 'quadrant.test' cannot be converted from type 'varchar(30)' to type 'varchar(64)'

This new bug inside 5.5.8 has caused big problem in our production database schema upgrade process.

How to repeat:
Simply do above and you will get the error and the replication will be broken!
[7 Apr 2011 2:09] MySQL Verification Team
Hi Steven,

You'll see I had similar thoughts in bug #59424 :)
Can you please try this option in my.cnf ?

slave_type_conversions=ALL_NON_LOSSY;
[7 Apr 2011 2:10] MySQL Verification Team
http://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html#replicat...
[7 Apr 2011 15:03] steven tang
Thanks Shane.

It works. However, since my Slave has a wider length than Master, which is not exactly the same as the setting description. But once again it did get rid of the silly error which I could not believe to occur.

Steven
[7 Apr 2011 15:17] steven tang
I would like MySQL to be consistant in behaviour between versions. In this case, the default value for this new variable introduced in MySQL 5.5 should be set to: slave_type_conversions=ALL_NON_LOSSY.
[8 Apr 2011 3:26] Valeriy Kravchuk
So, we have a reasonable feature request here to use slave_type_conversions=ALL_NON_LOSSY by default.
[25 Sep 2013 6:18] Simon Mudd
May not be completely related (still investigating) but seeing something similar on 5.6.13:

Slave SQL: Column 31 of table 'mydb.mytable' cannot be converted from type 'timestamp' to type 'timestamp', Error_code: 1677

This is during a migration process. With a 5.5.23 master talking to a 5.6.13 (intermediate master) and under this more 5.6.13 slaves. Some, but not all of the downstream slaves are affected. Trying to figure out why.
[1 Jul 2014 19:13] Wagner Bianchi
Running the version 5.5.37 recently I hit the same "bug" after removing some indexes from a table on one of my slave servers. After that, I restarted the replication and it tried to convert data type. 

mysql> set global slave_type_conversions="ALL_NON_LOSSY,ALL_LOSSY";
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.29
                  Master_User: heman
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: heman-bin.000218
          Read_Master_Log_Pos: 10708393
               Relay_Log_File: relay.000430
                Relay_Log_Pos: 243359929
        Relay_Master_Log_File: heman-bin.000215
             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: 1678
                   Last_Error: Can't create conversion table for table 'mydb.heman_table'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 243359784
              Relay_Log_Space: 816031017
              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: 1678
               Last_SQL_Error: Can't create conversion table for table 'mydb.heman_table'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
[25 Sep 2014 19:58] dom flannery
On our master (5.5.28), we have the field set to mediumint and want to increase the value to INT on our replica. When setting this to ALL_NON_LOSSY, I'm seeing all INT values being converted to 0 (zero).