Bug 264383 - akonadiserver uses ~/.my.cnf dangerously
Summary: akonadiserver uses ~/.my.cnf dangerously
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Unclassified
Component: server (show other bugs)
Version: 1.4.90
Platform: Ubuntu Packages Linux
: NOR crash with 20 votes (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-01-26 23:56 UTC by Ivan D Vasin
Modified: 2013-02-07 16:03 UTC (History)
1 user (show)

See Also:
Latest Commit:
Version Fixed In: 1.9.1


Attachments
test patch (949 bytes, patch)
2013-01-08 16:55 UTC, Christophe Giboudeaux
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ivan D Vasin 2011-01-26 23:56:35 UTC
Version:           1.4.80 (using KDE 4.6.0) 
OS:                Linux

this is actually with Akonadi 1.4.90, which is not listed in the available versions.

after upgrading to KDE 4.6.0 today, i had to battle with Akonadi to get it to work.  kontact wouldn't even start, and kmail would run but eventually show an Akonadi error dialog and crash after i closed it.

Akonadi's mysqld error log was showing a bunch of error messages like "InnoDB: Unable to lock ./ibdata1, error: 11", and ``akonadictl start`` was complaining about some DBUS service already being registered.

after some unhelpful digging and searching, i tried running ``akonadiserver start`` and was quite disturbed to find that it logged in to my company's production database server and start running REPAIR TABLE queries against a whole bunch of my company's tables.  why it even cared to look at those tables is beyond me, but the most troublesome part was that it was accessing my company's DB server in the first place, rather than the local mysqld-akonadi.

i found that commenting out the ``[clients]`` section in my ~/.my.cnf caused akonadiserver to start successfully and Akonadi to function properly.  why was akonadiserver reading ~/.my.cnf?  surely the settings there should be totally ignored by Akonadi.  i would like to use ~/.my.cnf for my convenience, not as a way to silently reconfigure Akonadi to read and modify my company's databases.

the REPAIR TABLE queries continued running until we discovered and killed them later in the day.

Reproducible: Always

Steps to Reproduce:
1. create a non-trivial ``[clients]`` section in ~/.my.cnf.
2. log in to KDE or run ``akonadiserver start``.

Actual Results:  
akonadiserver attempts to use whatever database is pointed to by ~/.my.cnf and runs REPAIR TABLE on various tables.

Expected Results:  
akonadiserver ignores ~/.my.cnf and uses its own database connection settings.
Comment 1 Torgny Nyblom 2011-01-27 08:30:42 UTC
Well the mysql manual states:
--- (http://dev.mysql.com/doc/refman/5.1/en/option-files.html)
The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients.
----
so the behavior you got is the expected one. However it might be possible to prevent akonadis mysql instance from using any system configured options by using the "--no-defaults" option.
Comment 2 Ivan D Vasin 2011-01-27 17:30:04 UTC
(In reply to comment #1)
> Well the mysql manual states:
> --- (http://dev.mysql.com/doc/refman/5.1/en/option-files.html)
> The [client] option group is read by all client programs (but not by mysqld).
> This enables you to specify options that apply to all clients.
> ----
> so the behavior you got is the expected one.

it may be the expected behavior from the point of view, "how should akonadiserver's mysql client behave if it is not configured to ignore MySQL defaults?", but the point of this bug is that it is very much /not/ the expected behavior from the point of view, "how can Akonadi function properly without requiring the user to know about its internal workings and be aware of them when configuring his or her personal MySQL settings?".

it is a usability issue if i--as a user of Akonadi--am required to understand that it uses a MySQL client, that its MySQL client will read my MySQL settings from ~/.my.cnf (which in any common use case i wouldn't set up for Akonadi to use), and that as a result i effectively cannot use ~/.my.cnf for my personal needs.  it is an even greater usability issue that i must find out that if i do use ~/.my.cnf, akonadiserver will inspect and issue REPAIR TABLE on the tables of my company's production database, which are certainly not related to Akonadi in any way and should never be touched by it.

therefore this behavior is a bug from a user's point of view, whether or not it is naively expected from a developer's point of view.

> However it might be possible to
> prevent akonadis mysql instance from using any system configured options by
> using the "--no-defaults" option.

can i configure my current installation of akonadiserver to use --no-defaults?  could someone please make this a default setting?  many thanks!
Comment 3 Ivan D Vasin 2011-02-03 17:55:32 UTC
i searched for and didn't find a way to configure akonadiserver to prevent this behavior when using a MySQL database for storage.  but i found a workaround: use a PostgreSQL database instead.  after installing PostgreSQL, the procedure i used (iirc) was:

$ sudo -u postgres createuser akonadi
$ sudo -u postgres psql
postgres=# \password akonadi
<set the database password for akonadi>
$ sudo -u postgres createdb akonadi
^C
$ akonadictl stop
$ akonaditray
<use AkonadiTray to point akonadiserver at the new akonadi database on localhost, authenticating as akonadi>
$ rm -rf $HOME/.config/akonadi $HOME/.local/share/akonadi
$ akonadictl start

i then ran into the issues in bug 265155, but these don't seem to visibly inhibit Akonadi's functionality.
Comment 4 Ivan D Vasin 2011-06-20 22:12:14 UTC
as of Akonadi 1.5.3 (possibly earlier), the default akonadiserverrc includes a QMYSQL/Options setting that specifies an Akonadi-specific socket to use for MySQL communications.  as far as i can tell, this totally resolves this issue.
Comment 5 Ivan D Vasin 2011-10-03 19:07:54 UTC
this issue is back.  actually, i'm not sure if it was ever really gone, although my previous comment suggests that it was (sadly, without proof).

the Akonadi-specific mysqld instance seems to use the right options:

/usr/sbin/mysqld --defaults-file=/home/ivan/.local/share/akonadi//mysql.conf --datadir=/home/ivan/.local/share/akonadi/db_data/ --socket=/home/ivan/.local/share/akonadi/socket-ivasin-workstation/mysql.socket

and the akonadiserverrc seems to point to that instance correctly:

----
[QMYSQL]
Name=akonadi
Host=
Options="UNIX_SOCKET=/home/ivan/.local/share/akonadi/socket-ivasin-workstation/mysql.socket"
ServerPath=/usr/sbin/mysqld-akonadi
StartServer=true
----

however, if i do ``akonadictl restart``, it is readily apparent from the output that Akonadi still runs mysqlcheck on the db server specified in ~/.my.cnf.  i can confirm this by seeing an Akonadi-specific mysqlcheck in ``ps aux``.

as before, this is a bug, and it is hazardous.  please fix this, so i can use ~/.my.cnf as i want (not as i want for Akonadi) without Akonadi ever touching my company's production database server.  until then, i'm switching back to Akonadi + PostgreSQL.
Comment 6 Ivan D Vasin 2011-11-30 01:33:25 UTC
still present with Akonadi 1.6.2
Comment 7 Simon Williams 2012-12-21 11:40:56 UTC
Still present with Akonadi 1.8.0. In addition, Akonadi does something stupid and causes the entire MySQL server to crash when I logout, so this behaviour is now INCREDIBLY DANGEROUS.

It took a whole month to track this down.
Comment 8 Simon Williams 2013-01-08 09:37:17 UTC
The severity of this bug MUST be upgraded to CRITICAL. This is not just a crash.

1. It affects other programs
2. It crashes other programs
3. It risks corrupting data
4. It risks corrupting data of other programs
5. It risks corrupting data on other machines
Comment 9 Christophe Giboudeaux 2013-01-08 13:50:51 UTC
To clarify things. Please correct if anything is incorrect

AFAIU the mysql doc, the my.cnf files are ignored if --defaults-file is used. That's the case for the mysqld process launched by the akonadi server:

ps x |grep mysqld returns
/usr/sbin/mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf --datadir=$HOME/.local/share/akonadi/db_data/ --socket=$HOME/.local/share/akonadi/socket-yuuko.site/mysql.socket

Now, when starting up, Akonadi also runs mysqlcheck --check-upgrade --all-databases --auto-repair --socket=$HOME/.local/share/akonadi/socket-$HOST/mysql.socket

would adding --defaults-file help you?
ie mysqlcheck --defaults-file=$HOME/.local/share/akonadi/mysql.conf --check-upgrade --all-databases --auto-repair --socket=$HOME/.local/share/akonadi/socket-$HOST/mysql.socket
Comment 10 Simon Williams 2013-01-08 16:15:29 UTC
That is indeed the case for the mysqld process launched by Akonadi. But to be extra clear, this only applies to the *SERVER* process. The whole point of this bug is that Akonadi isn't using it's own server process because any *CLIENT* connections it creates are incorrectly reading ~/.my.cnf.

I suspect that --defaults-file should fix the problem. However, this bug is NOT specific to mysqlcheck. *ALL* client processes must use --defaults-file *WITHOUT FAIL* in order to solve this problem.

Please be aware that if Akonadi access the database in any other manner (for example, using libmysqlclient directly) that will also have to be guaranteed to not use ~/.my.cnf.
Comment 11 Christophe Giboudeaux 2013-01-08 16:55:40 UTC
Created attachment 76312 [details]
test patch

If anyone is willing to test, please apply this patch to the akonadi server source, build, install then run akonadictl restart and look at the output

A normal one should be:
Found mysqlcheck:  "/usr/bin/mysqlcheck" 
akonadi.collectionattributetable                   OK
akonadi.collectionmimetyperelation                 OK
akonadi.collectionpimitemrelation                  OK
akonadi.collectiontable                            OK
akonadi.flagtable                                  OK
akonadi.mimetypetable                              OK
akonadi.parttable                                  OK
akonadi.pimitemflagrelation                        OK
akonadi.pimitemtable                               OK
akonadi.resourcetable                              OK
akonadi.schemaversiontable                         OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

If that's still not enough, I have other ideas
Comment 12 Simon Williams 2013-01-11 09:29:04 UTC
Unless that patch somehow applies to clients as well that won't fix it. The clients are connecting to the wrong server. Changing the server is not going to help.
Comment 13 Simon Williams 2013-01-11 09:31:46 UTC
Sorry ignore that, I was getting confused. I forget that the akonadiserver is the mysql client as well.
Comment 14 Simon Williams 2013-01-11 09:52:40 UTC
The supplied patch should solve the problem with mysqlcheck, but a similar change needs to be made for mysqladmin.

I'm concerned that there may be other client processes that we're missing. I've checked through the akonadi source and can't see any. How does Akonadi interact with the server once it is set up?

From what I can work out, it seems to use QMYSQL_EMBEDDED. I have no idea if that reads my.cnf or not.
Comment 15 Simon Williams 2013-02-04 09:05:46 UTC
This issue is reproducable in 1.8.1. A read of the source for 1.9.0 suggests the bug is still there also.
Comment 16 Christophe Giboudeaux 2013-02-06 08:56:44 UTC
Unless someone *really* tests the patch in comment#11, nothing will be done.

(ie: I don't need "should solve" answers, I need "it solves" or "it does not solve")

eventually I'll create a system db to test it but that puts this bug very far on my todo list
Comment 17 Simon Williams 2013-02-07 10:15:46 UTC
The patch in comment #11 appears to solve the problem for mysqlcheck. Without the patch akonadi issues many CHECK TABLE statements against the wrong MySQL server when it starts up. With the patch this is no longer the case.

However, this does not solve the problem completely. When the akonadi server is stopped it still issues a shutdown to the wrong MySQL server via mysqladmin.
Comment 18 Christophe Giboudeaux 2013-02-07 15:59:22 UTC
Git commit b45e783ebe548280f78c0a659a6f8c6f7e09e3ea by Christophe Giboudeaux.
Committed on 07/02/2013 at 16:59.
Pushed by cgiboudeaux into branch '1.9'.

Ignore the custom settings in ~/.my.cnf
Looks like passing the socket path in our arguments is not enough for MySQL. It still tries to run the commands on any database defined in ~/.my.cnf.

We're now adding the --defaults-file argument to mysqladmin and mysqlcheck to only run these commands on the Akonadi DB.
FIXED-IN: 1.9.1

M  +4    -2    server/src/storage/dbconfigmysql.cpp

http://commits.kde.org/akonadi/b45e783ebe548280f78c0a659a6f8c6f7e09e3ea
Comment 19 Christophe Giboudeaux 2013-02-07 16:03:42 UTC
Thanks for the feedback. both issues should be addressed with this change.