Bug #27093 Exception when using large values in IN UInt64 parameters
Submitted: 13 Mar 2007 16:30 Modified: 26 Mar 2007 6:24
Reporter: Eran Kutner Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.0.5 OS:
Assigned to: CPU Architecture:Any
Tags: IN, SP, Uint64

[13 Mar 2007 16:30] Eran Kutner
Description:
The current implementation treats UInt64 as Int64. When a value larger than long.MaxValue is used in a parameter it causes an exception when executing the stored procedure because it is assigned to a Int64 type.

How to repeat:
create any stored procedure which accepts unsigned bigint value.
Call that SP using the .NET connector, and assign a large value like long.MaxValue+10

Suggested fix:
Line number 439 in parameter.cs is currently:
case DbType.UInt64: mySqlDbType = MySqlDbType.Int64; break;

but it should be:
case DbType.UInt64: mySqlDbType = MySqlDbType.UInt64; break;
[13 Mar 2007 20:12] Tonci Grgin
Hi Eran and thanks for your observation. Although I agree that line is present in the code, I don't see test case supporting your claims. You should provide us with as much info as you can especially when setting severity to S1.

Now, I've slightly reworked our standard test and got correct results:
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - NET fw 2.0, c/NET 5.0.5 SVN

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306;Allow Zero Datetime=True";//;
            conn.Open();

            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS Bug27093", conn);
            cmdCreateTable.CommandTimeout = 0;
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE Bug27093 (id INT(10) UNSIGNED AUTO_INCREMENT, PRIMARY KEY (id)) ";
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = ("drop procedure if exists spTestBug27093");
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = ("CREATE PROCEDURE spTestBug27093 (OUT id BIGINT UNSIGNED) " +
                      "BEGIN INSERT INTO Bug27093 VALUES (NULL); SET id=9223372036854775957; END");//LAST_INSERT_ID()
            cmdCreateTable.ExecuteNonQuery();

            MySqlCommand cmd = new MySqlCommand("spTestBug27093", conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("?id", MySqlDbType.UInt64);
			cmd.Parameters[0].Direction = ParameterDirection.Output;
			cmd.ExecuteNonQuery();

            object o = cmd.Parameters[0].Value;
			Assert.IsTrue(o is ulong);
            Assert.AreEqual(9223372036854775957, o);
            Console.WriteLine("Done.");
[15 Mar 2007 18:26] Eran Kutner
Sorry about that, I opened it as critical because it causes an exception with no workaround I could think of and I thought it would be easily recreated.

Here are the steps to recreate:

Create a table:

DROP TABLE IF EXISTS `tests`.`t1`;
CREATE TABLE  `tests`.`t1` (
  `f1` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then create a stored procedure:

CREATE PROCEDURE `tests`.`sp1` (
  in _val bigint unsigned
)
BEGIN
  insert into t1 set f1=_val;
END

Now run this code:

	DbConnection conn = new MySql.Data.MySqlClient.MySqlConnection("Database=tests;Server=localhost;User ID=root;Charset=utf8;password=xxx;");
	conn.Open();
	DbCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
	cmd.Connection = conn;
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = "sp1";
	DbParameter param = cmd.CreateParameter();
	param.DbType = DbType.UInt64;
	param.Direction = ParameterDirection.Input;
	param.ParameterName = "?_val";
	ulong bigval = long.MaxValue;
	bigval += 1000;
	param.Value = bigval;
	cmd.Parameters.Add(param);
	cmd.ExecuteNonQuery();

When you run the ExecuteNonQuery(), you get the exception:

An unhandled exception of type 'System.OverflowException' occurred in mscorlib.dll

Additional information: Value was either too large or too small for an Int64.
[15 Mar 2007 18:35] Eran Kutner
Reopened the bug.
[16 Mar 2007 16:20] Tonci Grgin
Eran, we have two problems here:

Parameter.cs, line 439: case DbType.UInt64: mySqlDbType = MySqlDbType.Int64; should indeed read:
case DbType.UInt64: mySqlDbType = MySqlDbType.UInt64;

About test case:
>	ulong bigval = long.MaxValue;
>	bigval += 1000;
will never work as it evaluates to -9223372036854775808 (in fact won't compile without *uncheck* at all) which is not acceptable to defined SP and underlaying table... You should use "ulong.MaxValue-50" instead.

Thanks for your report.
[16 Mar 2007 21:25] Eran Kutner
Hi Tonci, long.MaxValue is a positive integer (the largest positive integer that can be stored in a signed int (=2^31-1). There should be no problem assigning it to a ulong which has a max value of 2^32-1. So my sample code should, and does compile just fine.
Anyway, the important thing is that you see the bug and will incorporate the fix to the main code base, so the fix will be included in the next version.

I would also like to thank you and the rest of the guys for the wonderful job you're done with this connector.

Eran
[20 Mar 2007 13:41] 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/22343
[20 Mar 2007 13:42] Reggie Burnett
Fixed in 5.0.6
[26 Mar 2007 6:24] MC Brown
A note has been added to the 5.0.6 changelog.