| 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: | |||
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
|
SUMMARY KMail started failing to send emails for me with a message saying the item couldn't be stored. I did find this in the journal: Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: DATABASE ERROR: Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: Error code: "1292" Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: DB error: "Incorrect datetime value: '2024-03-10 02:10:09.341000' for column `akonadi`.`pimitemtable`.`atime` at row 1" Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: Error text: "Incorrect datetime value: '2024-03-10 02:10:09.341000' for column `akonadi`.`pimitemtable`.`atime` at row 1 QMYSQL3: Unable to execute statement" Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(QDateTime, QDateTime(2024-03-10 02:10:09.341 UTC Qt::UTC)))(":1", QVariant(qlonglong, 770803))) Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: Query: "UPDATE PimItemTable SET atime = :0 WHERE ( ( PimItemTable.id = :1 ) )" Mar 09 21:10:09 tjmaciei-mobl5 akonadiserver[745525]: org.kde.pim.akonadiserver: Unable to update item access time After stressing a bit about whether this was a database or filesystem corruption, I figured out that the issue was the actual time in that timestamp. MySQL rejects it: MariaDB [akonadi]> UPDATE PimItemTable SET atime = timestamp('2024-03-10 02:10:55.625000') WHERE ( ( PimItemTable.id = 770804 ) ); ERROR 1292 (22007): Incorrect datetime value: '2024-03-10 02:10:55.625000' for column `akonadi`.`pimitemtable`.`atime` at row 1 If you look at the error message in the log, as well as the journalctl timestamp, 02:10:55 is an UTC time. But when this was sent to the DB server, there's no information that it is UTC. Thus the server rejects it thinking it's local time. STEPS TO REPRODUCE 1. Set your system time such that the universal time, if computed as local time, would fall into the spring forward gap 2. Try to send an email OBSERVED RESULT Email sending fails. EXPECTED RESULT Email sending works. SOFTWARE/OS VERSIONS KDE Plasma Version: 5.27.10 KDE Frameworks Version: 5.115.0 Qt Version: 5.15.2 ADDITIONAL INFORMATION This is probably a QMYSQL bug, because the QVariant as printed did have UTC time. The toMySqlDate() function was added for Qt 6.6, but I don't see a way to specify the timezone with MYSQL_TIME. But maybe Akonadi can work around the issue by starting mysqld with TZ=UTC.