Bug 264383

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.

(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!

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.

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.

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.

still present with Akonadi 1.6.2

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.

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

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

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.

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

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.

Sorry ignore that, I was getting confused. I forget that the akonadiserver is the mysql client as well.

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.

This issue is reproducable in 1.8.1. A read of the source for 1.9.0 suggests the bug is still there also.

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

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.

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

Thanks for the feedback. both issues should be addressed with this change.