[Akonadi] [Bug 451963] New: MySQL backend stores timestamps without adapting timezone

[Friedrich W. H. Kossebau]

https://bugs.kde.org/show_bug.cgi?id=451963

            Bug ID: 451963
           Summary: MySQL backend stores timestamps without adapting
                    timezone
           Product: Akonadi
           Version: GIT (master)
          Platform: Other
                OS: Linux
            Status: REPORTED
          Severity: normal
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: kossebau at kde.org
  Target Milestone: ---

PROBLEM:

On my CET/CEST timezone system, tonight when there was the daylight saving time
hour adaption, Akonadi reported some errors for a while. Here the extract from
systemd log (sorry, german local, but most should be obvious):
--- 8< ---
Mär 27 04:00:45 klaplax akonadi_pop3_resource[2955]: org.kde.pim.pop3resource:
"Fehler beim Speichern heruntergeladener E-Mails.\nFailed to append item"
Mär 27 04:00:45 klaplax akonadi_pop3_resource[2955]: org.kde.pim.pop3resource:
============== ERROR DURING POP3 SYNC ==========================
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver: Error
while handling command CreateItem on connection akonadi_pop3_resource_0
(0x55e58e538930)
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver: Error
during insertion into table "PimItemTable" "Incorrect datetime value:
'2022-03-27 02:00:45.979000' for column `akonadi`.`pimitemtable`.`datetime` at
row 1 QMYSQL3: Der Befehl konnte nicht ausgeführt werden"
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:  
Query: "INSERT INTO PimItemTable (rev, remoteRevision, gid, collectionId,
mimeTypeId, datetime, atime, dirty, size) VALUES (:0, :1, :2, :3, :4, :5, :6,
:7, :8)"
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:  
Values: QMap((":0", QVariant(int, 0))(":1", QVariant(QString, ""))(":2",
QVariant(QString, ""))(":3", QVariant(qlonglong, 13))(":4", QVariant(qlonglong,
7))(":5", QVariant(QDateTime, QDateTime(2022-03-27 02:00:45.979 UTC
Qt::UTC)))(":6", QVariant(QDateTime, QDateTime(2022-03-27 02:00:45.979 UTC
Qt::UTC)))(":7", QVariant(bool, true))(":8", QVariant(qlonglong, 0)))
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:   Error
text: "Incorrect datetime value: '2022-03-27 02:00:45.979000' for column
`akonadi`.`pimitemtable`.`datetime` at row 1 QMYSQL3: Der Befehl konnte nicht
ausgeführt werden"
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:   DB
error:  "Incorrect datetime value: '2022-03-27 02:00:45.979000' for column
`akonadi`.`pimitemtable`.`datetime` at row 1"
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:   Error
code: "1292"
Mär 27 04:00:45 klaplax akonadiserver[2593]: org.kde.pim.akonadiserver:
DATABASE ERROR:
--- 8< ---

It seems those errors appeared during 4 am & 5 am in my (now) CEST timezone,
whereas the timestamps used in the data were in the 2 am - 3 am range, which
obviously this night would not exist in my timezone, given the 2 am -> 3 am
time jump for the daylight saving time switch.

So there might be a mismatch in the timezones used for interpreting the
timestamps by the mysql/mariadb server and akonadi.

>From https://mariadb.com/kb/en/datetime/#time-zones:
"If a column uses the DATETIME data type, then any inserted values are stored
as-is, so no automatic time zone conversions are performed."

Looking at Akonadi code, e.g. for creating PimItems, often
QDateTime::currentDateTimeUtc() is used, so the QDateTime instances would be
based on UTC if values are directly queried.

From
https://code.qt.io/cgit/qt/qtbase.git/tree/src/plugins/sqldrivers/mysql/qsql_mysql.cpp?h=5.15:
Qt's MySQL driver stores only the current date & time values of a QDateTime
instance, without any conversion of the timezone to match the timezone
configured for the database, using QVariant::toDate() & QVariant::toTime() and
QDate::year()/month()/day() & QTime::hour()/minute()/second()/msec(). And reads
the value via QDateTime::fromString(yyyyMMddhhmmssString, Qt::ISODate).

So a QDateTime::currentDateTimeUtc() generated at 4:00:45 am CEST would be
turned into a string "02:00:45" by the current code, which then is rejected by
mariadb due to matching that against the system timezone which is CEST and that
very day does not have such a timestamp.

Similar issues with any other QDateTime instances used for PimItem::datetime &
PimItem::atime and being based on a timezone different from the one used for
the db.


SOLUTION:
?

I guess the mysql akonadi backend would need to know what timezone is used for
the database and adjust/normalize the QDateTime instances accordingly before
passing things over to the Qt db driver.

Then there is also the issue of  migrating existing databases once this is
fixed. Even more as one cannot be sure that the current stored values are all
based on UTC or perhaps something else, depending on what the resources use.

-- 
You are receiving this mail because:
You are the assignee for the bug.