Bug #13541 Prepare breaks if a parameter is used more than once
Submitted: 27 Sep 2005 20:41 Modified: 6 Oct 2005 1:15
Reporter: Mark Modrall Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.4 OS:Microsoft Windows (Windows)
Assigned to: Reggie Burnett CPU Architecture:Any

[27 Sep 2005 20:41] Mark Modrall
Description:
If you have a query that re-uses an input parameter (say, in an ON DUPLICATE KEY UPDATE clause), Prepare() causes the execution to break claiming an invalid number of parameters.  Works fine if you *don't* use Prepare().

How to repeat:
	MySqlConnection conn = new MySqlConnection ("<connection string>" );
	conn.Open();
	MySqlCommand createCmd = conn.CreateCommand(),
		prepBug = conn.CreateCommand();
			
	createCmd.CommandText = "CREATE TABLE IF NOT EXISTS prepBug (" +
			"input TEXT NOT NULL, UNIQUE (input(100))," +
			"state INT NOT NULL, " +
			"score INT NOT NULL)";
	createCmd.ExecuteNonQuery();
	prepBug.CommandText = "Insert into prepBug "+
		"(input, state, score) " +
		"VALUES (?query, ?st, ?sc) ON DUPLICATE KEY UPDATE " +
		"state=state|?st;";
	prepBug.Parameters.Add (new MySqlParameter("?input", ""));
	prepBug.Parameters.Add (new MySqlParameter("?st", Convert.ToInt32(0)));
	prepBug.Parameters.Add (new MySqlParameter("?sc", Convert.ToInt32 (0)));
	prepBug.Prepare();

	prepBug.Parameters["input"].Value = "test";
	prepBug.Parameters["st"].Value = 1;
	prepBug.Parameters["sc"].Value = 42;
	int result = prepBug.ExecuteNonQuery();
// blows up.  Comment out Prepare() and it works just fine.  Look at the command's
// parameterMap and even though "?st" is repeated, it's in the map twice.

Suggested fix:
don't use Prepare()
[28 Sep 2005 9:47] Vasily Kishkin
Thanks for bug report. I was able to reproduce the bug. The test case is attached.
[28 Sep 2005 9:47] Vasily Kishkin
Test case

Attachment: 13541.zip (application/x-zip-compressed, text), 5.77 KiB.

[3 Oct 2005 16:53] Mark Modrall
By the by, the other workaround is to simply add the duplicated variable again with a different name.  To reference the sample, instead of having ?st in two places, use ?st and ?st2 and add another parameter named st2 with the same value.
[4 Oct 2005 15:02] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 1.0.7
[6 Oct 2005 1:15] Paul Dubois
Noted in 1.0.7 changelog.