4

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?

1

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

  • So you're saying that if the column data type mismatch causes replication failure, dropping the slave column will fix it? – mustaccio Aug 19 '15 at 23:14
  • @mustaccio No, I am simply saying that if the scenario played out as the OP originally posted, this would allow the user to copy the data to the new column. Once the data is copied to the new column (with the new data type), the user can drop the old column, that he couldn't convert. Then rename the new column, to the original column name (I left that step out, I apologize). I didn't address replication, because the error doesn't indicate failure because of replication. Being that replication is row based, this solution should be sufficient. – Nathan Aug 20 '15 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.