Using digiKam with MySQL

By default, digiKam uses SQLite as its back-end for storing important metadata and thumbnails. But the photo management application also provides support for the popular MySQL database engine, and it comes with a database migration tool that can help you to move your data from SQLite to MySQL. Of course, you might wonder why you’d want to switch to MySQL when SQLite already does a good job of managing the data? Using MySQL as digiKam’s database back-end allows you to store the data on a remote server. This way, you can use multiple digiKam installations (e.g., on your notebook and desktop machine) to access and manage your photo collections. You can also use MySQL tools to back up and analyze digiKam’s data.

Obviously, to use digiKam with MySQL, you need a MySQL server. You also have to create two MySQL databases: one for storing photo metadata (e.g., digikam) and the other for storing thumbnails (e.g., thumbnails-digikam). And before you proceed, note the database connection information such as MySQL server address, user name, and password — you’ll need it when configuring MySQL settings in digiKam.

There are two ways to switch digiKam from SQLite to MySQL. If you’ve just installed digiKam, and you haven’t yet added any collection to it,  choose Settings | Configure digiKam, and switch to the Database section. Select the MySQL item from the Type drop-down list.

Enter the IP address of your MySQL server in the Host Name field and specify the correct port in the Port field (the default port is 3306). In the Schema Name field, enter the name of the database for storing photo metadata, and specify the name of the database for storing thumbnails in the Thumbnails Schema Name field. Enter your MySQL user name and password in the appropriate fields. To check whether the database connection works properly, press the Check DB Connection button. If everything works as it’s supposed to, switch to the Collections sections, and add the directories containing your photos. Hit OK, and wait till digiKam populates the databases with data from photos. This can take a while if you have a lot of photos.

If you are already using digiKam with SQLite, you can use the database migration tool to move the existing data to MySQL databases. Choose Tools | Database Migration, and select the MySQL item in the Type drop-down list in the right section. Fill out the required fields as previously described, and use the Check DB Connection button to test the connection. Finally, press the Migrate button to copy the data from SQLite to MySQL.

Freelance tech writer covering Linux and open source software.

Tagged with: , ,
Posted in Open Source, Photography, Software
19 comments on “Using digiKam with MySQL
  1. vivek says:

    Thanks! This is very useful information.

  2. montfras says:

    Awesome!
    What about possible nepomuk integration?

  3. Fri13 says:

    Does that really move the data and not just copy? The original would be good to kept untouched if there comes problems.

  4. mouring says:

    This would be more useful if it supported the actually raw files itself. I could see digiKam fitting a nice market set where you need multiple people doing work on photos where you could “check out” an image, work on it and return it.

    Just putting the metadata and thumbnails up isn’t as useful unless you are looking at tying this into a website or some type of simple client preview system.

  5. MySQL support is all very well, but it seems like Digikam since 1.3 or 1.4 requires it, so I have to build it even if I don’t want to use MySQL backend. I don’t mind optional dependencies, vast majority of KDE people has to have MySQL installed anyway, but not me. Is it intended behaviour (“feature”) or some kind of undesired bug?

  6. tranche says:

    What about a PostgreSQL support to ?
    Personally I prefer PostgreSQL to Mysql, and I don’t want to install a new SQL server.
    This is a very good news but it a could be very very good news if I can use Digikam with my PostgreSQL server

  7. Bryce Schober says:

    Do you have any quantitative comparison of performance between the two database back-ends?

  8. Malik says:

    By doing this will i achieve any performance enhancements. Cause the thumbnail database loading speed is very slow even with all the thumbnails generated!

  9. alin says:

    Hi,

    I try to setup this for digikam2.0
    I created to databases in my mysql dikigamdb and digikamthumdb and grant all the privileages to some user digikamuser

    as: mysql -u root -p

    CREATE DATABASE digikamdb;
    GRANT ALL PRIVILEGES ON digikamdb.* TO ‘digikamuser’ IDENTIFIED BY ‘password'; FLUSH PRIVILEGES;
    CREATE DATABASE digikamthumbdb;
    GRANT ALL PRIVILEGES ON digikamthumbdb.* TO ‘digikamuser’ IDENTIFIED BY ‘password'; FLUSH PRIVILEGES;

    now I setup digikam to use these databases…

    [Database Settings]
    Database Connectoptions=
    Database Hostname=localhost
    Database Name=digikamdb
    Database Name Thumbnails=digikamthumbdb
    Database Password=password
    Database Port=3306
    Database Type=QMYSQL
    Database Username=digikamuser
    Internal Database Server=false

    however with this config I get an error at startup about CREATE and TRIGGER privileages not given to user… I checked the password and it is ok…

    of course if I setup it to use the root user I have no problems in accessing it… Because some other kde apps share the same mysql server I would prefer digikam to be restricted at accessing only these to databases. Is this possible do I miss somthing?

    Alin

    • smith says:

      Hi Alin,

      I had the same problem. I solved it by GRANT ALL ON *.* to… So, I did not only grant all privileges on digikam and digikam_thumbnails but also to other databases (like mysql?). This solved it.

      • alin says:

        thank you smith.
        I did more or less something similar

        https://bugs.kde.org/show_bug.cgi?id=262321

        I would prefer digikamuser to be able to access only its databases and nothing more.
        I had a discussion with Bruno see bugs link #c1 and it seems that some changes in digikam are needed to achive this.

        Alin

      • schultzter says:

        I tried this, and a number of other things. On a MySQL 5.0.24a server (on a ReadyNAS Duo). But I’m still getting privilege errors. In particular I get “digikam(3416)/digikam (core): Insufficient rights on databse.”

        Any idea what I can do?

  10. Marco says:

    Very nice! Thank you. After loosing my database about the 4th time (my own fault, I had the database on my nfs-server since my home-dir is over nfs) I decided to give it a try!
    I’d advise everyone to store all the metadata directly in the images. It is painfully slow sometimes, but the first time you kill your database you’ll be happy (sometimes my paranoia pays out :-) )

  11. TeeGee says:

    Thank you for bringing me close to solving the problem, but… I use phpMyAdmin as MySQL server on a NAS Synology 212+. Checking the connexion to the database is succesfull. Migrating the database from my computer to the network server is done without a problem. But when I try to add a collection from the directory where are my photo collection on the NAS (through Network>Samba shares), I get the answer “The selected folder does not exist or is not readable”.
    Any idea for a solution ?

  12. Robert S. says:

    But you have a Problem when you want use a Linux and Windows. Because the “Mountpoint” is different.

  13. UbIx says:

    Thanks for your description.

    But it doesn’t work with host name “192.168.1.1” – I’ve the error:
    “Der Datenbankverbindungstest ist nicht erfolgreich gewesen.
    Der Fehler lautet: Access denied for user ‘digikam_user’@’192.168.1.120′ (using password: YES) QMYSQL: Verbindungsaufbau nicht möglich” (in EN – no connection possible)

    (Information 192.168.1.120 is the IP from local PC not from server which have .1)

    If I change the IP to a other value then 1 – the error is with the right IP – but I can’t change the IP of the server in the moment.

    Any suggestions?

Comments are closed.

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Follow

Get every new post delivered to your Inbox.

Join 451 other followers

%d bloggers like this: