Welcome to TDBSoverflow, Our class's own StackOverflow. Our rules:
  1. Use only meaningful and self-explanatory titles
  2. Tag your questions with meaningful keywords
  3. Use upvotes and downvotes to rate the answers
  4. When you receive a satisfying answer - Click the "V" button
Remember: you may get up to 5 bonus points to your final grade!

mysql DEADLOCK

+4 votes
38 views
Hi

While working with our DB (late submission), we can't perform any operation on one of the tables including DROP or TRUNCATE, since each operation hangs.

We found online the following post indication this might be a deadlock, and we should kill the process causing this.

https://dba.stackexchange.com/questions/11806/why-is-drop-database-taking-so-long-mysql

The problem is we don't have any permissions on the remote DB server.

Did anyone experienced something similar?

What are our options? We are on a tight timeline here...

Thanks,

Yossi
asked Jan 22, 2018 by Yossielman (990 points)
Do you see the process but can't kill it, or you don't see it at all?
I'm supposed to run:
SHOW ENGINE INNODB STATUS;
or
USE INFORMATION_SCHEMA;
SELECT * FROM INNODB_LOCK_WAITS;

but both fail with this error:
Error Code: 1227. Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

2 Answers

+2 votes
 
Best answer

Hi Yossi, 

You can find the Mysql processes that are currently running including their state by running the following command:

show processlist

Then, use the following command to kill the relevant mysql queries:

kill query <processId>

peace and love,

Geffen <3

answered Jan 22, 2018 by babygirl55 (10,420 points)
selected Jan 22, 2018 by Yossielman
+3 votes
Hi Yossi, I would go to the system staff for help, they have the permissions..
answered Jan 22, 2018 by Amit (27,910 points)
...