Bug #84220 cannot call specified stored procedure which name contains dot character
Submitted: 15 Dec 2016 16:39 Modified: 16 Dec 2016 6:30
Reporter: Wilhelm Liao Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.9 OS:Any
Assigned to: CPU Architecture:Any

[15 Dec 2016 16:39] Wilhelm Liao
Description:
In MySql 5.7 the name of the stored procedure contain dot character is valid. BUT call the stored procedure from C# will get MySqlException, even if the "`" symbol be used in quoting the name.

Example:

The following SQL script is work in MySql 5.7.
/* ***
 in SQL:
 */

USE `blackjack`;

CREATE PROCEDURE `app.create_game` (
    INOUT  _code             CHAR(32)      CHARSET latin1,
    IN     _table            INT UNSIGNED,
    IN     _players          TINYINT UNSIGNED,
    IN     _min_wager        INT UNSIGNED,
    OUT    _result           INT
  )
...

But the following code will get exception.
/*
 in C#

 using System.Data;
*/

MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();

MySqlCommand command = connection.CreateCommand();
command.CommandText = "`app.create_game`";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@_code"            , DBNull.Value);
command.Parameters.AddWithValue("@_table"           , 1);
command.Parameters.AddWithValue("@_players"         , 4);
command.Parameters.AddWithValue("@_min_wager"       , 100);
command.Parameters.Add("@_result", MySqlDbType.Int32);

command.Parameters["@_code"  ].Direction = ParameterDirection.InputOutput;
command.Parameters["@_result"].Direction = ParameterDirection.Output;

command.ExecuteNonQuery();  // <-- throw MySqlException:
                            //  Procedure or function 'create_game' cannot
                            //  be found in database 'app'.  

result = (int) command.Parameters["@_result"].Value;
if (result == 0)
{
    code = (string) command.Parameters["@_code"].Value;
}

How to repeat:
Let the "`" symbol can be resolved.

file: MySql.Data/StoredProcedure.cs

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -104,9
    private string FixProcedureName(string name)
    {
      string[] parts = name.Split('.');
      for (int i = 0; i < parts.Length; i++)
        if (!parts[i].StartsWith("`", StringComparison.Ordinal))
          parts[i] = String.Format("`{0}`", parts[i]);
      if (parts.Length == 1) return parts[0];
      return String.Format("{0}.{1}", parts[0], parts[1]);
    }

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -154,15
    public override void Resolve(bool preparing)
    {
      // check to see if we are already resolved
      if (resolvedCommandText != null) return;

      serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing;

      // first retrieve the procedure definition from our
      // procedure cache
      string spName = commandText;
      if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database))
        spName = Connection.Database + "." + spName;
      spName = FixProcedureName(spName);

      MySqlParameter returnParameter = GetReturnParameter();

Suggested fix:
file: MySql.Data/StoredProcedure.cs

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -104,9  +104,31 @@
-   private string FixProcedureName(string name)
-   {
-     string[] parts = name.Split('.');
-     for (int i = 0; i < parts.Length; i++)
-       if (!parts[i].StartsWith("`", StringComparison.Ordinal))
-         parts[i] = String.Format("`{0}`", parts[i]);
-     if (parts.Length == 1) return parts[0];
-     return String.Format("{0}.{1}", parts[0], parts[1]);
+   private string FixProcedureName(string spName)
+   {
+     string schema = null;
+     string name = spName;
+
+     int i = 0;
+     bool theCharWithinQuote = false;
+     while (i < spName.Length)
+     {
+       char ch = spName[i];
+       switch (ch)
+       {
+         case '`':
+           theCharWithinQuote = !theCharWithinQuote;
+           break;
+
+         case '.':
+           if (!theCharWithinQuote)
+             if (schema == null)
+               schema = spName.Substring(0, i);
+           break;
+       }
+
+       if (schema != null)
+         break;
+       i++;
+     }
+
+     if (!String.IsNullOrEmpty(schema))
+       schema = NormalizeName(schema);
+     else if (!String.IsNullOrEmpty(Connection.Database))
+       schema = NormalizeName(Connection.Database);
+     
+     if (i < spName.Length)
+       name = NormalizeName(
+           spName.Substring(i + 1, spName.Length - i - 1));
+
+     if (schema == null)
+       return name;
+     return schema + "." + name;
    }

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -154,15 +154,12 @@
    public override void Resolve(bool preparing)
    {
      // check to see if we are already resolved
      if (resolvedCommandText != null) return;

      serverProvidingOutputParameters = Driver.SupportsOutputParameters && preparing;

      // first retrieve the procedure definition from our
      // procedure cache
-     string spName = commandText;
-     if (spName.IndexOf(".") == -1 && !String.IsNullOrEmpty(Connection.Database))
-       spName = Connection.Database + "." + spName;
-     spName = FixProcedureName(spName);
+     string spName = FixProcedureName(commandText);

      MySqlParameter returnParameter = GetReturnParameter();
[15 Dec 2016 16:42] Wilhelm Liao
fixed StoredProcedure.cs

Attachment: StoredProcedure.cs (text/plain), 12.33 KiB.

[16 Dec 2016 6:30] Chiranjeevi Battula
Hello  Wilhelm Liao,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.
[16 Dec 2016 6:31] Chiranjeevi Battula
Screenshot

Attachment: 84220.JPG (image/jpeg, text), 183.71 KiB.