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 18.104.22.1685 and now trying again with 22.214.171.1243 I encounter the following:System.FormatExceptionInput 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?
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.
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).
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.
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=64268If 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 126.96.36.1992 (and we're almost in Dec.).
As a work around I've discovered that I can "trim(timediff(runendtime, runstarttime))" and the query will run ok in Toad for MySQL.