1

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe?

1 queries executed, 1 success, 0 errors, 1 warnings

Query: call User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', null, null, null, null)

1 row(s) affected, 1 warning(s)

Execution Time : 0.042 sec
Transfer Time  : 0.093 sec
Total Time     : 0.136 sec

Note Code : 1592
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

Here is the call:

CALL User_Signup('lee3@gmail.com', 'password', 'Lee', 'Brooks', NULL, NULL, NULL, NULL);

Here is the stored procedure:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  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,
      (SELECT 
        id 
      FROM
        Role 
      WHERE `Code` = 'CUSTOMER'),
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$

DELIMITER ;
3

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 !!!

4
  • Wow great answer,in my case as you correctly assumed the table is a lookup so there are not going to be any race conditions. – Lee Jan 16 '14 at 23:22
  • That's what I thought... – RolandoMySQLDBA Jan 16 '14 at 23:22
  • I shifted the select roleId part to before the insert and that did the trick as you suggested. – Lee Jan 16 '14 at 23:26
  • @RolandoMySQLDBA I disagree with your interpretation. It's about determinism of the individual query, not execution order. The warning is there because when you INSERT...SELECT and multiple rows are involved, the source rows may go in to the target table with different pairing to auto-increment values on master and slave, since unspecified SELECT order is undefined. A scalar subquery can't match more than one row, so the statement is actually deterministic, and this is a false alarm. Better advice is still to move away from STATEMENT based logging and switch to ROW or MIXED. – Michael - sqlbot Jan 17 '14 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.