TinyInt parameters mess up data from consequent parameters.

matvei · Post by **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 · Post by **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 · Post by **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.

Pinturiccio · Post by **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 · Post by **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.

matvei · Post by **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.

Pinturiccio · Post by **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.

matvei · Post by **matvei** » Fri 09 Dec 2011 04:11

Sorry to bug you, but - can you give me any estimate on the fix time frame?

Pinturiccio · Post by **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 · Post by **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 · Post by **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

matvei · Post by **matvei** » Mon 26 Dec 2011 05:16

Thank you. Confirming that all my problems went way. Happy holidays, devs!