MySQL: Why am I getting this warning?
That warning is quite real and correct. Why??

The warning is meant to have DBA and Developer pick off anachronisms.

What's an anachronism ? Think of these analogies:


Having a cellphone in the year 1930
Using in a LapTop in 1976
Having a Coin with the Year 5 B.C. (would probably be fake)


In essence, it is having an object that exists before its time.

Look at the query. The first part of the INSERT to be evaluated would be the

(SELECT id FROM Role 
  WHERE `Code` = 'CUSTOMER')


In a high write DB environment, if the id was inserted into the Role table in one DB connection and you executed the INSERT in another DB Connection, you could get a race condition. How?


If the Customer Role was INSERTed into the Role table first, you get a numeric value for the id
If the Customer Role was INSERTed into the Role table last, you get a NULL for the id


This situation, governed by the order the entries were made, would yield inconsistent results should you ever try to recover from the binary logs. This situation is even more evident when doing Master/Slave Replication because the Slave could theoretically be inconsistent for this reason. 

In your particular case, you can breathe a sigh of relief because you, the Developer/DBA, already predefined the role CUSTOMER in the Role table. You need not worry, right ???

Therefore, that warning is just a reminder for everyone to streamline their input of data.

You could ignore that particular warning.

However, for the sake of clarity, or if you want the error log to stop growing due to the warning, you should do this:

STEP 01

Make the Stored Procedure as follows:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  User_Role INT,
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
) 
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  ) 
  VALUES
    (
      email,
      hashedPassword,
      User_Role,
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$


STEP 02

When calling the Stored Procedure, retrieve the role_id and pass it into the Stored Procedure:

SELECT id INTO @GivenRoleID FROM Role  WHERE `Code` = 'CUSTOMER';
User_Signup('lee3@gmail.com', 'password', @GivenRoleID, 'Lee', 'Brooks',
null, null, null, null);


Give it a Try !!!