Skip to content

Commit 5becbf0

Browse files
phulakunphulakun
committed
Bug#32326510 - REPREPARE AT THE BEGINNING OF AN IMPLICIT TRANS WILL CAUSE 1837 ERROR
When autocommit is enabled and gtid_next is set, executing prepared statement results in an ER_GTID_NEXT_TYPE_UNDEFINED_GROUP error. In autocommit mode if gtid_next is set then during statement transaction commit or rollback gtid_next is invalidated. Even for a prepared statement execution gtid_next is invalidated during transaction commit or rollback. But for a prepared statement, if re-prepare is triggered (due to dependent table metadata change) then prepared statement execution returns with re-prepare error and transaction is rolled back. During transaction rollback gtid_next is invalidated. On execution of re-prepared statement, an error is reported for incorrect gtid_next value. This issue exists with stored programs too. LEX instance of a SQL statement in stored program is cached. If metadata of a table used by SQL statement changes then re-prepare is triggered for stored program statements and gtid_next is invalidated. To fix this issue, gtid_next update during transaction rollback is skipped for the prepared statement and stored program statements if re-prepare is triggered. Same gtid_next value is reused for executing re-prepared statement with this change. Change-Id: I5eda7a056f0b2083af316b92f7ecc2d9185c2576
1 parent 260373b commit 5becbf0

File tree

6 files changed

+317
-10
lines changed

6 files changed

+317
-10
lines changed

‎mysql-test/suite/binlog/r/binlog_gtid_ps_reprepare.result

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
# ==== Initialize ====
2+
CREATE TABLE t1 (a INT);
3+
CREATE TABLE t2 (a INT);
4+
PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)';
5+
CREATE PROCEDURE p1 ()
6+
BEGIN
7+
INSERT INTO t2 VALUES(2);
8+
END
9+
$
10+
CREATE PROCEDURE p2 ()
11+
BEGIN
12+
EXECUTE stmt1;
13+
END
14+
$
15+
# ==== Case 1 ====
16+
# Test case to verify gtid_next update with prepared statement re-prepare.
17+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
18+
BEGIN;
19+
EXECUTE stmt1;
20+
COMMIT;
21+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
22+
TRUNCATE TABLE t1;
23+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
24+
# Without fix, following statement execution fails.
25+
EXECUTE stmt1;
26+
# ==== Case 2 ====
27+
# Test case to verify gtid_next update with stored procedure statement
28+
# re-prepare.
29+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4';
30+
CALL p1();
31+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:5';
32+
TRUNCATE TABLE t2;
33+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:6';
34+
# Without fix, following statement execution fails.
35+
CALL p1();
36+
# ==== Case 3 ====
37+
# Test case to verify gtid_next update with prepared statement re-prepare
38+
# in stored procedure.
39+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:7';
40+
CALL p2();
41+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8';
42+
TRUNCATE TABLE t1;
43+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:9';
44+
# Without fix, following statement execution fails.
45+
CALL p2();
46+
# === Case 4 ===
47+
# Test case to verify gtid_next update with prepared statement max
48+
# re-prepare attempt hits.
49+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10';
50+
TRUNCATE TABLE t1;
51+
# Adding debug point 'simulate_max_reprepare_attempts_hit_case' to @@SESSION.debug
52+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:11';
53+
# Error is reported when max re-prepare limit is hit. Transaction is
54+
# rolled back and gtid_next is updated.
55+
EXECUTE stmt1;
56+
ERROR HY000: Prepared statement needs to be re-prepared
57+
INSERT INTO t1 VALUES(10);
58+
ERROR HY000: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:11'.
59+
# === Case 5 ===
60+
# Test case to verify gtid_next update with stored procedure statement
61+
# max re-prepare attempt hits.
62+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:12';
63+
TRUNCATE TABLE t2;
64+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13';
65+
# Error is reported when max re-prepare limit is hit. Transaction is
66+
# rolled back and gtid_next is updated.
67+
CALL p1;
68+
ERROR HY000: Prepared statement needs to be re-prepared
69+
INSERT INTO t2 VALUES(10);
70+
ERROR HY000: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13'.
71+
# ==== Cleanup ====
72+
SET gtid_next = DEFAULT;
73+
# Removing debug point 'simulate_max_reprepare_attempts_hit_case' from @@SESSION.debug
74+
DEALLOCATE PREPARE stmt1;
75+
DROP TABLE t1, t2;
76+
DROP PROCEDURE p1;
77+
DROP PROCEDURE p2;

‎mysql-test/suite/binlog/t/binlog_gtid_ps_reprepare.test

Lines changed: 197 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,197 @@
1+
# ==== Purpose ====
2+
#
3+
# If re-prepare of a prepared statement or statement in stored program is
4+
# triggered then gtid_next should not be updated. Tests in this file verifies
5+
# gtid_next value update with prepared statement and stored procedure
6+
# re-prepare. Re-prepare statement code is same for all the stored programs. So
7+
# behavior is verified with only stored procedure in this file.
8+
#
9+
# ==== Requirements ====
10+
#
11+
# When a session has set GTID_NEXT to a UUID:NUMBER and AUTOCOMMIT is enabled,
12+
# the following requirements shall hold:
13+
#
14+
# R1. If a statement is re-prepared, it shall leave GTID_NEXT unchanged:
15+
#
16+
# R1.1. For prepared statements (EXECUTE)
17+
# R1.2. For stored procedures (CALL)
18+
#
19+
# R2. If a statement exhausts the re-prepare count so that it fails with
20+
# ER_NEED_REPREPARE, it shall set GTID_NEXT to UNDEFINED:
21+
#
22+
# R2.1. For prepared statements (EXECUTE)
23+
# R2.2. For stored procedures (CALL)
24+
#
25+
# ==== Implementation ====
26+
#
27+
# Initialize
28+
# a) create table
29+
# b) create prepared statement and stored procedure using table created
30+
# in step a.
31+
#
32+
# 1) Verify behavior with a prepared statement
33+
# 1.1) SET gtid_next = UUID:NUMBER
34+
# 1.2) Execute prepared statement.
35+
# 1.3) SET gtid_next = UUID:NUMBER
36+
# 1.4) Truncate table so that table version is updated.
37+
# 1.5) SET gtid_next = UUID:NUMBER
38+
# 1.6) Re-execute prepared statement. Statement re-prepare is triggered
39+
# as table version is changed. This operation should *not* invalidate
40+
# gtid_next.
41+
#
42+
# 2) Verify behavior with a stored procedure.
43+
# 2.1) SET gtid_next = UUID:NUMBER
44+
# 2.2) Execute stored procedure.
45+
# 2.3) SET gtid_next = UUID:NUMBER
46+
# 2.4) Truncate table so that table version is updated.
47+
# 2.5) SET gtid_next = UUID:NUMBER
48+
# 2.6) Re-execute stored procedure. Statement re-prepare is triggered as
49+
# table version is changed. This operation should *not* invalidate
50+
# gtid_next.
51+
#
52+
# 3) Verify behavior with a prepared statement in stored procedure.
53+
# 3.1) SET gtid_next = UUID:NUMBER
54+
# 3.2) Execute stored procedure.
55+
# 3.3) SET gtid_next = UUID:NUMBER
56+
# 3.4) Truncate table so that table version is updated.
57+
# 3.5) SET gtid_next = UUID:NUMBER
58+
# 3.6) Re-execute stored procedure. Statement re-prepare is triggered as
59+
# table version is changed. This operation should *not* invalidate
60+
# gtid_next.
61+
#
62+
# 4) Verify behavior with a prepared statement in max re-prepare hit situation.
63+
# 4.1) SET gtid_next = UUID:NUMBER
64+
# 4.2) Execute prepared statement.
65+
# 4.3) SET gtid_next = UUID:NUMBER
66+
# 4.4) Truncate table so that table version is updated.
67+
# 4.5) SET gtid_next = UUID:NUMBER
68+
# 4.6) Simulate max prepared statement re-prepare hit condition.
69+
# 4.7) Re-execute prepared statement. Statement re-prepare is triggered as
70+
# table version is changed. This operation fails and gtid_next is
71+
# invalidated.
72+
#
73+
# 5) Verify behavior with a stored procedure in max re-prepare hit situation.
74+
# 5.1) SET gtid_next = UUID:NUMBER
75+
# 5.2) Execute stored procedure.
76+
# 5.3) SET gtid_next = UUID:NUMBER
77+
# 5.4) Truncate table so that table version is updated.
78+
# 5.5) SET gtid_next = UUID:NUMBER
79+
# 5.6) Simulate max prepared statement re-prepare hit condition.
80+
# 5.7) Re-execute stored procedure. Statement re-prepare is triggered as
81+
# table version is changed. This operation fails and gtid_next is
82+
# invalidated.
83+
#
84+
#
85+
# ==== References ====
86+
#
87+
# Bug#32326510 - REPREPARE AT THE BEGINNING OF AN IMPLICIT TRANS WILL CAUSE 1837
88+
# ERROR.
89+
#
90+
91+
--source include/have_gtid.inc
92+
--source include/have_debug.inc
93+
94+
--echo # ==== Initialize ====
95+
CREATE TABLE t1 (a INT);
96+
CREATE TABLE t2 (a INT);
97+
PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)';
98+
DELIMITER $;
99+
CREATE PROCEDURE p1 ()
100+
BEGIN
101+
INSERT INTO t2 VALUES(2);
102+
END
103+
$
104+
105+
CREATE PROCEDURE p2 ()
106+
BEGIN
107+
EXECUTE stmt1;
108+
END
109+
$
110+
DELIMITER ;$
111+
112+
113+
--echo # ==== Case 1 ====
114+
--echo # Test case to verify gtid_next update with prepared statement re-prepare.
115+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
116+
BEGIN;
117+
EXECUTE stmt1;
118+
COMMIT;
119+
120+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
121+
TRUNCATE TABLE t1;
122+
123+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
124+
--echo # Without fix, following statement execution fails.
125+
EXECUTE stmt1;
126+
127+
128+
--echo # ==== Case 2 ====
129+
--echo # Test case to verify gtid_next update with stored procedure statement
130+
--echo # re-prepare.
131+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:4';
132+
CALL p1();
133+
134+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:5';
135+
TRUNCATE TABLE t2;
136+
137+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:6';
138+
--echo # Without fix, following statement execution fails.
139+
CALL p1();
140+
141+
142+
--echo # ==== Case 3 ====
143+
--echo # Test case to verify gtid_next update with prepared statement re-prepare
144+
--echo # in stored procedure.
145+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:7';
146+
CALL p2();
147+
148+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8';
149+
TRUNCATE TABLE t1;
150+
151+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:9';
152+
--echo # Without fix, following statement execution fails.
153+
CALL p2();
154+
155+
156+
--echo # === Case 4 ===
157+
--echo # Test case to verify gtid_next update with prepared statement max
158+
--echo # re-prepare attempt hits.
159+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10';
160+
TRUNCATE TABLE t1;
161+
162+
--let $debug_point= simulate_max_reprepare_attempts_hit_case
163+
--let $debug_type= SESSION
164+
--source include/add_debug_point.inc
165+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:11';
166+
--echo # Error is reported when max re-prepare limit is hit. Transaction is
167+
--echo # rolled back and gtid_next is updated.
168+
--error ER_NEED_REPREPARE
169+
EXECUTE stmt1;
170+
--error ER_GTID_NEXT_TYPE_UNDEFINED_GROUP
171+
INSERT INTO t1 VALUES(10);
172+
173+
174+
--echo # === Case 5 ===
175+
--echo # Test case to verify gtid_next update with stored procedure statement
176+
--echo # max re-prepare attempt hits.
177+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:12';
178+
TRUNCATE TABLE t2;
179+
180+
SET gtid_next = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13';
181+
--echo # Error is reported when max re-prepare limit is hit. Transaction is
182+
--echo # rolled back and gtid_next is updated.
183+
--error ER_NEED_REPREPARE
184+
CALL p1;
185+
--error ER_GTID_NEXT_TYPE_UNDEFINED_GROUP
186+
INSERT INTO t2 VALUES(10);
187+
188+
189+
--echo # ==== Cleanup ====
190+
SET gtid_next = DEFAULT;
191+
--let $debug_point= simulate_max_reprepare_attempts_hit_case
192+
--let $debug_type= SESSION
193+
--source include/remove_debug_point.inc
194+
DEALLOCATE PREPARE stmt1;
195+
DROP TABLE t1, t2;
196+
DROP PROCEDURE p1;
197+
DROP PROCEDURE p2;

‎sql/sp_instr.cc

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -705,7 +705,6 @@ bool sp_lex_instr::validate_lex_and_execute_core(THD *thd,
705705
bool open_tables)
706706
{
707707
Reprepare_observer reprepare_observer;
708-
int reprepare_attempt= 0;
709708

710709
while (true)
711710
{
@@ -771,12 +770,14 @@ bool sp_lex_instr::validate_lex_and_execute_core(THD *thd,
771770
raise it to the user;
772771
- we take only 3 attempts to reprepare the query, otherwise we might end
773772
up in the endless loop.
773+
- Reprepare_observer ensures that the statement is retried a maximum
774+
number of times, to avoid an endless loop.
774775
*/
775776
if (stmt_reprepare_observer &&
776777
!thd->is_fatal_error &&
777778
!thd->killed &&
778779
thd->get_stmt_da()->mysql_errno() == ER_NEED_REPREPARE &&
779-
reprepare_attempt++ < 3)
780+
stmt_reprepare_observer->can_retry())
780781
{
781782
assert(stmt_reprepare_observer->is_invalidated());
782783

‎sql/sql_parse.cc

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4956,6 +4956,12 @@ case SQLCOM_PREPARE:
49564956
finish:
49574957
THD_STAGE_INFO(thd, stage_query_end);
49584958

4959+
if (res && thd->get_reprepare_observer() != NULL &&
4960+
thd->get_reprepare_observer()->is_invalidated() &&
4961+
thd->get_reprepare_observer()->can_retry()) {
4962+
thd->skip_gtid_rollback = true;
4963+
}
4964+
49594965
// Cleanup EXPLAIN info
49604966
if (!thd->in_sub_stmt)
49614967
{
@@ -5103,6 +5109,8 @@ case SQLCOM_PREPARE:
51035109
DEBUG_SYNC(thd, "restore_previous_state_after_statement_failed");
51045110
}
51055111

5112+
thd->skip_gtid_rollback = false;
5113+
51065114
DBUG_RETURN(res || thd->is_error());
51075115
}
51085116

‎sql/sql_prepare.cc

Lines changed: 10 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2977,6 +2977,7 @@ Reprepare_observer::report_error(THD *thd)
29772977
thd->get_stmt_da()->reset_diagnostics_area();
29782978
thd->get_stmt_da()->set_error_status(ER_NEED_REPREPARE);
29792979
m_invalidated= TRUE;
2980+
m_attempt++;
29802981

29812982
return TRUE;
29822983
}
@@ -3541,17 +3542,20 @@ Prepared_statement::set_parameters(String *expanded_query,
35413542
validation error, prepare a new copy of the prepared statement,
35423543
swap the old and the new statements, and try again.
35433544
If there is a validation error again, repeat the above, but
3544-
perform no more than MAX_REPREPARE_ATTEMPTS.
3545+
perform not more than a maximum number of times. Reprepare_observer
3546+
ensures that a prepared statement execution is retried not more than a
3547+
maximum number of times.
35453548
35463549
@note We have to try several times in a loop since we
35473550
release metadata locks on tables after prepared statement
35483551
prepare. Therefore, a DDL statement may sneak in between prepare
3549-
and execute of a new statement. If this happens repeatedly
3550-
more than MAX_REPREPARE_ATTEMPTS times, we give up.
3552+
and execute of a new statement. If a prepared statement execution
3553+
is retried for a maximum number of times then we give up.
3554+
35513555
35523556
@return TRUE if an error, FALSE if success
3553-
@retval TRUE either MAX_REPREPARE_ATTEMPTS has been reached,
3554-
or some general error
3557+
@retval TRUE either statement execution is retried for
3558+
a maximum number of times or some general error.
35553559
@retval FALSE successfully executed the statement, perhaps
35563560
after having reprepared it a few times.
35573561
*/
@@ -3562,10 +3566,8 @@ Prepared_statement::execute_loop(String *expanded_query,
35623566
uchar *packet,
35633567
uchar *packet_end)
35643568
{
3565-
const int MAX_REPREPARE_ATTEMPTS= 3;
35663569
Reprepare_observer reprepare_observer;
35673570
bool error;
3568-
int reprepare_attempt= 0;
35693571

35703572
/* Check if we got an error when sending long data */
35713573
if (state == Query_arena::STMT_ERROR)
@@ -3617,7 +3619,7 @@ Prepared_statement::execute_loop(String *expanded_query,
36173619
if ((sql_command_flags[lex->sql_command] & CF_REEXECUTION_FRAGILE) &&
36183620
error && !thd->is_fatal_error && !thd->killed &&
36193621
reprepare_observer.is_invalidated() &&
3620-
reprepare_attempt++ < MAX_REPREPARE_ATTEMPTS)
3622+
reprepare_observer.can_retry())
36213623
{
36223624
assert(thd->get_stmt_da()->mysql_errno() == ER_NEED_REPREPARE);
36233625
thd->clear_error();

0 commit comments

Comments
 (0)