Bug 373782 - akonadi failed to append item
Summary: akonadi failed to append item
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: server (show other bugs)
Version: unspecified
Platform: Neon Linux
: NOR grave with 65 votes (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
: 373871 (view as bug list)
Depends on:
Blocks:
 
Reported: 2016-12-17 18:34 UTC by pipapo
Modified: 2017-01-03 22:10 UTC (History)
6 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.4.1


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description pipapo 2016-12-17 18:34:00 UTC
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
Comment 1 Bernhard Scheirle 2016-12-17 18:50:09 UTC
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"
Comment 2 John Salatas 2016-12-18 10:38:17 UTC
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"
Comment 3 Bernhard Scheirle 2016-12-19 08:13:33 UTC
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
Comment 4 John Salatas 2016-12-19 09:32:38 UTC
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!
Comment 5 T Zachmann 2016-12-25 17:42:14 UTC
*** Bug 373871 has been marked as a duplicate of this bug. ***
Comment 6 Kai Uwe Broulik 2017-01-03 10:37:09 UTC
Seeing the same thing...
Comment 7 Nicolas 2017-01-03 11:50:28 UTC
*** This bug has been confirmed by popular vote. ***
Comment 8 Daniel Vrátil 2017-01-03 14:58:26 UTC
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).
Comment 9 Nicolas 2017-01-03 15:03:58 UTC
In my case it is Neon User Edition with mysql-server-core-5.7 version 5.7.16-0ubuntu0.16.04.1
Comment 10 Bernhard Scheirle 2017-01-03 19:04:27 UTC
(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
Comment 11 Luis Silva 2017-01-03 21:16:40 UTC
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
Comment 12 John Salatas 2017-01-03 21:21:18 UTC
# 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
Comment 13 Daniel Vrátil 2017-01-03 22:10:54 UTC
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