MySql - Unable to add foreign key constraint

Question

I have two tables: user and user_session_data

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  [ ... more columns ... ]
   PRIMARY KEY (`id`),
  [ ... some unique keys ... ]
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

and

CREATE TABLE `user_session_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  [ ... more columns ... ]
  PRIMARY KEY (`id`),
  [ ... some unique keys ... ]
  KEY `IDX_abcdefg` (`user_id`),
  [ ... more indexes ... ]
  CONSTRAINT `FK_abcdefg` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE,
  [ ... more similar foreign key constraints ... ]
  ) ENGINE=InnoDB AUTO_INCREMENT=11334248 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

At some point, someone created a copy of user_session_data, let's say user_session_data_COPY, fixed some incorrect data on it, then renamed the tables:

user_session_data > user_session_data_ORIGINAL

user_session_data_COPY > user_session_data

so all the constraints remained linked with the ORIGINAL table.

Now, I have to fix this, so I dropped the constraints on the ORIGINAL table ant tried to add them on the new table. All of the constraints were created successfully (to some other tables), except the one highlighted in the table structure above. It just won't.

The tables have many records (millions) so the query runs about 10 minutes. I only have access to a phpMyAdmin interface, that after running the query for 5 minutes tells me #2006 - MySQL server has gone away. However, this also happened when recreating the other constraints and they worked just fine. Running SHOW FULL PROCESSLIST; shows that the query is running in background.

Anyway, when it finishes (the query disappears from the process list), the constraint is not there. The MySql general logging is disabled and I have no rights to enable it to see if I got some error at some point.

I've tried to add the constraint on the ORIGINAL table (which has fewer records) and it works just fine.

I've also tried to rebuild the indexes (recreating the tables) by running some null sqls, just as the MySql documentation suggests:

ALTER TABLE user_session_data ENGINE = InnoDB;
ALTER TABLE user ENGINE = InnoDB;

But still no success.

Are there anymore alternatives I should try?

Thank you!

Did you check if the data is consistent? Maybe you have some user_id which are not in user anymore? Like: `Sle — djnose
– djnose, Commented Jun 20, 2018 at 8:09
Sorry, to stupid to edit my comment on the phone :-) Select count(*) from user_session_data where user_id not in (select ID from user_data) — djnose
– djnose, Commented Jun 20, 2018 at 8:11
Yeap! I guess this is it. :) I'll clean those id's and let you know if it worked this time so you post an answer. Thanks! — VMC
– VMC, Commented Jun 20, 2018 at 8:15
All good! Post the answer, please, so I can accept it. Thanks a bunch! — VMC
– VMC, Commented Jun 20, 2018 at 8:32

djnose · Accepted Answer · 2018-06-20 08:39:59Z

1

Please check if your data is consistent.

Select count (*) from user_session_data     where user_id not in (select ID from user_data)

If that gives you any result, delete the wrong data from the user_session_data and try again.

answered Jun 20, 2018 at 8:39

djnose

9458 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql - Unable to add foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related