To be honest I’m not a big fan of Stored Procedures, at least not in the form they are currently implemented in MySQL 5.0
Only SQL as a Language Which is ancient ugly for algorithmic programming and slow. It is also forces you to use a lot of foreign constructs to “original” MySQL style – to process data via cursors, handle error via Handlers etc. If you spent last 10 years writing Stored Procedures for Oracle or DB2 it may be cool and convenient for you, but not for me 🙂
Lack of Debugging I like to be able to debug software, if not full blown debugger I’d like to have things like echo and var_dump. Due to the context of execution these are not easy though. Of course you can code a little helper Debug Storage Procedure which will log some information in MySQL table but it is not convenient enough.
Bad Parser Error Messages MySQL Parser is in general far from perfect when it comes to error handling. “You have an error in your SQL syntax … near ” at line 1″ is not very helpful even when dealing with large queries but for Stored Procedures that is the real issue. So you have to overcome few road blocks even before you start fighting with debugging.
No Profiling tools If you’re interested in Performance you need a way to profile what inside stored procedures is taking the time, which is not something readily available. Even if you look at most simple and typically most time consuming part of Stored Procedure execution – running of SQL queries – you do not get these logged, instead slow query log will contain full stored procedure calls.
On the other hand Stored Procedures Indeed can help to Improve MySQL Performance. For DBT2 benchmarks we’ve tried a while back MySQL 5.0 was about 10% slower than 4.1 without stored procedures but was 20% faster if Stored Procedures are used. And this is of course not the limit. You may also have other reasons to use Stored Procedures besides performance and these can be valid.
What I tend to do if Stored Procedures are helpful for Performance reasons is to have two code versions, one using stored procedures and other doing same thing using direct statements. This allows to debug and profile most of the things comfortably and works pretty well especially if you keep your stored procedures simple so you do not add much bugs converting code from your language of choice to SQL.
There is one more thing you should beware with Stored Procedures is to put a lot of computational load in them. Not only the language is slow at it but you’re also loading CPU on your Database Server, which is typically more expensive to scale than Application/Web Servers. It is OK however to do some simple math if it can help you to avoid sending large result set back to the client as in this case server may need to do more work to send it back than Stored Procedure to process it.
Stored Procedures also have number of performance gotchas which I’ve been running into production but have not taken a time to research into details. So just beware.
I also hope other time MySQL and third parties will develop tools and extensions to target many of the problems mentioned.
Hey Peter,
“Only SQL as a Language” – it’s all right, “foreign” constructs came from SQL 2003 standard, I hope all database vendors would follow it, including Oracle 🙂
And I so agree with a lack of debugging (breakpoints, step by step execution, variable content, etc), and proper messaging.
Dmitri
I completely agree, trying to develop stored procedures on MySQL is a nightmare. No debugging, you have a syntax error often gives you the wrong line.
You also missed the strange situation you are not allowed to use variable in certain constructs – e.g. in the LIMIT clause
I also completely agree. We are developing some complex game logics using stored procedures – in order to avoid non-useful data roundtrips between server application and database – but it seems that there is no way to trace down MySQL logfile every SQL statement executed in a stored procedure/function… Therefore we can’t use any analysis tool and we must check every index and every SQL statement by ourselves, especially when game logic design changes (which is quite often…).
vogon5,
Right I mentioned I’m not trying to look at limitations and gotchas of stored procedures themselves.
how to run in mysql browser
I completely agree. MySQL is clearly sub-par in many aspects, specifically the development tools. DECLARE statements that work in some contexts and not others, variable initialization inconsistencies, lack of debugging ability in the provided tools, IF/CONDITIONAL confusion, operator confusion ( :=/= ), and the list goes on an on. Maybe they should hire Anders Hejlsberg to fix this lemon. My guess is Sun will make this better but then charge for tools that target real devs.
They’re very fortunate it’s a free tool. Otherwise I’m quite sure it would have been abandon years ago.
Andrew
hi im harpreet. my problem concern to stored procedure.i m unable to create stored procdure………so plz help me
hi im harpreet. my problem concern to stored procedure.i m unable to create stored procdure in java………and after wards i wanna to access stored procdure…………
harpreet,
You have problem concern with stored procedure as do I too very much. Java stored procedure create does work not. Plz me help too.
hi im yukipupu. i problem have java procedure stored. two day is my first time. plz me help give.
Inside DECLARE EXIT HANDLER ON SQLEXCEPTION, is there any way to find the error code and/or message that MySQL would have returned to the caller if the handler wasn’t there?
Or is it necessary to write an individual handler for each and every possible error code in each and every stored procedure…
oops, meant
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
— in here, how do I see what the error was, and what message MySQL would have returned?
END;
I had very bad experience with mysql stored procedure and triggers. I think mysql should provide a tool for stored procedure programming with debug facility.
Can I use a variable name from a cursor loop in an update statement within said loop … in a trigger?
OPEN cursor1;
cursor_loop:LOOP
FETCH cursor1 INTO l_customer_name;
IF l_last_row_fetched=1 THEN
LEAVE cursor_loop;
END IF;
UPDATE l_customer_name SET active = 1 WHERE customer_id = NEW.id;
END LOOP cursor_loop;
CLOSE cursor1;
right now, when i run this, i get ‘l_customer_name’ table does not exist.
hi bad programes,
I think every one is not stating from mysql as first db. It is very cool and very good db architecture .
in every pl/sql server is poor debugging . not only mysql . I am working mysql good clients like google , facebook it is free .
wast fellow don’t descrese othere . mysql is 4 rank database
Peter, thank you for the great post. Though I’m not completely agree with you that stored procedures are evil. After all, they significantly improve performance while on the other side of the medal is code duplication and break up of a business logic. They are tricky to debug, yes, but there are plenty of debuggers out there which claim to solve the issue, at least partially. BTW, I’m in the process of choosing some debugger. Have found these ones so far, could anyone comment if any of them is worth to look at?
– Toad for MySQL (Free)
– Illatis StepIn ($39)
– Debugger for MySQL ($49)
– dbForge Studio for MySQL ($99)
Brandon.
Two approaches may be used to debug code in MySQL:
1. Control execution of stored code on the server (used in dbForge Studio for MySQL )
2. Interpretation of stored code in an external program with execution of stored code expressions on the server (used in Illatis StepIn and in Debugger for MySQL from MyDebugger.com)
The first approach is the same as use of debugging package DBMS_DEBUG in Oracle; it is used in dbForge Studio for MySQL. This approach guarantees validity of executed code, as an executed stored procedure/function/trigger does the same operations and gives the same result as during usual execution. See more information about the approach here: http://www.devart.com/dbforge/mysql/studio/articles/debugging.html
The second approach allows escaping code locating on the server. This does not guarantee debugging validity. The code executed during debugging and in a usual mode differs. The causes may be either an error in an external interpreter (errors during processing conditionals, errors during retrieving data during reading a cursor, work with variables) or an error on the server.
From release to release, sometimes errors appear in MySQL interpreter, or they are fixed. And in an external interpreter, it is impossible to take into account all the peculiarities of executing stored code with each released server version.
The first approach is more beneficial during debugging stored procedures, functions, and triggers, and other important code.
how to get dynamic sql queries on calling stored procedure?
i am not able to tackle error…
please help
Sleepstealing is the word best describing MySQL Stored Procedures.
A very simple SELECT is working interactively and giving NULL in the SP.
Leveraging MySQL 5.1 features like SP, Events, Prepared Statements I am was able to do some very interesting things, but this SP is giving me hard times.
It’s a shame because this “dark” side of the database could be quite powerful.
I think modeling in traditional way such as MVC may produce the same results but in fact for a some one excepted to be serious programmer has to program computer in its natural way. Let me say
to manuplate DOM use javascript and css
or if your server is linux use php or perl if its windows use .net enviroment.
So if data will be programmed better way will be using its own development tools stored programs , triggers , events etc. to build model.
Best Regards
Osman Corluk