Not the answer you need?
Register and ask your own question!

Lost table with schema upgrade

JamesDeenJamesDeen EntrantCurrent User Role Beginner
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';
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.