Wouter described how to get Akonadi (the back-end for KDE PIM) to use PostgreSQL [1].
I don’t agree with his “MySQL is a toy” sentiment. But inspired by his post I decided to convert some of my systems to use a MySQL instance running on a server instead of one instance for each user. In the default configuration you have 140M of disk space and 200M of RAM used by each user for a private MySQL installation which has about 24K of data (at least at the moment on systems I run, maybe more in future).
Here’s some pseudo shell script to dump the database and get a new config:
mysqldump --socket=$HOME/.local/share/akonadi/socket-$HOSTNAME/mysql.socket akonadi > dump.sql
akonadictl stop
rm -rf .config/akonadi
rm -rf .local/share/akonadi
mkdir .config/akonadi
cat > .config/akonadi/akonadiserverrc <<EOF
[%General]
Driver=QMYSQL
SizeThreshold=4096
ExternalPayload=false
[QMYSQL]
Name=${USER}_akonadi
Host=IP_OR_HOST
User=$USER
Password=$PASS
StartServer=false
Options=
ServerPath=/usr/sbin/mysqld
[Debug]
Tracer=null
EOF
Then with DBA privs you need to run the following in the mysql client:
create database $USER_akonadi;
GRANT ALL PRIVILEGES ON $USER_akonadi.* to '$USER'@'%' IDENTIFIED BY '$PASS';
Then run the following to import the SQL data:
mysql $USER_akonadi < dump.sql
Ideally that would be it, but on my test installation (Debian/Squeeze MySQL server and Debian/Unstable KDE workstations) I needed to run the following SQL commands to deal with some sort of case problem.
rename table schemaversiontable to SchemaVersionTable;
rename table resourcetable to ResourceTable;
rename table collectionattributetable to CollectionAttributeTable;
rename table collectionmimetyperelation to CollectionMimeTypeRelation;
rename table collectionpimitemrelation to CollectionPimItemRelation;
rename table collectiontable to CollectionTable;
rename table flagtable to FlagTable;
rename table mimetypetable to MimeTypeTable;
rename table parttable to PartTable;
rename table pimitemflagrelation to PimItemFlagRelation;
rename table pimitemtable to PimItemTable;
I am not using any PIM features other than the AddressBook (which hasn’t been working well for a while), so I’m not sure that this is working correctly. But I would prefer that something I don’t use and is probably broken take up disk space and RAM on a server instead of a workstation anyway.
I am REALLY interested in a standard way to do it. I mean, a native kde wizard or so to use an external mysql database to store user’s data.
Please keep on posting about it!
Best regards.
I would recommend against this part of the procedure:
rm -rf .local/share/akonadi
if the intent is to remove the database files, better do this explicitly
rm -rf .local/share/akonadi/db_data
rm -rf .local/share/akonadi/db_misc
Depending on version and config the base directory can also contain a subdirectory which holds transient data that has not yet written to its actual storage location, e.g. remote flle or server based storage that was not reachable when the data was created or changed in Akonadi.
If you think MySQL isn’t a toy and you want to use it for anything, that’s like saying David Hasselhoff is hot and you want to see him wear Borat’s swim suit.
Martin: MySQL is working reasonably well for many big sites. I agree that there’s lots of things to improve (like checksums on all data) but it generally works. It’s certainly a lot more than is needed for storing some KDE PIM stuff. Chromium tends to accumulate hundreds of megs of cache and stuff without needing a SQL server, I can’t imagine why KDE PIM’s small amount of data would need one.