1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
drop table if exists t1,t2;
drop procedure if exists p1;
#
#MDEV-6985: MariaDB crashes on stored procedure call
#
CREATE TABLE `t1` (
`ID` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB;
CREATE TABLE `t2` (
`ID` int(11) NOT NULL,
`DATE` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB;
CREATE PROCEDURE `p1`()
BEGIN
DECLARE _mySelect CURSOR FOR
SELECT DISTINCT t1.ID
FROM t1
LEFT JOIN t2 AS t2 ON
t2.ID = t1.ID
AND t2.DATE = (
SELECT MAX(T3.DATE) FROM t2 AS T3 WHERE T3.ID = t2.ID AND T3.DATE<=NOW()
)
WHERE t1.ID = 1;
OPEN _mySelect;
CLOSE _mySelect;
END ;;
CALL p1();
CALL p1();
drop procedure p1;
drop table t1,t2;
#
# BUG 16041903: CONTINUE HANDLER NOT INVOKED
# IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT
#
# Save and set lock wait timeout
SET @lock_wait_timeout_saved= @@lock_wait_timeout;
SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout;
SET @@lock_wait_timeout= 1;
SET @@innodb_lock_wait_timeout= 1;
# Create a function with exit handler:
CREATE FUNCTION f1() RETURNS VARCHAR(20)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table';
INSERT INTO no_such_table VALUES (1);
END//
# Create a function calling f1():
CREATE FUNCTION f2() RETURNS VARCHAR(20)
BEGIN
RETURN f1();
END//
# Create a function provoking deadlock:
CREATE FUNCTION f3() RETURNS VARCHAR(20)
BEGIN
UPDATE t1 SET i= 1 WHERE i= 1;
RETURN 'Will never get here';
END//
# Create a function calling f3, to create
# a deadlock indirectly:
CREATE FUNCTION f4() RETURNS VARCHAR(20)
BEGIN
RETURN f3();
END//
# Open another connection, create and initialize a table
# to be used for provoking deadlock, put a lock on the table:
connect con1,localhost,root,,;
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
SET AUTOCOMMIT= 0;
UPDATE t1 SET i=1 WHERE i=1;
# On the default connection, do an update to provoke a
# deadlock, then call the function with handler. This case
# fails without the patch (with error ER_NO_SUCH_TABLE):
connection default;
SET AUTOCOMMIT= 0;
UPDATE t1 SET i=1 WHERE i=1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT f1() AS 'f1():';
f1():
No such table
# Provoke another deadlock, then call the function with
# handler indirectly. This case fails without the patch
# (with error ER_NO_SUCH_TABLE):
UPDATE t1 SET i= 1 WHERE i= 1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT f2() AS 'f2():';
f2():
No such table
# Provoke yet another deadlock, but now from within a function,
# then call the function with handler. This succeeds even
# without the patch because is_fatal_sub_stmt_error is reset
# in restore_sub_stmt after the failing function has been
# executed. The test case is included anyway for better coverage:
SELECT f3() AS 'f3():';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT f1() AS 'f1():';
f1():
No such table
# Provoke yet another deadlock, but now from within a function,
# calling another function, then call the function with handler.
# This succeeds even without the patch because
# is_fatal_sub_stmt_error is reset in restore_sub_stmt after
# the failing function has been executed. The test case is
# included anyway for better coverage:
SELECT f4() AS 'f4():';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT f1() AS 'f1():';
f1():
No such table
# Disconnect, drop functions and table:
disconnect con1;
DROP FUNCTION f4;
DROP FUNCTION f3;
DROP FUNCTION f2;
DROP FUNCTION f1;
DROP TABLE t1;
# Reset lock wait timeouts
SET @@lock_wait_timeout= @lock_wait_timeout_saved;
SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved;
#
# BUG 16041903: End of test case
#
|
