I am by now a few days late, but I meant to point out once the milestone had been reached:
MySQL Bug #11660, “Expose either SQLState, mysql_error() or other diagnostics in stored procedures” was opened on June 30, 2005. It has now been open for more than five years with no fix in the server, no fix in sight, and no real workaround. This makes it very difficult to recommend the use of MySQL stored procedures for most serious applications, as it is somewhere between very cumbersome and impossible to handle errors properly. How can something so important go so long without a fix?
Jeremy Cole 
There are a slew of these out there and it really doesn’t present a good message about MySQL and it’s development. Like the one for how innodb doesn’t properly recover disk space even after large deletions. That’s been open for something like 7 years, and it’s one I keep butting my head up against.
Currently the ‘fix’ is either to dump and re-import the database which is a pain, or install the latest InnoDB plugin which will return space if you do TRUNCATE TABLE.
http://bugs.mysql.com/bug.php?id=1341
The more I use MySQL and come across long existing ‘funnies’ the less I want to use it and start thinking about alternatives.
MySQL has been great this year about fixing bugs — much better than in previous years. Maybe they missed it.
> How can something so important go so long without a fix?
Perhaps because it’s not so important?
Captain Obvious: The alternative, obviously, is that people are (a) writing really poor code without error checking; or (b) not using stored procedures. Now, I would agree that not using stored procedures would often be the best bet, but I don’t think that would be held as a universal truth.
Tweets that mention jcole’s weblog: Jeremy Cole’s take on life. » Blog Archive » What’s five years between friends? -- Topsy.com
Jeremy: So, it seems no one really uses stored procedures. SQL Server does not have get diagnostics, neither does PostgreSQL (well, only for ROW_COUNT), nor Oracle. Which particular item you need that is not currently available?
@Twirrim – Jeremy’s rant was excellent. Yours was not.
InnoDB can return space to the OS — use innodb_file_per_table. Alas, people with older versions of InnoDB might have to dump/reload — too bad. If dump/reload is really a problem, then someone can step up and sponsor Percona to add an option that truncates the end of the ibdata1 file when all space at the end is unused.
not long after that bug was opened mr Boauman blogged this workaround:
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html
to the commenter with the really long nick – something about “trooths”: the workaround I posted relates to http://bugs.mysql.com/bug.php?id=11661 not http://bugs.mysql.com/bug.php?id=11660.
The topic of Jeremy’s post is that you can’t detect what error occurred in a general case.
There is actually a workaround for http://bugs.mysql.com/bug.php?id=11660 though, but it is really ugly and I wouldn’t recommend it. Here’s a post that explains the workaround:
http://blogs.mysql.com/peterg/2009/06/17/get-the-error-return-value-in-a-variable/
Basically it depends on declaring a handler for every possible known error state and capturing whatever info you can inside the handler. In response to that post, I wrote
http://rpbouman.blogspot.com/2009/06/mysql-stored-functions-impact-of.html
which clearly shows that cramming a lot of handlers in a stored routine is not free, even if they are not doing any interesting work at all.
So in my opinion, bug 11660 is not fixed. I think it should be really easy to fix if the route is taken of setting a server variable (just like proposed in 11660). However, I believe one of the reasons why it wasn’t picked up is because MySQL wants to support the SQL standard’s solution to this problem, something called diagnostics. The problem is that this is actually a very complicated design that supports a stack of errors, that can hold quite a bit of context at the time the error occurred – not something you could hack together in a sunday afternoon. (but to be fair, 5 years is…too long)
Personally, I think that indeed, people are not writing that many stored procedures (chicken – egg: would they write more of them if they had better error detection methods), and yes, if they are writing them, they probably don’t have much error handling at the stored procedure end – they still might have at the application level though.
Roland,
> However, I believe one of the reasons why it wasn’t picked
> up is because MySQL wants to support the SQL standard’s
> solution to this problem, something called diagnostics. The
> problem is that this is actually a very complicated design
> that supports a stack of errors, that can hold quite a bit of
> context at the time the error occurred – not something you
MySQL 5.5 has a diagnostic area whose code was augmented
and fixed in the process of implementing SIGNAL/RESIGNAL.
> could hack together in a sunday afternoon. (but to be fair,
> 5 years is…too long)
Actually, there is a prototype already if you look at the commits
associated with the bug report.
See Bug#11661.
Davi,
thanks for pointing out a solution is being worked on. Apologies for not being aware of the prototype. But frankly, it’s easy to lose track of these things – it has been quite a while between filing the bug and something becoming available.
Also, while I appreciate MySQL building a solution that is in line with standard, many users could have been saved lots of pain by implementing the simpler non-standard compliant solution proposed in bug 11660 – i.e. a simple session variable to hold simply and only the last error code (and another one for the message). Perhaps it would even have not held stored procedure adoption back.
Anyway, I suppose I’ll just wait for the diagnostics feature to pop up in some future MySQL version. Do you think it will appear in MySQL 5.5?
TIA,
Roland.