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:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:1.0.6 OS:Microsoft Windows (Windows 2000)
Assigned to: Reggie Burnett CPU Architecture:Any

[18 Oct 2005 14:16] Mark Modrall
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.
[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.