MySQL Bugs: #27093: Exception when using large values in IN UInt64 parameters

Bug #27093	Exception when using large values in IN UInt64 parameters
Submitted:	13 Mar 2007 17:30	Modified:	26 Mar 2007 8:24
Reporter:	Eran kutner
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	5.0.5	OS:
Assigned to:		Target Version:
Tags:	Uint64, IN, SP

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;

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.");

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.

Reopened the bug.

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.

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

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

Fixed in 5.0.6

A note has been added to the 5.0.6 changelog.