Error calling procedure in MySQL: Error Code: 2013. Lost connection to MySQL server during query

2k views Asked by At

I need help on MySQL, when I call a specific procedure the Workbench lose connection and the MySQL80 Windows service is arrested!

I already tried to set higher parameter (600) on Edit -> Preferences -> SQL Editor -> DBMS connection read time out (in seconds).

I don't know if it can help but here is my procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS drop_unfinished //

CREATE PROCEDURE drop_unfinished(
        OUT p_return    INT,
        IN  p_forum_id  INT
    ) 
    COMMENT 'Drops all unscraped batches for the given forum'
    READS SQL DATA
BEGIN
    DECLARE v_limit INT; 
    DECLARE v_offset INT; 
    DECLARE v_scraped_batches INT;
    DECLARE v_scraped_topics INT;
    DECLARE v_edit_time DATETIME;
    DECLARE v_start_time DATETIME;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET p_return = -1; 

    SELECT scraped_topics, scraped_batches, edit_time
    INTO v_scraped_topics, v_scraped_batches, v_edit_time
    FROM dashboard_view
    WHERE forum_id = p_forum_id;

    SET v_limit = 25;
    SET v_offset = IF(v_scraped_batches = 1, 0, v_limit * v_scraped_batches);

    CREATE TEMPORARY TABLE 
    IF NOT EXISTS topic_ids
    SELECT topic_id 
    FROM topics 
    WHERE edit_time > v_edit_time 
    LIMIT v_scraped_topics 
    OFFSET v_offset;

    DELETE FROM logs 
    WHERE step_time > v_edit_time
    AND parent_id = p_forum_id
    AND object_id IN (SELECT topic_id FROM topic_ids);

    DELETE FROM torrents_tmp 
    WHERE forum_id = p_forum_id
    AND topic_id IN (SELECT topic_id FROM topic_ids);

    DELETE FROM ed2k_links_tmp 
    WHERE forum_id = p_forum_id
    AND topic_id IN (SELECT topic_id FROM topic_ids);

    DELETE FROM posts_tmp 
    WHERE forum_id = p_forum_id
    AND topic_id IN (SELECT topic_id FROM topic_ids);

    COMMIT;

    DROP TEMPORARY TABLE topic_ids;

    SET p_return = 0;
END
//

DELIMITER ;

If I call all the queries separately (obviously with same parameters) I don't have any problem!

Server Logs:

    , , , 17:14:03 UTC - mysqld got exception 0xc0000005 ;
, , , 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=8388608
, , , read_buffer_size=8192
, , , max_used_connections=4
, , , max_threads=151
, , , thread_count=5
, , , connection_count=4
, , , It is possible that mysqld could use up to
, , , key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49570 K  bytes of memory
, , , Hope that's ok; if not, decrease some variables in the equation.
, , , Thread pointer: 0x22398e95dc0
, , , 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...
, , , 7ff7e4ff764d    mysqld.exe!?this_item@Item_splocal@@UEAAPEAVItem@@XZ()
, , , 7ff7e4ffa256    mysqld.exe!?val_int@Item_sp_variable@@UEAA_JXZ()
, , , 7ff7e50cbcb5    mysqld.exe!?set_limit@SELECT_LEX_UNIT@@QEAA_NPEAVTHD@@PEAVSELECT_LEX@@@Z()
, , , 7ff7e534e258    mysqld.exe!?execute@Sql_cmd_create_table@@UEAA_NPEAVTHD@@@Z()
, , , 7ff7e50f2d38    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
, , , 7ff7e52e7e3a    mysqld.exe!?exec_core@sp_instr_stmt@@UEAA_NPEAVTHD@@PEAI@Z()
, , , 7ff7e52e9e82    mysqld.exe!?reset_lex_and_exec_core@sp_lex_instr@@AEAA_NPEAVTHD@@PEAI_N@Z()
, , , 7ff7e52ea619    mysqld.exe!?validate_lex_and_execute_core@sp_lex_instr@@QEAA_NPEAVTHD@@PEAI_N@Z()
, , , 7ff7e52e82aa    mysqld.exe!?execute@sp_instr_stmt@@UEAA_NPEAVTHD@@PEAI@Z()
, , , 7ff7e50d0193    mysqld.exe!?execute@sp_head@@AEAA_NPEAVTHD@@_N@Z()
, , , 7ff7e50d109b    mysqld.exe!?execute_procedure@sp_head@@QEAA_NPEAVTHD@@PEAV?$List@VItem@@@@@Z()
, , , 7ff7e5376bee    mysqld.exe!?execute_inner@Sql_cmd_call@@MEAA_NPEAVTHD@@@Z()
, , , 7ff7e524c706    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
, , , 7ff7e50f2d38    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
, , , 7ff7e50f3816    mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
, , , 7ff7e50ed6b8    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
, , , 7ff7e50ee5e5    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
, , , 7ff7e4f847f8    mysqld.exe!?pop_front@?$list@PEAVChannel_info@@V?$allocator@PEAVChannel_info@@@std@@@std@@QEAAXXZ()
, , , 7ff7e6093e87    mysqld.exe!??1?$lock_guard@Vmutex@std@@@std@@QEAA@XZ()
, , , 7ff7e5cab1dc    mysqld.exe!?my_thread_join@@YAHPEAUmy_thread_handle@@PEAPEAX@Z()
, , , 7fff7aaec4ce    ucrtbase.dll!_o_ceil()
, , , 7fff7d453034    KERNEL32.DLL!BaseThreadInitThunk()
, , , 7fff7de11461    ntdll.dll!RtlUserThreadStart()
, , , Trying to get some variables.
, , , Some pointers may be invalid and cause the dump to abort.
, , , Query (223994b4e98): CREATE TEMPORARY TABLE
, , , IF NOT EXISTS topic_ids
, , , SELECT topic_id
, , , FROM topics
, , , WHERE edit_time > v_edit_time
, , , LIMIT v_scraped_topics
, , , OFFSET v_offset
, , , Connection ID (thread ID): 10
, , , 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.
2

There are 2 answers

1
Ex. On BEST ANSWER

I solved by updating the DBMS! It was a bug of my MySQL version!

1
Wilson Hauck On

Suggestions to consider for your my.ini [mysqld] section

read_rnd_buffer_size=256K  # from 1 - pretty astounding you run at all
read_buffer_size=128K  # from 8K - for a more reasonable limit
thread_cache_size=40  # from 10 to prepare for growth
innodb_io_capacity=1600  # from 200 to allow higher IOPS
max_connections=50  # from default of 151 until you have more activity
innodb_buffer_pool_size=256M  # from 8M likely you will get to innodb tables soon

These changes are necessary to achieve stability for your instance.

Reviewing your PROCEDURE, will try to comment on your procedure by Friday afternoon. Some of the requested data is only available on Linux systems. To determine RAM on your machine, right clk on Window icon in left of Task Bar, clk System and you should see how much RAM is on your server.

For additional suggestions, view profile, Network profile for contact info and get in touch via Skype, please.