Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Mysql foreign keys error when trying to partition the table

rreddyrreddy ContributorCurrent User Role Beginner
Hi, I am trying to do few things together in one ALTER statement on a table that is not referenced by any other tables, NO child tables.

1. Drop foreign keys.
2. Drop primary key, add new primary key that also includes the partition key ( id, when_logged)
3. Range partition the table using when_logged partition key.

Here is the problem, I am unable to do all of these in one shot, mysql gives a strange error which is unrelated to the work I am doing as the table that I am modifying is not referenced by any other table, but the alter fails with this statement.

"Cannot delete or update a parent row: a foreign key constraint fails "

ALTER TABLE CSR_AUDIT_LOG_TEST DROP FOREIGN KEY CSR_AUDIT_LOG_TEST_ibfk_1,DROP FOREIGN KEY CSR_AUDIT_LOG_TEST_ibfk_2, DROP FOREIGN KEY CSR_AUDIT_LOG_TEST_ibfk_3, DROP FOREIGN KEY CSR_AUDIT_LOG_TEST_ibfk_4,DROP PRIMARY KEY, ADD PRIMARY KEY(ID, WHEN_LOGGED)
PARTITION BY RANGE (to_days(WHEN_LOGGED))
( PARTITION p200701 VALUES LESS THAN (to_days('2007-02-01')) ENGINE = InnoDB,
PARTITION p200702 VALUES LESS THAN (to_days('2007-03-01'))
.
.
ENGINE = InnoDBPARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

But when I breakdown my ALTER into two statements, it runs fine, so I do all my DROP foreign key and primary key related changes in one go and partitioning in one go , I can make these changes.

Not sure why my initial ALTER statement fails with unrelated error message.
Info

Table: CSR_AUDIT_LOG_TEST
Create Table: CREATE TABLE `CSR_AUDIT_LOG_TEST` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CZEN_USER_ID` int(10) unsigned DEFAULT NULL,
`MEMBER_ID` int(10) unsigned DEFAULT NULL,
`ACTION` varchar(50) NOT NULL,
`WHEN_LOGGED` datetime NOT NULL,
`BUCKET_TYPE` enum('GENERAL_INFO','SERVICE_PROFILE','REFERENCE', 'PHOTO','RECORDED_REFERENCE','REVIEW','VIDEO','JOB ','CARE_ON_CALL_JOB','EXCHANGE_AD','BACKGROUND_CHE CK','BASIC_BACKGROUND_CHECK','EMPLOYER_JOB','RESUM E','CARE_GROUP','LEAD_GEN_PARTNER_LOCATION','LEAD_ GEN_PARTNER_LOCATION_PROFILE','LOCATION_PROFILE_IM AGE','LOCATION_PROFILE_SPECIAL_OFFER','LOCATION_PR OFILE_MULTIPLE_PHOTO','RECRUITMENT_PROFILE','RECRU ITMENT_JOB','WRITTEN_REFERENCE','MEMBER_HIRE') NOT NULL,
`BUCKET_ID` int(10) unsigned DEFAULT NULL COMMENT 'id of the bucket',
`APPROVAL_STATUS` enum('pending','pendAudit','reviewed','approved',' rejected','clearedForSafety') DEFAULT NULL,
`SERVICE_ID` varchar(9) DEFAULT NULL,
`REASON_CODE` varchar(20) DEFAULT NULL,
`BUCKET_CHANGE_STATUS` varchar(20) DEFAULT NULL COMMENT 'the status of the bucket, can be NEW or ACTIVE',
`BUCKET_CHANGE_XML` varchar(10000) DEFAULT NULL,
`MEMBER_CHANGE_STATUS` varchar(20) DEFAULT NULL COMMENT 'the status of the member, can be NEW or ACTIVE',
`BUCKET_REVIEW_TIME` int(10) unsigned DEFAULT NULL COMMENT 'Stores the time taken to review the bucket by CSR in milliseconds',
`CZEN_USER_SHIFT_ID` int(10) unsigned DEFAULT NULL COMMENT 'CSR Shift in which this log got created',
PRIMARY KEY (`ID`),
KEY `CZEN_USER_ID` (`CZEN_USER_ID`),
KEY `MEMBER_ID` (`MEMBER_ID`),
KEY `SERVICE_ID` (`SERVICE_ID`),
KEY `CZEN_USER_SHIFT_ID` (`CZEN_USER_SHIFT_ID`),
KEY `IX_WHEN_LOGGED` (`WHEN_LOGGED`),
CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_1` FOREIGN KEY (`CZEN_USER_ID`) REFERENCES `CZEN_USER` (`ID`),
CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_2` FOREIGN KEY (`MEMBER_ID`) REFERENCES `MEMBER` (`ID`),
CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_3` FOREIGN KEY (`SERVICE_ID`) REFERENCES `SERVICE` (`ID`),
CONSTRAINT `CSR_AUDIT_LOG_TEST_ibfk_4` FOREIGN KEY (`CZEN_USER_SHIFT_ID`) REFERENCES `CZEN_USER_SHIFT` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=117808378 DEFAULT CHARSET=latin1 COMMENT='Audit log for changes made by a CSR to a member account'
1 row in set (0.00 sec)

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Can you tell me which MySQL/PS version you are using? Can you also provide "SHOW ENGINE INNODB STATUS" while you are getting that error? Because you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS. error.log will be also helpful to troubleshoot the issue.
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.