After the upgrade to neon LTS with akonadi 4:16.12.0-0neon+16.04+build2 kmail does not recieve or send mails. After creating a new user I get the message "failed to append item". In aconadiconsole there was a hint about mysql incorrect datetime values. After some research I found this: sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER" I put this line to ~.local/share/mysql.cnf and akonadi was able to write to the database again. This options can also be found as comment under # TODO: make Akonadi work with those settings enabled
I also encounter this bug (KDE Neon User Edition - 16.12 update) > akonadictl restart [...] org.kde.pim.akonadiserver: DATABASE ERROR: org.kde.pim.akonadiserver: Error code: 1292 org.kde.pim.akonadiserver: DB error: "Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime' at row 1" org.kde.pim.akonadiserver: Error text: "Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime' at row 1 QMYSQL3: Der Befehl konnte nicht ausgeführt werden" 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: '0000-00-00 00:00:00' for column 'datetime' at row 1 QMYSQL3: Der Befehl konnte nicht ausgeführt werden"
Same here :( (In reply to Bernhard Scheirle from comment #1) > I also encounter this bug (KDE Neon User Edition - 16.12 update) > > > akonadictl restart > [...] > org.kde.pim.akonadiserver: DATABASE ERROR: > org.kde.pim.akonadiserver: Error code: 1292 > org.kde.pim.akonadiserver: DB error: "Incorrect datetime value: > '0000-00-00 00:00:00' for column 'datetime' at row 1" > org.kde.pim.akonadiserver: Error text: "Incorrect datetime value: > '0000-00-00 00:00:00' for column 'datetime' at row 1 QMYSQL3: Der Befehl > konnte nicht ausgeführt werden" > 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: '0000-00-00 00:00:00' for column 'datetime' at > row 1 QMYSQL3: Der Befehl konnte nicht ausgeführt werden"
With no manual config changes^1 SELECT @@sql_mode; returns on my machine: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION If I now set sql_mode in `~/.local/share/akonadi/mysql.conf` to sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (Only removed `STRICT_TRANS_TABLES`) everything works fine again. My first thought was that maybe an mysql update changed the default value of sql_mode and added STRICT_TRANS_TABLES to it. But STRICT_TRANS_TABLES is enabled by default since mysql 5.7.5 ^2 ^1: especially with no sql_mode in `~/.local/share/akonadi/mysql.conf` ^2: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Bernhard Scheirle's suggestions to add sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION in ~/.local/share/akonadi/mysql.conf works for me too. Thanks!
*** Bug 373871 has been marked as a duplicate of this bug. ***
Seeing the same thing...
*** This bug has been confirmed by popular vote. ***
Assuming you all use Oracle MySQL, please state the version that you have. The package will be called something like mysql-server (but depends on a distro).
In my case it is Neon User Edition with mysql-server-core-5.7 version 5.7.16-0ubuntu0.16.04.1
(In reply to Daniel Vrátil from comment #8) > Assuming you all use Oracle MySQL, please state the version that you have. I'm also using KDE Neon (User Edition). $ mysqld-akonadi --version /usr/sbin/mysqld Ver 5.7.16-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) $ mysql --version mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper $ dpkg -s mysql-server-core-5.7 | grep -i version Version: 5.7.16-0ubuntu0.16.04.1
Using KDE Neon Edition > mysqld-akonadi --version /usr/sbin/mysqld Ver 5.7.16-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) > dpkg -s mysql-server-core-5.7 | grep -i version Version: 5.7.16-0ubuntu0.16.04.1
# mysqld-akonadi --version /usr/sbin/mysqld Ver 5.7.16-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) # dpkg -l mysql-server-core dpkg-query: no packages found matching mysql-server-core # dpkg -l | grep mysql-server-core ii mysql-server-core-5.7 5.7.16-0ubuntu0.16.04.1 amd64 MySQL database server binaries # However the issue is resolved after applying Bernhard Scheirle's suggestion in Comment 3: https://bugs.kde.org/show_bug.cgi?id=373782#c3
Git commit 85c5f22b836805ecd20a7ac8eaae0a6861bdcd24 by Daniel Vrátil. Committed on 03/01/2017 at 22:02. Pushed by dvratil into branch 'Applications/16.12'. AKAPPEND: ensure we always insert with a valid date Latest MySQL does not like null datetime for some reason, so make sure we always have a valid date on INSERT. FIXED-IN: 5.4.1 M +5 -4 src/server/handler/akappend.cpp https://commits.kde.org/akonadi/85c5f22b836805ecd20a7ac8eaae0a6861bdcd24