Bug #14115 Prepare() with compound statements breaks

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.

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.

Thanks for bug report. I was able to reproduce the bug. Test case is attached.

Test case

Attachment: 14115.zip (application/x-zip-compressed, text), 5.86 KiB.

Just curious if there is any more information on this one...
Thanks
_mark

This is a server issue: the binary protocol does not support multi-statements.

(And this is by design).

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.

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

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

We have no plans to support multi-statements in PS mode.

Workaround: use a stored procedure.

We've decided we are not going to support this.

Bug #113767 marked as duplicate of this one.