akonadiserver and MySQL upgrades

This is a wiki page. Be bold and improve it!

If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.

Sometimes, akonadi will not start because a SQL upgrade is required.

DBUpdater: query error

org.kde.pim.akonadiserver: Running DB initializer
org.kde.pim.akonadiserver: DB initializer done
org.kde.pim.akonadiserver: DBUpdater: query error: "Invalid default value for 'atime' QMYSQL: Unable to execute query" " "
org.kde.pim.akonadiserver: Query was:  "ALTER TABLE PimItemTable ROW_FORMAT=DYNAMIC"
org.kde.pim.akonadiserver: Target version was:  41
org.kde.pim.akonadiserver: Mandatory:  true
org.kde.pim.akonadiserver: DBUpdater: query error: "Invalid default value for 'atime' QMYSQL: Unable to execute query" " "
org.kde.pim.akonadiserver: Query was:  "ALTER TABLE PimItemTable MODIFY COLUMN remoteId VARBINARY(1024)"
org.kde.pim.akonadiserver: Target version was:  41
org.kde.pim.akonadiserver: Mandatory:  true
org.kde.pim.akonadiserver: Failed to commit transaction for database update
org.kde.pim.akonadiserver: Unable to initialize database.
org.kde.pim.akonadiserver: Shutting down AkonadiServer...
org.kde.pim.akonadicontrol: Application '/usr/bin/akonadiserver' exited normally...
QFileSystemWatcher::removePaths: list is empty
QFileSystemWatcher::removePaths: list is empty

Check if the mysql server for akonadi is running:

$ ps aux | grep mysql
17276 /usr/sbin/mysqld --defaults-file=/home/user/.local/share/akonadi/mysql.conf --datadir=/home/user/.local/share/akonadi/db_data/ --socket=/var/run/user/1001/akonadi/mysql.socket --pid-file=/var/run/user/1001/akonadi/mysql.pid

Kill the corresponding process:
$ kill 17276

Manually start the mysql server:

$ /usr/sbin/mysqld --defaults-file=/home/user/.local/share/akonadi/mysql.conf --datadir=/home/user/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-mysql.socket

In another console tab, connect to it:
$ mysql -S /tmp/akonadi-mysql.socket
mysql> USE akonadi

Then perform any SQL query upgrade that is required:
ALTER TABLE PimItemTable CHANGE atime atime timestamp NOT NULL DEFAULT current_timestamp();
mysql> exit

Kill the mysql instance that you started above. Start akonadi server as usual.

akonadiserver won't start at all

$ akonadictl start
Connecting to deprecated signal QDBusConnectionInterface::serviceOwnerChanged(QString,QString,QString)
org.kde.pim.akonadiserver: Starting up the Akonadi Server...
org.kde.pim.akonadiserver: database server stopped unexpectedly
org.kde.pim.akonadiserver: Database process exited unexpectedly during initial connection!
org.kde.pim.akonadiserver: executable: "/usr/sbin/mysqld"
org.kde.pim.akonadiserver: arguments: ("--defaults-file=/home/user/.local/share/akonadi/mysql.conf", "--datadir=/home/user/.local/share/akonadi/db_data/", "--socket=/var/run/user/1001/akonadi/mysql.socket", "--pid-file=/var/run/user/1001/akonadi/mysql.pid")
org.kde.pim.akonadiserver: stdout: ""
org.kde.pim.akonadiserver: stderr: ""
org.kde.pim.akonadiserver: exit code: 1
org.kde.pim.akonadiserver: process error: "Unknown error"
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/user/1001/akonadi/mysql.socket' (2)'
Check that mysqld is running and that the socket: '/var/run/user/1001/akonadi/mysql.socket' exists!
org.kde.pim.akonadiserver: Failed to remove runtime connection config file
org.kde.pim.akonadiserver: Shutting down AkonadiServer...
org.kde.pim.akonadicontrol: Application '/usr/bin/akonadiserver' exited normally...
QFileSystemWatcher::removePaths: list is empty
QFileSystemWatcher::removePaths: list is empty

Generally, the way to debug this kind of problem, is to try to start the MySQL server manually, and see what errors come up.

In one console window, follow the content mysql.err, and see what errors come up while you try to start the server:

$ tail -f ~/.local/share/akonadi/db_data/mysql.err

In another console, start the server, using the same options as noted in the above akonadi server output:
$ /usr/sbin/mysqld  --defaults-file=/home/user/.local/share/akonadi/mysql.conf --datadir=/home/user/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-mysql.socket

Now, if you get errors like below, simply delete the corresponding variables from mysql.conf:

[ERROR] [MY-000067] [Server] unknown variable 'log_warnings=2'
[ERROR] [MY-000067] [Server] unknown variable 'query_cache_size=0'.
[ERROR] [MY-000067] [Server] unknown variable 'query_cache_type=0'.