[Akonadi] [Bug 402673] New: Akonadi server incompatible with latest Mysql defaults (NO_ZERO_DATE enabled by default) when using database created using older akonadi versions

[Matthew Dawson]

https://bugs.kde.org/show_bug.cgi?id=402673

            Bug ID: 402673
           Summary: Akonadi server incompatible with latest Mysql defaults
                    (NO_ZERO_DATE enabled by default) when using database
                    created using older akonadi versions
           Product: Akonadi
           Version: unspecified
          Platform: Gentoo Packages
                OS: Linux
            Status: REPORTED
          Severity: major
          Priority: NOR
         Component: server
          Assignee: kdepim-bugs at kde.org
          Reporter: matthew at mjdsystems.ca
  Target Milestone: ---

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!).

-- 
You are receiving this mail because:
You are the assignee for the bug.