MySQL Bugs: #68297: Connector gives me MySqlDateTime type, but then doesn't know what to do with it.

Bug #68297	Connector gives me MySqlDateTime type, but then doesn't know what to do with it.
Submitted:	7 Feb 2013 2:02	Modified:	6 Jul 2014 16:13
Reporter:	Patrick Zahra	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.6.5	OS:	Windows (Windows 8, x64)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The connector does not recognise the MySqlDateTime type in parameters, and instead passes the query a ToString with no formatting information. As a result, the system formatting is used and an invalid date is passed into the query. This can be proven by changing the system date format to YYYY/MM/DD HH:mm:ss and seeing that it does, in fact, produce valid parameter values. On the other hand, it does not solve the problem of Zero dates getting 12:00:00 for time instead of 00:00:00, thereby making another invalid datetime entry.

Loading in a DataTable and then passing it back into another connection therefore requires a great deal of processing in-between because the program needs to manually format the MySqlDateTime values into strings in the middle.

How to repeat:
Load a table with dates (including zero dates) into a DataTable using MySqlDataAdapter.Fill

For each row in this table, SetModified

Now use the adapter and Update.

Assuming you are using a common date/time format, MySql will complain about the dates. Change the system date format to YYYY/MM/DD etc. and try again. Now it will only complain about the zero dates, which it is getting as 0000/00/00 12:00:00 AM.

Suggested fix:
Make the MySqlCommand or whatever it is inside the connector that reads parameters recognise its own datetime object and format it appropriately.

Project: MySql.Data
File: parameter.cs
Object: MySql.Data.MySqlClient.MySqlParameter
Method: SetTypeFromValue
Line: 6 (relative to method)
Add:
	  else if(paramValue is MySqlDateTime)
		  DbType = DbType.DateTime;
Problem: solved.

Hi P. Zahra,
Thanks for your feedback. I tried to reproduce the behavior you mentioned but I didn't see any invalid date into the table. Could you please give us more details or perhaps some code about the values that gave you this issue?, that way we can have a better idea about where the problem is.

The solution you proposed is not accurate for all cases. Since there will be some values that are valid for MySqlDateTime type but not for .net DateTime type.

Thanks in advance.

The source table resides on MySQL 5.1 innodb, the destination is MySQL 5.6 innodb. The table was copied via SHOW CREATE TABLE and the following C# code:

incomm.CommandText = string.Format("SELECT * FROM {0} LIMIT {1},{2}", tbl, tbl.Moved, tbl.Chunk);
if(Cancel) break;
adt.Fill(data);
if(!qset) {
	var pars = new string[data.Columns.Count];
	int i = 0;
	foreach(DataColumn col in data.Columns) {
		otcomm.Parameters.Add(otcomm.CreateParameter()).SourceColumn = col.ColumnName;
		pars[i++] = "?";
	}
	otcomm.CommandText = "INSERT INTO " + tbl + " VALUES (" + string.Join(",", pars) + ")";
	qset = true;
}
foreach(DataRow row in data.Rows) {
	row.SetAdded();
}
if(Cancel) break;
tbl.Moved += adt.Update(data);
++Prog;
data.Clear();

At first the source threw an exception when attempting to read the zero dates, until AllowZeroDateTime was switched on, then the destination started throwing "invalid date format" exceptions on all of them. It only accepted the regular dates when I changed the computer's date format to universal, and it still complained with the zero dates. Using INSERT IGNORE did alter the value of the zero dates to 12AM, and zeroed all the others.

Hello Patrick.

Net framework uses as default the Date-Time format from the host where the application is running (local or server).

MySql server just support the following Date-Time format: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'.
That's why when you change your system date format you have valid parameters values for dates but not for time because MySql handle time in 24 hours format, and doesn't support AM or PM.

You can read about the valid Date and Time format handled by MySql server here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-literals.html

As a solution or work around you can apply a valid format to the date values that you are sending to the server at the moment you generates the INSERT command, here is a sample of a valid format that MySql will support: "yyyy/MM/dd H:mm:ss".
The double M in upper case is to differ the months from minutes, and the H in upper case is to handle the time in 24 hours format.

Thanks for your time.
Saludos

Yes, I'm aware of this. However, the system date/time format is for display to the end user, and is not appropriate for inter-process communication, which is why this is still a bug.

Hello Patrick.

Can you post the complete function/method of the code previously posted?
There are many details that are not clear, many objects that are not related in the code posted, for that reason is very difficult to try to understand the full logic.

incomm.CommandText = string.Format("SELECT * FROM {0} LIMIT {1},{2}", tbl, tbl.Moved, tbl.Chunk); //<-- where is used this command?
if(Cancel) break;
adt.Fill(data); //-->which connection is using this data adapter: source or target? what command is used in the data adapter?

if(!qset) {
	var pars = new string[data.Columns.Count];
	int i = 0;
	foreach(DataColumn col in data.Columns) {
		otcomm.Parameters.Add(otcomm.CreateParameter()).SourceColumn = col.ColumnName; //<-- where is used this commnad? (see below comment)
		pars[i++] = "?";
	}
	otcomm.CommandText = "INSERT INTO " + tbl + " VALUES (" + string.Join(",", pars) + ")";//<-- is this command used as UpdateCommand for any data adapter?
	qset = true;
}
foreach(DataRow row in data.Rows) {
	row.SetAdded();
}
if(Cancel) break;
//no UpdateCommand needed for this adapter?
tbl.Moved += adt.Update(data);//<-- updating the data retrieved on target or source?
++Prog;
data.Clear();

If you are using Parameters in your commands to update/insert the data you should not get any error, because the parameter handle the data type for the value to insert, if you can share the complete code will be easier to find what is causing the exception.

Thanks for your time.

The code is for a tool that transfers data from one server to another. In order:

private void TransferData(MySqlCommand incomm, MySqlCommand otcomm, TransferTable tbl) {
	bool qset = false;
	using(var adt = new MySqlDataAdapter(incomm))
	using(var data = new DataTable()) {
		adt.InsertCommand = otcomm;
		for(int w = 0; w < tbl.Work; ++w) {

* incomm is the command for incoming data, it's the select command set to adt by default.
* this is where incomm grabs the data from the source and fills the local DataTable
* this loop happens only on the first chunk for each table, making sure all the parameters for the output command (otcomm) are set to the same type as the fields it received from the input command (incomm).
* yes, it got set as the InsertCommand for adt earlier on.
* see above.
* the last loop set all the rows as added, so this runs inserts on all of them.

}}}

The TransferTable type contains tracking info: name, is table or view, number of rows, size of chunk, how much has already been transferred. The function gets called once per table, for every table in the database.

Hello Patrick.

Sorry for the delay tracking your report.
I did some tests with the last information that you provided, but I can't reproduce the issue.

I'm using the following schema in the tables that I'm using (each one in his own server):
-Source Table: create table DatesSource(Id int primary key auto_increment, DateField Datetime);
-Target Table: create table DatesTarget(Id int, DateField Datetime);

And here is the code that I'm using to transfer the data, which is a code based on the code that you posted:
. . .
     try
     {
       string connStringSource = "server=192.168.50.8;user id=user;password=pass;database=mytests;port=3306;connection timeout=100000;default command timeout=100000;";
       string connStringTarget = "server=localhost;user id=root;database=mytest;port=3305;connection timeout=100000;default command timeout=100000;";
       var connSource = new MySqlConnection(connStringSource);
       var connTarget = new MySqlConnection(connStringTarget);

       var command = new MySqlCommand("SELECT * FROM DatesSource;", connSource);
       var targetCommand = new MySqlCommand();
       targetCommand.Connection = connTarget;
       var sourceAdapter = new MySqlDataAdapter(command);
       sourceAdapter.InsertCommand = targetCommand;
       var sourceData = new DataTable();
       sourceAdapter.Fill(sourceData);

       var pars = new string[sourceData.Columns.Count];
       int i = 0;
       foreach (DataColumn col in sourceData.Columns)
       {
         targetCommand.Parameters.Add(targetCommand.CreateParameter()).SourceColumn = col.ColumnName;
         pars[i++] = "?";
       }
       targetCommand.CommandText = "INSERT INTO DatesTarget VALUES (" + string.Join(",", pars) + ")";
       foreach (DataRow row in sourceData.Rows)
         row.SetAdded();
       sourceAdapter.Update(sourceData);
     }
     catch (Exception ex)
     {
       throw ex;
     }
. . .

See the result in the image attached, the transfer works as expected with any trouble.

Thanks for your time.

Data transfer.

Attachment: MySql_68297.png (image/png, text), 63.72 KiB.

Hello.

Please see my last comments, Do you have any additional information for this issue?

Thanks for your time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".