CODEWITHSUNDEEP
mysql
SenorCardgage
SenorCardgage

Reputation: 194

Is there a way to kill this stored procedure?

I ran into this weird issue when I was experimenting with stored procedures. I made a stored procedure to quickly populate a table with 0-n values.

DELIMITER $$
CREATE PROCEDURE `insert_range`(a INT)
BEGIN set @x = 1; REPEAT INSERT INTO free_codes 
VALUES (@x); SET @x = @x + 1; UNTIL @x = a END 
REPEAT; END $$
DELIMITER ;

So I then tried to insert 10 million with it:

CALL insert_range(10000000)

But it was taking a long time so I used ctrl-c to stop, but it wasn't stopping. Then I just closed the shell and reconnected hoping that would stop it. However I realized when I ran this command:

select * from free_codes order by code DESC limit 1;

It always returns a consistently higher result:

1291101

Later:

1302581

So each time I run that command it shows a higher number depending on how much time has passed. Which indicates it's still running my stored procedure. It also takes a full second for mysql to return this simple query.

I know you're able to kill processes by using 'SHOW PROCESSLIST' then 'KILL process_id' but when I do that, I don't see the process in the list of running processes. I only see the event_scheduler, and my user connection. I thought I would see a third entry there for the stored procedure, but I'm not seeing one.

+----+-----------------+-----------------+---------+- --------+--------+------------------------+--------------------------+
| Id | User            | Host            | db      | Command | Time   | State                  | Info                     |
+----+-----------------+-----------------+---------+---------+--------+------------------------+--------------------------+
|  5 | event_scheduler | localhost       | NULL    | Daemon  | 161795 | Waiting on empty queue | NULL                     |
| 15 | myusername      | 127.0.0.1:36790 | monster | Query   |      0 | init                   | PLUGIN: show processlist |
+----+-----------------+-----------------+---------+---------+--------+------------------------+--------------------------+

How can I stop the stored procedure? Please help, I've tried all I can think of and it's slowing down all my queries that I run using that table.

Upvotes: 0

Views: 292

Answers (1)

Luuk
Luuk

Reputation: 14958

I was planning on saying: Speed will increase if you do all these updates in one transaction, but MyISAM does not support transactions:

DELIMITER $$
CREATE PROCEDURE `insert_range`(a INT)
BEGIN 
  set @x = 1; 
  START TRANSACTION;
  REPEAT 
     INSERT INTO free_codes VALUES (@x); 
     SET @x = @x + 1; 
  UNTIL @x = a 
  END REPEAT; 
  COMMIT;
END $$
DELIMITER ;

Upvotes: 1

Related Questions