Why does Akonadi not start after upgrade from Ubuntu 19.04 to 19.10?

Getting the same trouble, akonadi not running because of MySQL after the upgrade. For me installing MariaDB is not an option, because of my work. I was using MariaDB before and I had to switch to MySQL.

   1   │ 2019-11-17T22:14:02.183446Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: C
       │ urrent value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
   2   │ 2019-11-17T22:14:02.183483Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17-0ubuntu2) startin
       │ g as process 30942
   3   │ 2019-11-17T22:14:02.186416Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an a
       │ lias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider usi
       │ ng UTF8MB4 in order to be unambiguous.
   4   │ 2019-11-17T22:14:02.186429Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a colla
       │ tion of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation ins
       │ tead.
   5   │ 2019-11-17T22:14:02.194794Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
   6   │ 2019-11-17T22:14:02.194929Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
   7   │ 2019-11-17T22:14:02.195077Z 0 [ERROR] [MY-010119] [Server] Aborting
   8   │ 2019-11-17T22:14:02.195315Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.1
       │ 7-0ubuntu2)  (Ubuntu).

These are the error and warnings I am getting.

First will get rid of noisy warnings vim /home/mathieu/.local/share/akonadi/mysql.conf

replace character_set_server=utf8 to become utf8mb4 comment out collation_server= according to this doc, default is good https://dev.mysql.com/doc/refman/8.0/en/charset-server.html

I don't think we can do anything about secure_file_priv= I believe akonadi needs it empty to be able to load files from arbitrary locations. doc: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

then, the actual error Failed to find valid data directory

since akonadi is mostly temporary data, I think the easiest is to kill the db_data directory and start over. we'll rename the folder instead of deleting it

$ cd ~/.local/share/akonadi
$ mv db_data db_databkp
$ mkdir db_data
$ /usr/sbin/mysqld-akonadi --defaults-file=/home/mathieu/.local/share/akonadi/mysql.conf --datadir=/home/mathieu/.local/share/akonadi/db_data/ --socket=/run/user/1000/akonadi/default/mysql.socket --pid-file=/run/user/1000/akonadi/default/mysql.pid --initialize --console

the --initialize option starts the db_data dir fresh. if you compare both dir, you will see a bunch of files that are different from the previous one.

now getting this error

[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').

I am switching this option lower_case_table_names= in mysql.conf from 1 to 0

you also need to comment out this deprecated option

log_warnings=2

I am no longer calling mysqld-akonadi directly, with the long set of arguments, but simply running akonadiserver and cating the mysql.error log file

getting this error now [Server] unknown variable 'query_cache_size=0'

will comment this one on

also need to comment out query_cache_type=0

and akonadi is able to run with MySQL 8

In Summary:

• utf8 to utf8mb4 is a good change, leaving both commented out is the new default
• comment out the 4 deprecated options
• move away your old db_data directory, create an empty dir instead
• restart akonadiserver

Hope this helps

Update: if you are getting this error

org.kde.pim.akonadiserver: Running DB initializer
org.kde.pim.akonadiserver: "\nSql error: Duplicate column name 'version' QMYSQL: Unable to execute query\nQuery: ALTER TABLE SchemaVersionTable ADD COLUMN version INTEGER NOT NULL DEFAULT 0"

it means the column was already added, but the database migration was not flagged as done. I would recommend killing off the db_data folder again, running the initialize manually. and starting akonadiserver

its finally running for me. and korganizer that was constantly crashing in 19.04 now runs ;)

Update (2020): Be very careful of this bug when resetting your Akonadi DB https://bugs.kde.org/show_bug.cgi?id=414400

Since 19.10, I had too many issues. Neither MariaDB or MySQL 8 worked fine. Kept having to reset Akonadi. Finally ran MySQL 5.6 and 5.7 via Docker

sudo docker run --name mysql57 --rm -p 3306:3306 -v /var/lib/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=docker -d mysql:5.7

but kept having issues. I gave up on Akonadi/kdepim, uninstalled all related packages and switch to Thunderbird. Plasma runs smoothly.

mariadb should now be used. mysql 8 is not comapatible.

sudo apt install mariadb-server-core-10.3 mariadb-client-core-10.3

I also had Akonadi problems since updating from Kubuntu 19.04 to 19.10. The mysql log file ~/.local/share/akonadi/db_data/mysql.err contained errors like:

unknown variable 'log-warnings=2'

So, I commented those out in /home/NNN/.local/share/akonadi/mysql.conf:

# print warnings and connection errors (default:1)
#log_warnings=2
 .
 .
# Memory allocated for caching query results (default:0 (disabled))
#query_cache_size=0 
. .
# Do not cache results (default:1)
#query_cache_type=0

To be honest, I didn't care, what variables were changed and what the consequences would be...

The error is saying that there is a user table in MySQL with the name index_stats which seems strange. Unless you or one of the programs you use created that table.

In other words you can't have tables with that name anymore because MySQL 8.0 uses a table with that name.

You can try renaming the file to something like index_stats_bak.frm but its hard to say what will happen to whatever program uses that.

This post has a list of table names that are now used by the system, index_stats among them.

Looking at the source for Akonadi that creates tables it seems very unlikely there would be a conflict with MySQL 8. My guess is that there was a partial upgrade of MySQL that has left the part of the new tables but not all. index_stats was probably created in that partial update.