2

I have a table app_user:

CREATE TABLE app_user (
  id            BINARY(16) NOT NULL,
  email_address VARCHAR(255),
  password      VARCHAR(255),
  username      VARCHAR(255),
  role          VARCHAR(255),
  credits       INTEGER,
  PRIMARY KEY (id)
);

I also have a table played_game_round that references the id of the app_user with a foreign key constraint:

CREATE TABLE played_game_round (
  id                  BINARY(16) NOT NULL,
  game_shots          LONGTEXT,
  game_picture_set_id BINARY(16),
  user_id             BINARY(255),
  PRIMARY KEY (id)
);

If I do an INSERT into the played_game_round table, I get a foreign key violation:

Cannot add or update a child row: a foreign key constraint fails (`myappdb`.`played_game_round`, CONSTRAINT `FK_67s32eu4d5d1m18ub5brp5fk2` 
FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`))

But i am sure the id is correct.

This is what show engine innodb status shows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-02-04 15:05:52 0x700000d51000 Transaction:
TRANSACTION 10052, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 123145316274176, query id 1698 localhost 127.0.0.1 root
INSERT INTO myappdb.played_game_round (game_picture_set_id, game_shots, user_id, id) VALUES (uuid_to_bin("f63b99f0-9f33-46dc-8a30-e716394f44e7"), "bla", (SELECT id from app_user where id = uuid_to_bin("9d025d10-4fe1-4af1-a361-e91852f00733")), uuid_to_bin("37f3ec14-c65c-4603-b1d2-04bb801b24f1"))
Foreign key constraint fails for table `myappdb`.`played_game_round`:
,
  CONSTRAINT `FK_67s32eu4d5d1m18ub5brp5fk2` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`)
Trying to add in child table, in index FK_67s32eu4d5d1m18ub5brp5fk2 tuple:
DATA TUPLE: 2 fields;
 0: len 255; hex 9d025d104fe14af1a361e91852f007330000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000; asc   ] O J  a  R  3                                                                                                                                                                                                                                               ;;
 1: len 16; hex 37f3ec14c65c4603b1d204bb801b24f1; asc 7    \F       $ ;;

But in parent table `myappdb`.`app_user`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 16; hex 9d025d104fe14af1a361e91852f00733; asc   ] O J  a  R  3;;
 1: len 6; hex 000000002722; asc     '";;
 2: len 7; hex ba0000012e0110; asc     .  ;;
 3: len 16; hex 706c6179657240676d61696c2e636f6d; asc [email protected];;
 4: len 6; hex 706c61796572; asc player;;
 5: len 6; hex 706c61796572; asc player;;
 6: len 6; hex 504c41594552; asc PLAYER;;
 7: len 4; hex 80000032; asc    2;;

I have tested from my Java code and I also reproduced it in the MySQL console (Using the uuid_to_bin() function from https://gist.github.com/damienb/159151).

What could be the problem?

1 Answer 1

3

Found myself 4 minutes after posting it after having searched for hours:

The user_id column was defined as BINARY(255), but should be BINARY(16) like the id column of the app_user table.

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

1 Comment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.