MySQL Bugs: #58674: Unsafe warnings are still produced after unsafety has been eliminated

Bug #58674	Unsafe warnings are still produced after unsafety has been eliminated
Submitted:	2 Dec 2010 20:42	Modified:	21 Apr 2011 0:37
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.6, 5.5.8	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	regression

Description:
I have two tables with a trigger between them; the trigger writes into a table2 (with an auto-increment) after inserting into a table1.
There is also a procedure which executes insert into the table1.

In SBR mode, such insert (and the procedure call) produces an unsafety warning.

Then I drop the trigger, so there is no unsafe insert into the table with auto-increment anymore.

However, the warning on the procedure call is still produced inside the current connection.

Output for the test from 'How to repeat':

DROP TABLE IF EXISTS t1, t2;
DROP TRIGGER IF EXISTS tr;
DROP PROCEDURE IF EXISTS p;
CREATE TABLE t1 (f INT);
CREATE TABLE t2
(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT);
CREATE PROCEDURE p()
BEGIN
INSERT INTO t1 (f) VALUES (1);
END |
SET binlog_format = STATEMENT;
CALL p();
# No warnings, everything is safe so far:
SHOW WARNINGS;
Level   Code    Message
CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t2 (f) VALUES (new.f);
CALL p();
# The warning appeared after we added the trigger:
SHOW WARNINGS;
Level   Code    Message
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
DROP TRIGGER tr;
CALL p();
# No trigger anymore, but the warning is still there:
SHOW WARNINGS;
Level   Code    Message
Note    1592    Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
CALL p();
# No warnings for the new connection:
SHOW WARNINGS;
Level   Code    Message
DROP TABLE t1, t2;
DROP PROCEDURE p;

How to repeat:
--source include/have_log_bin.inc
--disable_warnings

DROP TABLE IF EXISTS t1, t2;
DROP TRIGGER IF EXISTS tr;
DROP PROCEDURE IF EXISTS p;
CREATE TABLE t1 (f INT);
CREATE TABLE t2 
(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT);

--delimiter |
CREATE PROCEDURE p()
BEGIN
INSERT INTO t1 (f) VALUES (1);
END |
--delimiter ;

SET binlog_format = STATEMENT;

CALL p();
--echo # No warnings, everything is safe so far:
SHOW WARNINGS;

CREATE TRIGGER tr AFTER INSERT ON t1 
FOR EACH ROW INSERT INTO t2 (f) VALUES (new.f);

CALL p();
--echo # The warning appeared after we added the trigger:
SHOW WARNINGS;

DROP TRIGGER tr;

CALL p();
--echo # No trigger anymore, but the warning is still there:
SHOW WARNINGS;

--connect (con1,localhost,root,,test)
CALL p();
--echo # No warnings for the new connection:
SHOW WARNINGS;

# cleanup

DROP TABLE t1, t2;
DROP PROCEDURE p;

This is not a replication bug, apparently there is a bug on how the stored procedure cache is updated.

The LEX::BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS unsafe warnings are set in sql_base.cc --> lock_tables after analysing if tables are being locked
on behalf of a trigger or a stored function and one of the tables has
an AUTO_INCREMENT column.

When there is no trigger and INSERT INTO t1 (f) VALUES (1) is processed, only t1 is locked.

When there is a trigger and INSERT INTO t1 (f) VALUES (1) is processed, both t1 and t2 are locked.

When the trigger is dropped and INSERT INTO t1 (f) VALUES (1) is processed, both t1 and t2 are still locked.

Either there is a problem in the sp_head or sp_lex_keeper objects which are not being updated.

Find in what follows a new test case:

CREATE PROCEDURE p()
BEGIN
INSERT INTO t1 (f) VALUES (1);
END |
--delimiter ;

CALL p();
--echo # No warnings, everything is safe so far:
SHOW WARNINGS;

CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t3 (extra) VALUES (new.f);
SHOW WARNINGS;

CALL p();
SHOW WARNINGS;

DROP TRIGGER tr;

CALL p();
SHOW WARNINGS;

INSERT INTO t2 (extra) VALUES (10);

CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t2 (extra) VALUES (new.f);

--error 1146
CALL p();
SHOW WARNINGS;

--connect (con1,localhost,root,,test)

CALL p();
SHOW WARNINGS;

# cleanup

DROP TABLE t1, t2, t3;
DROP PROCEDURE p;

In the test case presented above, the fourth call to p() fails because t2 allegedly does not exist. However, the fifth call to p() made through a different connection does not show any problem.

See in what follows a complete version of the test case partially pasted in
[28 Dec 2010 17:12] Alfranio Correia:

--source include/have_log_bin.inc
--source include/have_binlog_format_statement.inc
--disable_warnings

CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");

CREATE TABLE t1 (f INT);
CREATE TABLE t2
(i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, extra INT);
CREATE TABLE t3 (extra INT);

--delimiter |
CREATE PROCEDURE p()
BEGIN
INSERT INTO t1 (f) VALUES (1);
END |
--delimiter ;

########################################################

##### FIRST CALL *WITHOUT* TRIGGER #######
CALL p();

########################################################
CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t3 (extra) VALUES (new.f);

##### SECOND CALL *WITH* TRIGGER #######
CALL p();

########################################################
DROP TRIGGER tr;

##### THIRD CALL *WITHOUT* TRIGGER #######
CALL p();

########################################################
INSERT INTO t2 (extra) VALUES (10);

CREATE TRIGGER tr AFTER INSERT ON t1
FOR EACH ROW INSERT INTO t2 (extra) VALUES (new.f);

##### FORTH CALL *WITH* TRIGGER #######
SELECT * FROM t2;
--error 1146  ##### FAILURE ???? ######
CALL p();
SELECT * FROM t2;

########################################################
--connect (con1,localhost,root,,test)

SELECT * FROM t2;
##### FITH CALL *WITH* TRIGGER #######
CALL p();
SELECT * FROM t2;

# cleanup

DROP TABLE t1, t2, t3;
DROP PROCEDURE p;

Noted in 5.5.11, 5.6.3 changelogs.

CREATE TRIGGER and DROP TRIGGER can change the prelocking list of 
stored routines, but the routine cache did not detect such changes,
resulting in routine execution with an inaccurate locking list.

CHANGESET - http://lists.mysql.com/commits/132730