How to stop a running procedure in MySQL?

mysql procedure
licaomeng picture licaomeng · Nov 23, 2015 · Viewed 11.3k times · Source

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();

Answer

maxhb picture maxhb · Nov 23, 2015

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;

Related questions

How to call a mysql stored procedure, with arguments, from command line?

How can I call a stored procedure from command line? I have a procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME) NO SQL BEGIN SET @eventIDOut = NULL; IF EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN SELECT `eID` …

Read More
Creating a procedure in mySql with parameters

I am trying to make a stored procedure using mySQL. This procedure will validate a username and a password. I'm currently running mySQL 5.0.32 so it should be possible to create procedures. Heres the code I've used. All I get is …

Read More
MySQL - Define a variable within select and use it within the same select

Is there a possibility to do something like this? SELECT @z:=SUM(item), 2*@z FROM TableA; I always get NULL for the second column. The strange thing is, that while doing something like SELECT @z:=someProcedure(item), 2*@z FROM TableA; …

Read More