Bug #86810 mysql crashes when CALL Procedures
Submitted: 23 Jun 2017 9:07 Modified: 28 Jun 2017 6:18
Reporter: sansom wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.7.14 OS:Red Hat (Red Hat Enterprise Linux Server release 6.8 (Santiago))
Assigned to: CPU Architecture:Any
Tags: crashes

[23 Jun 2017 9:07] sansom wang
Description:
When a USER has only full permissions for the a database and is the definer of the PROCEDURE,CALL PROCEDURE the database will be crash. but GRANT SELECT on server privileges to the USER will be is ok. 

While testing 5.7.14, I got an assert failure as follows:

06:51:44 UTC - 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=33554432
read_buffer_size=1048576
max_used_connections=2
max_threads=2000
thread_count=3
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 10299455 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f155c000b30
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 = 7f1575bf9e28 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xf271d5]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x4a4)[0x7b9014]
/lib64/libpthread.so.0[0x3b1120f7e0]
/lib64/libc.so.6[0x3b10f35d92]
/usr/local/mysql/bin/mysqld(_Z16name_hash_searchP7st_hashPKcS2_S2_S2_S2_bb+0x58)[0x7c5178]
/usr/local/mysql/bin/mysqld(_Z18check_grant_columnP3THDP10GRANT_INFOPKcS4_S4_mP16Security_contextm+0x126)[0xe06e16]
/usr/local/mysql/bin/mysqld(_Z31check_column_grant_in_table_refP3THDP10TABLE_LISTPKcmm+0x93)[0xe09b03]
/usr/local/mysql/bin/mysqld(_ZN20Item_direct_view_ref23check_column_privilegesEPh+0x7b)[0x8191db]
/usr/local/mysql/bin/mysqld(_ZN8Item_ref4walkEM4ItemFbPhENS0_9enum_walkES1_+0xf3)[0x8261b3]
/usr/local/mysql/bin/mysqld(_ZN8Item_ref4walkEM4ItemFbPhENS0_9enum_walkES1_+0x8f)[0x82614f]
/usr/local/mysql/bin/mysqld(_Z23find_field_in_table_refP3THDP10TABLE_LISTPKcmS4_S4_S4_PP4ItemmbPjbPS2_+0x743)[0xca4233]
/usr/local/mysql/bin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typemb+0x12b)[0xca457b]
/usr/local/mysql/bin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x1ee)[0x82376e]
/usr/local/mysql/bin/mysqld(_ZN9Item_func12fix_func_argEP3THDPP4Item+0x149)[0x86ad59]
/usr/local/mysql/bin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0xc7)[0x86d4b7]
/usr/local/mysql/bin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0x10b)[0x83012b]
/usr/local/mysql/bin/mysqld(_ZN13st_select_lex11setup_condsEP3THD+0x8b)[0xd2d59b]
/usr/local/mysql/bin/mysqld(_ZN13st_select_lex7prepareEP3THD+0x426)[0xd33766]
/usr/local/mysql/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x1ca)[0xd398ba]
/usr/local/mysql/bin/mysqld(_Z18mysql_multi_updateP3THDP4ListI4ItemES4_15enum_duplicatesP13st_select_lexPP19Query_result_update+0x8c)[0xd82cac]
/usr/local/mysql/bin/mysqld(_ZN14Sql_cmd_update26execute_multi_table_updateEP3THD+0x15e)[0xd82eae]
/usr/local/mysql/bin/mysqld(_ZN14Sql_cmd_update7executeEP3THD+0x84)[0xd82ff4]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0xda1)[0xcfc0d1]
/usr/local/mysql/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x5d)[0xc8074d]
/usr/local/mysql/bin/mysqld(_ZN12sp_lex_instr23reset_lex_and_exec_coreEP3THDPjb+0x3bc)[0xc80c2c]
/usr/local/mysql/bin/mysqld(_ZN12sp_lex_instr29validate_lex_and_execute_coreEP3THDPjb+0xb4)[0xc82ee4]
/usr/local/mysql/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x128)[0xc83268]
/usr/local/mysql/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x805)[0xc7bb25]
/usr/local/mysql/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x80b)[0xc7c6eb]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x1d39)[0xcfd069]
/usr/local/mysql/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x5d)[0xc8074d]
/usr/local/mysql/bin/mysqld(_ZN12sp_lex_instr23reset_lex_and_exec_coreEP3THDPjb+0x3bc)[0xc80c2c]
/usr/local/mysql/bin/mysqld(_ZN12sp_lex_instr29validate_lex_and_execute_coreEP3THDPjb+0xb4)[0xc82ee4]
/usr/local/mysql/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x128)[0xc83268]
/usr/local/mysql/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x805)[0xc7bb25]
/usr/local/mysql/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x80b)[0xc7c6eb]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x1d39)[0xcfd069]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3a5)[0xd00695]
/usr/local/mysql/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x115d)[0xd0185d]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0x194)[0xd02344]
/usr/local/mysql/bin/mysqld(handle_connection+0x29c)[0xdcf5dc]
/usr/local/mysql/bin/mysqld(pfs_spawn_thread+0x171)[0xfacb21]
/lib64/libpthread.so.0[0x3b11207aa1]
/lib64/libc.so.6(clone+0x6d)[0x3b10ee8bcd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f155c9dfd20): is an invalid pointer
Connection ID (thread ID): 4
Status: NOT_KILLED

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.
2017-06-23T06:51:44.753638Z mysqld_safe Number of processes running now: 0
2017-06-23T06:51:44.756686Z mysqld_safe mysqld restarted
2017-06-23T14:51:45.013684+08:00 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' inst
ead.
2017-06-23T14:51:45.013907+08:00 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be me
rged with strict mode in a future release.
2017-06-23T14:51:45.013915+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2017-06-23T14:51:45.013957+08:00 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting 
it to a valid, non-empty path.

How to repeat:
The table used to test:

CRATE DATABASE CRASH_TEST;
USE CRASH_TEST;

CREATE TABLE `crash_test01` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT ,
  `id_card_no` varchar(50) NOT NULL ,
  `card_name` varchar(50) DEFAULT NULL ,
  `succ_flag` tinyint(1) NOT NULL ,
  `order_money` decimal(9,2) NOT NULL DEFAULT '0.00',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_id_card_no` (`id_card_no`,`card_name`,`member_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `crash_test02` (
  `member_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `industry` varchar(255) DEFAULT NULL,
  `sub_industry` varchar(255) DEFAULT NULL,
  `industry1` varchar(255) DEFAULT NULL,
  `sub_industry1` varchar(255) DEFAULT NULL,
  `industry2` varchar(255) DEFAULT NULL,
  `sub_industry2` varchar(255) DEFAULT NULL,
  KEY `index_1` (`member_id`) USING BTREE,
  KEY `idx_mm` (`member_id`,`sub_industry`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `crash_test03` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT ,
  `id_card_no` varchar(50) DEFAULT NULL ,
  `card_name` varchar(50) DEFAULT NULL ,
  `A21160001` int(11) DEFAULT NULL ,
  `A21160002` decimal(11,2) DEFAULT NULL ,
  `A21160003` int(11) DEFAULT NULL ,
  `A21160004` int(11) DEFAULT NULL ,
  `A21160005` int(11) DEFAULT NULL ,
  `A21160006` int(11) DEFAULT NULL ,
  `A21160007` varchar(20) DEFAULT NULL ,
  `A21160008` int(11) DEFAULT NULL ,
  `A21160009` int(11) DEFAULT NULL ,
  `A21160010` int(11) DEFAULT NULL ,
  PRIMARY KEY (`id`),
  KEY `IDX_id_card_no_name` (`id_card_no`,`card_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE USER 'test_user'@'%' IDENTIFIED BY '123456';

GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `CRASH_TEST`.* TO 'test_user'@'%';

GRANT GRANT OPTION ON `CRASH_TEST`.* TO 'test_user'@'%';

DROP PROCEDURE IF EXISTS `pro_test`;

CREATE DEFINER = `test_user`@`%` PROCEDURE `pro_test`(in_id_card_no varchar(50),
in_id_card_name varchar(50),
in_apply_time varchar(50))
BEGIN

UPDATE CRASH_TEST.crash_test03 t,
(SELECT
t.id_card_no,
t.card_name,
case when score<0 then 5 when score>950 then 950 else score end score
FROM
(
SELECT
id_card_no,
card_name,
round(650-30/LOG(2)*
(-0.58919584
+
case when g10<=0.5 then -0.622711019286885 when g10<=1.5 then 0.62812059215021 when g10<=2.5 then 1.18306520261328 else 1.83657432460102 end *-1.09267346
+
case when g13<=0.5 then -0.622711019286885 when g13<=3.5 then 0.639527848902575 else 1.25709819104618 end *0.30010549
+
case when g16<=0.5 then -0.400750643438527 when g16<=6.5 then 0.582133721666636 else 1.28966846986745 end *0.21684102
+
case when g213<=0.5 then -0.429009806252028 when g213<=1.5 then 0.77852855705375 else 1.40417084240806 end *-0.3479718
+
case when g245<=0.5 then -0.280517744439985 when g245<=1.5 then 0.726426847612987 else 1.50242197932031 end *0.24938664
+
case when g246<=0.5 then -0.520545310324589 when g246<=1.5 then 0.577674847536868 when g246<=2.5 then 1.01361724357145 when g246<=4.5 then 1.40878163145562 else 2.13833437181833 end *0.01489584
+
case when g25<=0.5 then -0.400750643438527 when g25<=1.5 then 0.528757860118587 else 1.10234791469834 end *-0.51331345
+
case when g278<=0.5 then -0.261252139019479 when g278<=1.5 then 1.78561266861866 else 3.00040354952584 end *-0.03710785
+
case when g279<=0.5 then -0.330716025165795 when g279<=1.5 then 1.63842051497078 else 2.79728786507063 end *0.39787673
+
case when g28<=0.5 then -0.472266307522609 when g28<=1.5 then 1.37952697222201 else 2.55568062182457 end *0.26513442
+
case when g318<=0.5 then -0.538237289155048 when g318<=3.5 then 1.03348201430701 else 2.04233601108739 end *0.20698833
+
case when g319<=0.5 then -0.638252422041796 when g319<=5.5 then 1.00530915879882 else 1.60287602786078 end *0.31264746
+
case when g326<=0.5 then -0.274716005187153 when g326<=1.5 then 0.561653788827746 when g326<=2.5 then 1.12073461848598 else 1.85866683422398 end *0.78628583
+
case when g34<=0.5 then -0.622711019286885 when g34<=1.5 then 1.07298820986409 when g34<=3.5 then 0.643397046981457 else 1.1741362211109 end *-0.01728997
+
case when g343<=0.5 then -0.370852486181804 else 1.0449897885383 end *0.01032761
+
case when g350<=0.5 then -0.280326676880756 when g350<=2.5 then 1.06114720049621 else 2.14295331767462 end *0.36763138
+
case when g351<=0.5 then -0.343873479994774 when g351<=4.5 then 1.02978601287096 else 1.75761973997374 end *0.17554152
+
case when g37<=0.5 then -0.37329279351161 else 0.911614897464296 end *-0.59270866
+
case when g4<=0.5 then -0.894589079924976 when g4<=1.5 then 0.572996083998539 when g4<=2.5 then 1.10367590100804 else 1.69135843783896 end *1.04985061
+
case when g52<=45.5 then -0.894589079924976 when g52<=182.5 then 0.87326631669763 else -0.365214628778947 end *0.35627035
+
case when g58<=45.5 then -0.622711019286885 when g58<=109.5 then 1.31472015949241 else 0.80452586443985 end *0.13529024
+
case when g7<=0.5 then -0.894589079924976 when g7<=4.5 then 0.651779325734261 else 1.14143987400403 end *-0.51626139
))
as score
FROM(
SELECT
t1.id_card_no,
t1.card_name,
IFNULL(count(DISTINCT(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL END)),0)  g4,
IFNULL(count(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then 1 else NULL END),0)  g7,
IFNULL(count(DISTINCT(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t.succ_flag=1 then t.member_id else NULL END)),0)  g10,
IFNULL(count(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t.succ_flag=1 then 1 else NULL END),0)  g13,
IFNULL(count(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t2.sub_industry='sub_industry01' then 1 else NULL end),0)  g16,
IFNULL(count(DISTINCT case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t2.sub_industry='sub_industry02' then t.member_id else NULL end),0)  g25,
IFNULL(count(DISTINCT case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t2.sub_industry='sub_industry03' then t.member_id else NULL end),0)  g28,
IFNULL(sum(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t.succ_flag=1 THEN t.order_money else 0 end),0) g34,
IFNULL(sum(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t.succ_flag=-1 THEN t.order_money else 0 end),0) g37,
IFNULL(DATEDIFF(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),max(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then create_time else NULL end)),0) g52,
IFNULL(DATEDIFF(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),max(case when create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) and t.succ_flag=1 then create_time else NULL end)),0) g58,
IFNULL(count(DISTINCT(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 15 DAY) and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end)),0) g213,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 15 DAY) AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g245,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 1 MONTH) AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g246,
IFNULL(count(DISTINCT(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 1 MONTH) AND t2.sub_industry='sub_industry04' AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end)),0) g278,
IFNULL(count(DISTINCT(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 3 MONTH) AND t2.sub_industry='sub_industry04' AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end)),0) g279,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 1 MONTH) AND t.member_id not in(84110050,91591915,91070814,61839151,19783424) and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g318,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 3 MONTH) AND t.member_id not in(84110050,91591915,91070814,61839151,19783424) and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g319,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 1 MONTH) AND t.member_id in(84110050,91591915,91070814,61839151,19783424) AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g326,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 3 MONTH) AND t.member_id not in(84110050,91591915,91070814,61839151,19783424) AND t.succ_flag=1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g343,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 1 MONTH) AND t.member_id not in(84110050,91591915,91070814,61839151,19783424) AND t.succ_flag=-1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g350,
IFNULL(count(case when create_time>=subdate(ADDDATE(date(in_apply_time),INTERVAL 1 DAY),INTERVAL 3 MONTH) AND t.member_id not in(84110050,91591915,91070814,61839151,19783424) AND t.succ_flag=-1 and create_time<ADDDATE(date(in_apply_time),INTERVAL 1 DAY) then t.member_id else NULL end),0) g351
FROM (SELECT in_id_card_no as id_card_no,in_id_card_name as card_name)t1
LEFT JOIN CRASH_TEST.crash_test01 t on t1.id_card_no=t.id_card_no and t1.card_name=t.card_name
LEFT JOIN CRASH_TEST.crash_test02 t2 ON t.member_id=t2.member_id
GROUP BY t1.id_card_no,t1.card_name
)t
)t
)tp
SET t.A21160001=tp.score
where tp.id_card_no=t.id_card_no
and tp.card_name=t.card_name
;

END
;

DROP PROCEDURE IF EXISTS just_for_test_pro;
CREATE DEFINER = `test_user`@`%` PROCEDURE just_for_test_pro()
BEGIN
DECLARE i int(11) DEFAULT 0;
WHILE i < 10000 DO
SET i=i+1;

call pro_test('1111111111','test','2017/06/23');
END WHILE;
END;

This is important: 
the test_user  has only full permissions for the CRASH_TEST database and is the definer of the PROCEDURE.

call just_for_test_pro();

The database will be crash, but GRANT SELECT on server privileges to the USER will be is ok. 

GRANT SELECT ON *.* TO `test_user`@`%`;

call just_for_test_pro();

Suggested fix:
I don't know
[23 Jun 2017 9:44] MySQL Verification Team
Hello Sansom wang,

Thank for the report and test case.
I'm not seeing any assert on 5.7.18 release/debug builds.
Could you please confirm if you are able to reproduce this with latest GA 5.7.18 build? If you are seeing this on 5.7.18 then please provide exact conf file used in the environment to reproduce the issue at our end.

Thanks,
Umesh
[23 Jun 2017 9:44] MySQL Verification Team
test results

Attachment: 86810_5.7.18.results (application/octet-stream, text), 14.00 KiB.

[24 Jun 2017 2:32] sansom wang
Hi,Umesh Shastry

Thank you for your reply, In my test case, the version is 5.7.14 not 5.7.18

mysql> select @@version
    -> ;
+------------+
| @@version  |
+------------+
| 5.7.14-log |
+------------+
1 row in set (0.00 sec)
[25 Jun 2017 22:13] MySQL Verification Team
Thank you for the feedback. Bugs report are tested against the last released version and current source server, the reason is there is no back fix for older version so if you are able to test against the version 5.7.18 and if you repeat it there is a chance you can provide more details for. Thanks.
[28 Jun 2017 5:51] sansom wang
Hello,Miguel Solorzano,

I have confirmed that on 5.7.18 version not seeing any assert.
Thank you.
[28 Jun 2017 6:18] MySQL Verification Team
Thank you for confirming, Sansom wang.
Closing the report for now.

Thanks,
Umesh