MySQL

Stored procedure error

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2008

Posts: 2

Reputation: AdamGr is an unknown quantity at this point

Solved Threads: 0

AdamGr

Offline

Newbie Poster

Stored procedure error

Jun 30th, 2008

This must be a pretty newbie mistake, but I'm still flummoxed by it. I created a very simple procedure:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CREATE PROCEDURE deleteAll ()
BEGIN
DELETE FROM test1;
DELETE FROM test2;
END
create procedure deleteAll ()
begin
delete from test1;
delete from test2;
end

but when I run it I get a Error code -1: Error executing SQL command.

The strangest part is that it still does what its supposed to, the data IS deleted from those two tables. So if its necessary I can go on with my life; but I would like to know why the error is occurring.

Thanks,
Adam

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Stored procedure error

Jun 30th, 2008

Hello,

unfortunately and opposite to other database vendors, mysql does not publish a meaningful list of all error numbers or even warnings, they only explain some errors here and there. So one cannot found an explanation of "error" -1 what seems to be a negative SQLCODE (unfortunately there isn't an official standardized SQLCODE -1). Anyway, there are various reasons for such error:

You must have execute privilege if you want to execute procedure or function.

Are you owner of those tables, did you create them?

Are there any foreign keys related to one or both tables?

Maybe your tables are still locked? For example you have just inserted some data and auto commit is off then you would not allow to delete them. To unlock tables enter commit.

Ah, what version of mysql are you working with? You must have version 5.0.1 and up, eventually check proc table whether it exists.

Last but not least, the body of stored procedure is clasped by BEGIN .... END ; <--- ends with semicolon.

krs,
tesu

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.