Message Title

Wilson Gee created an issue

Issue Type:	Task
Assignee:	Unassigned
Created:	23/Aug/2022 18:23 PM
Priority:	Minor
Reporter:	Wilson Gee

We upgraded from 5.2.17.Final to 5.4.32.Final and discovered an issue with violation of foreign key constraints in MySQL InnoDB tables running 5.7.mysql_aurora.2.09.2.

We discovered in the 5.4 Migration Guide that we can revert the behavior introduced in 5.3 with the disable_delayed_identity_inserts property and that we should report our use case.

If you find you need to use this configuration setting, be sure to report the mapping to us in a JIRA issue so that we can review it and determine if the mapping corner case should be included in our algorithm since the configuration setting is meant to bridge behavior support for this across a few releases.

An example of our logged error:

                              2022-02-11T21:10:11,699 severity=ERROR class=SqlExceptionHelper Cannot add or update a child row: a foreign key constraint fails (`profiles`.`profile_attributes`, CONSTRAINT `fk_type_namespace_user` FOREIGN KEY (`profile_type`, `namespace_id`, `namespace_user_id`) REFERENCES `profiles` (`profile_type`, `namespace_id`, `n)
                              

The table schemas in question (with extraneous details omitted):

                              CREATE TABLE `profiles` (
  `profile_type` tinyint(4) NOT NULL,
  `namespace_id` int(11) NOT NULL,
  `namespace_user_id` bigint(20) NOT NULL,
  ...
  PRIMARY KEY (`profile_type`,`namespace_id`,`namespace_user_id`),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `profile_attributes` (
  `profile_type` tinyint(4) NOT NULL,
  `namespace_id` int(11) NOT NULL,
  `namespace_user_id` bigint(20) NOT NULL,
  ...
  PRIMARY KEY (`profile_type`,`namespace_id`,`namespace_user_id`),
  CONSTRAINT `fk_type_namespace_user` FOREIGN KEY (`profile_type`, `namespace_id`, `namespace_user_id`) REFERENCES `profiles` (`profile_type`, `namespace_id`, `namespace_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
                              

Add Comment

Get Jira notifications on your phone! Download the Jira Cloud app for Android or iOS

This message was sent by Atlassian Jira