MySQL Bugs: #16884: Invalid DateTime Values from DataReader

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

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.

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

Thank you for a problem report. Please, try to repeat with never version of MySQL server, 5.0.18, and inform about the results.

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.

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

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.

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

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

A note has been added to the 1.0.8 changelog.