373782 2016-12-17 18:34:00 +0000 akonadi failed to append item 2017-01-03 22:10:54 +0000 1 1 3 Frameworks and Libraries Akonadi server unspecified Neon Linux RESOLVED FIXED NOR grave --- 1 piral pim-bugs-null bernhard+kde dvratil jsalatas kde lacsilva nicolas https://commits.kde.org/akonadi/85c5f22b836805ecd20a7ac8eaae0a6861bdcd24 5.4.1 65 oldest_to_newest 1645598 0 piral 2016-12-17 18:34:00 +0000 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 1645600 1 bernhard+kde 2016-12-17 18:50:09 +0000 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" 1645729 2 jsalatas 2016-12-18 10:38:17 +0000 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" 1645885 3 bernhard+kde 2016-12-19 08:13:33 +0000 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 1645893 4 jsalatas 2016-12-19 09:32:38 +0000 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! 1647415 5 t.zachmann 2016-12-25 17:42:14 +0000 *** Bug 373871 has been marked as a duplicate of this bug. *** 1649192 6 kde 2017-01-03 10:37:09 +0000 Seeing the same thing... 1649212 7 nicolas 2017-01-03 11:50:28 +0000 *** This bug has been confirmed by popular vote. *** 1649246 8 dvratil 2017-01-03 14:58:26 +0000 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). 1649247 9 nicolas 2017-01-03 15:03:58 +0000 In my case it is Neon User Edition with mysql-server-core-5.7 version 5.7.16-0ubuntu0.16.04.1 1649290 10 bernhard+kde 2017-01-03 19:04:27 +0000 (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 1649311 11 lacsilva 2017-01-03 21:16:40 +0000 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 1649314 12 jsalatas 2017-01-03 21:21:18 +0000 # 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 1649333 13 dvratil 2017-01-03 22:10:54 +0000 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