akonadi fails to start after upgrade to yakkety

[Marco Gamberoni]

After yesterday 15 sept. upgrade to Kubuntu 16.10 Yakkety Yak, kmail fails to start, because akonadi backend fails.

Starting akonadi from command line,
    $ akonadictl start
the output contains:
   stderr: "mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)\n2016-10-16T11:54:23.365277Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.\n2016-10-16T11:54:23.365377Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).\n2016-10-16T11:54:23.365479Z 0 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files\n2016-10-16T11:54:23.365488Z 0 [ERROR] Aborting\n\n2016-10-16T11:54:23.365508Z 0 [Note] Binlog end\n"

The cause is a regression in the file
    /etc/xdg/akonadi/mysql-global.conf
provided by the package
    akonadi-backend-mysql

Diff of the content of mysql-global.conf from Yakkety vs Xenial:
<code>

diff -u "akonadi-backend-mysql_16.04.3-0ubuntu2_all.deb - mysql-global.conf" "akonadi-backend-mysql_15.12.3-0ubuntu6_all.deb - mysql-global.conf"
--- "akonadi-backend-mysql_16.04.3-0ubuntu2_all.deb - mysql-global.conf" 2016-06-24 15:51:12.000000000 +0200
+++ "akonadi-backend-mysql_15.12.3-0ubuntu6_all.deb - mysql-global.conf" 2016-04-20 10:42:09.000000000 +0200
@@ -100,5 +100,14 @@
 # We use InnoDB, so don't let MyISAM eat up memory
 key_buffer_size=16K

+# KUBUNTU:
+# Unset the export dir check as only the full mysql-server package creates it
+secure_file_priv=
+
+# KUBUNTU:
+# workaround for 5.7 being more strict with column types
+# (reverts to 5.6 behavior)
+sql_mode=NO_ENGINE_SUBSTITUTION
+
 [client]
 default-character-set=utf8

</code>

Applying this patch fixes the bug.

The "secure_file_priv" setting went missing is referenced in the package changelog:
   akonadi (4:15.12.3-0ubuntu5) xenial; urgency=medium

     * kubuntu_disable_secure_file_priv_check.diff:
    - Set secure_file_priv to empty as that directory is not set in a default
      kubuntu install

    -- Philip Muškovac <email address hidden> Mon, 18 Apr 2016 15:24:43 +0200

Marco Gamberoni

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[xabilon (xabilon)]

Fixed with this command :

sudo mkdir /var/lib/mysql-files

[xor (xor)]

Also affects Ubuntu 14.04.5 LTS.
The whole of Kontact/Kmail is broken.
The reason seems to be the mysql security updates of 2016-10-26:

------------------------------------------------------------------------
Start-Date: 2016-10-26 00:40:44
Upgrade: mysql-common:amd64 (5.5.52-0ubuntu0.14.04.1, 5.5.53-0ubuntu0.14.04.1)
End-Date: 2016-10-26 00:40:51

Start-Date: 2016-10-26 00:41:16
Upgrade: mysql-server-core-5.5:amd64 (5.5.52-0ubuntu0.14.04.1, 5.5.53-0ubuntu0.14.04.1)
End-Date: 2016-10-26 00:41:25

Start-Date: 2016-10-26 00:41:45
Upgrade: mysql-client-core-5.5:amd64 (5.5.52-0ubuntu0.14.04.1, 5.5.53-0ubuntu0.14.04.1), mysql-common:amd64 (5.5.52-0ubuntu0.14.04.1, 5.5.53-0ubuntu0.14.04.1), libmysqlclient18:amd64 (5.5.52-0ubuntu0.14.04.1, 5.5.53-0ubuntu0.14.04.1)
End-Date: 2016-10-26 00:41:53
------------------------------------------------------------------------

The log file "~/.local/share/akonadi/akonadiserver.error" says:
------------------------------------------------------------------------
Database process exited unexpectedly during initial connection!
executable: "/usr/sbin/mysqld-akonadi"
arguments: ("--defaults-file=/home/username/.local/share/akonadi/mysql.conf", "--datadir=/home/username/.local/share/akonadi/db_data/", "--socket=/tmp/akonadi-username.EZqgDd/mysql.socket")
stdout: ""
stderr: "/usr/sbin/mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2)
161027 5:22:54 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
161027 5:22:54 [ERROR] Aborting
------------------------------------------------------------------------

[Philip Muškovac (yofel)]

Right, the patches don't get applied anymore in yakkety.

The trusty issue technically has a different cause, but it's the same issue so lets get both fixed.

[Marc Deslauriers (mdeslaur)]

I'll handle akonadi in precise and trusty since they were caused by the MySQL security update.

[Marc Deslauriers (mdeslaur)]

After installing updated akonadi packages, users may still be required to remove their ~/.local/share/akonadi/mysql.conf file to get the system one copied over again.

[Philip Muškovac (yofel)]

All supported versions of akonadi will check the timestamps of the user and system config files and auto-update it unless the user one was modified. So that should cover most cases.

[Launchpad Janitor (janitor)]

This bug was fixed in the package akonadi - 1.12.1-0ubuntu1.2

---------------
akonadi (1.12.1-0ubuntu1.2) trusty-security; urgency=medium

  * debian/patches/kubuntu_disable_secure_file_priv_check.diff: fix
    compatibility with stricter defaults in mysql security update.
    (LP: #1633855)

 -- Marc Deslauriers <email address hidden> Thu, 27 Oct 2016 07:21:38 -0400

[Launchpad Janitor (janitor)]

This bug was fixed in the package akonadi - 1.7.2-0ubuntu1.1

---------------
akonadi (1.7.2-0ubuntu1.1) precise-security; urgency=medium

  * debian/patches/kubuntu_disable_secure_file_priv_check.diff: fix
    compatibility with stricter defaults in mysql security update.
    (LP: #1633855)

 -- Marc Deslauriers <email address hidden> Thu, 27 Oct 2016 07:23:48 -0400

[Clive Johnston (clivejo)]

I have uploaded what I believe to be a fixed package for akonadi to my PPA

https://launchpad.net/~clivejo/+archive/ubuntu/yakkety/+packages?field.name_filter=akonadi&field.status_filter=published&field.series_filter=

Can someone who has this problem, please test that the fix works as expected? Please only use my PPA to test this one package and remove it afterwards.

[Lars Tangvald (lars-tangvald)]

I don't fully understand the cause of this issue, so I'm wondering if this is also likely to be problem with the MySQL 5.5.53 update coming on Debian/Ubuntu releases?

[Marc Deslauriers (mdeslaur)]

Lars,

Akonadi is failing because of the new secure_file_priv default location. Since akonadi doesn't install the full mysql-server-5.5 package, but only the mysql-server-core-5.5 package, the secure_file_priv directory isn't created, and mysql won't start.

This was fixed by having Akonadi set the secure_file_priv setting to empty.

[Lars Tangvald (lars-tangvald)]

Ah, ok. Thanks :)

As an aside: Does Akonadi use MySQL's import/export operations? If not it's recommended to set secure-file-priv to NULL (disabled) instead of empty (unrestricted).

Support for setting it to NULL was added with this server update, so wasn't possible before in <5.7.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Flames_in_Paradise (ellisistfroh-deactivatedaccount)]

That ppa from comment #10 here seems to be gone?

[axel (axel334)]

I could not use Kalarm because for trusty I had this error:

Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files

I did:
sudo mkdir /var/lib/mysql-files

Then I had another error, something along this lines (can't remember exactly)
can't connect to local MySQL server through socket '/tmp/akonadi

so I deleted akonadi folders in /home .local and .config
and restarted system and Kalarm works.

For reference:
https://forum.kde.org/viewtopic.php?f=215&t=120777

[Launchpad Janitor (janitor)]

This bug was fixed in the package akonadi - 4:16.12.3-0ubuntu1

---------------
akonadi (4:16.12.3-0ubuntu1) artful; urgency=medium

  [ Simon Quigley ]
  * Refresh symbols
  * New upstream release
  * Remove set_dependency_order.diff as it can now be reverse-applied
  * Refresh patches

  [ Clive Johnston ]
  * Add missing file
  * Adding libmysqlclient-dev or default-libmysqlclient-dev as build deps

  [ Rik Mills ]
  * Fix breaks, replaces and misc depends
  * New upstream release (16.12.0)
  * Correct symbols versions so not incorrectly showing a debian revison
  * Update build deps to use new KF5 style names
  * New upstream release (16.12.1)
  * Update symbols from build logs

  [ Darin Miller ]
  * New upstream release (16.12.2)

  [ José Manuel Santamaría Lema ]
  * New upstream release (16.12.3)

 -- José Manuel Santamaría Lema <email address hidden> Mon, 01 May 2017 20:27:12 +0100

[Emily Ratliff (emilyr)]

Yakkety and Zesty are EOL, so marking them Won't Fix.

[Steve Langasek (vorlon)]

The Precise Pangolin has reached end of life, so this bug will not be fixed for that release