MySQL Bugs: #63186: DateTime field retrieved as a "date" field only

Bug #63186	DateTime field retrieved as a "date" field only
Submitted:	10 Nov 2011 15:24	Modified:	9 Jan 2012 11:53
Reporter:	Jorge Bastos	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.4.4 / last trunk	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Howdy,

Using last 6.4.4 or trunk version of the connector, doesn't retrieve datetime fields.
On this specific case, it's the asteriskCDRdb information, on MySQL 5.0x.

How to repeat:
mysql> describe cdr;
+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| clid        | varchar(80)  | NO   |     |                     |       |
| src         | varchar(80)  | NO   |     |                     |       |
| dst         | varchar(80)  | NO   |     |                     |       |
| dcontext    | varchar(80)  | NO   |     |                     |       |
| channel     | varchar(80)  | NO   |     |                     |       |
| dstchannel  | varchar(80)  | NO   |     |                     |       |
| lastapp     | varchar(80)  | NO   |     |                     |       |
| lastdata    | varchar(80)  | NO   |     |                     |       |
| duration    | int(11)      | NO   |     | 0                   |       |
| billsec     | int(11)      | NO   |     | 0                   |       |
| disposition | varchar(45)  | NO   |     |                     |       |
| amaflags    | int(11)      | NO   |     | 0                   |       |
| accountcode | varchar(20)  | NO   |     |                     |       |
| uniqueid    | varchar(32)  | NO   |     |                     |       |
| userfield   | varchar(255) | NO   |     |                     |       |
+-------------+--------------+------+-----+---------------------+-------+
16 rows in set (0.00 sec)

The "calldate" field, only retrieves the date.
The connectionstring used is:

FunctionsReturnString=true;character set=utf8;pooling=false;compress=false;Persist Security Info=False;Connect Timeout=30;Default Command Timeout=300;Data Source=" & server_host & ";Database=asteriskcdrdb;User ID=root;Password=somepwd;Port=3306"

for now, the only way I have to get the information correct was to do a:

select concat(calldate,'') as calldate,otherfields from cdr;

Hi Jorge,

I could not repeat the problem using my test code.
It shows the correct result with the date and time part:

System.Int32 : 1
System.DateTime : 9/12/2011 4:22:33 PM

Can you please edit it to help me reproduce the incorrect behavior?
-------------------------------------------------------------------

  private void button11_Click(object sender, EventArgs e)
  {
    MySqlConnection con = new MySqlConnection();
    try
    {
      con.ConnectionString = "Data Source=localhost;database=test;" +
                             "user id=********;Password=********;" +
                             "FunctionsReturnString=true;" +
                             "character set=utf8;" +
                             "pooling=false;compress=false;" +
                             "Persist Security Info=False;" +
                             "Connect Timeout=30;" + 
                             "Default Command Timeout=300;";
      con.Open();
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = con;

      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "DROP TABLE IF EXISTS bug63186";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "CREATE TABLE bug63186 " +
          "(id int auto_increment primary key," +
          " dtcol datetime default '0000-00-00 00:00:00' NOT NULL)";
      cmd.ExecuteNonQuery();

      cmd.CommandText = "INSERT INTO bug63186 (id, dtcol) VALUES " +
                        "(1, '2011-12-09 16:22:33')";
      cmd.ExecuteNonQuery();

      cmd.CommandText = "SELECT id, dtcol FROM bug63186";
      MySqlDataReader dr = cmd.ExecuteReader();

      while (dr.Read())
      {
        String s = dr[0].GetType().ToString() + " : " + 
                   dr.GetUInt32(0).ToString() +
                   "\r\n" + dr[1].GetType().ToString() + " : " + 
                   dr.GetDateTime(1).ToString();

        MessageBox.Show(s);
      }

    }
    catch (Exception ex)
    {
      string error = ex.Message.ToString();
      error += ex.StackTrace;
      MessageBox.Show("=============================================\r\n" +
                      "Error!\r\n" + error + "\r\n" +
                      "=============================================\r\n");

    }
    con.Close();
  }

Which MySQL version are you running?
This is with 5.0x, and the problem may be it.

You're on 5.5x, right?

Yes, I run it with 5.5.19

Let me try with 5.0

I believe it's that, I had other problem where works OK in 5.5x, and that's a problem that its not going to be fixed in previous versions that 5.5.

Let me know the result.

Tested with MySQL Server 5.0.91. Result is the same.

hum, let me do some more investigation on this and I'll let you know.

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".