Bug #23342 Incorrect parsing of Datetime object for MySQL versions below 4.1
Submitted: 16 Oct 2006 18:19 Modified: 20 May 2007 6:17
Reporter: Mark Stubbs Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.0.1 OS:Microsoft Windows (XP SP2)
Assigned to: CPU Architecture:Any

[16 Oct 2006 18:19] Mark Stubbs
Description:
Attempts to access tables containing pre-4.1 format datetime fields result in a 'Value was either too large or too small for an Int32.' exception.  This is because the code in MySqlDateTime.cs is only checking for timestamps, not datetime fields.

How to repeat:
Attempt to load any MySQL table containing Datetime fields into a DataSet from a pre 4.1 MySQL server.

Suggested fix:
Change line 338 in MySqlDateTime.cs from:

if (type == MySqlDbType.Timestamp && ! is41)
 return Parse40Timestamp(s);

to

if ((type == MySqlDbType.Timestamp || type == MySqlDbType.Datetime) && ! is41)
 return Parse40Timestamp(s);
[22 Oct 2006 13:44] Tonci Grgin
Hi Mark and thanks for your problem report. Please provide as much info as possible in the future.
Explanation:
 - MySQL server version
 - SQL statements needed to create test case
 - Small but complete test case exhibiting reported behavior
 - NET framework version

I can't repeat this problem using following code / tables:

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=munja;Database=test;UserID=root;Password=;PORT=3306";
            conn.Open();

            MySqlCommand command = new MySqlCommand();
            command.CommandText = "SELECT * FROM dt_test;";
            command.CommandType = CommandType.Text;
            command.Connection = (MySqlConnection)conn;

            MySqlDataReader dr = command.ExecuteReader();
            MessageBox.Show("Before READ");
            dr.Read();
            MessageBox.Show("After READ");

            MessageBox.Show(dr.GetName(0));
            MessageBox.Show(dr.GetDateTime(0).ToString());

            MessageBox.Show(dr.GetName(1));
            MessageBox.Show(dr.GetDateTime(1).ToString());
            
            dr.Close();
            MessageBox.Show("Ready");
            command.Dispose();
            conn.Close();

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table dt_test;
+---------+---------------------------------------------------------------------
------------------------------------------------------------+
| Table   | Create Table
                                                            |
+---------+---------------------------------------------------------------------
------------------------------------------------------------+
| dt_test | CREATE TABLE `dt_test` (
  `fld1` timestamp(14) NOT NULL,
  `fld2` datetime default NULL,
  PRIMARY KEY  (`fld1`)
) TYPE=MyISAM |
+---------+---------------------------------------------------------------------
------------------------------------------------------------+
1 row in set (0.00 sec)
...
mysql> select * from dt_test;
+----------------+---------------------+
| fld1           | fld2                |
+----------------+---------------------+
| 20061022153857 | 2006-10-19 00:00:00 |
| 20061022153938 | 2006-10-19 00:00:00 |
| 20061022153946 | 2006-10-19 00:00:00 |
| 20061022153948 | 2006-10-19 00:00:00 |
+----------------+---------------------+
4 rows in set (0.00 sec)

Environment: 
 - MySQL server 4.0.28BK on remote Suse 10.0 host, no defaults file
 - XP Pro SP 2 client, NET fw 2.0
 - connector/NET 5.0.1 beta SVN

Mark, please reopen this report if you think I missed something important.
[8 May 2007 18:29] Daniel Moore
I am having the same problem on my 4.0.18-max-log MySQL server.

I copied over values from my 5.0.16-standard test server to the aforementioned production server and my code broke whenever I tried to select a TIMESTAMP value (which was a DATETIME on the test server) with the int32 too large/small error.

Upon recompiling the 5.0.6 .NET connector with Mark's suggestion, everything worked just fine. Thanks, Mark.
[10 May 2007 13:29] Tonci Grgin
Now I'm confused... According to our tag in SVN tree, version 5.0.6 has this code on Ln 339:
	private MySqlDateTime ParseMySql(string s, bool is41)
	{
		if (type == MySqlDbType.Timestamp && !is41)
			return Parse40Timestamp(s);

So Daniel, what did you change and where?
In any case, please please do not post reports/questions without test cases.

Thanks for your interest in MySQL.
[10 May 2007 18:10] Daniel Moore
I didn't post it to the SVN tree, since we haven't confirmed it to work outside of Mark's and my case.

My test case:

Database schema and data:
-- 
-- Table structure for table `test`
-- 

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `test`
-- 

INSERT INTO `test` VALUES (1, '2007-05-10 12:58:24');
INSERT INTO `test` VALUES (2, '2007-05-10 12:58:30');
INSERT INTO `test` VALUES (3, '2007-05-10 12:58:33');

---------------------------------------
Application & Source (C#) Attached

log.txt:
[13:05.000]  Changing connection state from Closed to Connecting
[13:05.343]  Changing connection state from Connecting to Open
[13:05.078]  Attempting to read from database...
[13:05.078]  Filling DataTable...
[13:05.421]  System.OverflowException: Value was either too large or too small for an Int32.
[13:05.750]  Changing connection state from Open to Closed
[10 May 2007 18:11] Daniel Moore
Test case application

Attachment: MySQLTimeStampTest.zip (application/x-zip-compressed, text), 52.74 KiB.

[14 May 2007 14:33] Tonci Grgin
Thanks for your efforts, I'm analyzing new test case.
[14 May 2007 15:50] Tonci Grgin
Daniel, thanks for your efforts.

You misunderstood me about posting changes. I thought there is a requested change in our sources already so I wondered what did you change to make this test work.

In any case, here's what I've found:
 - MySQL 4.0.31BK on Suse 10.0 server
 - WinXP Pro SP2 client on NET FW 2.0 with c/NET 5.0.6 SVN
 - Test case fails as described reading *2nd* row of data.
During first read, field 'date' has type set to MySqlDbType.Timestamp thus  MySQLDateTime.cs, Ln: 339   
private MySqlDateTime ParseMySql(string s, bool is41)
   {
    if (type == MySqlDbType.Timestamp && !is41)
     return Parse40Timestamp(s);
is processed correctly. Reading second row of data, field 'date' gets type Datetime and above lines never gets processed resulting in error described.

Thanks again for nice test case.
[14 May 2007 17:36] Daniel Moore
Your welcome for the case.

As I said, I implemented Mark's suggestion. Specifically,
[ln 339]
private MySqlDateTime ParseMySql(string s, bool is41)
{
	if ((type == MySqlDbType.Timestamp || type == MySqlDbType.Datetime) && !is41)
		return Parse40Timestamp(s);
...

It works just fine with these changes.
[14 May 2007 19:28] 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/26637
[14 May 2007 19:29] Reggie Burnett
Fixed in 5.0.7 and 5.1.1
[14 May 2007 19:47] Tonci Grgin
I forgot one important piece of problem, my observations were true for builder.AllowZeroDateTime = true;. Using "AllowZeroDateTime = false" yields reported behavior from the first row fetched.
[15 May 2007 8:02] Tonci Grgin
Daniel, just to inform you, as of Rev. 722 (719) your test case functions for all servers from 4.0.x to 5.1.x. Thanks again for complete test case. You helped me a lot.
[19 May 2007 0:19] Daniel Moore
I'm glad to have helped. This software has certainly made my life a lot easier.
[19 May 2007 12:12] Tonci Grgin
Daniel, if I can get all the reporters to explain their case like you did my life would be much much easier. Since you took over this report fix was ready in 6(!) days (thanks to Reggie). If I'm stuck with badly formed report it can take forever which helps no one... Thanks again.
[20 May 2007 6:17] MC Brown
A note has been added to the 5.0.7 and 5.1.1 changelogs.