Skip to content

Commit a386ac0

Browse files
author
Nisha Gopalakrishnan
committed
BUG#20094067: BACKPORT BUG#19683834 TO 5.6.
Backporting the patch and the test case fixed as part of BUG#16041903 and BUG#19683834 respectively. Note: The refactoring requested by the cluster team has not been backported.
1 parent cac6fc8 commit a386ac0

File tree

8 files changed

+330
-28
lines changed

8 files changed

+330
-28
lines changed

‎mysql-test/r/sp.result‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7759,4 +7759,104 @@ MyISAM
77597759
SET @@default_storage_engine = @default_storage_engine_saved;
77607760
DROP PROCEDURE p1;
77617761
DROP TABLE t1;
7762+
7763+
#
7764+
# BUG 16041903: CONTINUE HANDLER NOT INVOKED
7765+
# IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT
7766+
#
7767+
7768+
# Save and set lock wait timeout
7769+
SET @lock_wait_timeout_saved= @@lock_wait_timeout;
7770+
SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout;
7771+
SET @@lock_wait_timeout= 1;
7772+
SET @@innodb_lock_wait_timeout= 1;
7773+
7774+
# Create a function with exit handler:
7775+
CREATE FUNCTION f1() RETURNS VARCHAR(20)
7776+
BEGIN
7777+
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table';
7778+
INSERT INTO no_such_table VALUES (1);
7779+
END//
7780+
7781+
# Create a function calling f1():
7782+
CREATE FUNCTION f2() RETURNS VARCHAR(20)
7783+
BEGIN
7784+
RETURN f1();
7785+
END//
7786+
7787+
# Create a function provoking deadlock:
7788+
CREATE FUNCTION f3() RETURNS VARCHAR(20)
7789+
BEGIN
7790+
UPDATE t1 SET i= 1 WHERE i= 1;
7791+
RETURN 'Will never get here';
7792+
END//
7793+
7794+
# Create a function calling f3, to create
7795+
# a deadlock indirectly:
7796+
CREATE FUNCTION f4() RETURNS VARCHAR(20)
7797+
BEGIN
7798+
RETURN f3();
7799+
END//
7800+
7801+
# Open another connection, create and initialize a table
7802+
# to be used for provoking deadlock, put a lock on the table:
7803+
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
7804+
INSERT INTO t1 VALUES (1);
7805+
SET AUTOCOMMIT= 0;
7806+
UPDATE t1 SET i=1 WHERE i=1;
7807+
7808+
# On the default connection, do an update to provoke a
7809+
# deadlock, then call the function with handler. This case
7810+
# fails without the patch (with error ER_NO_SUCH_TABLE):
7811+
SET AUTOCOMMIT= 0;
7812+
UPDATE t1 SET i=1 WHERE i=1;
7813+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
7814+
SELECT f1() AS 'f1():';
7815+
f1():
7816+
No such table
7817+
7818+
# Provoke another deadlock, then call the function with
7819+
# handler indirectly. This case fails without the patch
7820+
# (with error ER_NO_SUCH_TABLE):
7821+
UPDATE t1 SET i= 1 WHERE i= 1;
7822+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
7823+
SELECT f2() AS 'f2():';
7824+
f2():
7825+
No such table
7826+
7827+
# Provoke yet another deadlock, but now from within a function,
7828+
# then call the function with handler. This succeeds even
7829+
# without the patch because is_fatal_sub_stmt_error is reset
7830+
# in restore_sub_stmt after the failing function has been
7831+
# executed. The test case is included anyway for better coverage:
7832+
SELECT f3() AS 'f3():';
7833+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
7834+
SELECT f1() AS 'f1():';
7835+
f1():
7836+
No such table
7837+
# Provoke yet another deadlock, but now from within a function,
7838+
# calling another function, then call the function with handler.
7839+
# This succeeds even without the patch because
7840+
# is_fatal_sub_stmt_error is reset in restore_sub_stmt after
7841+
# the failing function has been executed. The test case is
7842+
# included anyway for better coverage:
7843+
SELECT f4() AS 'f4():';
7844+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
7845+
SELECT f1() AS 'f1():';
7846+
f1():
7847+
No such table
7848+
7849+
# Disconnect, drop functions and table:
7850+
DROP FUNCTION f4;
7851+
DROP FUNCTION f3;
7852+
DROP FUNCTION f2;
7853+
DROP FUNCTION f1;
7854+
DROP TABLE t1;
7855+
7856+
# Reset lock wait timeouts
7857+
SET @@lock_wait_timeout= @lock_wait_timeout_saved;
7858+
SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
7859+
#
7860+
# BUG 16041903: End of test case
7861+
#
77627862
# End of 5.6 tests

‎mysql-test/r/trigger-trans.result‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -195,3 +195,28 @@ b val
195195
14 g
196196
drop trigger t1_after_insert;
197197
drop table t1,t2;
198+
#
199+
#Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION
200+
# AND TRIGGER HANDLERS TO BE IGNORED
201+
#Code fixed in Bug#16041903
202+
CREATE TABLE t1 (id int unsigned PRIMARY KEY, val int DEFAULT 0)
203+
ENGINE=InnoDB;
204+
INSERT INTO t1 (id) VALUES (1), (2);
205+
CREATE TABLE t2 (id int PRIMARY KEY);
206+
CREATE TABLE t3 LIKE t2;
207+
CREATE TRIGGER bef_insert BEFORE INSERT ON t2 FOR EACH ROW
208+
BEGIN
209+
DECLARE CONTINUE HANDLER FOR 1062 BEGIN END;
210+
INSERT INTO t3 (id) VALUES (NEW.id);
211+
INSERT INTO t3 (id) VALUES (NEW.id);
212+
END//
213+
START TRANSACTION;
214+
UPDATE t1 SET val = val + 1;
215+
connect con2,localhost,root,,test,,;
216+
SET SESSION innodb_lock_wait_timeout = 2;
217+
UPDATE t1 SET val = val + 1;
218+
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
219+
INSERT INTO t2 (id) VALUES (1);
220+
disconnect con2;
221+
connection default;
222+
DROP TABLE t3, t2, t1;

‎mysql-test/t/sp.test‎

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,9 @@
1515
# Tests that require multibyte character sets, which are not always available,
1616
# go into separate files (e.g. sp-ucs2.test)
1717

18+
# Save the initial number of concurrent sessions
19+
--source include/count_sessions.inc
20+
1821
use test;
1922

2023
# Test tables
@@ -9005,4 +9008,119 @@ DROP PROCEDURE p1;
90059008

90069009
DROP TABLE t1;
90079010

9011+
9012+
--echo
9013+
--echo #
9014+
--echo # BUG 16041903: CONTINUE HANDLER NOT INVOKED
9015+
--echo # IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT
9016+
--echo #
9017+
9018+
--echo
9019+
--echo # Save and set lock wait timeout
9020+
SET @lock_wait_timeout_saved= @@lock_wait_timeout;
9021+
SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout;
9022+
SET @@lock_wait_timeout= 1;
9023+
SET @@innodb_lock_wait_timeout= 1;
9024+
9025+
--echo
9026+
--echo # Create a function with exit handler:
9027+
DELIMITER //;
9028+
CREATE FUNCTION f1() RETURNS VARCHAR(20)
9029+
BEGIN
9030+
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table';
9031+
INSERT INTO no_such_table VALUES (1);
9032+
END//
9033+
9034+
--echo
9035+
--echo # Create a function calling f1():
9036+
CREATE FUNCTION f2() RETURNS VARCHAR(20)
9037+
BEGIN
9038+
RETURN f1();
9039+
END//
9040+
9041+
--echo
9042+
--echo # Create a function provoking deadlock:
9043+
CREATE FUNCTION f3() RETURNS VARCHAR(20)
9044+
BEGIN
9045+
UPDATE t1 SET i= 1 WHERE i= 1;
9046+
RETURN 'Will never get here';
9047+
END//
9048+
9049+
--echo
9050+
--echo # Create a function calling f3, to create
9051+
--echo # a deadlock indirectly:
9052+
CREATE FUNCTION f4() RETURNS VARCHAR(20)
9053+
BEGIN
9054+
RETURN f3();
9055+
END//
9056+
DELIMITER ;//
9057+
9058+
--echo
9059+
--echo # Open another connection, create and initialize a table
9060+
--echo # to be used for provoking deadlock, put a lock on the table:
9061+
connect (con1,localhost,root,,);
9062+
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
9063+
INSERT INTO t1 VALUES (1);
9064+
SET AUTOCOMMIT= 0;
9065+
UPDATE t1 SET i=1 WHERE i=1;
9066+
9067+
--echo
9068+
--echo # On the default connection, do an update to provoke a
9069+
--echo # deadlock, then call the function with handler. This case
9070+
--echo # fails without the patch (with error ER_NO_SUCH_TABLE):
9071+
--connection default
9072+
SET AUTOCOMMIT= 0;
9073+
--error ER_LOCK_WAIT_TIMEOUT
9074+
UPDATE t1 SET i=1 WHERE i=1;
9075+
SELECT f1() AS 'f1():';
9076+
9077+
--echo
9078+
--echo # Provoke another deadlock, then call the function with
9079+
--echo # handler indirectly. This case fails without the patch
9080+
--echo # (with error ER_NO_SUCH_TABLE):
9081+
--error ER_LOCK_WAIT_TIMEOUT
9082+
UPDATE t1 SET i= 1 WHERE i= 1;
9083+
SELECT f2() AS 'f2():';
9084+
9085+
--echo
9086+
--echo # Provoke yet another deadlock, but now from within a function,
9087+
--echo # then call the function with handler. This succeeds even
9088+
--echo # without the patch because is_fatal_sub_stmt_error is reset
9089+
--echo # in restore_sub_stmt after the failing function has been
9090+
--echo # executed. The test case is included anyway for better coverage:
9091+
--error ER_LOCK_WAIT_TIMEOUT
9092+
SELECT f3() AS 'f3():';
9093+
SELECT f1() AS 'f1():';
9094+
9095+
--echo # Provoke yet another deadlock, but now from within a function,
9096+
--echo # calling another function, then call the function with handler.
9097+
--echo # This succeeds even without the patch because
9098+
--echo # is_fatal_sub_stmt_error is reset in restore_sub_stmt after
9099+
--echo # the failing function has been executed. The test case is
9100+
--echo # included anyway for better coverage:
9101+
--error ER_LOCK_WAIT_TIMEOUT
9102+
SELECT f4() AS 'f4():';
9103+
SELECT f1() AS 'f1():';
9104+
9105+
--echo
9106+
--echo # Disconnect, drop functions and table:
9107+
--disconnect con1
9108+
DROP FUNCTION f4;
9109+
DROP FUNCTION f3;
9110+
DROP FUNCTION f2;
9111+
DROP FUNCTION f1;
9112+
DROP TABLE t1;
9113+
9114+
--echo
9115+
--echo # Reset lock wait timeouts
9116+
SET @@lock_wait_timeout= @lock_wait_timeout_saved;
9117+
SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
9118+
9119+
--echo #
9120+
--echo # BUG 16041903: End of test case
9121+
--echo #
9122+
9123+
# Wait till we reached the initial number of concurrent sessions
9124+
--source include/wait_until_count_sessions.inc
9125+
90089126
--echo # End of 5.6 tests

‎mysql-test/t/trigger-trans.test‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,9 @@
22
# (or just InnoDB storage engine)
33
--source include/have_innodb.inc
44

5+
# Save the initial number of concurrent sessions
6+
--source include/count_sessions.inc
7+
58
--disable_warnings
69
drop table if exists t1;
710
--enable_warnings
@@ -182,3 +185,54 @@ insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
182185
select * from t2 order by b;
183186
drop trigger t1_after_insert;
184187
drop table t1,t2;
188+
189+
--echo #
190+
--echo #Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION
191+
--echo # AND TRIGGER HANDLERS TO BE IGNORED
192+
193+
--echo #Code fixed in Bug#16041903
194+
--enable_connect_log
195+
196+
CREATE TABLE t1 (id int unsigned PRIMARY KEY, val int DEFAULT 0)
197+
ENGINE=InnoDB;
198+
INSERT INTO t1 (id) VALUES (1), (2);
199+
200+
CREATE TABLE t2 (id int PRIMARY KEY);
201+
CREATE TABLE t3 LIKE t2;
202+
203+
# Trigger with continue handler for ER_DUP_ENTRY(1062)
204+
DELIMITER //;
205+
CREATE TRIGGER bef_insert BEFORE INSERT ON t2 FOR EACH ROW
206+
BEGIN
207+
DECLARE CONTINUE HANDLER FOR 1062 BEGIN END;
208+
INSERT INTO t3 (id) VALUES (NEW.id);
209+
INSERT INTO t3 (id) VALUES (NEW.id);
210+
END//
211+
DELIMITER ;//
212+
213+
# Transaction 1: Grab locks on t1
214+
START TRANSACTION;
215+
UPDATE t1 SET val = val + 1;
216+
217+
# Transaction 2:
218+
--connect (con2,localhost,root,,test,,)
219+
SET SESSION innodb_lock_wait_timeout = 2;
220+
# Trigger lock timeout (1205)
221+
--error ER_LOCK_WAIT_TIMEOUT
222+
UPDATE t1 SET val = val + 1;
223+
224+
# This insert should go through, as the continue handler should
225+
# handle ER_DUP_ENTRY, even after ER_LOCK_WAIT_TIMEOUT (Bug#16041903)
226+
INSERT INTO t2 (id) VALUES (1);
227+
228+
# Cleanup
229+
disconnect con2;
230+
--source include/wait_until_disconnected.inc
231+
connection default;
232+
233+
DROP TABLE t3, t2, t1;
234+
235+
--disable_connect_log
236+
237+
# Wait till we reached the initial number of concurrent sessions
238+
--source include/wait_until_count_sessions.inc

‎sql/ha_ndbcluster.cc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2004, 2015, Oracle and/or its affiliates. All rights reserved.
22

33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -8154,7 +8154,7 @@ static int ndbcluster_rollback(handlerton *hton, THD *thd, bool all)
81548154
of the transaction
81558155
*/
81568156
DBUG_PRINT("info", ("Rollback before start or end-of-statement only"));
8157-
mark_transaction_to_rollback(thd, 1);
8157+
thd->mark_transaction_to_rollback(1);
81588158
my_error(ER_WARN_ENGINE_TRANSACTION_ROLLBACK, MYF(0), "NDB");
81598159
DBUG_RETURN(0);
81608160
}

‎sql/sql_base.cc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -4118,7 +4118,7 @@ request_backoff_action(enum_open_table_action action_arg,
41184118
if (action_arg != OT_REOPEN_TABLES && m_has_locks)
41194119
{
41204120
my_error(ER_LOCK_DEADLOCK, MYF(0));
4121-
mark_transaction_to_rollback(m_thd, true);
4121+
m_thd->mark_transaction_to_rollback(true);
41224122
return TRUE;
41234123
}
41244124
/*

0 commit comments

Comments
 (0)