Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.13, 10.3.5
-
Windows x64 and CENTOS 7.4
-
10.2.14
Description
https://mariadb.com/kb/en/library/mariadb-10213-call-procedure-param-last_insert_id/
Before the update MariaDB 10.2.13 I was able to call a procedure inside the other passing as argument LAST_INSERT_ID (), now of the error in MariaDB, after the update I have to assign the LAST_INSERT_ID () in a variable before calling the procedure
Before MariaDB 10.2.13
in procedure1
CALL procedure2 (LAST_INSERT_ID ());
|
After MariaDB 10.2.13
in procedure1
SET id = LAST_INSERT_ID (); |
CALL procedure2 (id);
|
Run CALL procedure1 ();
SET FOREIGN_KEY_CHECKS=0; |
-- ----------------------------
|
-- Table structure for `table`
|
-- ----------------------------
|
DROP TABLE IF EXISTS `table`; |
CREATE TABLE `table` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`name` varchar(20) DEFAULT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; |
-- ----------------------------
|
-- Procedure definition for `procedure1`
|
-- ----------------------------
|
DROP PROCEDURE IF EXISTS `procedure1`; |
DELIMITER ;;
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure1`() |
MAIN:BEGIN |
DECLARE id INT(11) DEFAULT NULL; |
|
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION |
BEGIN |
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
|
SELECT @text AS message; |
ROLLBACK; |
END; |
|
|
START TRANSACTION; |
|
|
INSERT INTO `table` (name) VALUES ('Elvis'); |
|
|
SET id = LAST_INSERT_ID(); |
|
|
CALL procedure2(LAST_INSERT_ID());
|
|
SELECT 'Add' AS message; |
|
|
COMMIT; |
END
|
;;
|
DELIMITER ;
|
-- ----------------------------
|
-- Procedure definition for `procedure2`
|
-- ----------------------------
|
DROP PROCEDURE IF EXISTS `procedure2`; |
DELIMITER ;;
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure2`(IN id int(11)) |
BEGIN
|
#Routine body goes here...
|
|
|
END
|
;;
|
DELIMITER ;
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Crashes for me as well on Ubuntu 14.04:
MariaDB [test]> CREATE PROCEDURE foo ( IN i INT UNSIGNED ) BEGIN END;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> CALL foo( LAST_INSERT_ID() );
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
Crash report:
180220 21:49:42 [ERROR] mysqld got signal 11 ;
|
This could be because you hit a bug. It is also possible that this binary
|
or one of the libraries it was linked against is corrupt, improperly built,
|
or misconfigured. This error can also be caused by malfunctioning hardware.
|
|
|
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
|
|
|
We will try our best to scrape up some info that will hopefully help
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
|
Server version: 10.2.13-MariaDB-10.2.13+maria~trusty-log
|
key_buffer_size=134217728
|
read_buffer_size=2097152
|
max_used_connections=1
|
max_threads=1002
|
thread_count=18
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6308732 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x7fcd60000a88
|
Attempting backtrace. You can use the following information to find out
|
where mysqld died. If you see no messages after this, something went
|
terribly wrong...
|
stack_bottom = 0x7fcd1cb65e40 thread_stack 0x49000
|
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7fcd916a027e]
|
/usr/sbin/mysqld(handle_fatal_signal+0x355)[0x7fcd91125295]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7fcd8f3df330]
|
/usr/sbin/mysqld(_ZN24Item_func_last_insert_id10fix_fieldsEP3THDPP4Item+0x37)[0x7fcd9118d2a7]
|
/usr/sbin/mysqld(_Z20sp_prepare_func_itemP3THDPP4Item+0x3f)[0x7fcd912557ef]
|
/usr/sbin/mysqld(_Z12sp_eval_exprP3THDP5FieldPP4Item+0x3c)[0x7fcd912558bc]
|
/usr/sbin/mysqld(_ZN11sp_rcontext12set_variableEP3THDP5FieldPP4Item+0x17)[0x7fcd9125e357]
|
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0xe3)[0x7fcd91257e73]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6b88)[0x7fcd90f972e8]
|
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x351)[0x7fcd90f99fa1]
|
/usr/sbin/mysqld(+0x4e688f)[0x7fcd90f9a88f]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x22e0)[0x7fcd90f9d3b0]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x149)[0x7fcd90f9e6f9]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x7fcd9106535a]
|
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x7fcd9106547d]
|
/usr/sbin/mysqld(+0x828d7d)[0x7fcd912dcd7d]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7fcd8f3d7184]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fcd8eafabed]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7fcd6000f280): CALL foo( LAST_INSERT_ID() )
|
|
|
Connection ID (thread ID): 49
|
Status: NOT_KILLED
|
|
|
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
|
|
|
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
|
information that should help you find out what is causing the crash.
|
Thanks to all, reproducible as described.
Here is the shortest test case from the comment above and MDEV-15449, courtesy of Rich:
CREATE PROCEDURE foo ( IN i INT UNSIGNED ) BEGIN END; |
CALL foo( LAST_INSERT_ID() );
|
Here is the exact same test case as suggested in the description, only in MTR-friendly form, please make sure it also stops failing after the bugfix:
|
Test case from the description |
DROP TABLE IF EXISTS `table`; |
CREATE TABLE `table` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`name` varchar(20) DEFAULT NULL, |
PRIMARY KEY (`id`) |
);
|
|
|
DROP PROCEDURE IF EXISTS `procedure1`; |
--delimiter ;;
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure1`() |
MAIN:BEGIN |
DECLARE id INT(11) DEFAULT NULL; |
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION |
BEGIN |
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
|
SELECT @text AS message; |
ROLLBACK; |
END; |
|
START TRANSACTION; |
|
INSERT INTO `table` (name) VALUES ('Elvis'); |
|
SET id = LAST_INSERT_ID(); |
|
CALL procedure2(LAST_INSERT_ID());
|
|
SELECT 'Add' AS message; |
|
COMMIT; |
END
|
;;
|
--delimiter ;
|
|
|
DROP PROCEDURE IF EXISTS `procedure2`; |
--delimiter ;;
|
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure2`(IN id int(11)) |
BEGIN
|
#Routine body goes here...
|
|
END
|
;;
|
--delimiter ;
|
|
|
CALL procedure2(LAST_INSERT_ID());
|
|
10.2 d0cc7a52255fd |
#3 <signal handler called>
|
#4 0x000055cbd7313e52 in st_select_lex::master_unit (this=0x0) at /data/src/10.2/sql/sql_lex.h:962
|
#5 0x000055cbd759da7b in LEX::uncacheable (this=0x7f897c0045e0, cause=4 '\004') at /data/src/10.2/sql/sql_lex.h:2912
|
#6 0x000055cbd766bbe1 in Item_func_last_insert_id::fix_fields (this=0x7f897c0127e8, thd=0x7f897c000b00, ref=0x7f897c0128b0) at /data/src/10.2/sql/item_func.cc:4401
|
#7 0x000055cbd775ec99 in sp_prepare_func_item (thd=0x7f897c000b00, it_addr=0x7f897c0128b0) at /data/src/10.2/sql/sp_head.cc:376
|
#8 0x000055cbd775edcf in sp_eval_expr (thd=0x7f897c000b00, result_field=0x7f897c015048, expr_item_ptr=0x7f897c0128b0) at /data/src/10.2/sql/sp_head.cc:413
|
#9 0x000055cbd776cfbc in sp_rcontext::set_variable (this=0x7f897c013ee0, thd=0x7f897c000b00, field=0x7f897c015048, value=0x7f897c0128b0) at /data/src/10.2/sql/sp_rcontext.cc:383
|
#10 0x000055cbd733b93b in sp_rcontext::set_variable (this=0x7f897c013ee0, thd=0x7f897c000b00, var_idx=0, value=0x7f897c0128b0) at /data/src/10.2/sql/sp_rcontext.h:201
|
#11 0x000055cbd7762f8a in sp_head::execute_procedure (this=0x7f897c056308, thd=0x7f897c000b00, args=0x7f897c0053d0) at /data/src/10.2/sql/sp_head.cc:2047
|
#12 0x000055cbd7372235 in do_execute_sp (thd=0x7f897c000b00, sp=0x7f897c056308) at /data/src/10.2/sql/sql_parse.cc:2894
|
#13 0x000055cbd737b54a in mysql_execute_command (thd=0x7f897c000b00) at /data/src/10.2/sql/sql_parse.cc:5808
|
#14 0x000055cbd73815d1 in mysql_parse (thd=0x7f897c000b00, rawbuf=0x7f897c0124f8 "CALL procedure2(LAST_INSERT_ID())", length=33, parser_state=0x7f898e854200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7902
|
#15 0x000055cbd736f4f1 in dispatch_command (command=COM_QUERY, thd=0x7f897c000b00, packet=0x7f897c16b561 "CALL procedure2(LAST_INSERT_ID())", packet_length=33, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1806
|
#16 0x000055cbd736de54 in do_command (thd=0x7f897c000b00) at /data/src/10.2/sql/sql_parse.cc:1360
|
#17 0x000055cbd74bc646 in do_handle_one_connection (connect=0x55cbd95fb3e0) at /data/src/10.2/sql/sql_connect.cc:1335
|
#18 0x000055cbd74bc3d3 in handle_one_connection (arg=0x55cbd95fb3e0) at /data/src/10.2/sql/sql_connect.cc:1241
|
#19 0x000055cbd78dc1f8 in pfs_spawn_thread (arg=0x55cbd95d5ea0) at /data/src/10.2/storage/perfschema/pfs.cc:1862
|
#20 0x00007f899624f494 in start_thread (arg=0x7f898e855700) at pthread_create.c:333
|
#21 0x00007f899463593f in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
|
10.3 ASAN d70573564cb270 |
==17630==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000010 (pc 0x55d909181ca5 sp 0x7f9fbd3b1130 bp 0x7f9fbd3b1140 T5)
|
#0 0x55d909181ca4 in st_select_lex::master_unit() /data/src/10.3/sql/sql_lex.h:1062
|
#1 0x55d90982dc87 in LEX::uncacheable(unsigned char) /data/src/10.3/sql/sql_lex.h:3088
|
#2 0x55d909ad30e3 in Item_func_last_insert_id::fix_fields(THD*, Item**) /data/src/10.3/sql/item_func.cc:4233
|
#3 0x55d9090a9889 in THD::sp_fix_func_item(Item**) /data/src/10.3/sql/sp_head.cc:376
|
#4 0x55d9090a95d2 in THD::sp_prepare_func_item(Item**, unsigned int) /data/src/10.3/sql/sp_head.cc:362
|
#5 0x55d9098e8295 in Field::sp_prepare_and_store_item(THD*, Item**) /data/src/10.3/sql/field.cc:1277
|
#6 0x55d9090a9d41 in THD::sp_eval_expr(Field*, Item**) /data/src/10.3/sql/sp_head.cc:411
|
#7 0x55d9090df8d6 in sp_rcontext::set_variable(THD*, unsigned int, Item**) /data/src/10.3/sql/sp_rcontext.cc:634
|
#8 0x55d9090d3705 in sp_rcontext::set_parameter(THD*, unsigned int, Item**) (/data/bld/10.3-asan/bin/mysqld+0xc7b705)
|
#9 0x55d9090b529d in sp_head::execute_procedure(THD*, List<Item>*) /data/src/10.3/sql/sp_head.cc:2199
|
#10 0x55d90928b027 in do_execute_sp /data/src/10.3/sql/sql_parse.cc:2936
|
#11 0x55d90928ca7d in Sql_cmd_call::execute(THD*) /data/src/10.3/sql/sql_parse.cc:3178
|
#12 0x55d9092a04a5 in mysql_execute_command(THD*) /data/src/10.3/sql/sql_parse.cc:6280
|
#13 0x55d9092aab6b in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.3/sql/sql_parse.cc:8009
|
#14 0x55d909285244 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.3/sql/sql_parse.cc:1838
|
#15 0x55d9092822db in do_command(THD*) /data/src/10.3/sql/sql_parse.cc:1383
|
#16 0x55d9095edbb2 in do_handle_one_connection(CONNECT*) /data/src/10.3/sql/sql_connect.cc:1402
|
#17 0x55d9095ed5c7 in handle_one_connection /data/src/10.3/sql/sql_connect.cc:1308
|
#18 0x55d90a108ce7 in pfs_spawn_thread /data/src/10.3/storage/perfschema/pfs.cc:1862
|
#19 0x7f9fc9988493 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x7493)
|
#20 0x7f9fc7d6e93e in __clone (/lib/x86_64-linux-gnu/libc.so.6+0xe893e)
|
|
|
AddressSanitizer can not provide additional info.
|
SUMMARY: AddressSanitizer: SEGV /data/src/10.3/sql/sql_lex.h:1062 st_select_lex::master_unit()
|
Thread T5 created by T0 here:
|
#0 0x7f9fc9bc1bba in pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.1+0x23bba)
|
#1 0x55d90a1092af in spawn_thread_v1 /data/src/10.3/storage/perfschema/pfs.cc:1912
|
#2 0x55d90900a5ae in inline_mysql_thread_create /data/src/10.3/include/mysql/psi/mysql_thread.h:1268
|
#3 0x55d909020118 in create_thread_to_handle_connection(CONNECT*) /data/src/10.3/sql/mysqld.cc:6531
|
#4 0x55d90902081d in create_new_thread /data/src/10.3/sql/mysqld.cc:6601
|
#5 0x55d90902182e in handle_connections_sockets() /data/src/10.3/sql/mysqld.cc:6876
|
#6 0x55d90901f5d0 in mysqld_main(int, char**) /data/src/10.3/sql/mysqld.cc:6153
|
#7 0x55d90900894f in main /data/src/10.3/sql/main.cc:25
|
#8 0x7f9fc7ca62b0 in __libc_start_main (/lib/x86_64-linux-gnu/libc.so.6+0x202b0)
|
The problem appeared in 10.2 tree with this commit:
commit 6b7dcefdc83c4444ac8a4623b46810ff940528db
|
Author: Monty <monty@mariadb.org>
|
Date: Sun Jan 21 20:16:22 2018 +0200
|
|
|
Reset thd->lex->current_select for SP
|
|
current_select may point to data from old parser states
|
when calling a stored procedure with CALL
|
|
The failure happens in Item::Item when testing if we are
|
in having.
|
|
Fixed by explicitely reseting current_select in do_execute_sp()
|
and in sp_rcontext::create(). The later is also needed for
|
stored functions().
|
Above patch removed current select for expressions during variable assignment, and I do not see any usable SELECT in this situation, so Items should work is there is no current_select.
revision-id: 96b3872bc5b8a80d17809ed691f04108f0358160 (mariadb-10.2.13-30-g96b3872bc5b)
parent(s): 8f98835bb86550a68a6c412d8417cc169dfdc206
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2018-03-05 17:43:30 +0100
message:
MDEV-15328: MariaDB 10.2.13 Crashes upon CALL PROCEDURE PARAM LAST_INSERT_ID ()
There is not current SELECT during assigning SP parameters, do not use it if current_select is empty.
—
This actually causes a server crash. I reproduced using the test case in the description:
Server version: 10.2.13-MariaDB-10.2.13+maria~trusty-logkey_buffer_size=16384read_buffer_size=131072max_used_connections=2max_threads=802thread_count=8It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1762270 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x7fa9700024d8Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0x7fa9ba739e70 thread_stack 0x40000addr2line: 'mysqld': No such filemysqld(my_print_stacktrace+0x2e)[0x5593b77d627e]mysqld(handle_fatal_signal+0x355)[0x5593b725b295]/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7fa9d6d6a330]addr2line: 'mysqld': No such filemysqld(_ZN24Item_func_last_insert_id10fix_fieldsEP3THDPP4Item+0x37)[0x5593b72c32a7]mysqld(_Z20sp_prepare_func_itemP3THDPP4Item+0x3f)[0x5593b738b7ef]mysqld(_Z12sp_eval_exprP3THDP5FieldPP4Item+0x3c)[0x5593b738b8bc]mysqld(_ZN11sp_rcontext12set_variableEP3THDP5FieldPP4Item+0x17)[0x5593b7394357]mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0xe3)[0x5593b738de73]mysqld(_Z21mysql_execute_commandP3THD+0x6b88)[0x5593b70cd2e8]mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x15)[0x5593b7389b25]mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x9d)[0x5593b738fdcd]mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x205)[0x5593b73903a5]mysqld(_ZN7sp_head7executeEP3THDb+0x7b0)[0x5593b738cb50]mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x5ef)[0x5593b738e37f]mysqld(_Z21mysql_execute_commandP3THD+0x6b88)[0x5593b70cd2e8]mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x351)[0x5593b70cffa1]mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x2b9d)[0x5593b70d3c6d]mysqld(_Z10do_commandP3THD+0x149)[0x5593b70d46f9]mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x5593b719b35a]mysqld(handle_one_connection+0x3d)[0x5593b719b47d]nptl/pthread_create.c:312(start_thread)[0x7fa9d6d62184]x86_64/clone.S:113(clone)[0x7fa9d648503d]Trying to get some variables.Some pointers may be invalid and cause the dump to abort.Query (0x7fa9700602b0): is an invalid pointerConnection ID (thread ID): 11Status: NOT_KILLEDOptimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=onThe manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.We think the query pointer is invalid, but we will try to print it anyway.Query: CALL procedure2(LAST_INSERT_ID())Anything else I can provide?