How to setup your external SQL server for Akonadi

The reasons why Akonadi starts its own database instance are sometimes unclear to users.
In a perfect world, Akonadi, Amarok, Digikam… would use the same database but well… stop dreaming, the reality is different, there are valid reasons.

Currently Akonadi can start and work with both MySQL (and variants) or Postgresql.  It even supports using an external server.

You may wonder why we prefer starting a dedicated instance of your favorite server. There are two main reasons for that:

  • Users should not have to be root to set up Akonadi. Using an external server means you have to create a user, grant him some privileges and create a database before starting Akonadi; In fact, users should not have to bother about what Akonadi is.
  • Akonadi needs some special settings to work correctly. For obvious reasons, the Akonadi server cannot change your central MySQL settings.

 

How to configure your external server ?

The answer for MySQL is easy: use the same settings as the internal server.

For desktop users: mysql-global.conf

For mobile users: mysql-global-mobile.conf

The wait_timeout tweak is *very* important. The default value is 8 hours and once reached, you end up with this error: MySQL server has gone away QMYSQL: Unable to execute query which leads to all kind of issues (eg. 219710#3226400#c8)

 

Using an external Postresql server is also possible but you must have standard_conforming_strings = on in your postgres.conf. If off, Akonadi will not be able to fill its tables and you end up with issues such as bug 267807.

The Postgres doc claims «the default will change to on in a future release for improved standards compliance» since 8.2. Nothing happened yet…

 

 

 

About these ads

Written by krop

21 July 2011 at 23:24

Posted in Akonadi, KDE, kdepim

16 Responses

Subscribe to comments with RSS.

  1. In a perfect world Akonadi is unnecessary, but well… stop dreaming, the reality is different, there are valid reasons… like killing a pleasure to use computers.

    merez

    22 July 2011 at 08:09

    • Hey Merez, I’m sure someone with such strong opinions as yours must be speaking from a position of authority… could you go ahead and post some links to your publicly available work so we can see the class and quality of the contributions you’ve made to the F/OSS community?

      Nathan

      22 July 2011 at 22:05

    • So you don’t contribute anything, you just bad mouth the work of others? Why should anyone listen to you?

      Nathan

      24 July 2011 at 12:52

  2. Wouldn’t it still be possible to have a centrally controlled shared non-root mysql for KDE applications? So you wouldn’t use the system one, but would at least have only one additional database, not 3.

    Andre

    22 July 2011 at 08:16

    • While Akonadi server (the process working on the database) is not a KDE application, a KDE configuration tool could of course create an Akonadi configuration that makes it share an otherwise started MySQL instance.

      No idea about the other programs but very likely it would work for them as well.

      Kevin Krammer

      26 July 2011 at 17:28

  3. Hi, thank you for blogging about this issue, as I’ve explained @ https://bugs.kde.org/show_bug.cgi?id=274750#c8 the reasons mentioned still don’t convince me, sadly I don’t feel to have enough time to dig deeply in Akonadi source code to try to fix the situation at the moment but everibody starting it server is just wrong.

    Cannot speak for postgres issues but for mysql it’s as simple as doing a mysql_ping() before queryes to avoid timeout issues, the max_allowed_packet and max_connections are nowadays set to reasonable defaults by every distro out there. In addition these can be checked at startup easily.

    Francesco R.

    22 July 2011 at 11:34

  4. Is it possible to migrate the local mysql db to the “central” one?

    Dimitar

    22 July 2011 at 13:01

    • « In a perfect world, Akonadi, Amarok, Digikam… would use the same database but well… stop dreaming, the reality is different, there are valid reasons. »

      Could you please let us have a short list of those valid reasons? And while we’re at it, I would also like to understand why there is a need for mysql at all.

      Philippe Clérié

      22 July 2011 at 15:42

  5. Interesting, I have all them running in an external mysql server right now. Never thought it wasn’t possible…

    Laerte

    22 July 2011 at 18:47

  6. I did the migration, too :) Besides some problems with kmail not mixing the paths to mail directories, everything looks fine.

    Dimitar

    22 July 2011 at 23:42

  7. @krop, thanks for your reply. I did google for the info and I am surprised I did not get those links. :-)

    Anyway, I believe Digikam’s use of Mysql is entirely optional. On the other hand, should one choose that option, I don’t know if Digikam will use a running instance of mysql. I use sqlite and I have no intention of changing.

    I am not entirely buying Amarok’s justification for using Mysql. I have no doubt about the performance gains. But, the way Amarok is used, the difference between a microsecond and a millisecond is probably not going to be noticeable. And how often does one rebuild a collection? Even if done daily, the few minutes it takes don’t seem to be so bad. Not with dual and quad cores as standard equipment. Additionally, it’s not at all clear what is meant by “large collection”. I have about 4000 tracks, all flac, all ripped from my CD collection. It’s such a small number that surely the speed of mysql is not likely to make a difference. But what is a big number? At what point does rebuilding a collection becomes painful? I don’t know and the FAQ does not say.

    Akonadi has a different story. They are using Mysql apparently because sqlite has issues with “concurrency”. Unfortunately there are no descriptions of these issues. But, recent versions of sqlite do handle concurrent read access by multiple processes and serialize writes. Even if one uses kontact heavily, I hardly think that concurrent writes are going to be so frequent that sqlite cannot handle them. I haven’t tested it so I may be totally wrong here. Still, I would expect something like sqlite to be sufficiently well behaved in most cases.

    In conclusion, I am not at all convinced that using mysql is mandatory for these applications. On the other hand, that’s what we have. As you said, we have to deal with reality. But I don’t have to like it. ;-)

    Philippe Clérié

    23 July 2011 at 00:34

    • “Even if one uses kontact heavily, I hardly think that concurrent writes are going to be so frequent that sqlite cannot handle them.”

      I don’t think heavy use is required, sounds like even moderate or light use may trigger such problems easily. As it seem like any edit action like writing and sending a new mail, moving or removing mails, updating calendar or address book at the same time as Akonadi is fetching new mails will result in concurrent writes. Even for light use this would be a rather common scenario.

      Morty

      23 July 2011 at 19:21

  8. I actually use Akonadi with SQLite …so that’s also an option, although kinda hidden ;)

    Matija "hook" Šuklje

    23 July 2011 at 21:10

    • I had not been aware that it was even an option. And, there’s even a debian package for it in testing (akonadi-backend-sqlite). I am going to try it soonest!

      Thanks for the tip!!!
      :-)

      Philippe Clérié

      23 July 2011 at 22:54


Comments are closed.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: