|
Replies:
11
-
Pages:
1
-
Last Post:
Nov 28, 2012 7:07 AM
by: thorin
|
Threads:
[
Previous
|
Next
]
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
using timediff causes exception
Posted:
Feb 6, 2012 10:33 AM
|
|
|
The following query works fine in mysql commandline client on Ubuntu:
mysql> select label, count(distinct ip), timediff(runendtime,runstarttime) from MyRuns group by label;
The final column (the timediff) contains entries like "01:10:57.522932".
Running the same query against the same DB via Toad for MySQL initially 5.0.0.345 and now trying again with 6.0.1.1723 I encounter the following:
System.FormatException
Input string was not in a correct format.
Stack Trace:
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at MySql.Data.Types.MySqlTimeSpan.ParseMySql(String s)
at MySql.Data.Types.MySqlTimeSpan.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlDataReader.Read()
at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
If I do some sort of math on the timediff value (like "sum(timediff(runendtime,runstarttime))" or "timediff(runendtime,runstarttime)/60") then it works via Toad for MySQL.
It seems like Toad for MySQL is trying to convert the colon delimited time values as numbers into a string and failing when it hits the colon....
PS > Is there no way to do code or pre-formatted text in these posts?
|
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 6, 2012 10:54 AM
in response to: thorin
|
|
|
As a work around I've discovered that I can "trim(timediff(runendtime, runstarttime))" and the query will run ok in Toad for MySQL.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 7, 2012 2:45 AM
in response to: thorin
|
|
|
I don't see how you could ever get the result you see below. As far as I know MySQL don't contain timestamps with higher resolution than a second.
|
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 7, 2012 2:58 PM
in response to: Mauritz
|
|
|
Ahhh that might be the problem. The data isn't stored as an actual time or date column in the DB (it's some sort of char or string...I can't access the system with the details right at the moment) and is set by a python program.
I still find it strange that the MySQL commandline client handles the data fine though and Toad can't.
I'll give it a whirl with MySQL Query Broswer and see if that works.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 7, 2012 3:01 PM
in response to: thorin
|
|
|
Toad tries to do clever things with the data depending on the data type. This is something that the command line client doesn't do where all data is treated as text.
|
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 8, 2012 9:16 AM
in response to: Mauritz
|
|
|
So the columns are char(32) the command line client handles the timediff just fine, but Toad is trying to do something smart with the result it gets back from timediff (which is likely some sort of real MySQL Time or Date value) and fails.
If Toad tries to outsmart MySQL and fails to do so that would seem like a real Bug to me. Is there a bug tracker I should open a ticket in or is this forum the spot to get things fixed/tracked?
(As mentioned TRIMming the timediff works around the problem, but that introduces additional process which shouldn't be necessary. I assume this is because trim forces the query to return a string not a time or date to Toad).
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 8, 2012 12:33 PM
in response to: thorin
|
 |
Helpful |
|
|
The bug is actually in the driver that we use to connect to MySQL (Which comes from Oracle themselves).
The issue is that generally timediff values can never have part of seconds if they come from database values because MySQL doesn't have split second precision datetime.
I found that this simple query reproduces the issue and I will report it to the proper Oracle site so that they can hopefully fix it.
select timediff('01:10:57.522932', '01:10:57.522933')
That said this fix will most likely not be available before 6.3 is released.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 8, 2012 1:50 PM
in response to: Mauritz
|
|
Helpful |
|
|
Here is the bug report to Oracle for this issue.
http://bugs.mysql.com/bug.php?id=64268
|
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 8, 2012 5:48 PM
in response to: Mauritz
|
|
|
Thanks Mauritz!
|
|
|
Posts:
1
Registered:
2/12/12
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 12, 2012 4:37 AM
in response to: thorin
|
|
|
I suppose that it is new fractional seconds feature in MySQL time/timestamp data types.
It was introduced only in December.
http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
I tried to open in Toad a table with old-style TIMESTAMP column and everything worked perfectly.
When I changed the data type to TIMESTAMP(6) according to new specifications, the above error message appeared.
In command-line mode fractional seconds are processed with no problem. Other GUIs (e.g. HeidiSQL) have no problems with fractional seconds in table data, but I like Toad better.
Please upgrade Toad to this new feature.
|
|
|
Posts:
314
Registered:
8/23/07
|
|
|
|
Re: using timediff causes exception
Posted:
Feb 12, 2012 5:10 PM
in response to: Antipodus
|
|
|
The moment Oracle fixes this in their MySQL driver we will make sure to upgrade and they have acknowledged the bug (See the link above). That said it will probably not make the 6.3 release unfortunately.
|
|
|
Posts:
7
Registered:
2/6/12
|
|
|
|
Re: using timediff causes exception
Posted:
Nov 28, 2012 7:05 AM
in response to: Mauritz
|
|
|
So it seems the bug has been closed and it suggests the issue has been addressed (in Feb or Apr)...
http://bugs.mysql.com/bug.php?id=64268
If the fix is in the changelog for 6.5.3 and 6.3.9 (though the bug seems to be associated to 6.3.7) can someone tell me when any of those versions will be available? Because the latest non-beta download seems to be 6.3.0.642 (and we're almost in Dec.).
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
