TinyInt parameters mess up data from consequent parameters.

TinyInt parameters mess up data from consequent parameters.

Postby matvei » Wed 30 Nov 2011 00:09

Hello.

I am using parametrized statements for INSERT, involving a couple columns of type TinyInt. I noticed that if I have a TinyInt parameter in a prepared statement, consequent parameters' values are not sent correctly. Here is an example:

Simple schema:
Code: Select all
CREATE TABLE IF NOT EXISTS `tiny_int_test_case` (
  `key` int(11) NOT NULL AUTO_INCREMENT,
  `value` tinyint(4) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=456;


The update query, and parameter setup:
Code: Select all
string UpdateCommandText = "UPDATE `tiny_int_test_case` SET `value`=? WHERE `key`=?;";
var updateCmd = new MySqlCommand( UpdateCommandText, connection );
updateCmd.Parameters.Add( "value", MySqlType.TinyInt );
updateCmd.Parameters.Add( "key", MySqlType.Int );
updateCmd.Prepare();


And finally the code that calls it:
Code: Select all
updateCmd.Parameters[0].Value = (byte)123;
updateCmd.Parameters[1].Value = 456;
updateCmd.ExecuteNonQuery();


One would expect the query produced to be:
Code: Select all
UPDATE `tiny_int_test_case` SET `value`=123 WHERE `key`=456


However, mysqld's general log shows that it receives
Code: Select all
UPDATE `tiny_int_test_case` SET `value`=123 WHERE `key`=116736


The expected parameter value is 456 (0x1C8), and the actual parameter value is 116736 (0x1C800). Here is a simple test case: TinyIntTestCase.zip (2.8 KB)

Please let me know if this is something I can fix, or if it's a bug.
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20

Postby matvei » Wed 30 Nov 2011 00:16

I should probably mention that I am using .NET Framework 3.5 sp1, Devart.Data.dll version 5.0.343.0, Devart.Data.MySql.dll version 6.50.244.0, and mysqlnd 5.0.8-dev.
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20

Postby matvei » Wed 30 Nov 2011 01:25

Quick correction for the first post: I am using parametrized statements for UPDATE, not insert. I did, however, just test this with insert, and got exact same problems.
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20

Postby Pinturiccio » Thu 01 Dec 2011 09:22

We tried to reproduce the issue with .NET Framework 3.5 sp1, dotConnect for Oracle 6.50.244, and MySQL Server versions 5.0.87 and 5.5.9, but we could not do it.

Try performing the following:

1. Do not use updateCmd.Prepare();
2. Or do not specify the datatype of parameters:
Code: Select all
updateCmd.Parameters.Add("value", (byte)ExpectedValue);
updateCmd.Parameters.Add("key", insertId);


Please notify us if it helps you or not. Looking forward to your reply.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Thu 01 Dec 2011 13:51

We have reproduced the issue. We will investigate the issue and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby matvei » Fri 02 Dec 2011 01:24

Thank you for suggestions and for a quick response.

If I do not use updateCmd.Prepare(), the query works properly. This is not really an option for me though, due to performance considerations.

It also works properly if I do not specify datatypes of parameters, with or without updateCmd.Prepare(). I am not sure if there are any upsides/downsides to specifying datatypes explicitly - I could not find any information regarding this in the documentation.
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20

Postby Pinturiccio » Fri 02 Dec 2011 13:53

When you you don't specify a parameter type you have to write less code. But in this case our provider will set parameter type based on parameter value. Some parameter types represent the same CLR type. For example: MySqlType.Date, MySqlType.DateTime, MySqlType.TimeStamp are represented by System.DateTime. Or MySqlType.Year and MySqlType.SmallInt are represented by System.Int16. When you do not specify a parameter type then dotConnect for MySQL will set the parameter type that represents the same CLR type, and it may differ from expected MySqlType. In this case, behavior can be unexpected.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby matvei » Fri 09 Dec 2011 04:11

Sorry to bug you, but - can you give me any estimate on the fix time frame?
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20

Postby Pinturiccio » Tue 13 Dec 2011 09:58

We are planning to fix the problem in a month. Could you please using temporary workarounds (do not specify the MySqlType.TinyInt type for the corresponding parameter explicitly) until the fix is implemented?
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Thu 15 Dec 2011 14:20

We have fixed the bug with the MySqlType.TinyInt parameter type. We will post here when the corresponding build of dotConnect for MySQL is available for download.
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Fri 23 Dec 2011 08:43

New build of dotConnect for MySQL 6.60.268 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22976
Pinturiccio
Devart Team
 
Posts: 1933
Joined: Wed 02 Nov 2011 09:44

Postby matvei » Mon 26 Dec 2011 05:16

Thank you. Confirming that all my problems went way. Happy holidays, devs!
matvei
 
Posts: 6
Joined: Tue 29 Nov 2011 23:20


Return to dotConnect for MySQL