MySQL Bugs: #21830: unsigned Int32 and Int64 do not function as expected with Connector/NET

Bug #21830	unsigned Int32 and Int64 do not function as expected with Connector/NET
Submitted:	25 Aug 2006 9:45	Modified:	30 Aug 2006 10:07
Reporter:	david	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.0.7 and 5 alpha	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	UInt32 UInt64 unsigned LAST_INSERT_ID() 0 zero bytes[]

Description:
CAUTION to everyone: unsigned INTS ARE NOT SUPPORTED in both 
connector/NET 
v1.0.7.30072 
AND 
the current MySQL Connector Net 5.0.0.0 alpha release 

How to repeat:
===============
steps to reproduce bug:
cmd2.CommandText = "select LAST_INSERT_ID()"; 
newfileid = cmd2.ExecuteScalar().ToString(); 

newfileid at this point is "0", where it should be an integer.
(in the case where the primary auto-increment column is of usigned Int32 or Int64).
===============
also see my post
http://forums.mysql.com/read.php?38,112416,112416#msg-112416

Suggested fix:
no idea sorry.

David

Based on this report I was unable to reproduce the bug. Here is the unit test I wrote to try and reproduce it.  Can you see how to change this unit test to demonstrate the problem?

execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY(id))");

MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(NULL, 'test')", conn);
cmd.ExecuteNonQuery();

cmd.CommandText = "select LAST_INSERT_ID()";
string s = cmd.ExecuteScalar().ToString();
Assert.AreEqual("1", s);

have tried to reproduce my own bug and can no longer do so!
i would consider the bug closed. i believe i may not have been inserting within the same connection and then calling last_insert_id() in a new connection! sorry!

however, one issue still remains. and that is, that a stored procedure that returns this last_insert_id() as UInt64, returns System.Byte[] rather than the value. for example:

            cmd.Parameters.Add("?newfileid", MySqlDbType.UInt64);
            cmd.Parameters["?newfileid"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            newfileid = cmd.Parameters["?newfileid"].Value.ToString();

this gives newfileid = "System.Byte[]"

whereas, if i just change
cmd.Parameters.Add("?newfileid", MySqlDbType.UInt64);
to
cmd.Parameters.Add("?newfileid", MySqlDbType.Int64);

returns newfileid = "98"
(although the underlying table field remains unsigned int 64).

the same goes for Int32.

David, please post small but complete test cases showing the problem you have.

CREATE PROCEDURE `insert_content`(OUT newfileid BIGINT) 
NOT DETERMINISTIC 
SQL SECURITY DEFINER 
COMMENT '' 
BEGIN 
INSERT INTO content (description, date_added, filetype) 
VALUES("desc", NOW(), "jpg"); 
SET newfileid =LAST_INSERT_ID(); 
END; 

in the cs file i have: 
cmd.Parameters.Add("?newfileid", MySqlDbType.UInt64); 
cmd.Parameters["?newfileid"].Direction = ParameterDirection.Output; 
cmd.ExecuteNonQuery(); 
string newfileid = cmd.Parameters["?newfileid"].Value.ToString(); 

this gives 
newfileid = "System.Byte[]"

David, I have to persist in asking for complete test case. You can take a look at, for example, report http://bugs.mysql.com/bug.php?id=20707 to see how to make one.

Since this is a bug report on two versions of connector/NET I have to write two answers:
  connector/NET 1.07: "Note. Please be aware that the DataColumn class in .NET 1.0 and 1.1 does not allow columns with type of UInt16, UInt32, or UInt64 to be autoincrement columns. If you plan to use autoincremement columns with MySQL, you should consider using signed integer columns." so this would be "Unsupported"
  connector/NET 5.0 alpha: Works as expected, thus "Can't repeat".