mirror of
https://github.com/MariaDB/server.git
synced 2025-12-20 19:25:43 +01:00
5689d1275f
There is inconsistency happens between two INSERTs. First INSERT happens after only tr1 was created. It enters DML_prelocking_strategy::handle_table() and sets need_prelocking as there is table->triggers. But there is no relevant triggers: tr1 is TRG_EVENT_DELETE, so nothing was added for prelocking. Nonetheless, extend_table_list() calls mark_as_requiring_prelocking() based on need_prelocking. It sets lex->query_tables_own_last to save_query_tables_last which is double pointer to NULL. Then reset_lex_and_exec_core() based on that value (double pointer is non-NULL itself) propagates that to SP's m_lex. After tr2 created, next execution of INSERT thinks it has prelocking list. extend_table_list() receives has_prelocking_list:true and skips handle_table() (and therefore any prelocking list updates). As a result when process_triggers() is trying to open t2 it does not see it in the list of open (and prelocked) tables and fails with ER_NO_SUCH_TABLE (which is misleading error code as t2 exists). The fix avoids setting need_prelocking for the cases when no triggers executed (i.e. no matching event type). Note that we can not disable prelocking for triggers without any tables like that: *need_prelocking|= trigger->add_used_tables_to_table_list(thd, &prelocking_ctx->query_tables_last, table_list->belong_to_view); because trigger must be executed under LTM_PRELOCKED, otherwise it will close not own tables (this can be further improved, see TODO in the patch). Thanks to Chǔ Huáxīng <15381158111@163.com> for the patch idea.
151 lines
4.3 KiB
Text
151 lines
4.3 KiB
Text
Tests of synchronization of stored procedure execution.
|
|
SET DEBUG_SYNC= 'RESET';
|
|
#
|
|
# Bug #30977 Concurrent statement using stored function and
|
|
# DROP FUNCTION breaks SBR
|
|
#
|
|
# A stored routine could change after dispatch_command()
|
|
# but before a MDL lock is taken. This must be noticed and the
|
|
# sp cache flushed so the correct version can be loaded.
|
|
#
|
|
connect con2, localhost, root;
|
|
connection default;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 1;
|
|
# Get f1 cached
|
|
SELECT f1();
|
|
f1()
|
|
1
|
|
# Then start executing it again...
|
|
SET DEBUG_SYNC= 'before_execute_sql_command SIGNAL before WAIT_FOR changed';
|
|
# Sending:
|
|
SELECT f1();
|
|
connection con2;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR before';
|
|
# ... but before f1 is locked, change it.
|
|
DROP FUNCTION f1;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 2;
|
|
SET DEBUG_SYNC= 'now SIGNAL changed';
|
|
# We should now get '2' and not '1'.
|
|
connection default;
|
|
# Reaping: SELECT f1()
|
|
f1()
|
|
2
|
|
disconnect con2;
|
|
DROP FUNCTION f1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
#
|
|
# Field translation items must be cleared in case of back-offs
|
|
# for queries that use Information Schema tables. Otherwise
|
|
# memory allocated in fix_fields() for views may end up referring
|
|
# to freed memory.
|
|
#
|
|
DROP FUNCTION IF EXISTS f1;
|
|
connect con2, localhost, root;
|
|
connect con3, localhost, root;
|
|
connection default;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 0;
|
|
connection con2;
|
|
SET DEBUG_SYNC= 'after_wait_locked_pname SIGNAL locked WAIT_FOR issued';
|
|
# con2 will now have an x-lock on f1
|
|
# Sending:
|
|
ALTER FUNCTION f1 COMMENT 'comment';
|
|
connection default;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR locked';
|
|
# This query will block due to the x-lock on f1 and back-off
|
|
SHOW OPEN TABLES WHERE f1()=0;
|
|
connection con3;
|
|
# Check that the IS query is blocked before releasing the x-lock
|
|
SET DEBUG_SYNC= 'now SIGNAL issued';
|
|
connection default;
|
|
# Reaping: ALTER FUNCTION f1 COMMENT 'comment'
|
|
DROP FUNCTION f1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con2;
|
|
disconnect con3;
|
|
#
|
|
# Bug #48246 assert in close_thread_table
|
|
#
|
|
CREATE TABLE t0 (b INTEGER);
|
|
CREATE TABLE t1 (a INTEGER);
|
|
CREATE FUNCTION f1(b INTEGER) RETURNS INTEGER RETURN 1;
|
|
CREATE PROCEDURE p1() SELECT COUNT(f1(a)) FROM t1, t0;
|
|
INSERT INTO t0 VALUES(1);
|
|
INSERT INTO t1 VALUES(1), (2);
|
|
connect con2, localhost, root;
|
|
CALL p1();
|
|
COUNT(f1(a))
|
|
2
|
|
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked_t1 WAIT_FOR go_for_t0';
|
|
# This call used to cause an assertion. MDL deadlock with upcoming
|
|
# LOCK TABLES statement will cause back-off and retry.
|
|
# A variable indicating if a prelocking list exists, used to be not
|
|
# reset properly causing an eventual assert.
|
|
# Sending:
|
|
CALL p1();
|
|
connection default;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR locked_t1';
|
|
# Issue LOCK TABLES statement which will enter in MDL deadlock
|
|
# with CALL statement and as result will cause it to perform
|
|
# back-off and retry.
|
|
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL go_for_t0';
|
|
LOCK TABLES t0 WRITE, t1 WRITE;
|
|
UNLOCK TABLES;
|
|
connection con2;
|
|
# Reaping: CALL p1()
|
|
COUNT(f1(a))
|
|
2
|
|
connection default;
|
|
disconnect con2;
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
DROP TABLES t0, t1;
|
|
#
|
|
# test for bug#11756013
|
|
#
|
|
DROP SCHEMA IF EXISTS s1;
|
|
CREATE SCHEMA s1;
|
|
CREATE PROCEDURE s1.p1() BEGIN END;
|
|
connect con3, localhost, root;
|
|
SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema';
|
|
CALL s1.p1;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR check_db';
|
|
DROP SCHEMA s1;
|
|
SET DEBUG_SYNC='now SIGNAL dropped_schema';
|
|
connection con3;
|
|
ERROR 42000: Unknown database 's1'
|
|
connection default;
|
|
disconnect con3;
|
|
SET DEBUG_SYNC = 'RESET';
|
|
#
|
|
# MDEV-26048 Table 'test._test_new' doesn't exist when add trigger (use pt-osc)
|
|
#
|
|
create table t1 (x int);
|
|
create procedure sp()
|
|
begin
|
|
declare a int default 1;
|
|
while a <= 2 do
|
|
set debug_sync= concat("now wait_for created_", a);
|
|
insert into t1 values(a);
|
|
set debug_sync= "now signal inserted";
|
|
set a= a + 1;
|
|
end while;
|
|
end$$
|
|
connect con1,localhost,root,,test;
|
|
call sp();
|
|
connection default;
|
|
create table t2 (y int);
|
|
create trigger tr1 after delete on t1 for each row delete from t2;
|
|
set debug_sync= "now signal created_1";
|
|
set debug_sync= "now wait_for inserted";
|
|
create trigger tr2 after insert on t1 for each row insert t2 values (0);
|
|
set debug_sync= "now signal created_2";
|
|
connection con1;
|
|
disconnect con1;
|
|
connection default;
|
|
drop trigger tr1;
|
|
drop trigger tr2;
|
|
drop table t2;
|
|
drop procedure sp;
|
|
drop table t1;
|
|
set debug_sync= "reset";
|