[Akonadi] [Bug 483060] New: Akonadi sending UTC timestamps as local time to MySQL (and this failing around DST change) - QMYSQL bug?

[Thiago Macieira]

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

            Bug ID: 483060
           Summary: Akonadi sending UTC timestamps as local time to MySQL
                    (and this failing around DST change) - QMYSQL bug?
    Classification: Frameworks and Libraries
           Product: Akonadi
           Version: 5.24.4
          Platform: Other
                OS: Linux
            Status: REPORTED
          Severity: normal
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: thiago at kde.org
                CC: carl at carlschwan.eu
  Target Milestone: ---

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.

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