Bug #29526 syntax error: "show create procedure" with catalog names containing hyphens
Submitted: 3 Jul 2007 18:17 Modified: 6 Jul 2007 2:50
Reporter: Maxim Mass Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5.0.6 OS:Any
Assigned to: CPU Architecture:Any

[3 Jul 2007 18:17] Maxim Mass
Description:
I'm getting a syntax error when calling a sproc within a catalog whose name contains a hyphen. The procedure cache is used to retrieve the sproc body with the SHOW CREATE PROCEDURE command. This results in a syntax error when the sproc is in a catalog containing a hyphen (or possibly other characters).

How to repeat:
For example

SHOW CREATE PROCEDURE foo-bar.some_sproc;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-bar.some_sproc'

The same command with an escaped catalog name will work correctly.
SHOW CREATE PROCEDURE `foo-bar`.some_sproc;

Storing the `quoted` version of the catalog name in the connection string results in other errors--such as permissions to the db (it doesn't find that exact catalog name).

Suggested fix:
Here's a possible fix to the .net connector that seems to work for me. I'm not certain though if it should be fixed here in the connector or in the mysql parser.

Index: ISSchemaProvider.cs
===================================================================
--- ISSchemaProvider.cs	(revision 706)
+++ ISSchemaProvider.cs	(working copy)
@@ -352,7 +352,7 @@
 
             foreach (DataRow routine in routines.Rows)
             {
-                string showCreateSql = String.Format("SHOW CREATE {0} {1}.{2}",
+                string showCreateSql = String.Format("SHOW CREATE {0} `{1}`.{2}",
                     routine["ROUTINE_TYPE"], routine["ROUTINE_SCHEMA"],
                     routine["ROUTINE_NAME"]);
                 cmd.CommandText = showCreateSql;
[5 Jul 2007 19:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30405
[5 Jul 2007 19:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30405
[5 Jul 2007 19:34] Reggie Burnett
Fixed in 5.0.8 and 5.1.3
[6 Jul 2007 2:47] Paul Dubois
Noted in 5.0.8, 5.1.13 changelogs.

Calling SHOW CREATE PROCEDURE for routines with a hyphen in the
catalog name produced a syntax error.
[18 Jul 2007 22:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31140