we are facing with strange behavior on one of our slave.
We have to change one of our columns from smallint to int in about 100 tables. Some tables have few rows but the biggest one has more than 400 000 000 rows. Some of the tables are using foreign keys. We created queries which simply modify column for tables without FK. For tables with FK, we prepared queries which remove the FK and modify columns. For each table its single query doing both. Then we prepared the single query for modifying the referenced table. The last prepared queries just add FK back. Because some tables are too big we decided to make changes on each server separately.
On each slave we ran:
STOP SLAVE; SET sql_log_bin = 0; -- LOT OF QUERIES MODIFYING TABLES START SLAVE;
We did this on two servers without problem. On the third server we are facing with error:
Could not execute Update_rows event on table db.Item; Cannot add or update a child row: a foreign key constraint fails (`db`.`Item`, CONSTRAINT `FK_Item_1` FOREIGN KEY (`itemId`) REFERENCES `Item` (`itemId`) ON UPDATE CASCADE), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log server-bin.006694, end_log_pos 193170102
The table structure is same on master and slave:
CREATE TABLE `ItemState` ( `itemStateId` int(10) unsigned NOT NULL AUTO_INCREMENT, `itemId` item(10) unsigned NOT NULL, `pid` int(10) unsigned NOT NULL, `startTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `endTime` datetime DEFAULT NULL, `errno` int(10) unsigned NOT NULL DEFAULT '0', `action` enum('d','p','def') COLLATE utf8_czech_ci NOT NULL DEFAULT 'default', `info` varbinary(30000) DEFAULT NULL COMMENT 'some comment', `mailSent` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'some comment', PRIMARY KEY (`itemStateId`), KEY `itemId` (`itemId`,`action`) USING BTREE, KEY `cinn_errno_casKonec` (`action`,`errno`,`startTime`), KEY `casKonec_cinnost` (`endTime`,`action`), CONSTRAINT `FK_Table_1` FOREIGN KEY (`itemId`) REFERENCES `Item` (`itemId`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=172552322 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci ROW_FORMAT=DYNAMIC COMMENT='some comment'
And the referenced row exists in the referenced table on master and slave. mysqlbinlog utility shows this in the query
@2=-27170 (38366) /* SHORTINT meta=0 nullable=0 is_null=0 */
It looks like that it is trying to insert signed value instead of unsigned. Can someone confirm it? What can cause this issue?
The slave is in version 5.6.36-82.0-log (but at the time of modifying tables was in version 5.6.34-79.1-log) and the master is in version 5.6.32-78.1-log. Version 5.6.34-79.1-log is also on the other two servers where is everything ok. The slave servers should have the same settings.