| Bug #14115 | Prepare() with compound statements breaks | ||
|---|---|---|---|
| Submitted: | 18 Oct 2005 14:16 | Modified: | 29 Sep 2008 22:14 |
| Reporter: | Mark Modrall | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 1.0.6 | OS: | Windows (Windows 2000) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[18 Oct 2005 14:59]
Mark Modrall
Just to put some numbers on it, I've run some comparisons between taking the compound statements apart and using multiple, Prepare()d statements vs using a single, compound, non-Prepare()d statement. My test case involves getting the last_insert_id() and using it as a foreign key to another table, so there are 3 statements in the former, and one in the latter. All are running over a network to a remote server. Using multiple prepared statements: ~7.5 seconds / 5000 inserts Using a compound, unprepared statement: ~11.2 seconds / 5000 inserts Using multiple unprepared statements: ~15.5 seconds / 5000 inserts To put it another way, Prepare() on highly repetitive statements cuts it by half. Using a single compound statement cuts it by a third. If you could use Prepare() on the single compound statement it seems reasonable to think you could cut it by 2/3.
[20 Oct 2005 7:18]
Vasily Kishkin
Thanks for bug report. I was able to reproduce the bug. Test case is attached.
[20 Oct 2005 7:19]
Vasily Kishkin
Test case
Attachment: 14115.zip (application/x-zip-compressed, text), 5.86 KiB.
[5 Jan 2006 19:37]
Mark Modrall
Just curious if there is any more information on this one... Thanks _mark
[22 Jul 2006 15:59]
Konstantin Osipov
This is a server issue: the binary protocol does not support multi-statements.
[22 Jul 2006 15:59]
Konstantin Osipov
(And this is by design).
[26 Sep 2006 20:14]
Reggie Burnett
This is currently expected behavior, however I have created a worklog entry to fix this in a later version. In order to fix this, the provider will need to detect that multiple statements have been passed in prepare them individually. I don't expect this to be accomplished in the 5.0.x timeframe but would expect it in the 5.1 provider.
[26 Sep 2006 20:15]
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/12570
[26 Sep 2006 20:15]
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/12571
[29 Sep 2008 22:14]
Konstantin Osipov
We have no plans to support multi-statements in PS mode.
[29 Sep 2008 22:14]
Konstantin Osipov
Workaround: use a stored procedure.
[5 Mar 2009 20:30]
Reggie Burnett
We've decided we are not going to support this.
[6 Feb 12:32]
MySQL Verification Team
Bug #113767 marked as duplicate of this one.
Description: If you use a compound sql statement (i.e. Insert ...; select last_insert_id()) in a MySqlCommand, it works fine if you don't use Prepare() but throws a parsing error when Prepare() is called. How to repeat: string createTable = "CREATE TABLE IF NOT EXISTS insertTest (\n"+ "qid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\n" + "test1 INT UNSIGNED,\n" + "test2 INT UNSIGNED\n" + ") ;"; createCmd.CommandText = createTable; createCmd.ExecuteNonQuery(); MySqlCommand insTest = conn.CreateCommand(); insTest.CommandText = "INSERT INTO insertTest (test1, test2) VALUES (?test1, ?test2) ON DUPLICATE KEY UPDATE qid=last_insert_id(qid); Select last_insert_id()"; insTest.Parameters.Clear(); insTest.Parameters.Add (new MySqlParameter("?test1", MySqlDbType.Int32)); insTest.Parameters.Add (new MySqlParameter("?test2", MySqlDbType.Int32)); // insTest.Prepare(); -- causes sql parse error if called for (int i=0; i < 500; i++) { insTest.Parameters["test1"].Value = i; insTest.Parameters["test2"].Value = i; uint pid = (uint)insTest.ExecuteScalar(); } Suggested fix: you can get it to work not using Prepare(), but it would be nice to get the performance benefit of it.