Hi,
I used pt-online-schema-change to alter a table in order to convert the primary key from INT to BIGINT. At the end of the process I lost the table and I’d like to understand the reason.
The table is 36M rows big and compressed to 2.6GB.
The command I used :
pt-online-schema-change -uroot -pXxXx --alter="CHANGE id id BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT" D=factors,t=codes --dry-run --alter-foreign-keys=drop_swap
The error I’ve got:
Altered factors.codes but there were errors or warnings.
Error updating foreign key constraints: 2016-10-03T11:51:24 DBD::mysql:db do failed: Error on rename of './factors/_codes_new' to './factors/codes' (errno: 150 "Foreign key constraint is incorrectly formed") [for Statement "RENAME TABLE factors._codes_new TO factors.code"] at /usr/bin/pt-online-schema-change line 10568
As stated in the manual, this method is critical if the rename fails, and the table is lost, though I don’t understand why it has failed.
The foreign key constraint can’t be wrong, it has been working the whole time.
Voilà the only constraint of another table pointing to the codes table :
ADD CONSTRAINT `_historics_ibfk_1` FOREIGN KEY (`code_id`) REFERENCES `codes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
Voilà the only constraint for a column in the table codes itself:
ADD CONSTRAINT `_codes_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
The table schema:
CREATE TABLE IF NOT EXISTS `factors` (
`id` int(11) unsigned NOT NULL,
`serial` varchar(14) NOT NULL,
`code` bigint(20) NOT NULL,
`points` float(10,2) NOT NULL,
`date` timestamp NULL DEFAULT NULL COMMENT 'Creation',
`used_date` timestamp NULL DEFAULT NULL COMMENT 'Used',
`transaction_id` int(11) DEFAULT NULL,
`expiration_date` timestamp NULL DEFAULT NULL,
`weight` float(10,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB AUTO_INCREMENT=134335235 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='Codes';