MYSQL is terminating by itself when executing a procedure

Question

MYSQL is terminating by itself when executing a procedure

Navigation

#1 by (0 votes)

0

I have a problem with my mysql on localhost. I use xampp as a service and HeidiSQL to facilitate the programming process. Finally, when executing the procedure below the MYSQL service is terminating alone and the error LOG returns the information below.

I'm posting the full content of the procedure because it's from a private project and has no problem displaying the full text.

PROC:

CREATE DEFINER='root'@'localhost' PROCEDURE 'spr_reset_user_habilities'(
    IN 'P_USER_ID' MEDIUMINT(8),
    IN 'P_ADMIN_ID' MEDIUMINT(8),
    IN 'P_ADMIN_IP' VARCHAR(255)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Rotina para resetar as habilidades do usuário e devolver os pontos.'
BEGIN 
    /*
    /// 
    /// Autor: Juarez Turrini Gomide do Nascimento
    /// Data: 08/09/2018
    /// Descrição: Procedure para resetar os pontos de habilidades e atributos do usuário, devolvendo as pontuações já existentes.
    ///
    ///
    /// Log de Alteracoes:
    ///     DD/MM/YYYY - NOME DA PESSOA
    ///     DESCRITIVO DA ALTERAÇÃO.
    ///     
    ///
    ///
    ///
    /// 
    */

    -- Atualizo os dados do usuário com base nos pontos que ele já possui hoje.
    UPDATE zks_user_atributos AS UPD_UA
    INNER JOIN (
        SELECT
            ua.uatr_userid,
            CASE WHEN ml_qt_atr > SUM(ua.uatr_add_atr + ua.uatr_for + ua.uatr_des + ua.uatr_int + ua.uatr_con + ua.uatr_car + ua.uatr_per) THEN ml_qt_atr ELSE SUM(ua.uatr_add_atr + ua.uatr_for + ua.uatr_des + ua.uatr_int + ua.uatr_con + ua.uatr_car + ua.uatr_per) END AS uatr_add_atr,
            CASE WHEN ml_qt_hab > SUM(IFNULL(h.hab_value, 0) + ua.uatr_add_hab) THEN ml_qt_hab ELSE SUM(IFNULL(h.hab_value, 0) + ua.uatr_add_hab) END AS uatr_add_hab,
            CASE WHEN ml_qt_con > SUM(IFNULL(c.hab_value, 0) + ua.uatr_add_con) THEN ml_qt_con ELSE SUM(IFNULL(c.hab_value, 0) + ua.uatr_add_con) END AS uatr_add_con,
            CASE WHEN ml_qt_qua > SUM(IFNULL(q.hab_value, 0) + ua.uatr_add_qua) THEN ml_qt_qua ELSE SUM(IFNULL(q.hab_value, 0) + ua.uatr_add_qua) END AS uatr_add_qua,
            CASE WHEN ml_qt_fra > SUM(IFNULL(f.hab_value, 0) + ua.uatr_add_fra) THEN ml_qt_fra ELSE SUM(IFNULL(f.hab_value, 0) + ua.uatr_add_fra) END AS uatr_add_fra
        FROM zks_user_atributos ua
        JOIN zks_users u ON ua.uatr_userid = u.user_id
        JOIN vw_magic_levels ml ON ml.ml_level = u.user_level
        LEFT JOIN vw_user_atributes_sum h ON ua.uatr_userid = h.user_id AND h.hab_column_name = 'uatr_add_hab'
        LEFT JOIN vw_user_atributes_sum c ON ua.uatr_userid = c.user_id AND c.hab_column_name = 'uatr_add_con'
        LEFT JOIN vw_user_atributes_sum q ON ua.uatr_userid = q.user_id AND q.hab_column_name = 'uatr_add_qua'
        LEFT JOIN vw_user_atributes_sum f ON ua.uatr_userid = f.user_id AND f.hab_column_name = 'uatr_add_fra'
        GROUP BY
            ua.uatr_userid
    ) AS SEL_UA ON UPD_UA.uatr_userid = SEL_UA.uatr_userid
    SET
        UPD_UA.uatr_add_atr = SEL_UA.uatr_add_atr,
        UPD_UA.uatr_add_hab = SEL_UA.uatr_add_hab,
        UPD_UA.uatr_add_con = SEL_UA.uatr_add_con,
        UPD_UA.uatr_add_qua = SEL_UA.uatr_add_qua,
        UPD_UA.uatr_add_fra = SEL_UA.uatr_add_fra
    WHERE
        UPD_UA.uatr_userid = P_USER_ID;

    -- limpando as respectivas colunas na tabela de atributos.
    UPDATE zks_user_atributos SET
        uatr_for = 0,
        uatr_des = 0,
        uatr_int = 0,
        uatr_con = 0,
        uatr_car = 0,
        uatr_per = 0
    WHERE 
        uatr_userid = P_USER_ID;

    -- Limpando as habilidades do usuário.
    DELETE FROM zks_user_habilidades WHERE uhb_userid = P_USER_ID;

    -- Inserindo o LOG desta ação.
    INSERT INTO zks_user_logs_action (logs_type, logs_adm, logs_affect, logs_use, logs_action, logs_motivo, logs_date, logs_ip)
    VALUES ('hab_reset', P_ADMIN_ID, P_USER_ID, 'Ficha do Personagem', 'Permissões e Pontos Adicionais', NULL, NOW(), P_ADMIN_IP);

END

Mysql LOG Error Message:


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.1.25-MariaDB
key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=2
max_threads=1001
thread_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 787107 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x6d3dda8
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...
mysqld.exe!?exec@JOIN@@QAEXXZ()
mysqld.exe!?mysql_derived_fill@@YA_NPAVTHD@@PAULEX@@PAUTABLE_LIST@@@Z()
mysqld.exe!?mysql_handle_single_derived@@YA_NPAULEX@@PAUTABLE_LIST@@I@Z()
mysqld.exe!?preread_init@st_join_table@@QAE_NXZ()
mysqld.exe!?disjoin@?$List@VItem@@@@QAEXPAV1@@Z()
mysqld.exe!?exec_inner@JOIN@@QAEXXZ()
mysqld.exe!?exec@JOIN@@QAEXXZ()
mysqld.exe!?mysql_multi_update@@YA_NPAVTHD@@PAUTABLE_LIST@@PAV?$List@VItem@@@@2PAVItem@@_KW4enum_duplicates@@_NPAVst_select_lex_unit@@PAVst_select_lex@@PAPAVmulti_update@@@Z()
mysqld.exe!?mysql_execute_command@@YAHPAVTHD@@@Z()
mysqld.exe!?exec_core@sp_instr_stmt@@UAEHPAVTHD@@PAI@Z()
mysqld.exe!?reset_lex_and_exec_core@sp_lex_keeper@@QAEHPAVTHD@@PAI_NPAVsp_instr@@@Z()
mysqld.exe!?execute@sp_instr_stmt@@UAEHPAVTHD@@PAI@Z()
mysqld.exe!?execute@sp_head@@AAE_NPAVTHD@@_N@Z()
mysqld.exe!?execute_procedure@sp_head@@QAE_NPAVTHD@@PAV?$List@VItem@@@@@Z()
mysqld.exe!?do_command@@YA_NPAVTHD@@@Z()
mysqld.exe!?mysql_execute_command@@YAHPAVTHD@@@Z()
mysqld.exe!?mysql_parse@@YAXPAVTHD@@PADIPAVParser_state@@@Z()
mysqld.exe!?dispatch_command@@YA_NW4enum_server_command@@PAVTHD@@PADI@Z()
mysqld.exe!?do_command@@YA_NPAVTHD@@@Z()
mysqld.exe!?threadpool_process_request@@YAHPAVTHD@@@Z()
mysqld.exe!?init_io@@YAHPAUconnection_t@@PAVTHD@@@Z()
kernel32.dll!GetCPInfoExW()
ntdll.dll!WinSqmStartSession()
ntdll.dll!TpSetTimer()
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlInitializeExceptionChain()
ntdll.dll!RtlInitializeExceptionChain()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x12a2b828): UPDATE zks_user_atributos AS UPD_UA
    INNER JOIN (
        SELECT
            ua.uatr_userid,
            CASE WHEN ml_qt_atr > SUM(ua.uatr_add_atr + ua.uatr_for + ua.uatr_des + ua.uatr_int + ua.uatr_con + ua.uatr_car + ua.uatr_per) THEN ml_qt_atr ELSE SUM(ua.uatr_add_atr + ua.uatr_for + ua.uatr_des + ua.uatr_int + ua.uatr_con + ua.uatr_car + ua.uatr_per) END AS uatr_add_atr,
            CASE WHEN ml_qt_hab > SUM(IFNULL(h.hab_value, 0) + ua.uatr_add_hab) THEN ml_qt_hab ELSE SUM(IFNULL(h.hab_value, 0) + ua.uatr_add_hab) END AS uatr_add_hab,
            CASE WHEN ml_qt_con > SUM(IFNULL(c.hab_value, 0) + ua.uatr_add_con) THEN ml_qt_con ELSE SUM(IFNULL(c.hab_value, 0) + ua.uatr_add_con) END AS uatr_add_con,
            CASE WHEN ml_qt_qua > SUM(IFNULL(q.hab_value, 0) + ua.uatr_add_qua) THEN ml_qt_qua ELSE SUM(IFNULL(q.hab_value, 0) + ua.uatr_add_qua) END AS uatr_add_qua,
            CASE WHEN ml_qt_fra > SUM(IFNULL(f.hab_value, 0) + ua.uatr_add_fra) THEN ml_qt_fra ELSE SUM(IFNULL(f.hab_value, 0) + ua.uatr_add_fra) END AS uatr_add_fra
        FROM zks_user_atributos ua
        JOIN zks_users u ON ua.uatr_userid = u.user_id
        JOIN vw_magic_levels ml ON ml.ml_level = u.user_level

        LEFT JOIN vw_user_atributes_sum h ON ua.uatr_userid = h.user_id AND h.hab_column_name = 'uatr_add_hab'
        LEFT JOIN vw_user_atributes_sum c ON ua.uatr_userid = c.user_id AND c.hab_column_name = 'uatr_add_con'
        LEFT JOIN vw_user_atributes_sum q ON ua.uatr_userid = q.user_id AND q.hab_column_name = 'uatr_add_qua'
        LEFT JOIN vw_user_atributes_sum f ON ua.uatr_userid = f.user_id AND f.hab_column_name = 'uatr_add_fra'
        GROUP BY
            ua.uatr_userid
    ) AS SEL_UA ON UPD_UA.uatr_userid = SEL_UA.uatr_userid
    SET
        UPD_UA.uatr_add_atr = SEL_UA.uatr_add_atr,

        UPD_UA.uatr_add_hab = SEL_UA.uatr_add_hab,
        UPD_UA.uatr_add_con = SEL_UA.uatr_add_con,
        UPD_UA.uatr_add_qua = SEL_UA.uatr_add_qua,
        UPD_UA.uatr_add_fra = SEL_UA.uatr_add_fra
    WHERE

        UPD_UA.uatr_userid =  NAME_CONST('P_USER_ID',57)
Connection ID (thread ID): 3
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=off

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.

Running the code out of the procedure everything runs smoothly, by PROC it gives the problem pointed out above. I do not know how to solve and I did not find on the WEB (nor here) about this more specific situation.

mysql stored-procedures

asked by anonymous 09.09.2018 / 03:43

1 answer

PHPMailer Linux Debian: EOF caught while checking if connected Manipulating image JS

score 0 · Accepted Answer

After opening this question I was immersed for about 2 days in the MYSQL forums in English looking for similar problems and doing the famous "trial and error". I found people who had the same problem but for other reasons.

It turns out that in my procedure, vw "vw_magic_levels" had a union that was causing the query to have a massive volume of data, causing mysql to be terminated.

I changed view to remove the union and the process, running via procedure worked. The only mystery I could not solve was: because it worked outside the procedure and not in the procedure. Anyway, thanks to those who lost some time reading the topic.

NOTE: I can not post the links here because I researched most of the time away from my PC and did not save, sorry.