Bug 451963

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.