MySQL Bugs: #48101: MySqlConnection.GetSchema on "Indexes" throws when there's a table named "b`a`d"

Bug #48101	MySqlConnection.GetSchema on "Indexes" throws when there's a table named "b`a`d"
Submitted:	15 Oct 2009 20:29	Modified:	23 Oct 2009 13:38
Reporter:	Andrey Belykh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.1.2.0	OS:	Windows
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
MySqlConnection.GetSchema on "Indexes" throws when there's a table named "b`a`d"

How to repeat:
1) Create a table like this:
CREATE TABLE `b``a``d` (a decimal(36,2));

2) Call 
DataTable schemaPrimaryKeys = connection.GetSchema(
  "Indexes",
  new string[] { null, schemaName, "b`a`d"});

Exception {"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 'a`d`' at line 1"} is thrown

Problem

Attachment: Bug48101.jpg (image/jpeg, text), 20.65 KiB.

Hi Andrey and thanks for your report.

The attached image shows where the problem lies. In any case, I do not see the need for S2, this is S3 at most as fine workaround exists:
   DataTable dt = cn.GetSchema("Indexes", new string[] { null, "test", "`b``a``d`" });

I am not convinced this is a valid bug to start with so assigning to Reggie to make final ruling. Otherwise, verified as described.

Test case:
            using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=**;UserID=**;Password=**;PORT=**;logging=True;charset=utf8"))
            {
                cn.Open();
                try
                {
                    MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS `b``a``d`", cn);
                    cmdCreateTable.ExecuteNonQuery();
                    cmdCreateTable.CommandText = "CREATE TABLE `b``a``d` (a decimal(10,2));";
                    cmdCreateTable.ExecuteNonQuery();
                    cmdCreateTable.CommandText = "INSERT INTO `b``a``d` VALUES (999.99);";
                    cmdCreateTable.ExecuteNonQuery();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = cn;

                    cmd.CommandText = "SELECT * FROM `b``a``d`";
                    MySqlDataReader rd = cmd.ExecuteReader();
                    rd.Read();
                    Console.Out.WriteLine("Result is: " + rd[0]);
                    rd.Close();

                    DataTable dt = cn.GetSchema("Indexes", new string[] { null, "test", "`b``a``d`" }); << Runs
                    DataTable dt = cn.GetSchema("Indexes", new string[] { null, "test", "b``a``d" }); << Fails

                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    da.Fill(dt);

                    //DisplayData(dt);
                    dt.Clear();
                    dt.Dispose();
                    da.Dispose();

                }
                catch (Exception ex)
                {
                    Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
                    throw;
                }
            }

Output with "`b``a``d`" line:
====== Query logged ======
Time of query: 16.10.09 8:47:19
host: **
time of execution: 0.0004989 seconds (0.4989 milliseconds
-- Result --
Rows returned: 1
Command text:
SELECT * FROM `b``a``d`
====== End of Query ======

INDEX_CATALOG = 
INDEX_SCHEMA = 
INDEX_NAME = 
TABLE_NAME = 
UNIQUE = 
PRIMARY = 
TYPE = 
COMMENT = 
a = 999.99
============================

Thank you. The only thing, if you are going to fix it in the future, I would like that this workaround to still work. 

I see the only problem would be with tables starting with ` (such as "`a" or ```a`). Maybe you could create another function overload to pass bool (something like unescapeName).

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/87843

712 Reggie Burnett	2009-10-22
      - fixed indexes schema collection so that it still works with bad table names such as b``a`d (bug #48101)

fixed in 5.2.8, 6.0.5, and 6.1.3

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/87857

713 Reggie Burnett	2009-10-22
      - fixed indexes schema collection so that it still works with bad table names such as b``a`d (bug #48101)

An entry was added to the 5.2.8, 6.0.5 and 6.1.3 changelogs:

If MySqlConnection.GetSchema was called for "Indexes" on a table named “b`a`d” as follows:

DataTable schemaPrimaryKeys = connection.GetSchema(
  "Indexes",
  new string[] { null, schemaName, "b`a`d"});

Then the following exception was generated:

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 'a`d`' at line 1