title: MySQL foreign key failure when inserting using UUIDs
body: <p>I have a table <code>app_user</code>:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>I also have a table <code>played_game_round</code> that references the <code>id</code> of the <code>app_user</code> with a foreign key constraint:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>If I do an INSERT into the <code>played_game_round</code> table, I get a foreign key violation:</p>
  
  <pre><code>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`))
  </code></pre>
  
  <p>But i am sure the id is correct.</p>
  
  <p>This is what <code>show engine innodb status</code> shows:</p>
  
  <pre><code>------------------------
  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 player@gmail.com;;
   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;;
  </code></pre>
  
  <p>I have tested from my Java code and I also reproduced it in the MySQL console (Using the <code>uuid_to_bin()</code> function from <a href="https://gist.github.com/damienb/159151" rel="nofollow">https://gist.github.com/damienb/159151</a>).</p>
  
  <p>What could be the problem?</p>