在mysql中调用过程时出错:错误代码:2013查询期间与mysql服务器失去连接

bxgwgixi  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(502)

我需要mysql方面的帮助,当我调用一个特定的过程时,工作台失去连接,mysql80 windows服务被逮捕!
我已经尝试在edit->preferences->sql editor->dbms connection read timeout(以秒为单位)上设置更高的参数(600)。
我不知道这是否有帮助,但这是我的程序:

  1. DELIMITER //
  2. DROP PROCEDURE IF EXISTS drop_unfinished //
  3. CREATE PROCEDURE drop_unfinished(
  4. OUT p_return INT,
  5. IN p_forum_id INT
  6. )
  7. COMMENT 'Drops all unscraped batches for the given forum'
  8. READS SQL DATA
  9. BEGIN
  10. DECLARE v_limit INT;
  11. DECLARE v_offset INT;
  12. DECLARE v_scraped_batches INT;
  13. DECLARE v_scraped_topics INT;
  14. DECLARE v_edit_time DATETIME;
  15. DECLARE v_start_time DATETIME;
  16. DECLARE EXIT HANDLER FOR SQLEXCEPTION SET p_return = -1;
  17. SELECT scraped_topics, scraped_batches, edit_time
  18. INTO v_scraped_topics, v_scraped_batches, v_edit_time
  19. FROM dashboard_view
  20. WHERE forum_id = p_forum_id;
  21. SET v_limit = 25;
  22. SET v_offset = IF(v_scraped_batches = 1, 0, v_limit * v_scraped_batches);
  23. CREATE TEMPORARY TABLE
  24. IF NOT EXISTS topic_ids
  25. SELECT topic_id
  26. FROM topics
  27. WHERE edit_time > v_edit_time
  28. LIMIT v_scraped_topics
  29. OFFSET v_offset;
  30. DELETE FROM logs
  31. WHERE step_time > v_edit_time
  32. AND parent_id = p_forum_id
  33. AND object_id IN (SELECT topic_id FROM topic_ids);
  34. DELETE FROM torrents_tmp
  35. WHERE forum_id = p_forum_id
  36. AND topic_id IN (SELECT topic_id FROM topic_ids);
  37. DELETE FROM ed2k_links_tmp
  38. WHERE forum_id = p_forum_id
  39. AND topic_id IN (SELECT topic_id FROM topic_ids);
  40. DELETE FROM posts_tmp
  41. WHERE forum_id = p_forum_id
  42. AND topic_id IN (SELECT topic_id FROM topic_ids);
  43. COMMIT;
  44. DROP TEMPORARY TABLE topic_ids;
  45. SET p_return = 0;
  46. END
  47. //
  48. DELIMITER ;

如果我分别调用所有查询(显然使用相同的参数),我就没有任何问题了!
服务器日志:

  1. , , , 17:14:03 UTC - mysqld got exception 0xc0000005 ;
  2. , , , This could be because you hit a bug. It is also possible that this binary
  3. , , , or one of the libraries it was linked against is corrupt, improperly built,
  4. , , , or misconfigured. This error can also be caused by malfunctioning hardware.
  5. , , , Attempting to collect some information that could help diagnose the problem.
  6. , , , As this is a crash and something is definitely wrong, the information
  7. , , , collection process might fail.
  8. , , , key_buffer_size=8388608
  9. , , , read_buffer_size=8192
  10. , , , max_used_connections=4
  11. , , , max_threads=151
  12. , , , thread_count=5
  13. , , , connection_count=4
  14. , , , It is possible that mysqld could use up to
  15. , , , key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49570 K bytes of memory
  16. , , , Hope that's ok; if not, decrease some variables in the equation.
  17. , , , Thread pointer: 0x22398e95dc0
  18. , , , Attempting backtrace. You can use the following information to find out
  19. , , , where mysqld died. If you see no messages after this, something went
  20. , , , terribly wrong...
  21. , , , 7ff7e4ff764d mysqld.exe!?this_item@Item_splocal@@UEAAPEAVItem@@XZ()
  22. , , , 7ff7e4ffa256 mysqld.exe!?val_int@Item_sp_variable@@UEAA_JXZ()
  23. , , , 7ff7e50cbcb5 mysqld.exe!?set_limit@SELECT_LEX_UNIT@@QEAA_NPEAVTHD@@PEAVSELECT_LEX@@@Z()
  24. , , , 7ff7e534e258 mysqld.exe!?execute@Sql_cmd_create_table@@UEAA_NPEAVTHD@@@Z()
  25. , , , 7ff7e50f2d38 mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
  26. , , , 7ff7e52e7e3a mysqld.exe!?exec_core@sp_instr_stmt@@UEAA_NPEAVTHD@@PEAI@Z()
  27. , , , 7ff7e52e9e82 mysqld.exe!?reset_lex_and_exec_core@sp_lex_instr@@AEAA_NPEAVTHD@@PEAI_N@Z()
  28. , , , 7ff7e52ea619 mysqld.exe!?validate_lex_and_execute_core@sp_lex_instr@@QEAA_NPEAVTHD@@PEAI_N@Z()
  29. , , , 7ff7e52e82aa mysqld.exe!?execute@sp_instr_stmt@@UEAA_NPEAVTHD@@PEAI@Z()
  30. , , , 7ff7e50d0193 mysqld.exe!?execute@sp_head@@AEAA_NPEAVTHD@@_N@Z()
  31. , , , 7ff7e50d109b mysqld.exe!?execute_procedure@sp_head@@QEAA_NPEAVTHD@@PEAV?$List@VItem@@@@@Z()
  32. , , , 7ff7e5376bee mysqld.exe!?execute_inner@Sql_cmd_call@@MEAA_NPEAVTHD@@@Z()
  33. , , , 7ff7e524c706 mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
  34. , , , 7ff7e50f2d38 mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
  35. , , , 7ff7e50f3816 mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
  36. , , , 7ff7e50ed6b8 mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
  37. , , , 7ff7e50ee5e5 mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
  38. , , , 7ff7e4f847f8 mysqld.exe!?pop_front@?$list@PEAVChannel_info@@V?$allocator@PEAVChannel_info@@@std@@@std@@QEAAXXZ()
  39. , , , 7ff7e6093e87 mysqld.exe!??1?$lock_guard@Vmutex@std@@@std@@QEAA@XZ()
  40. , , , 7ff7e5cab1dc mysqld.exe!?my_thread_join@@YAHPEAUmy_thread_handle@@PEAPEAX@Z()
  41. , , , 7fff7aaec4ce ucrtbase.dll!_o_ceil()
  42. , , , 7fff7d453034 KERNEL32.DLL!BaseThreadInitThunk()
  43. , , , 7fff7de11461 ntdll.dll!RtlUserThreadStart()
  44. , , , Trying to get some variables.
  45. , , , Some pointers may be invalid and cause the dump to abort.
  46. , , , Query (223994b4e98): CREATE TEMPORARY TABLE
  47. , , , IF NOT EXISTS topic_ids
  48. , , , SELECT topic_id
  49. , , , FROM topics
  50. , , , WHERE edit_time > v_edit_time
  51. , , , LIMIT v_scraped_topics
  52. , , , OFFSET v_offset
  53. , , , Connection ID (thread ID): 10
  54. , , , Status: NOT_KILLED
  55. , , , The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  56. , , , information that should help you find out what is causing the crash.
fquxozlt

fquxozlt1#

为my.ini[mysqld]部分考虑的建议

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

这些更改对于实现示例的稳定性是必需的。
回顾你的手术过程,我会在周五下午之前对你的手术过程发表评论。某些请求的数据仅在linux系统上可用。要确定您的计算机上的ram,右键单击任务栏左侧的窗口图标,然后单击系统,您应该看到服务器上有多少ram。
如需更多建议,请查看个人资料、联系信息的网络资料,并通过skype联系。

gblwokeq

gblwokeq2#

我通过更新数据库管理系统解决了这个问题!这是我mysql版本的一个bug!

相关问题