MySQL – Understanding Warnings During Replication

MySQLreplicationstored-procedureswarning

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 ;

Best Answer

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

Related Question