-- 5.7.18

: bin/mysql -uroot  -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> DROP DATABASE IF EXISTS CRASH_TEST;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [(none)]> CREATE DATABASE CRASH_TEST;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> USE CRASH_TEST;
Database changed
root@localhost [CRASH_TEST]>
root@localhost [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;

Query OK, 0 rows affected (0.01 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> 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;
Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> 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;
Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]> CREATE USER 'test_user'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> 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'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> GRANT GRANT OPTION ON `CRASH_TEST`.* TO 'test_user'@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> delimiter //
root@localhost [CRASH_TEST]> 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//
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]>
root@localhost [CRASH_TEST]> 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//
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [CRASH_TEST]> delimiter ;
root@localhost [CRASH_TEST]> \q
Bye

: bin/mysql -utest_user -hx.x.x.x -p --protocol=tcp  -S /tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

test_user@x.x.x.x [(none)]> use CRASH_TEST
Database changed
test_user@x.x.x.x [CRASH_TEST]> call just_for_test_pro();

Query OK, 0 rows affected (6.82 sec)

test_user@x.x.x.x [CRASH_TEST]>
test_user@x.x.x.x [CRASH_TEST]> call just_for_test_pro();
Query OK, 0 rows affected (6.82 sec)

test_user@x.x.x.x [CRASH_TEST]>