MySQL Bugs: #55425: ConvertZeroDateTime not honored by CommandBuilder

Bug #55425	ConvertZeroDateTime not honored by CommandBuilder
Submitted:	21 Jul 2010 5:33	Modified:	22 Jul 2010 2:41
Reporter:	Holger Mueller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.3.2	OS:	Windows
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	ConvertZeroDateTime Null Date

Description:
DateTime fields marked as 'not null' result in '0000-00-00 00:00:00' values when they get assigned to null or simply not initialized at all.
When trying to fill a table into a DataSet/DataTable which contains such illegal DateTime values an exception is raised.
The ConvertZeroDateTime parameter fixes this issue.
BUT: when updating such a DataSet/DataTable back to the Database a parallelization error is raised meaning that not the expected number of rows had been updated in the database.

How to repeat:
1. create a table with a DateTime field ('not null') and some other fields.
2. insert a new row into that table but do not set that datetime-field.
   after the insert it should read like this: '0000-00-00 00:00:00'
3. use ConvertZeroDateTime parameter in connection string and read that table into a DataSet. (i.e. DataAdapter.Fill(DataTable))
4. Change some value in the row which contains the null-date, but not that field itself.
5. update the dataset (i.e. DataAdapter.Update(DataTable))
6. See the error-message ;)

Suggested fix:
Somewhere in DataAdapter.Update or CommandBuilder should the ConvertZeroDateTime be honored also for writing values. 
My suggestion: DBNull.Value should be translated to '0000-00-00 00:00:00' for datetime-fields with 'not-null' when ConvertZeroDateTime is set.

Karl, can you please attach short but full example of  the code that illustrates the problem , including DDL/DML, and returned/expected  results, so it can be used as a standalone test case. It is much simpler to understand what you want, looking at the code.

Hi Karl,
I tried to write a repro case myself, and succeeded.
My observations on it.

1) Connector/.NET does not automagically insert zero date if column is marked not null.

It will throw MySql.Data.MySqlClient.MySqlException : Field 'dt' doesn't have a default value.

Using mysql client utility, situation is like you describe, and I think it is just using lax mode, for compatibility reasons.

2) To insert zero value with Connector/NET one needs to extend definition to be something like dt datetime not null default '0000-00-00 00:00:00', which I would not recommend , it is better to use NULL to designate missing, zero datetime is not something that .NET framework can start with.

3) If you use ConvertZeroDatetime, the values in your DataSet are all valid valid System.DateTime values. (this is what you ask for setting this parameter). Zero time is converted to *valid* date January 1, 0001. When dataset is updated, where clause in UPDATE statement is generated such that it check if some values were changed, and throw concurrency exception if they were-

Using example above with a single row represented in the database as
id=1, dt='0000:00:00 00:00:00'

If you use ConvertZeroDateTime, read the row into dataset, change "id" in the row from 1 to 2, following statement will be generated

UPDATE Test SET id=2 WHERE id=1 and dt='0001:01:01 00:0:00'

(note the vallue for dt column, it is not what it used to be originally (invalid datetime), but a representation of valid DateTime value used as substitute for zero time.

This update statement does not update anything (since the where clause cannot be satistied), thus the number of affected rows is 0, thus you get concurrency exception.

While it might sound like a bug, but it is not. You got what you asked for:) There is no way to represent an invalid datetime as System.DateTime, so you get a valid System.DateTime, which however is not the same as the stored database value.

So now , there is still a way to work with zero datetimes.
Instead of ConvertZeroDateTime, use "Allow Zero Datetime=true" parameter in your connection string. The representation of such a value in the dataset will be MySqlDateTime for invalid values, of System.DateTime for valid values.

If you can avoid invalid datetime values, I would go for it first.

That said, I close this bug as not a bug

ok, i think i was to slow.. :(
thanks for the quick response. i'll try the "Allow Zero Datetime=true" parameter - i think this will help.
you are right that i'ts better to avoid illegal date values but i cannot change the ddl since my application is not the only one working on this db. the others are written in different languages (VB6 and java). so i needed a was to deal with this without changing the db...
again, thanks for your help.