Summary: | Akonadi sending UTC timestamps as local time to MySQL (and this failing around DST change) - QMYSQL bug? | ||
---|---|---|---|
Product: | [Frameworks and Libraries] Akonadi | Reporter: | Thiago Macieira <thiago> |
Component: | server | Assignee: | kdepim bugs <kdepim-bugs> |
Status: | RESOLVED FIXED | ||
Severity: | normal | CC: | carl, Ch.Ehrlicher, joshua |
Priority: | NOR | ||
Version: | 5.24.4 | ||
Target Milestone: | --- | ||
Platform: | Other | ||
OS: | Linux | ||
Latest Commit: | https://invent.kde.org/pim/akonadi/-/commit/c98b05b79aa849ebba2f4c309484428dd08df042 | Version Fixed In: | |
Sentry Crash Report: |
Description
Thiago Macieira
2024-03-10 03:01:43 UTC
Or use the SET GLOBAL time_zone command. Duplicate of 483061? (In reply to Christophe Marin from comment #2) > Duplicate of 483061? Looks like two people experienced the same problem at around the same time, yes. *** Bug 483061 has been marked as a duplicate of this bug. *** My reading of the MySQL API is that there's no way to pass the timestamp in prepared queries as UTC. You can only pass it as a local timestamp. Feels so 1980s... When I was debugging this, someone in the #maria IRC channel said:
> As a workaround SQL mode flag ALLOW_INVALID_DATES should help, but it is better to store the UTC values properply as UTC.
So, there is that...but that does seem ugly. Can you even specify a TZ on a TIMESTAMP column in MySQL/MariaDB (it's been too long since I've actively used MySQL).
Another idea: have Akonadi set the client time zone to UTC when it connects. BUT: I don't know what that might do to the times it returns in other queries, so would require investigation, and if code would have to be changed elsewhere.
(In reply to Christophe Marin from comment #2) > Duplicate of 483061? That's quite funny. Mine is even the very next bug filed after this one. :) > Another idea: have Akonadi set the client time zone to UTC when it connects. BUT: I don't know what that might do to the times it returns in other queries, so would require investigation, and if code would have to be changed elsewhere. Applying that fix to QMYSQL instead. https://codereview.qt-project.org/c/qt/qtbase/+/546954 Git commit c98b05b79aa849ebba2f4c309484428dd08df042 by Daniel Vrátil. Committed on 12/03/2024 at 07:41. Pushed by dvratil into branch 'master'. Fix handling of UTC vs. local time for database engines Each database handles datetimes slightly differently. Our biggest problem was MySQL, where the QMYSQL driver strips timezone, so the MySQL server assumes the time is a local time. Akonadi was, however, passing a UTC time, which then MySQL tried to interpret as local time, which lead to two issues: 1) it failed if the time was not a valid time in local timezone (DST) 2) it required special handling when reading the time back from DB because MySQL returned UTC time but with local timezone set. So instead we make sure we handle datetime properly when insert it into the database. This can be handled from a singular place in QueryBuilder, so it's fully transparent to the caller. There's no migration for the existing database, since at worst the time is slightly off and shouldn't have actual impact on functionality. This has also been fixed independently in Qt, so in the future we might be able to drop this completely. M +2 -0 autotests/server/CMakeLists.txt A +139 -0 autotests/server/dbdatetimetest.cpp [License: LGPL(v2.0+)] A +1 -0 autotests/server/unittestenv M +1 -1 src/server/dbmigrator/dbmigrator.cpp M +1 -1 src/server/handler/itemfetchhelper.cpp M +0 -3 src/server/storage/entities-source.xsl M +0 -3 src/server/storage/entities.xsl M +6 -1 src/server/storage/querybuilder.cpp M +34 -24 src/server/utils.h https://invent.kde.org/pim/akonadi/-/commit/c98b05b79aa849ebba2f4c309484428dd08df042 |