[MDEV-15070] Crash when doing a CREATE VIEW inside a package routine Created: 2018-01-25  Updated: 2018-08-31  Resolved: 2018-01-26

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MDEV-15070.diff    
Issue Links:
Duplicate
is duplicated by MDEV-14949 Crash signal 11 in prepared statement... Closed
is duplicated by MDEV-14950 crash 11 in check, if sql command is ... Closed
is duplicated by MDEV-15080 ASAN heap-use-after-free in Query_tab... Closed
Relates
relates to MDEV-15028 Crash in open_tables() while calling ... Closed
relates to MDEV-15040 Crash in st_select_lex::cleanup() in ... Closed

 Description   

I run this script in bb-10.2-compatibility:

SET sql_mode=ORACLE;
 
DELIMITER $$
 
CREATE OR REPLACE PACKAGE pkg2 AS
 
  PROCEDURE p00();
 
END;
 
$$
 
CREATE OR REPLACE PACKAGE BODY pkg2 AS
 
  PROCEDURE p01() AS
 
  BEGIN
 
    SELECT 'This is p01';
 
  END;
 
  PROCEDURE p00() AS
 
  BEGIN
 
    CREATE OR REPLACE VIEW v1 AS SELECT 1;
 
    CALL p01();
 
  END;
 
END;
 
$$
 
DELIMITER ;
 
CALL pkg2.p00;

It crashes with the following stack trace:

#0  0x0000555555b47ad8 in MDL_key::mdl_namespace (this=0x8f8f8f8f8f8f8faf)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/mdl.h:320
 
#1  0x0000555555b3a889 in open_and_process_routine (thd=0x7fff5c000a98, 
    prelocking_ctx=0x7fff5c037d08, rt=0x8f8f8f8f8f8f8f8f, 
    prelocking_strategy=0x7fffe0548ed8, has_prelocking_list=false, 
    ot_ctx=0x7fffe0548dc0, need_prelocking=0x7fffe0548d60, 
    routine_modifies_data=0x7fffe0548d61)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_base.cc:3121
 
#2  0x0000555555b3c598 in open_tables (thd=0x7fff5c000a98, options=..., 
    start=0x7fffe0548e38, counter=0x7fffe0548e54, flags=0, 
    prelocking_strategy=0x7fffe0548ed8)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_base.cc:4060
 
#3  0x0000555555b3dbab in open_and_lock_tables (thd=0x7fff5c000a98, 
    options=..., tables=0x0, derived=true, flags=0, 
    prelocking_strategy=0x7fffe0548ed8)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_base.cc:4745
 
#4  0x0000555555b00901 in open_and_lock_tables (thd=0x7fff5c000a98, 
    tables=0x0, derived=true, flags=0)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_base.h:496
 
#5  0x0000555555bb7337 in Sql_cmd_call::execute (this=0x7fff5c0396b0, 
    thd=0x7fff5c000a98)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:3124
 
#6  0x0000555555bc15e8 in mysql_execute_command (thd=0x7fff5c000a98)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:6254
 
#7  0x0000555555af7cd2 in sp_instr_stmt::exec_core (this=0x7fff5c039c10, 
    thd=0x7fff5c000a98, nextp=0x7fffe0549bf4)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sp_head.cc:3591
 
#8  0x0000555555af72cf in sp_lex_keeper::reset_lex_and_exec_core (
 
    this=0x7fff5c039c60, thd=0x7fff5c000a98, nextp=0x7fffe0549bf4, 
    open_tables=false, instr=0x7fff5c039c10)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sp_head.cc:3336
 
#9  0x0000555555af7983 in sp_instr_stmt::execute (this=0x7fff5c039c10, 
    thd=0x7fff5c000a98, nextp=0x7fffe0549bf4)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sp_head.cc:3507
 
#10 0x0000555555af1a09 in sp_head::execute (this=0x7fff5c031d00, 
    thd=0x7fff5c000a98, merge_da_on_success=true)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sp_head.cc:1390
 
#11 0x0000555555af423a in sp_head::execute_procedure (this=0x7fff5c031d00, 
    thd=0x7fff5c000a98, args=0x7fff5c005588)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sp_head.cc:2313
 
#12 0x0000555555bb6a54 in do_execute_sp (thd=0x7fff5c000a98, sp=0x7fff5c031d00)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:2929
 
#13 0x0000555555bb757a in Sql_cmd_call::execute (this=0x7fff5c028720, 
    thd=0x7fff5c000a98)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:3169
 
#14 0x0000555555bc15e8 in mysql_execute_command (thd=0x7fff5c000a98)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:6254
 
#15 0x0000555555bc639d in mysql_parse (thd=0x7fff5c000a98, 
    rawbuf=0x7fff5c0152e0 "CALL pkg2.p00", length=13, 
    parser_state=0x7fffe054b230, is_com_multi=false, is_next_command=false)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:7974
 
#16 0x0000555555bb3c62 in dispatch_command (command=COM_QUERY, 
    thd=0x7fff5c000a98, packet=0x7fff5c0081e9 "CALL pkg2.p00", 
    packet_length=13, is_com_multi=false, is_next_command=false)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:1834
 
#17 0x0000555555bb25e1 in do_command (thd=0x7fff5c000a98)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_parse.cc:1382
 
#18 0x0000555555d07842 in do_handle_one_connection (connect=0x555558671b28)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_connect.cc:1335
 
#19 0x0000555555d075c2 in handle_one_connection (arg=0x555558671b28)
 
    at /home/bar/maria-git/server.10.2-compatibility/sql/sql_connect.cc:1241
 
#20 0x00007ffff7bbe36d in start_thread () from /lib64/libpthread.so.0
 
#21 0x00007ffff62f9b9f in clone () from /lib64/libc.so.6



 Comments   
Comment by Alexander Barkov [ 2018-01-25 ]

The problem happens because sp_head::execute() marks busy routines as follows:

m_flags|= IS_INVOKED;

This is correct for standalone routines, but is wrong for package routines.

Package routines should set the IS_INVOKED flag to the owner sp_package, something like this:

m_parent->m_flags|= IS_INVOKED;

Comment by Alexander Barkov [ 2018-01-25 ]

The same crash happens with a DROP TRIGGER inside a package routine:

SET sql_mode=ORACLE;
 
CREATE OR REPLACE TABLE t1 (a INT);
 
CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
 
 
 
DELIMITER $$
 
CREATE OR REPLACE PACKAGE pkg2 AS
 
  PROCEDURE p00();
 
END;
 
$$
 
CREATE OR REPLACE PACKAGE BODY pkg2 AS
 
  PROCEDURE p01() AS
 
  BEGIN
 
    SELECT 'This is p01';
 
  END;
 
  PROCEDURE p00() AS
 
  BEGIN
 
    DROP TRIGGER tr1;
 
    CALL p01();
 
  END;
 
END;
 
$$
 
DELIMITER ;
 
CALL pkg2.p00;

Comment by Alexander Barkov [ 2018-01-25 ]

The problem is also repeatable with two parallel sessions:

  • One session is using a package, without any DDL.
  • Another session is creating and dropping a VIEW.

Repeatable with this MTR test:

# # Save the initial number of concurrent sessions
 
--source include/count_sessions.inc
 
 
 
SET sql_mode=ORACLE;
 
DELIMITER $$;
 
CREATE PACKAGE pkg1 AS
 
  PROCEDURE p1;
 
END;
 
$$
 
CREATE PACKAGE BODY pkg1 AS
 
  PROCEDURE p2 AS
 
  BEGIN
 
    SELECT 'This is p2';
 
  END;
 
  PROCEDURE p1 AS
 
  BEGIN
 
    SELECT 'This is p1';
 
    DO SLEEP(3);
 
    CALL p2();
 
  END;
 
END;
 
$$
 
DELIMITER ;$$
 
send CALL pkg1.p1;
 
 
 
--connect (con2,localhost,root)
 
--connection con2
 
SELECT 'This is con2';
 
CREATE VIEW v1 AS SELECT 1 AS c;
 
DROP VIEW v1;
 
 
 
connection default;
 
disconnect con2;
 
reap;
 
 
 
DROP PACKAGE pkg1;
 
 
 
--source include/wait_until_count_sessions.inc

This stack trace is reported:

/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z11open_tablesP3THDRK14DDL_options_stPP10TABLE_LISTPjjP19Prelocking_strategy+0x53c)[0x556783cdf598]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z20open_and_lock_tablesP3THDRK14DDL_options_stP10TABLE_LISTbjP19Prelocking_strategy+0xb1)[0x556783ce0bab]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbj+0x58)[0x556783ca3901]
 
mysys/stacktrace.c:267(my_print_stacktrace)[0x556783d5a337]
 
sql/mdl.h:320(MDL_key::mdl_namespace() const)[0x556783d645e8]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x20)[0x556783c9acd2]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x1d1)[0x556783c9a2cf]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x1b3)[0x556783c9a983]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_ZN7sp_head7executeEP3THDb+0x757)[0x556783c94a09]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0xb44)[0x556783c9723a]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(+0x662a54)[0x556783d59a54]
 
sql/sql_base.h:496(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x556783d5a57a]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z21mysql_execute_commandP3THD+0xa021)[0x556783d645e8]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2a7)[0x556783d6939d]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0xf9f)[0x556783d56c62]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(_Z10do_commandP3THD+0x703)[0x556783d555e1]
 
sql/sql_connect.cc:1335(do_handle_one_connection(CONNECT*))[0x556783eaa842]
 
/home/bar/maria-git/server.10.2-compatibility/sql/mysqld(handle_one_connection+0x30)[0x556783eaa5c2]
 
perfschema/pfs.cc:1864(pfs_spawn_thread)[0x556784776ed3]
 
/lib64/libpthread.so.0(+0x736d)[0x7f97777e336d]
 
/lib64/libc.so.6(clone+0x3f)[0x7f9775f1eb9f]

Comment by Alexander Barkov [ 2018-01-26 ]

Similar scripts using GET_LOCK and RELEASE_LOCK instead of SLEEP (as the latter is not reliable enough on heavy loaded machines) also crash the server:

Using VIEW:

# Save the initial number of concurrent sessions
 
--source include/count_sessions.inc
 
 
 
SET sql_mode=ORACLE;
 
DELIMITER $$;
 
CREATE PACKAGE pkg1 AS
 
  PROCEDURE p1;
 
END;
 
$$
 
CREATE PACKAGE BODY pkg1 AS
 
  PROCEDURE p2 AS
 
  BEGIN
 
    SELECT 'This is p2';
 
  END;
 
  PROCEDURE p1 AS
 
  BEGIN
 
    SELECT 'This is p1';
 
    DO GET_LOCK('mdev15070',120);
 
    CALL p2();
 
    DO RELEASE_LOCK('mdev15070');
 
  END;
 
END;
 
$$
 
DELIMITER ;$$
 
 
 
connect (con2,localhost,root);
 
connection con2;
 
DO GET_LOCK('mdev15070', 120);
 
 
 
connection default;
 
send CALL pkg1.p1;
 
 
 
connection con2;
 
let $wait_condition=
 
  SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
 
  WHERE state = "User lock" AND info LIKE "%GET_LOCK%mdev15070%";
 
--source include/wait_condition.inc
 
CREATE VIEW v1 AS SELECT 1 AS c;
 
DROP VIEW v1;
 
DO RELEASE_LOCK('mdev15070');
 
disconnect con2;
 
 
 
connection default;
 
reap;
 
 
 
DROP PACKAGE pkg1;
 
 
 
--source include/wait_until_count_sessions.inc

Using PACKAGE:

# Save the initial number of concurrent sessions
 
--source include/count_sessions.inc
 
 
 
SET sql_mode=ORACLE;
 
DELIMITER $$;
 
CREATE PACKAGE pkg1 AS
 
  PROCEDURE p1;
 
END;
 
$$
 
CREATE PACKAGE BODY pkg1 AS
 
  PROCEDURE p2 AS
 
  BEGIN
 
    SELECT 'This is p2' AS msg;
 
  END;
 
  PROCEDURE p1 AS
 
  BEGIN
 
    SELECT 'This is p1' AS msg;
 
    DO GET_LOCK('mdev15070',120);
 
    CALL p2();
 
    DO RELEASE_LOCK('mdev15070');
 
  END;
 
END;
 
$$
 
DELIMITER ;$$
 
 
 
connect (con2,localhost,root);
 
connection con2;
 
DO GET_LOCK('mdev15070', 120);
 
 
 
connection default;
 
send CALL pkg1.p1;
 
 
 
connection con2;
 
let $wait_condition=
 
  SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
 
  WHERE state = "User lock" AND info LIKE "%GET_LOCK%mdev15070%";
 
--source include/wait_condition.inc
 
 
 
 
 
SET sql_mode=ORACLE;
 
DELIMITER $$;
 
CREATE OR REPLACE PACKAGE pkg1 AS
 
  PROCEDURE p1;
 
END;
 
$$
 
DELIMITER ;$$
 
DROP PACKAGE pkg1;
 
 
 
DO RELEASE_LOCK('mdev15070');
 
 
 
disconnect con2;
 
 
 
connection default;
 
reap;
 
 
 
DROP PACKAGE IF EXISTS pkg1;
 
 
 
--source include/wait_until_count_sessions.inc

Comment by Alexander Barkov [ 2018-01-26 ]

Pushed to bb-10.2-compatibility.
The patch was incorporated into "MDEV-11952 Oracle-style package".
Incremental patch is attached to this report.

Generated at Thu Feb 08 08:18:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.