How to stop a running procedure in MySQL?

Question

I called a procedure in MySQL, but it is in an endless loop now! How to stop a running procedure in MySQL? The following is my procedure code：

drop procedure if exists my_proc; 
DELIMITER $$
CREATE PROCEDURE my_proc
()
BEGIN
DECLARE VAR INT;
SET VAR=0;
WHILE VAR<10000
DO
INSERT INTO my_table () VALUES ();
END WHILE;
END; $$
DELIMITER;

CALL my_proc();

Stellular

Hello, please share your procedure and also other relevant info. Otherwise it will be marked as off-topic because you are asking for debugging help without minimum code to reproduce the problem. – Exchange

And I see you have accepted an answer, but proper approach would be to use this question to find out why it got into an infinite loop in the first place. – Exchange

Thank you, I have appended the procedure. – Stellular

Answer 1

Connect to the server (or use phpmyadmin or something similar) and do:

SHOW PROCESSLIST;

Find the process causing trouble, note its ID and do:

KILL ID_OF_YOUR_PROCESS;

Sendai

It's not showing the procedure in full processlist. What will be the command type for procedures? Can you help me? – Followthrough

Answer 2

I had same problem, no procedure in list. But I saw there nested(inner) INSERT query executing in the loop within the procedure. This row had state something like "query end". Then I killed it based on id and no new data populated in my table. So I assume that procedure really stopped

Payer

Probably it's more safe to restart the sql service. In Linux that would be: sudo service mysql restart (or sudo service mysql stop; sudo service mysql start) – Jeremy