Bug #24440 Prepared statement with more than 4 parameters and ON DUPLICATE KEY clause fails

• Submitted: 20 Nov 2006 15:06
• Modified: 21 Nov 2006 9:57
• Reporter: Gunnar Morling
• Status: Closed
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: Connctr./NET 1.0.7
• OS: Windows (Win XP SP2)
• CPU Architecture: Any

[20 Nov 2006 15:06] Gunnar Morling

Description:
Hello,

I have a simple table (see below) and want to insert/update data in(to) it using Connector/NET and a prepared statement. If this PS has more than 4 parameters, the call command.executeNonQuery() will throw an exception with the error message 

#HY000Incorrect arguments to mysql_stmt_execute

See the C# code below for more details. The error won't occur, if I

- ommit the command.prepare() call (could this be a hint, that the problem is related to bug #20707, because exactly that's the suggested solution there?)

- have only 4 instead of 5 parameters in the PS

- ommit the ON DUPLICATE KEY part of the query (which will be 

INSERT INTO bug_scenario (id, column_a, column_b, column_c, column_d, column_e) VALUES (1, ?a, ?b, ?c, ?d, ?e)

then.

I would appreciate any help on that problem. Kind regards,

Gunnar

How to repeat:
Create the table like that:

CREATE TABLE `bug_scenario` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `column_a` varchar(45) default NULL,
  `column_b` varchar(45) default NULL,
  `column_c` varchar(45) default NULL,
  `column_d` varchar(45) default NULL,
  `column_e` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Build a C# command line app with the following code:

string myConnectionString = "User Id=root;Password=admin;Host=localhost;Database=test";

MySqlConnection connection = new MySqlConnection(myConnectionString);
connection.Open();
            
MySqlCommand command = new MySqlCommand("INSERT INTO bug_scenario (id, column_a, column_b, column_c, column_d, column_e) VALUES (1, ?a, ?b, ?c, ?d, ?e) ON DUPLICATE KEY UPDATE column_a = ?a, column_b = ?b, column_c = ?c, column_d = ?d, column_e = ?e;", connection);

command.Parameters.Add(new MySqlParameter("?a", ""));
command.Parameters.Add(new MySqlParameter("?b", ""));
command.Parameters.Add(new MySqlParameter("?c", ""));
command.Parameters.Add(new MySqlParameter("?d", ""));
command.Parameters.Add(new MySqlParameter("?e", ""));

command.Prepare();

command.Parameters["?a"].Value = "AAA";
command.Parameters["?b"].Value = "BBB";
command.Parameters["?c"].Value = "CCC";
command.Parameters["?d"].Value = "DDD";
command.Parameters["?e"].Value = "EEE";

command.ExecuteNonQuery(); //will throw the exception
connection.Close();

[21 Nov 2006 9:57] Tonci Grgin

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation:
Hi Gunnar and thanks for your problem report but there is no info on NET fw version. Latest sources in branch do not exhibit the problem:
Executing command QUERY with text ='INSERT INTO bug_scenario (id,column_a, column_b, column_c, column_d, column_e) VALUES (1, 'AAA', 'BBB','CCC', 'DDD', 'EEE') ON DUPLICATE KEY UPDATE column_a = 'AAA', column_b = 'BBB',column_c = 'CCC', column_d = 'DDD', column_e = 'EEE''

As for c/NET 1.0.7, verified as described by you with provided test case on
 - MySQL 5.0.27BK on WinXP Pro SP2 localhost
 - Connector/NET 1.0.8RC SVN
 - NET FW 2.0
Server got:
1 Prepare     [1] INSERT INTO bug_scenario (id,column_a, column_b, column_c, column_d, column_e) VALUES (1, ?, ?,?, ?, ?) ON DUPLICATE KEY UPDATE column_a = ?, column_b = ?,column_c = ?, column_d = ?, column_e = ?
Server returned:
#HY000Incorrect arguments to mysql_stmt_execute