Column cannot be converted from type 'int' to type 'bigint(20) unsigned'?

Question

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?

Answer 1

In your specific case setting slave_conversion_type=ALL_NON_LOSSY would help. Execute these commands on the slave:

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

From the documentation:

This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.

Answer 2

I'm not sure why you are getting that error. I have 2 solutions. The solutions will give you an error if your data truly can't be converted to unsigned bigint.

ALTER TABLE `order_item_units` ADD COLUMN `parent_id2` BIGINT(255) UNSIGNED AFTER `parent_id`;
UPDATE `order_item_units` SET `parent_id2` = `parent_id`;
SELECT * FROM `order_item_units` WHERE `parent_id2` != `parent_id` OR `parent_id` IS NULL;

Verify data from the 3rd query. Once you're comfortable that the data is in place, drop the old column. Then rename the new column to the old column name. It is imperative that you verify your data before dropping.

ALTER TABLE `order_item_units` DROP COLUMN `parent_id`;
ALTER TABLE `order_item_units` CHANGE `parent_id2` `parent_id` BIGINT(255) UNSIGNED;

Don't forget to add any constraints to the new column.

You could also do the same process with a temporary table OR creating a new table with the new column definition. Then selecting your old table into it. Verify your data. Rename your original table by appending "_old" to it. Then rename your corrected table to the original table name