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 !!!
Best Answer
I've just upgraded my MySQL server version and this problem is fixed.
It must have been an issue related to using MySQL Workbench with an older version of MySQL server.