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: Bogdan Degtyariov CPU Architecture:Any

[10 Nov 2011 15:24] Jorge Bastos
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;
[9 Dec 2011 10:46] Bogdan Degtyariov
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();
  }
[9 Dec 2011 10:49] Jorge Bastos
Which MySQL version are you running?
This is with 5.0x, and the problem may be it.

You're on 5.5x, right?
[9 Dec 2011 10:52] Bogdan Degtyariov
Yes, I run it with 5.5.19
[9 Dec 2011 10:53] Bogdan Degtyariov
Let me try with 5.0
[9 Dec 2011 11:15] Jorge Bastos
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.
[9 Dec 2011 11:16] Bogdan Degtyariov
Tested with MySQL Server 5.0.91. Result is the same.
[9 Dec 2011 11:29] Jorge Bastos
hum, let me do some more investigation on this and I'll let you know.
[10 Jan 2012 7:00] Bugs System
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".