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: | |
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
[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.