Bug 402673

Summary:	Akonadi server incompatible with latest Mysql defaults (NO_ZERO_DATE enabled by default) when using database created using older akonadi versions
Product:	[Frameworks and Libraries] Akonadi	Reporter:	Matthew Dawson <matthew>
Component:	server	Assignee:	kdepim bugs <kdepim-bugs>
Status:	REPORTED ---
Severity:	major	CC:	flyser42, kde_bugs, nicolas
Priority:	NOR
Version:	unspecified
Target Milestone:	---
Platform:	Gentoo Packages
OS:	Linux
Latest Commit:		Version Fixed In:

Description Matthew Dawson 2018-12-29 03:37:26 UTC

After updating my system to the latest packages on Gentoo, akonadi (upgrading from 18.08.2 to 18.12.0) no longer starts.  Enabling verbose output gives the following text:

org.kde.pim.akonadiserver: Updating indexes
org.kde.pim.akonadiserver: "CREATE  INDEX PimItemTable_mimeTypeIndex ON PimItemTable (mimeTypeId)"
org.kde.pim.akonadiserver: Updating index failed:  
Sql error: Invalid default value for 'atime' QMYSQL: Unable to execute query
Query: CREATE  INDEX PimItemTable_mimeTypeIndex ON PimItemTable (mimeTypeId)

It appears mysql 5.7 by defaults enables NO_ZERO_DATE.  When attempting to update the database, mysql refuses to touch the affected table as the table definition is invalid.  I assume I didn't have issues on the older Akonadi versions as I only got upgraded to 5.7 in Novemeber, and there were no interesting updates at that time to the PimItemTable.  As the default wasn't used during insert, Akonadi was able to operate normally.

It seems to the best option is to add a migration to change all TIMESTAMP columns to have an appropriate default, or disable NO_ZERO_DATE in the mysql configuration explicitly.  I'd be happy to create/contribute a patch for either option (I have a backup of my database to test against!).

Comment 1 Nicolas 2019-02-03 18:47:36 UTC

I am also affected by this bug. After I had to manually upgrade the db via mysql_upgrade first, I ran into the above issue.

Comment 2 Anguo 2019-03-24 02:12:20 UTC

I couldn't start akodani nor kmail.

I had to edit the following configuration file and add the given sql_mode setting in [mysqld] in order to start kmail normally.

~/.local/share/akonadi/mysql.conf  
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION