MySQL Bugs: #23342: Incorrect parsing of Datetime object for MySQL versions below 4.1

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:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

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

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.

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.

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.

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

Test case application

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

Thanks for your efforts, I'm analyzing new test case.

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.

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.

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

Fixed in 5.0.7 and 5.1.1

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.

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.

I'm glad to have helped. This software has certainly made my life a lot easier.

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.

A note has been added to the 5.0.7 and 5.1.1 changelogs.