Bug #54386 MySqlDataAdapter Bulk Insert fails if statement has expression with parentheses
Submitted: 10 Jun 2010 5:58 Modified: 28 Jun 2010 15:31
Reporter: Foster Langbein Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.2.3 OS:Windows
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: bulk insert, MySqlDataAdapter

[10 Jun 2010 5:58] Foster Langbein
Description:
If you use a MySqlDataAdapter with an insert command where the VALUES clause contains an expression with parentheses in it and set the adapter.UpdateBatchSize parameter > 1, then the call to adpater.Update will EITHER fail with an 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 '' at line 1"
OR
will fail to batch the commands executing each insert one by one.

How to repeat:
Make a new console application with the following source (based on one of the test cases):

using System.Data;
using MySql.Data.MySqlClient;

namespace BulkInsertTest
{
    class Program
    {
        static void Main()
        {
            MySqlConnection conn = new MySqlConnection("Server = aaa; Database = Test; Uid = root; Pwd = aaa");
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("CREATE TABLE Test (id INT, expr INT, name VARCHAR(20), PRIMARY KEY(id))", conn);
            cmd.ExecuteNonQuery();

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommand ins = new MySqlCommand("INSERT INTO test (id, expr, name) VALUES (?p1, (?p2 * 2) + 3, ?p3)", conn);
            da.InsertCommand = ins;
            ins.UpdatedRowSource = UpdateRowSource.None;
            ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
            ins.Parameters.Add("?p2", MySqlDbType.Int32).SourceColumn = "expr";
            ins.Parameters.Add("?p3", MySqlDbType.VarChar, 20).SourceColumn = "name";

            DataTable dt = new DataTable();
            da.Fill(dt);

            for (int i = 1; i <= 100; i++)
            {
                DataRow row = dt.NewRow();
                row["id"] = i;
                row["expr"] = i;
                row["name"] = "name " + i;
                dt.Rows.Add(row);
            }

            da.UpdateBatchSize = 10;
            da.Update(dt);
        }
    }
}

Running this should fail with the exception at the line da.Update(dt);

Suggested fix:
The reason this occurs is that in the source for command.cs there is the following in the method called GetCommandTextForBatching that sets up insert statements for batching at lines 820-824 (this is version 6.2.3): 
while (token != null && token != ")") 
{ 
batchableCommandText += token; 
token = tokenizer.NextToken(); 
} 
which is supposed to be searching for the ending parenthesis of the VALUES clause. In fact it will find the very first closing parenthesis which may not be the end one if the VALUES clause contains an expression. 

This doesn't look correct to me - according to the MySQL docs: 
http://dev.mysql.com/doc/refman/5.1/en/insert.html 
the items in the VALUES clause are allowed to be expressions and so potentially contain any number of closing parentheses. batchableCommandText therefore winds up with a truncated clause and MySQL generates the syntax error.

My proposed fix would be something along the following lines:

        internal string GetCommandTextForBatching()
        {
            if (batchableCommandText == null)
            {
                // if the command starts with insert and is "simple" enough, then
                // we can use the multi-value form of insert
                if (String.Compare(CommandText.Substring(0, 6), "INSERT", true) == 0)
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT @@sql_mode", Connection);
                    string sql_mode = cmd.ExecuteScalar().ToString().ToUpper(CultureInfo.InvariantCulture);
                    MySqlTokenizer tokenizer = new MySqlTokenizer(CommandText);
                    tokenizer.AnsiQuotes = sql_mode.IndexOf("ANSI_QUOTES") != -1;
                    tokenizer.BackslashEscapes = sql_mode.IndexOf("NO_BACKSLASH_ESCAPES") == -1;
                    string token = tokenizer.NextToken().ToLower(CultureInfo.InvariantCulture);
                    while (token != null)
                    {
                        if (token.ToUpper(CultureInfo.InvariantCulture) == "VALUES" && 
                            !tokenizer.Quoted)
                        {
                            token = tokenizer.NextToken();
                            Debug.Assert(token == "(");
                            token = FindMatchingParenthesis(tokenizer, token);

                            if (token != null && (token == "," || 
                                token.ToUpper(CultureInfo.InvariantCulture) == "ON"))
                            {
                                batchableCommandText = null;
                                break;
                            }
                        }
                        token = tokenizer.NextToken();
                    }
                }
            }

            return batchableCommandText;
        }

        private string FindMatchingParenthesis(MySqlTokenizer tokenizer, string token)
        {
            while (token != null && token != ")")
            {
                batchableCommandText += token;
                token = tokenizer.NextToken();
                if (token == "(")
                    token = FindMatchingParenthesis(tokenizer, token);
            }
            if (token != null)
                batchableCommandText += token;
            token = tokenizer.NextToken();

            return token;
        }

Possibly the FindMatchingParenthesis method should belong in the tokenizer object.
[16 Jun 2010 12:03] Tonci Grgin
Hi Foster and thanks for your report.

Verified as described:
mysql Information: 3 : 1: Query Opened: INSERT INTO bug54386 (id, expr, name) VALUES (1, (1 * 2) + 3, 'name 1'), (2,(2*2), (3,(3*2), (4,(4*2), (5,(5*2), (6,(6*2), (7,(7*2), (8,(8*2), (9,(9*2), (10,(10*2), (11,(11*2), (12,(12*2), (13,(13*2), (14,(14*2), (15,(15*2), (16,(16*2), (17,(17*2), (18,(18*2), (19,(19*2), (20,(20*2), (21,(21*2), (22
mysql Information: 14 : 1: Query Normalized: INSERT INTO bug54386 (id, expr, name) VALUES (?, (? * ?) + ?, ?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?,(?*?), (?

Test case:
            MySqlConnection conn = new MySqlConnection("Server=xx;Database=test;Uid=xx;Pwd=xx;allow batch=true;logging=True");
            conn.Open();
            MySqlCommand cmd = new MySqlCommand("DROP TABLE IF EXISTS bug54386", conn);
            cmd.ExecuteNonQuery();
            cmd.CommandText = "CREATE TABLE bug54386 (id INT, expr INT,name VARCHAR(20), PRIMARY KEY(id))";
            cmd.ExecuteNonQuery();
            int changed = 0;
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM bug54386", conn);
            MySqlCommand ins = new MySqlCommand("INSERT INTO bug54386 (id, expr, name) VALUES (?p1, (?p2 * 2) + 3, ?p3)", conn);
            ins.UpdatedRowSource = UpdateRowSource.None;
            ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
            ins.Parameters.Add("?p2", MySqlDbType.Int32).SourceColumn = "expr";
            ins.Parameters.Add("?p3", MySqlDbType.VarChar, 20).SourceColumn = "name";
            DataTable dt = new DataTable();
            da.Fill(dt);
            da.InsertCommand = ins;
            da.UpdateBatchSize = 100;
            for (int i = 1; i <= 100; i++)
            {
                DataRow row = dt.NewRow();
                row["id"] = i;
                row["expr"] = i;
                row["name"] = "name " + i;
                dt.Rows.Add(row);
            }

            changed = da.Update(dt);
            Console.Out.WriteLine("INSERT INTO bug54386: {0} rows", changed);
[17 Jun 2010 0:20] 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/111335

815 Vladislav Vaintroub	2010-06-17
      Fix bug #54386  expression with parentheses in INSERT leads to invalid
      query when using batching. Fix is to ensure open and closed parens are
      balanced when generating batchable command text
[17 Jun 2010 0:28] Vladislav Vaintroub
pushed to 6.0 upwards
[24 Jun 2010 10:17] Vladislav Vaintroub
fixed in 6.0.7, 6.1.5, 6.2.4, and 6.3.3+
[28 Jun 2010 15:31] Tony Bedford
An entry has been added to the 6.0.7, 6.1.5, 6.2.4, 6.3.3 changelogs:

If MySqlDataAdapter was used with an INSERT command where the VALUES clause contained an expression with parentheses in it, and set the adapter.UpdateBatchSize parameter to be greater than one, then the call to adpater.Update either generated an exception or failed to batch the commands, executing each insert individually.