[MDEV-15070] Crash when doing a CREATE VIEW inside a package routine - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: N/A
Component/s: Stored routines
Labels:
None

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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MDEV-15070.diff
12 kB
2018-01-26 12:37

Issue Links

is duplicated by

MDEV-14949 Crash signal 11 in prepared statement (stored procedures)

Closed

MDEV-14950 crash 11 in check, if sql command is allowed to write into logfiles

Closed

MDEV-15080 ASAN heap-use-after-free in Query_tables_list::set_query_tables_list / Sp_handler::sp_cache_package_routine or crash in MDL_key::mdl_namespace

Closed

relates to

MDEV-15028 Crash in open_tables() while calling stored procedure from the package

Closed

MDEV-15040 Crash in st_select_lex::cleanup() in stored procedure from the package

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-01-25 14:47

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;

Alexander Barkov added a comment - 2018-01-25 14:47 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;

Alexander Barkov added a comment - 2018-01-25 15:03

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;

Alexander Barkov added a comment - 2018-01-25 15:03 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;

Alexander Barkov added a comment - 2018-01-25 16:21 - edited

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]

Alexander Barkov added a comment - 2018-01-25 16:21 - edited 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]

Alexander Barkov added a comment - 2018-01-26 04:04 - edited

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

Alexander Barkov added a comment - 2018-01-26 04:04 - edited 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

Alexander Barkov added a comment - 2018-01-26 10:25 - edited

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

Alexander Barkov added a comment - 2018-01-26 10:25 - edited Pushed to bb-10.2-compatibility. The patch was incorporated into " MDEV-11952 Oracle-style package". Incremental patch is attached to this report.

MariaDB Server

Crash when doing a CREATE VIEW inside a package routine

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration