Announcement

Announcement Module
Collapse
No announcement yet.

Mysql foreign keys error when trying to partition the table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql foreign keys error when trying to partition the table

    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)

    Last edited by niljoshi; 10-22-2013, 11:02 PM.

  • #2
    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.

    Comment

    Working...
    X