Bug#1031770: kontact: cannot show existing messages and will not retrieve new ones

[Paul Boddie]

Package: kontact
Version: 4:18.08.3-1
Severity: important

Dear Maintainer,

Today I found that Kontact would not load and show messages that already
reside in my mailboxes, and it refuses to download new ones. In the status
bar, messages like the following are shown:

Unable to fetch item from backend (collection -1)
Unable to retrieve item from resource

Opening akonadiconsole and its browser functionality does show messages in
the list for each mailbox, and it also manages to show each message payload.
However, the following error messages are generated in the terminal window:

org.kde.pim.akonadiserver: DATABASE ERROR:
org.kde.pim.akonadiserver: Error code: "1292"
org.kde.pim.akonadiserver: DB error: "Incorrect datetime value: '2023-02-22T12:00:36Z' for column `akonadi`.`pimitemtable`.`datetime` at row 1"
org.kde.pim.akonadiserver: Error text: "Incorrect datetime value: '2023-02-22T12:00:36Z' for column `akonadi`.`pimitemtable`.`datetime` at row 1 QMYSQL: Unable to execute query"
org.kde.pim.akonadiserver: Query: "INSERT INTO PimItemTable (rev, remoteId, remoteRevision, gid, collectionId, mimeTypeId, datetime, atime, dirty, size) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9)"
org.kde.pim.akonadiserver: Error during insertion into table "PimItemTable" "Incorrect datetime value: '2023-02-22T12:00:36Z' for column `akonadi`.`pimitemtable`.`datetime` at row 1 QMYSQL: Unable to execute query"

org.kde.pim.akonadiserver: DATABASE ERROR:
org.kde.pim.akonadiserver: Error code: "1292"
org.kde.pim.akonadiserver: DB error: "Incorrect datetime value: '2023-02-22T12:08:21Z' for column `akonadi`.`pimitemtable`.`atime` at row 1"
org.kde.pim.akonadiserver: Error text: "Incorrect datetime value: '2023-02-22T12:08:21Z' for column `akonadi`.`pimitemtable`.`atime` at row 1 QMYSQL: Unable to execute query"
org.kde.pim.akonadiserver: Query: "UPDATE PimItemTable SET atime = :0 WHERE ( PimItemTable.collectionId = :1 )"
org.kde.pim.akonadiserver: Unable to update item access time

It turns out that this is caused by a bug that was reported back in 2021:

"Kmail fails to display message, akonadiserver errors"
https://forums.opensuse.org/t/kmail-fails-to-display-message-akonadiserver-errors/147051

"Bug 1189184 - org.kde.pim.akonadiserver: Error code: "1292""
https://bugzilla.opensuse.org/show_bug.cgi?id=1189184

"Akonadi fails with Mariadb 10.6.3"
https://bugs.kde.org/show_bug.cgi?id=439769

"mysql client version detection broken with MariaDB 10.6"
https://bugreports.qt.io/browse/QTBUG-95071

I imagine that a fix for this bug needs to be introduced to the packaged
Qt 5 SQL library from whichever version was fixed. It looks like the
libqt5sql5-mysql:amd64 package on my system is based on 5.11:

5.11.3+dfsg1-1+deb10u5

But upstream fixes only covered 5.15 and 6.2 branches.

I see that the following package was upgraded on my system recently:

libmariadb3_1%3a10.3.38-0+deb10u1_amd64.deb

The libmariadb3 package has the following version:

1:10.3.38-0+deb10u1

So, this may have caused this bug to appear on my system.

If anyone really thought that they absolutely had to incorporate a full
relational database server into their desktop application, then at the very
least they should have used PostgreSQL and saved everyone the bother of
dealing with the circus that is MySQL. I will spare everyone the usual rant
about Akonadi, noting only that "akonadictl restart" is a regular command
invocation in my workflow.

Thanks in advance for any consideration of this issue,

Paul

-- System Information:
Debian Release: 10.13
APT prefers oldstable-updates
APT policy: (500, 'oldstable-updates'), (500, 'oldstable')
Architecture: amd64 (x86_64)

Kernel: Linux 5.5.0-0.bpo.2-amd64 (SMP w/8 CPU cores)
Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8), LANGUAGE=en_GB:en (charmap=UTF-8)
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages kontact depends on:
ii kdepim-runtime 4:18.08.3-4
ii kio 5.54.1-1
ii libc6 2.28-10+deb10u2
ii libgcc1 1:8.3.0-6
ii libkf5completion5 5.54.0-1
ii libkf5configcore5 5.54.0-1+deb10u1
ii libkf5configgui5 5.54.0-1+deb10u1
ii libkf5configwidgets5 5.54.0-1
ii libkf5coreaddons5 5.54.0-1
ii libkf5crash5 5.54.0-1
ii libkf5grantleetheme-plugins 18.08.3-1
ii libkf5grantleetheme5 18.08.3-1
ii libkf5i18n5 5.54.0-1
ii libkf5iconthemes5 5.54.0-1
ii libkf5kcmutils5 5.54.0-1
ii libkf5kdepimdbusinterfaces5 4:18.08.3-2
ii libkf5kiowidgets5 5.54.1-1
ii libkf5kontactinterface5 18.08.3-1
ii libkf5libkdepim-plugins 4:18.08.3-2
ii libkf5libkdepim5 4:18.08.3-2
ii libkf5parts5 5.54.0-1
ii libkf5service-bin 5.54.0-1
ii libkf5service5 5.54.0-1
ii libkf5widgetsaddons5 5.54.0-1
ii libkf5windowsystem5 5.54.0-1
ii libkf5xmlgui5 5.54.0-1
ii libqt5core5a 5.11.3+dfsg1-1+deb10u5
ii libqt5dbus5 5.11.3+dfsg1-1+deb10u5
ii libqt5gui5 5.11.3+dfsg1-1+deb10u5
ii libqt5webengine5 5.11.3+dfsg-2+deb10u1
ii libqt5webenginewidgets5 5.11.3+dfsg-2+deb10u1
ii libqt5widgets5 5.11.3+dfsg1-1+deb10u5
ii libstdc++6 8.3.0-6

Versions of packages kontact recommends:
ii accountwizard 4:18.08.3-1
ii akregator 4:18.08.3-2
ii kaddressbook 4:18.08.3-3
ii kmail 4:18.08.3-1
ii knotes 4:18.08.3-1
ii korganizer 4:18.08.3-1

Versions of packages kontact suggests:
pn gnokii <none>

-- no debconf information

[Otto Kekäläinen]

Seems Akonadi tried to execute

"INSERT INTO PimItemTable (rev, remoteId, remoteRevision, gid,
collectionId, mimeTypeId, datetime, atime, dirty, size) VALUES (:0,
:1, :2, :3, :4, :5, :6, :7, :8, :9)"

and gets error

"Incorrect datetime value: '2023-02-22T12:00:36Z' for column
`akonadi`.`pimitemtable`.`datetime` at row 1"

Can you check what the schema for that table is? How does the current
data look like?
(Unless you are sure the root cause is
https://bugreports.qt.io/browse/QTBUG-95071)

Or could this be a variant of
https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=993219 ?

Very weird that Akonadi would stop working on an upgrade of
1:10.3.38-0+deb10u1. Does it start working if you downgrade back to
1:10.3.37-0+deb10u1?

[Paul Boddie]

On 2023-02-22 17:03, Otto Kekäläinen wrote:
> Seems Akonadi tried to execute
>
> "INSERT INTO PimItemTable (rev, remoteId, remoteRevision, gid,
> collectionId, mimeTypeId, datetime, atime, dirty, size) VALUES (:0,
> :1, :2, :3, :4, :5, :6, :7, :8, :9)"
>
> and gets error
>
> "Incorrect datetime value: '2023-02-22T12:00:36Z' for column
> `akonadi`.`pimitemtable`.`datetime` at row 1"
>
> Can you check what the schema for that table is? How does the current
> data look like?

So, akonadiconsole produces a graphical representation of the schema, so
I had to use the command line client as follows:

mysql -S /tmp/akonadi-paulb.c9oVw2/mysql.socket

The socket file can be discovered by doing "ps -ef | grep mysql" and
reading the --socket option of the mysqld process command line.

After doing "use akonadi", I performed the following:

MariaDB [akonadi]> desc PimItemTable;
+----------------+----------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------+----------------+------+-----+---------------------+----------------+
| id | bigint(20) | NO | PRI | NULL |
auto_increment |
| rev | int(11) | NO | | 0 |
|
| remoteId | varbinary(255) | YES | MUL | NULL |
|
| remoteRevision | varbinary(255) | YES | | NULL |
|
| gid | varbinary(255) | YES | MUL | NULL |
|
| collectionId | bigint(20) | YES | MUL | NULL |
|
| mimeTypeId | bigint(20) | YES | MUL | NULL |
|
| datetime | timestamp | NO | | current_timestamp() |
|
| atime | timestamp | NO | | current_timestamp() |
|
| dirty | tinyint(1) | YES | | NULL |
|
| size | bigint(20) | NO | | 0 |
|
+----------------+----------------+------+-----+---------------------+----------------+
11 rows in set (0.001 sec)

> (Unless you are sure the root cause is
> https://bugreports.qt.io/browse/QTBUG-95071)

This was the closest thing I could find.

> Or could this be a variant of
> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=993219 ?

I don't think so. People have experienced configuration issues with
MySQL in the past, on one occasion involving a change in the default
behaviour of MySQL itself, but the library upgrade seems more likely to
have caused a problem than anything else.

> Very weird that Akonadi would stop working on an upgrade of
> 1:10.3.38-0+deb10u1. Does it start working if you downgrade back to
> 1:10.3.37-0+deb10u1?

Yes, I wouldn't expect the issue described in the upstream bug tracker
to occur at this point, but I don't know what goes into the Debian
patching of this library, either.

Using "apt-cache showpkg libmariadb3", I found that the following
version was available via apt:

1:10.3.34-0+deb10u1

However, in my /var/cache/apt/archive collection, I have this later
version:

1:10.3.36-0+deb10u2

Trying to use dpkg to install this later version seems to make Akonadi
non-functional, as does any attempt to use apt to install the earlier
version, these being the respective commands:

dpkg -i
/var/cache/apt/archives/libmariadb3_1%3a10.3.36-0+deb10u2_amd64.deb
apt-get install libmariadb3=1:10.3.34-0+deb10u1

Both attempts yield errors like this in the Akonadi server log:

Tokenizer Warning: trailing garbage after angle-addr in Return-Path!

Maybe I am just failing to downgrade the library properly, and perhaps I
would also need to ensure that the MySQL server is also downgraded.

Paul

[Rai]

Hi all,

The same thing here:
I updated my debian 10 here on Tuesday and restarting the system Wednesday morning kmail and calendar get broken with the mentioned mysql error. Even deletion of ./local/share/akonadi and others does not help.

I think the mariadb stuff is the culprit one.

Here is the relevant part of dpkg.log:

2023-02-21 07:30:26 startup archives unpack
2023-02-21 07:30:26 upgrade isc-dhcp-client:amd64 4.4.1-2+deb10u2 4.4.1-2+deb10u3
2023-02-21 07:30:26 status half-configured isc-dhcp-client:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status unpacked isc-dhcp-client:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status half-installed isc-dhcp-client:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status triggers-pending man-db:amd64 2.8.5-2
2023-02-21 07:30:26 status unpacked isc-dhcp-client:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:26 upgrade isc-dhcp-common:amd64 4.4.1-2+deb10u2 4.4.1-2+deb10u3
2023-02-21 07:30:26 status half-configured isc-dhcp-common:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status unpacked isc-dhcp-common:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status half-installed isc-dhcp-common:amd64 4.4.1-2+deb10u2
2023-02-21 07:30:26 status unpacked isc-dhcp-common:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:26 upgrade libssl1.1:amd64 1.1.1n-0+deb10u3 1.1.1n-0+deb10u4
2023-02-21 07:30:26 status triggers-pending libc-bin:amd64 2.28-10+deb10u2
2023-02-21 07:30:26 status half-configured libssl1.1:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:26 status unpacked libssl1.1:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:26 status half-installed libssl1.1:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:26 status unpacked libssl1.1:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:26 upgrade mariadb-common:all 1:10.3.36-0+deb10u2 1:10.3.38-0+deb10u1
2023-02-21 07:30:26 status half-configured mariadb-common:all 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status unpacked mariadb-common:all 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status half-installed mariadb-common:all 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status unpacked mariadb-common:all 1:10.3.38-0+deb10u1
2023-02-21 07:30:26 upgrade libmariadb3:amd64 1:10.3.36-0+deb10u2 1:10.3.38-0+deb10u1
2023-02-21 07:30:26 status half-configured libmariadb3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status unpacked libmariadb3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status half-installed libmariadb3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status unpacked libmariadb3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:26 upgrade libnss3:amd64 2:3.42.1-1+deb10u5 2:3.42.1-1+deb10u6
2023-02-21 07:30:26 status half-configured libnss3:amd64 2:3.42.1-1+deb10u5
2023-02-21 07:30:26 status unpacked libnss3:amd64 2:3.42.1-1+deb10u5
2023-02-21 07:30:26 status half-installed libnss3:amd64 2:3.42.1-1+deb10u5
2023-02-21 07:30:26 status unpacked libnss3:amd64 2:3.42.1-1+deb10u6
2023-02-21 07:30:26 upgrade mariadb-client-core-10.3:amd64 1:10.3.36-0+deb10u2 1:10.3.38-0+deb10u1
2023-02-21 07:30:26 status half-configured mariadb-client-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status unpacked mariadb-client-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:26 status half-installed mariadb-client-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:27 status unpacked mariadb-client-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:27 upgrade mariadb-server-core-10.3:amd64 1:10.3.36-0+deb10u2 1:10.3.38-0+deb10u1
2023-02-21 07:30:27 status half-configured mariadb-server-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:27 status unpacked mariadb-server-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:27 status half-installed mariadb-server-core-10.3:amd64 1:10.3.36-0+deb10u2
2023-02-21 07:30:27 status unpacked mariadb-server-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:27 upgrade openssl:amd64 1.1.1n-0+deb10u3 1.1.1n-0+deb10u4
2023-02-21 07:30:27 status half-configured openssl:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:27 status unpacked openssl:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:27 status half-installed openssl:amd64 1.1.1n-0+deb10u3
2023-02-21 07:30:27 status unpacked openssl:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:27 startup packages configure
2023-02-21 07:30:27 configure libssl1.1:amd64 1.1.1n-0+deb10u4 <none>
2023-02-21 07:30:27 status unpacked libssl1.1:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:27 status half-configured libssl1.1:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:28 status installed libssl1.1:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:28 configure isc-dhcp-client:amd64 4.4.1-2+deb10u3 <none>
2023-02-21 07:30:28 status unpacked isc-dhcp-client:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 status half-configured isc-dhcp-client:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 status installed isc-dhcp-client:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 configure libnss3:amd64 2:3.42.1-1+deb10u6 <none>
2023-02-21 07:30:28 status unpacked libnss3:amd64 2:3.42.1-1+deb10u6
2023-02-21 07:30:28 status half-configured libnss3:amd64 2:3.42.1-1+deb10u6
2023-02-21 07:30:28 status installed libnss3:amd64 2:3.42.1-1+deb10u6
2023-02-21 07:30:28 configure mariadb-common:all 1:10.3.38-0+deb10u1 <none>
2023-02-21 07:30:28 status unpacked mariadb-common:all 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status half-configured mariadb-common:all 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status installed mariadb-common:all 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 configure mariadb-server-core-10.3:amd64 1:10.3.38-0+deb10u1 <none>
2023-02-21 07:30:28 status unpacked mariadb-server-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status half-configured mariadb-server-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status installed mariadb-server-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 configure libmariadb3:amd64 1:10.3.38-0+deb10u1 <none>
2023-02-21 07:30:28 status unpacked libmariadb3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status half-configured libmariadb3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status installed libmariadb3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 configure mariadb-client-core-10.3:amd64 1:10.3.38-0+deb10u1 <none>
2023-02-21 07:30:28 status unpacked mariadb-client-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status half-configured mariadb-client-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 status installed mariadb-client-core-10.3:amd64 1:10.3.38-0+deb10u1
2023-02-21 07:30:28 configure isc-dhcp-common:amd64 4.4.1-2+deb10u3 <none>
2023-02-21 07:30:28 status unpacked isc-dhcp-common:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 status half-configured isc-dhcp-common:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 status installed isc-dhcp-common:amd64 4.4.1-2+deb10u3
2023-02-21 07:30:28 configure openssl:amd64 1.1.1n-0+deb10u4 <none>
2023-02-21 07:30:28 status unpacked openssl:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:28 status half-configured openssl:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:28 status installed openssl:amd64 1.1.1n-0+deb10u4
2023-02-21 07:30:28 trigproc man-db:amd64 2.8.5-2 <none>
2023-02-21 07:30:28 status half-configured man-db:amd64 2.8.5-2
2023-02-21 07:30:28 status installed man-db:amd64 2.8.5-2
2023-02-21 07:30:28 trigproc libc-bin:amd64 2.28-10+deb10u2 <none>
2023-02-21 07:30:28 status half-configured libc-bin:amd64 2.28-10+deb10u2
2023-02-21 07:30:29 status installed libc-bin:amd64 2.28-10+deb10u2

Regards,

Rai

[Paul Boddie]

Hello again,

I looked at the packaging repository for libmariadb3 and found the
following commit importing the upstream sources for 10.3.38:

https://salsa.debian.org/mariadb-team/mariadb-10.3/-/commit/773fb3e04ffae2b4868876be632fb7244329e7c3

Looking at the diff, I found the following change to
libmariadb/libmariadb/mariadb_lib.c:

@@ -3879,7 +3881,7 @@ int STDCALL mysql_set_server_option(MYSQL *mysql,

ulong STDCALL mysql_get_client_version(void)
{
- return MARIADB_VERSION_ID;
+ return MARIADB_PACKAGE_VERSION_ID;
}

ulong STDCALL mysql_hex_string(char *to, const char *from, unsigned
long len)

This appears to be what the Qt bug report is describing:

"MariaDB 10.6 changed the mysql_get_client_version output to return the
library version (30200 as of 10.6.3) instead of the server version"

https://bugreports.qt.io/browse/QTBUG-95071

So, it seems that the changes to MariaDB 10.6 have leaked into 10.3,
thus causing this issue.

Paul

[Paul Boddie]

Hello again,

I also found the applicable upstream KDE bug:

"Akonadi fails with Mariadb 10.6.3"
https://bugs.kde.org/show_bug.cgi?id=439769

Meanwhile, applying the fix suggested for Qt 5.15 in a slightly modified form
seems to prevent the error I experienced from occurring.

With the modified libqt5sql5-mysql package installed, I have managed to start
Kontact, view stored messages (after some messing around with akonadiconsole,
due to earlier troubleshooting attempts), and this message will have been sent
from Kontact.

I conclude, then, that the upstream fix needs backporting to Qt 5.11.3 for
Debian.

Paul

[Rai]

Hi Paul,

Great work and big thanks for the findings.
But indeed, this change in mariadb_lib.c is a functional change which should have never made it in a security update. :(

Regards
Rai

Rainald Lampl

[Paul Boddie]

On Friday, 24 February 2023 09:55:18 CET Rai wrote:
> Hi Paul,
>
> Great work and big thanks for the findings.
> But indeed, this change in mariadb_lib.c is a functional change which should
> have never made it in a security update. :(

Agreed. Since this bug merely manifests itself in Kontact but must be fixed
elsewhere, I have opened a new bug against libqt5sql5-mysql:

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1031863

Sorry if this bug might have been retargeted, but it isn't obvious how that
might be done.

I imagine that we could make this existing bug dependent on the new bug, and
when the fix is hopefully made, we might then close both of them. It is
conceivable that Debian maintainers may wish to revert the regression in
libmariadb3 instead, but that it a matter for them to decide, and so I won't
go and create another bug against libmariadb3. I have merely mentioned the
possibility in the new bug description.

Paul