Delimiter Woes

Hands up who's had an error message when they have issued an SQL statement with ; delimiter straight after they created a procedure, or had an error when they tried to create a procedure without first setting the delimiter. 1..2..3.."is that a hand or are you trying to swat a fly".. I imagine it's a fair few of you, in fact I know it's a fair few because over the past year or so it's one of the biggest things people have asked me about or have had when trying to create procedures. Not why have they made a mistake but why MySQL needs you to change the delimiter when creating stored routines (SQL/PSM).

When you think about it it's rather obvious, because lines are terminated with a semi-colon in SQL/PSM the MySQL command line client gets confused about what your trying to do. The delimiter needs to be changed so MySQL can tell the difference between straight SQL and SQL/PSM.

So what's the problem? Well there are two things, firstly the MySQL Stored Procedure documentation mentions that you need to change the delimiter but only ever mentions //. This is a problem because the MySQL Query Browser (or at least the version I and others have been using) and some other interfaces don't like // as a delimiter, secondly a lot of people I have been in contact with are coming from other databases, in many cases Oracle and on Oracle you don't need to set the delimiter to something different. There is also the issue of people writing code examples that use other delimiters (which isn't a problem in it's self) and this again has caused confusion.

With this in mind I have raised a feature request (#15880) asking if MySQL could deal with SQL/PSM without having to change the delimiter. I know it seems like a minor issue but it's the little things that often make all the difference.