Opened 14 years ago
Closed 13 years ago
Last modified 13 years ago
#8585 closed defect (fixed)
Qt:ISODate/mysql icompatibility
Reported by: | Owned by: | sphery | |
---|---|---|---|
Priority: | minor | Milestone: | 0.24.1 |
Component: | MythTV - General | Version: | Master Head |
Severity: | medium | Keywords: | |
Cc: | Ticket locked: | no |
Description
According to MySQL documentation ISO-8601 date format is not a correct format for datetime fields:
MySQL 5.5 datetime doc
MySQL 5.1 datetime doc
In fact on mysql 5.1 giving a string like "yyyy-MM-ddThh:mm:ss" works even it is not supported, but in 5.5 (5.5.4-m3) it does not work and for example "2010-06-20T14:13:00" is recognized as "2010-06-21 00:00:00" !![[BR]]
So On latest MySQL program guide does not work, and on liveTV the name of the current program is not correct and shows the program starting next day at 00:00
This is due to the use of DateTime::toString(Qt::ISODate) wich return a correct ISO-8601 date but wich is an incorrect MySQL datetime field, a correct field should be obtained for example with aDateTime.toString("yyyy-MM-dd hh:mm:ss")
Attachments (5)
Change History (21)
comment:1 Changed 14 years ago by
comment:3 Changed 14 years ago by
Owner: | changed from Isaac Richards to sphery |
---|---|
Status: | new → assigned |
comment:4 Changed 14 years ago by
Priority: | major → minor |
---|---|
Version: | Unspecified → Trunk Head |
Changing priority on this ticket since MySQL 5.5 is still a development branch of MySQL (becoming trunk and 5.1.x is currently the Generally Available Release version).
comment:5 Changed 13 years ago by
Unfortunately, there doesn't seem to be a good way to automatically find the places where MythTV hard-codes formatted string representations of dates into queries passed to MySQL. Therefore, to fix these cases, I need specific references to these failures.
Anyone who wants to test with MySQL 5.5 and find some of the errors should run (whichever application) with -v database logging and attach to this ticket the log output showing failures. Please attach the logs as files and do not paste them into the comments of the ticket. Or, if you notice any specific locations in the code where this is done, please point them out.
Thanks.
comment:6 Changed 13 years ago by
Status: | assigned → infoneeded |
---|
comment:7 Changed 13 years ago by
In: 7e33788e5b75049d8eaceee589606a69241eab24
Fix some invalid-format datetimes in queries.
Fix some hard-coded, string datetime representations in queries that were using an invalid format that no longer works with MySQL 5.5. These datetime literals now use the correct MySQL SQL datetime literal format (which is also the ISO SQL datetime literal format).
Huge thanks to Rob Smith for testing with MySQL 5.5 and seeking out the datetime-usage within queries.
Changed 13 years ago by
Attachment: | mythtv-8585-use_proper_ISO_SQL_format_in_database_logging.patch added |
---|
Simplified patch to prevent bound datetimes from showing as invalid format in logging
comment:8 Changed 13 years ago by
Rob, I committed the fixes for the datetime literals you found that were using an invalid format. I also attached a patch which you can use to prevent invalid format datetime values from appearing in the database logging. I don't plan to commit the patch, since it only changes the format of data within a very-low-level debug string, and isn't worth the resources required for the extra checks. However, if you would like to use it on your MySQL 5.5, it should allow you to grep the -v database logs for invalid-format datetimes without seeing the false-positives that occur due to the toString() used by the logging code. It should be simpler than changing each bindValue() using QDateTime to pass a string to the database.
Leaving the ticket open for now, and plan to backport fixes after a week or so, but I think you found all the problem datetime strings. Thanks.
comment:9 Changed 13 years ago by
Resolution: | → fixed |
---|---|
Status: | infoneeded → closed |
Fix some invalid-format datetimes in queries.
Fix some hard-coded, string datetime representations in queries that were using an invalid format that no longer works with MySQL 5.5. These datetime literals now use the correct MySQL SQL datetime literal format (which is also the ISO SQL datetime literal format).
Huge thanks to Rob Smith for testing with MySQL 5.5 and seeking out the datetime-usage within queries.
Refs #8585 (possibly fixes #8585).
Changeset: 7e33788e5b75049d8eaceee589606a69241eab24
comment:10 Changed 13 years ago by
Resolution: | fixed |
---|---|
Status: | closed → new |
This got closed by the "possibly fixes" when the commit hooks got enabled and a merge commit re-rolled a bunch of changes. Not ready for a fixed status, yet.
comment:11 Changed 13 years ago by
Don't use QDateTime::toString() for prepared queries.
When using bindValue() to specify datetime values, do not use toString(). Instead, pass the QDateTime to bindValue() so that it can pass the value using the MySQL-C-API MYSQL_TIME type--so that database datetime string literal format isn't an issue. Refs #8585.
Thanks, again, to Rob Smith for finding another incorrect pattern to search for.
Changeset: cd073b6e9b323958f785cab47884e605e4033885
comment:12 Changed 13 years ago by
Milestone: | unknown → 0.24.1 |
---|---|
Resolution: | → fixed |
Status: | new → closed |
Fix some invalid-format datetimes in queries.
Fix some hard-coded, string datetime representations in queries that were using an invalid format that no longer works with MySQL 5.5. These datetime literals now use the correct MySQL SQL datetime literal format (which is also the ISO SQL datetime literal format).
Huge thanks to Rob Smith for testing with MySQL 5.5 and seeking out the datetime-usage within queries.
Refs #8585 (possibly fixes #8585). (cherry picked from commit 7e33788e5b75049d8eaceee589606a69241eab24)
Changeset: c339887d24a316365ddfc3f768d628202dd57fe8
comment:13 Changed 13 years ago by
Don't use QDateTime::toString() for prepared queries.
When using bindValue() to specify datetime values, do not use toString(). Instead, pass the QDateTime to bindValue() so that it can pass the value using the MySQL-C-API MYSQL_TIME type--so that database datetime string literal format isn't an issue. Refs #8585.
Thanks, again, to Rob Smith for finding another incorrect pattern to search for. (cherry picked from commit cd073b6e9b323958f785cab47884e605e4033885)
Changeset: bfa882711578a1345e905961cffeb1e52cbe2e03
comment:14 Changed 13 years ago by
This time I really meant for this issue to be closed as fixed. If anyone can find any other instances where we use an invalid format string instead of a QDateTime, please let me know. However, I think this is all of them.
comment:15 Changed 13 years ago by
Fix invalid-string-format date passing to MySQL.
This fixes a lot of instances where we were passing incorrectly-formatted string representations of date/time values in prepared queries. This is an issue now that MySQL 5.5 is picky about receiving properly-formatted strings.
Thanks to Daniel K for finding another pattern to search for direct binding of string-formatted dates.
Refs #8585.
Branch: master Changeset: 3d42de592136a7f3ab9bfca197c73aaa5f6bc56d
comment:16 Changed 13 years ago by
Above commit was backported to 0.24-fixes in 8b9e5ce0d .
Also:
In f19e8b9f23:
Fix another invalid-string-format datetime binding.
Missed this one in 3d42de59 .
(and backported to 0.24-fixes in 4942639cfe ).
program guide doesn't work with MySQL 5.5.2 also