Finally, as promised, eventually: Fixing Akionadi over NFS

Firstly, apologies for the great lateness of this post.  I could say it was due to personal circumstances &c.; but I'll tell the truth for once, and say it was just me being a dizzy mare.

Anyway.  The scenario is this.  We have several clients running KDE, and using an NFS share for their home folder.  By default, Akonadi wants to run its own MySQL server, as the individual user, with data in the user's home folder.  Which means that every time a record needs to be modified, we have to lock a file  (to make sure only one process is going to modify it at once);  alter it; and then unlock it.  This is creating a lot of network traffic, as compared to just sending a query over the network, and it also has potential problems if the timing of all these messages gets messed up.

So the logical conclusion is, we need to have a central MySQL server for every user's Akonadi data, so the traffic is just queries and responses and we aren't relying on locking files on a remote server.  (If you read the first part of this story, you'll remember that this particular use case is actually violating assumption #3; this is a more complicated case than a simple home user, and there is someone in charge who ought to know better.)


As the NFS and NIS servers are already running on the same machine, we can easily set up a MySQL server there as well.  In fact, I already installed MySQL anyway when I built the box, because I suspected it might be necessary for something.  There's almost no good reason not to; MySQL hardly consumes any resources when idling.

I'm going to suppose the NIS / NFS /MySQL server is 192.168.0.2.  We'll create a single MySQL user "fred" for all the Akonadi databases  (there will be one per user)  and this user obviously requires a password as they are logging in remotely.  This is a tiny bit lax, as it means a savvy user could see other people's Akonadi data; but none of our users have any secrets from each other anyway. If your network needs more sophistication, so be it. You will just need to create a separate MySQL user for each Akonadi user, and keep track of their passwords.

First we need to make some changes to the server configuration, so as to match what Akonadi is expecting. Open a terminal and login to the server;

$ ssh root@192.168.0.2
# nano /etc/mysql/my.cnf

Insert the following somewhere after [mysqld] but before the next opening square bracket character;

# Make table names capitalisation-insensitive
lower_case_table_names=1
# Avoid "MySQL server has gone away" errors
wait_timeout=31536000

Save the file, and restart the MySQL server with the modified configuration;

# service mysqld restart
# mysql
mysql> CREATE DATABASE akonadi_julie;
mysql> GRANT ALL ON akonadi_julie TO "fred"@"192.168.%" IDENTIFIED BY "b00bies";

Create as many databases as you need, and grant privileges on them. The last command you need to give is

mysql> FLUSH PRIVILEGES;

to make sure that remote clients can connect properly.  Press ctrl-D to exit MySQL, and again to break the SSH connection to the server. You're now back to being you, on your workstation.

First of all, make a copy of a file that is about to get trashed;

$ cp .config/akonadi/akonadiserverrc .config/akonadi/akonadiserverrc.0

Now you need to start the "akonaditray" application;

$ akonaditray &

Wait for it to finish launching, and look for a new entry in the system tray  (it might be hidden behind the "show hidden icons" arrow).  Right-click it and select "Configure".  Then click on the right-hand tab (Akonadi Server Configuration).  Don't change anything just yet.

Now search for a MySQL process with a --socket=/home/something option in its command line;

$ ps aux | grep mysql

Highlight the whole --socket=..... string by left-clicking and dragging. Then you can middle-click to paste it into the next command;

$ mysqldump --socket=..... akonadi | mysql -h192.168.0.2 -ufred -p akonadi_julie

Give the password b00bies when prompted. This will take a dump of the existing "akonadi" database, and pipe it into the "akonadi_julie" database on 192.168.0.2.  Now all we need to do, is reconfigure Akonadi to use the remote database.  So, in "Akonadi Server Configuration", change the settings as follows:

Use internal MySQL server -- untick this
Database Name	akonadi_julie
Host	192.168.0.2
Username	fred
Password	b00bies
Options	MYSQL_OPT_RECONNECT=1

(the "options" line should also help avoid the risk of "MySQL server has gone away" errors.)  Restart the Akonadi server and make sure it does not give any errors -- you may have to do this a couple of times but it will work in the end.  In the terminal, do

$ ps aux | grep mysql

again and make sure the original MySQL process has gone away.  Right-click the akonaditray icon and close it down.

You now need to repeat this procedure for every user.

You could edit /home/*/.config/akonadi/akonadiserverrc directly, and use akonadictl restart to restart the Akonadi server; but the GUI method isn't really much slower.