Column Conversion Error – int to bigint(20) unsigned in MySQL

alter-tableMySQLperconareplication

I have a Percona MySQL cluster, with a master and a few slaves. Each of them is running "Ver 14.14 Distrib 5.5.40-36.1" of MySQL. Replication is row based.

I ran an alter query on one of the tables of the slave only. The plan is to run this query on all slaves and then do a master switch as we cannot afford to lock tables in master.

The query is:

ALTER TABLE order_item_units 
MODIFY parent_id BIGINT(20) unsigned ;

And post this when I checked the slave using show slave status, I see the replication is broken with following error:

Column 3 of table 'database_name.order_item_units' cannot be converted from type 'int' to type 'bigint(20) unsigned'

And when I checked the column, it did convert into BIGINT.

And now I am not able to fix this. I did stop slave and start slave, didn't help. Did stop slave and MySQL restart, didn't help. Did a skip counter, didn't help either.

Either the column should not have gotten converted, then the error would have be just, but then may be there should not have been any error at all.

And if the column did get converted, then why the error?

Any clue what am I missing here?

Best Answer

In your specific case setting 'slave_conversion_type=ALL_NON_LOSSY' would help. Execute on slave:

STOP SLAVE; SET GLOBAL SLAVE_TYPE_CONVERSIONS=ALL_NON_LOSSY; START SLAVE; SHOW SLAVE STATUS\G

For more information read here: https://dev.mysql.com/doc/mysql-replication-excerpt/5.5/en/replication-features-different-data-types.html

Related Question