MySQL Forums
Forum List  »  Replication

MySQL Slave Error 1677 - cannot be converted from type 'decimal(0,?)' to type 'decimal(12,3)'
Posted by: Wagner Bianchi
Date: August 28, 2014 05:44PM

Hi Folks, I'd like to hear form you if you have any experience fixing this kind of problem related with the replication on slave servers after rotating tables - this is at least curious.

The Context...

Some time ago I helped a customer to migrate his databases from 5.0 MyISAM to 5.5 InnoDB. Everything was going very well until the day we solved to start rotating some giant tables, removing part of the data from the central database. We done the data rotation avoiding DELETE data and then, we agreed with some criteria and went ahead. Considering the table x, new tables x_new and x_old were created, new data is the dataset that must remain in production and old is the history. The final data rotation removed from production data something about 1TB data and finally, we synced some of the slaves with this new small database - 7 slaves remained with the old giant database and 3 other with the new small database. OK!

The problem...

Some days ago, 2AM, technical folks from the customer side paged me about the problem related with the slave servers. After checking what was going on, I identified that the failed slaves were those that are running the newest database and the problem is this below:

mydb1001 mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.20.20.91
Master_User: wbrpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: wb-bin.003384
Read_Master_Log_Pos: 129505417
Relay_Log_File: relay.000921
Relay_Log_Pos: 252
Relay_Master_Log_File: wb-bin.003378
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 32 of table 'db.x_2' cannot be converted from type 'decimal(0,?)' to type 'decimal(12,3)'
Skip_Counter: 0
Exec_Master_Log_Pos: 48326650
Relay_Log_Space: 1692453193
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 32 of table 'db.x_2' cannot be converted from type 'decimal(0,?)' to type 'decimal(12,3)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

So, I've been running into this problem since Monday, 25/08/2014. Researching the internet, I've found that if I configure MySQL Servers with binlog_format=ROW, I'll be able to try slave_type_conversions=ALL_NON_LOSSY|ALL_LOSSY, since at this time my master is generating logs using MIXED as its format. BTW, after research a little bit more, I found that this is a BUG and that it was updated in trunk of 5.7.0 (as the last interaction here: http://bugs.mysql.com/bug.php?id=65551

Bug Reports (1677):

http://bugs.mysql.com/bug.php?id=60784
http://bugs.mysql.com/bug.php?id=70085
http://bugs.mysql.com/bug.php?id=70124
http://bugs.mysql.com/bug.php?id=65551

One last funny thing is that I found on the internet as well that a user reported that mysql was not writing the binary logs in properly way and due to that, it was messing up everything. This caught up my attention and I decided to have a look on my slave relay logs to check what was the last query to be executed, that one that break the replication and then...

### DELETE FROM cellcard.rel_compras_2
### WHERE
### @1=975189639 /* INT meta=0 nullable=0 is_null=0 */
### @2=152750 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
### @3='X' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### @4='X' /* VARSTRING(66) meta=66 nullable=1 is_null=0 */
### @5='X' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### @6='1' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @7='' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @8='X' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### @9='1' /* VARSTRING(8) meta=8 nullable=1 is_null=0 */
### @10=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @11='X' /* VARSTRING(17) meta=17 nullable=1 is_null=0 */
### @12=-1 (244) /* TINYINT meta=0 nullable=1 is_null=0 */
### @13='X' /* VARSTRING(25) meta=25 nullable=1 is_null=0 */
### @14=2014-06-02 06:19:54 /* DATETIME meta=0 nullable=0 is_null=0 */
### @15=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @16='02-06-2014' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @17='06/2014' /* VARSTRING(7) meta=7 nullable=1 is_null=0 */
### @18='X' /* STRING(2) meta=65026 nullable=1 is_null=0 */
### @19='X' /* VARSTRING(26) meta=26 nullable=1 is_null=0 */
### @20=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @21='X' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */
### @22=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @23='X' /* VARSTRING(14) meta=14 nullable=1 is_null=0 */
### @24=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @25='' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### @26=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @27='' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @28='X' /* VARSTRING(15) meta=15 nullable=1 is_null=0 */
### @29=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @30='X' /* VARSTRING(16) meta=16 nullable=1 is_null=0 */
### @31=1 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @32=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @33=!! Don't know how to handle column type=0 meta=0 (0000)hqP+UxkBAAAAugIAABoOJAMAAOgBAAAAAAAAL////////wAAAAAAgIo2IDo4agIZUEFOSUYuT0xJ

To my surprise I found that the column 32 of my table and the 33 column treated on the query above, MySQL "don't know how to handle column". Analyzing the column closer, it is a decimal(12,3) and the query above is simply like this:

DELETE FROM x WHERE dt >= '2014-06-02' and data < '2014-06-03' LIMIT 2000;

Btw, could you guys collaborate with any thoughts?

Cheers, WB

Wagner Bianchi - +55 31 8654-9510
Profile: bit.ly/toG94v
Blog: wagnerbianchi.com/blog
Twitter: @wagnerbianchijr
Skype: wbianchijr

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Slave Error 1677 - cannot be converted from type 'decimal(0,?)' to type 'decimal(12,3)'
18482
August 28, 2014 05:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.