Bug #16884 Invalid DateTime Values from DataReader
Submitted: 29 Jan 2006 21:59 Modified: 29 Oct 2006 4:58
Reporter: Chad Lee Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 OS:Microsoft Windows (Windows XP SP2)
Assigned to: Reggie Burnett CPU Architecture:Any

[29 Jan 2006 21:59] Chad Lee
Description:
The MySqlDataReader returns the previous row's datetime value if the datetime value for the current row is null.

How to repeat:
Using MySql 5.0.15 and Asp.Net 2.0 w/ Visual Studio 2005

Run following sql script:

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `testing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime,
  PRIMARY KEY(`id`)
)
TYPE = INNODB;
INSERT INTO testing (date) VALUES
(null),
(now()),
(null),
(ADDDATE(now(), INTERVAL 31 DAY)),
('0000-00-00 00:00:00');

Then step through the following c# code:

int id;
DateTime date;
MySqlDateTime myDate;

using (MySqlConnection conn = new MySqlConnection("server=localhost;database=test;uid=root;pwd=mars26"))
{
    using (MySqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM testing";

        conn.Open();
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            id = reader.GetInt32(0);
            myDate = reader.GetMySqlDateTime(1);
            date = (DateTime)myDate;
        }

        reader.Close();
    }
}

On the first row of results, myDate = 0000-00-00 00:00:00 corresponding to the null DateTime value in the database.  Consequently date = DateTime.MinValue which is the desired behavior.  However, on the third row of results, the value of myDate should be 0000-00-00 00:00:00 again.  But, instead, the value of myDate stays the same as it was before...the value of the date for the second row of results.
[29 Jan 2006 22:28] Chad Lee
Tried it again with this code -- same results -- date remains the same as previous result when current result is null:

int id;
DateTime date;

using (MySqlConnection conn = new MySqlConnection("server=localhost;database=test;uid=root;pwd=***"))
{
    using (MySqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM testing";

        conn.Open();
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            id = reader.GetInt32(0);
            try
            {
                date = reader.GetDateTime(1);
            }
            catch (MySqlConversionException)
            {
                date = DateTime.MinValue;
            }
        }

        reader.Close();
    }
}
[21 Feb 2006 9:15] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with never version of MySQL server, 5.0.18, and inform about the results.
[23 Feb 2006 1:53] Chad Lee
Just updated MySql to 5.0.18.  Same results as posted above.  I do not think it is a bug with the MySql server because Query Browser shows the correct values and so does phpMyAdmin and the MySql command line client.  Its just the .net connector that gets the values wrong.
[1 Mar 2006 10:03] Simonas Starkus
I found a solution, that just works fine for me it returns DBNUll value if field value is 000-00-00 :)

maybe someone will include in new release of connector??!??

you have to modify function:

 Mysql.Data.MySqlClient.MySqlDataReader.GetValue(int i)
here is the code:

public object GetValue(int i)
		{
			if (! isOpen) throw new Exception("No current query in data reader");
			if (i >= fields.Length) throw new IndexOutOfRangeException();

			MySqlValue val = GetFieldValue(i);
			if (val.IsNull) return DBNull.Value;

			// if the column is a date/time, then we return a MySqlDateTime
			// so .ToString() will print '0000-00-00' correctly
			if (val is MySqlDateTime) 
			{
				MySqlDateTime dt = (MySqlDateTime)val;
				if (! dt.IsValidDateTime && connection.Settings.ConvertZeroDateTime)
					return DateTime.MinValue;
				else if (connection.Settings.AllowZeroDateTime) 
					return val;

// changes made here
				else{
					try
					{
						return dt.GetDateTime();
					}
// if conversion did not succeeded then return DBNULL that solves the problem
					catch(MySqlConversionException ex)
					{
						return DBNull.Value;
					}
// end of changes

				}
			}

			return val.ValueAsObject;
		}
[4 Apr 2006 7:45] Tonci Grgin
Verified on WinXP and Asp.Net 2.0 w/ Visual Studio 2005 as described by reporter. Additional testing done on MyISAM and several server versions. It doesn't seem to be server / storage engine issue, just Connector/NET.
[26 Apr 2006 9:12] Thomas Kr├╝ger
I think it is caused by the MySqlDateTime.ReadValue method, which is reading to much data in case of 0000-00-00 etc. A fix working for me is below:

internal override MySqlValue ReadValue(PacketReader reader, long length)
		{
			if (length >= 0) 
			{
				string value = reader.ReadString( length );
				return ParseMySql( value, reader.Version.isAtLeast(4,1,0) );
			}

			long bufLength = reader.ReadByte();

            if (bufLength <= 3) // dont read to much in case of 0000-00-00
                return new MySqlDateTime(0, 0, 0, 0, 0, 0, mySqlDbType);

			int year = reader.ReadInteger(2);
			int month = reader.ReadByte();
			int day = reader.ReadByte();
			int hour = 0, minute = 0, second = 0;

			if (bufLength > 4) 
			{
				hour = reader.ReadByte();
				minute = reader.ReadByte();
				second = reader.ReadByte();
			}
		
			if (bufLength > 7)
				reader.ReadInteger(4);
		
			return new MySqlDateTime( year, month, day, hour, minute, second, mySqlDbType );
		}
[18 Sep 2006 15:27] 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/12138
[29 Oct 2006 4:58] MC Brown
A note has been added to the 1.0.8 changelog.