Modify
Warning Please read the Ticket HowTo before creating or commenting on a ticket. Failure to do so may cause your ticket to be rejected or result in a slower response.

Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#8585 closed defect (fixed)

Qt:ISODate/mysql icompatibility

Reported by: eltorio@… Owned by: mdean
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)

mysql-5.5.patch (7.9 KB) - added by kormoc 3 years ago.
First run at #8585
mysql-5.5.2.patch (8.8 KB) - added by kormoc 3 years ago.
Fixes REPLCATE INTO oldrecorded
mysql-5.5.3.patch (9.9 KB) - added by kormoc 3 years ago.
A few more spots…
mysql-5.5.4.patch (13.3 KB) - added by kormoc 3 years ago.
A few more spots…
mythtv-8585-use_proper_ISO_SQL_format_in_database_logging.patch (952 bytes) - added by mdean 3 years ago.
Simplified patch to prevent bound datetimes from showing as invalid format in logging

Download all attachments as: .zip

Change History (21)

comment:1 Changed 4 years ago by anonymous

program guide doesn't work with MySQL 5.5.2 also

comment:2 Changed 4 years ago by rlemeill@…

Tested with 5.5.3-m3 and bug confirmed

comment:3 Changed 4 years ago by robertm

  • Owner changed from ijr to mdean
  • Status changed from new to assigned

comment:4 Changed 4 years ago by mdean

  • Priority changed from major to minor
  • Version changed from Unspecified to 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 3 years ago by mdean

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 3 years ago by mdean

  • Status changed from assigned to infoneeded

Changed 3 years ago by kormoc

First run at #8585

Changed 3 years ago by kormoc

Fixes REPLCATE INTO oldrecorded

Changed 3 years ago by kormoc

A few more spots...

Changed 3 years ago by kormoc

A few more spots...

comment:7 Changed 3 years ago by mdean

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.

Refs #8585 (possibly fixes #8585).

Changed 3 years ago by mdean

Simplified patch to prevent bound datetimes from showing as invalid format in logging

comment:8 Changed 3 years ago by mdean

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 3 years ago by Michael T. Dean

  • Resolution set to fixed
  • Status changed from infoneeded to 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 3 years ago by mdean

  • Resolution fixed deleted
  • Status changed from closed to 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 3 years ago by Michael T. Dean

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 3 years ago by Github

  • Milestone changed from unknown to 0.24.1
  • Resolution set to fixed
  • Status changed from new to 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 3 years ago by Github

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 3 years ago by mdean

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 3 years ago by Github

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 3 years ago by mdean

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

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'new'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.