I need a help that some of mysql tables are not accessable ( looks like deadlock) as my website is down and I can't open these tables through Navicat nor phpMyAdmin , I tried to kill the query by knowing the thread ID using:
我需要一些帮助,一些mysql表不可访问(看起来像死锁),因为我的网站已关闭,我无法通过Navicat或phpMyAdmin打开这些表,我试图通过使用以下方式知道线程ID来终止查询:
SHOW FULL PROCESSLIST
but I didn't find any rows related to the issue.
但我没有找到任何与此问题相关的行。
Noting that the issue caused after I tried to run some heavy query to 2 tables which contain > 10 millions of row, and I cancelled the query after it took more than 3 hours.
注意到在我尝试对包含> 10百万行的2个表运行一些繁重查询后引起的问题,并且在超过3个小时后我取消了查询。
I don't have access to reboot server, and I am using godaddy hosting, and they doesn't have online chatting support to my country!
我没有访问重启服务器,我使用godaddy托管,他们没有在我的国家的在线聊天支持!
Can I do anything to fix it by myside without rebooting! please advice.
我可以做任何事情来修复myside而不重新启动!请指教。
2 个解决方案
#1
1
Even though it is 2 million rows the query should not take 3 hours, which means the SQL itself needs serious attention. As you said 'looks like deadlock' you can examine by SHOW ENGINE INNODB STATUS If we get more tables in locked state then we may able to resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.
即使它是200万行,查询也不应该花费3个小时,这意味着SQL本身需要认真关注。正如您所说的“看起来像死锁”,您可以通过SHOW ENGINE INNODB状态进行检查如果我们在锁定状态下获得更多表,那么我们可以通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。
For reference:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_deadlock https://www.percona.com/blog/2012/09/19/logging-deadlocks-errors/
#2
0
In Unix-like OS you can use "system" or "\!" prefix to execute any command of shell inside mysql interpreter. See this. Of course you must have admin privileges to run any commands in shell.
在类Unix操作系统中,您可以使用“system”或“\!”在mysql解释器中执行shell的任何命令的前缀。看到这个。当然,您必须具有管理员权限才能在shell中运行任何命令。
With this you can use some mysql admin command to get de PID and analise your problem by OS.
有了这个,您可以使用一些mysql管理命令来获取de PID并通过操作系统分析您的问题。
#1
1
Even though it is 2 million rows the query should not take 3 hours, which means the SQL itself needs serious attention. As you said 'looks like deadlock' you can examine by SHOW ENGINE INNODB STATUS If we get more tables in locked state then we may able to resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.
即使它是200万行,查询也不应该花费3个小时,这意味着SQL本身需要认真关注。正如您所说的“看起来像死锁”,您可以通过SHOW ENGINE INNODB状态进行检查如果我们在锁定状态下获得更多表,那么我们可以通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。
For reference:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_deadlock https://www.percona.com/blog/2012/09/19/logging-deadlocks-errors/
#2
0
In Unix-like OS you can use "system" or "\!" prefix to execute any command of shell inside mysql interpreter. See this. Of course you must have admin privileges to run any commands in shell.
在类Unix操作系统中,您可以使用“system”或“\!”在mysql解释器中执行shell的任何命令的前缀。看到这个。当然,您必须具有管理员权限才能在shell中运行任何命令。
With this you can use some mysql admin command to get de PID and analise your problem by OS.
有了这个,您可以使用一些mysql管理命令来获取de PID并通过操作系统分析您的问题。