MySQL Bugs: #60784: Last_SQL

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

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!

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;

http://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html#replicat...

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

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.

So, we have a reasonable feature request here to use slave_type_conversions=ALL_NON_LOSSY by default.

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.

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)

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).