Warning, /documentation/digikam-doc/setup_application/database_settings.rst is written in an unsupported language. File is not indexed.
0001 .. meta:: 0002 :description: digiKam Database Settings 0003 :keywords: digiKam, documentation, user manual, photo management, open source, free, learn, easy, database, setup, mysql, mariadb, sqlite, migration, local, remote, server 0004 0005 .. metadata-placeholder 0006 0007 :authors: - digiKam Team 0008 0009 :license: see Credits and License page for details (https://docs.digikam.org/en/credits_license.html) 0010 0011 .. _database_settings: 0012 0013 Database Settings 0014 ================= 0015 0016 .. contents:: 0017 0018 .. note:: 0019 0020 For an introduction of internal data storage, please refer to :ref:`Introduction of digiKam Databases <database_intro>` section. 0021 0022 .. _sqlite_database: 0023 0024 The Sqlite Database 0025 ------------------- 0026 0027 `SQLite <https://sqlite.org/>`_ is a relational database management system, written in C programming library. SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle or PostgreSQL. Rather, it is an embedded SQL database engine, i.e. it is embedded in an end program. SQLite reads and writes directly to ordinary disk files. For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps things simple. SQLite "just works". 0028 0029 By default, digiKam uses SQLite as its back-end for storing important metadata and thumbnails. Three SQLite files used for storing them are named respectively: 0030 0031 ============== ============================= 0032 Database File-Name 0033 ============== ============================= 0034 **Core** :file:`digikam4.db` 0035 **Thumbs** :file:`thumbnails-digikam.db` 0036 **Similarity** :file:`similarity.db` 0037 **Faces** :file:`recognition.db` 0038 ============== ============================= 0039 0040 To make your application run fast and smoothly, it is recommended to check and optimize your databases once in awhile. This could be achieved with the menu option :menuselection:`Tools --> Maintenance...` and the stage **Perform Database Cleaning**. See this 0041 :ref:`Maintenance tool section <maintenance_database>` for details. A recommended tool is `SQLite Browser <https://sqlitebrowser.org/>`_, a high quality and easy to use visual tool for managing database objects. For Ubuntu and its derivatives, it could be retrieved using `sudo apt install sqlitebrowser`. Now you can switch to the directory where databases are stored and visualize the database contents. 0042 0043 .. note:: 0044 0045 Take care to use a place hosted by fast hardware (such as SSD or NVMe) with enough free space especially for thumbnails database. A remote file system such as NFS cannot be used here. For performance and technical reasons relevant of SQLite, you cannot use a media from the network. 0046 0047 SQLite database files could be found in your *collection* folder, which you have added to digiKam. (By default, if you add your “Pictures” collection, the database files will be present in :file:`~/Pictures` folder). 0048 0049 .. figure:: images/setup_database_sqlite.webp 0050 :alt: 0051 :align: center 0052 0053 The digiKam SQLite Configuration Page 0054 0055 .. _mysql_database: 0056 0057 The **WAL** SQLite mode is a very important option that we recommend to turn on with large databases to optimize transactions and improve performances. 0058 0059 The MySQL Database 0060 ------------------ 0061 0062 MySQL Versus SQLite 0063 ~~~~~~~~~~~~~~~~~~~ 0064 0065 `MySQL <https://en.wikipedia.org/wiki/MySQL>`_ is an open-source, relational database management system, written in C and C++. Original development of MySQL by Michael Widenius and David Axmark beginning in 1994. Sun Microsystems acquired MySQL in 2008, which was later acquired by Oracle in 2010. MySQL currently works on almost all system platforms (Linux, Microsoft Windows, OS X, SunOS …). 0066 0067 `MariaDB <https://en.wikipedia.org/wiki/MariaDB>`_ server is a community developed fork of MySQL server. Started by core members of the original MySQL team, MariaDB actively works with outside developers to deliver the most featureful, stable, and sanely licensed open SQL server in the industry. 0068 0069 MariaDB has actually overtaken MySQL, because of few basic reasons: 0070 0071 - MariaDB development is more open and vibrant. 0072 0073 - More cutting edge features. 0074 0075 - More storage engines. 0076 0077 - Better performance. 0078 0079 - Compatible and easy to migrate. 0080 0081 digiKam also provides support for popular MySQL database engine. Of course, you might wonder why you’d want to switch to MySQL when SQLite already does a good job of managing the data? MySQL offers many advantages for storing digiKam data, especially when collections include **more than 100,000 items**. With such large collections, SQLite introduces latency which slows down the application. 0082 0083 .. note:: 0084 0085 With **WAL** option enabled, SQLite can be easily used for more than 100,000 items especially with an SSD or NVMe storage. It must be even faster than MySQL and more stable. See `this page <https://www.sqlite.org/wal.html>`_ for technical details. 0086 0087 Using MySQL as digiKam’s database back-end allows you to store the data on local as well as remote server. Local, to replace the local SQLite storage and latter, to use a shared computer through network. 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 (For instance,on your notebook and PC) to access and manage your photo collections. You can also use MySQL tools to backup and analyze digiKam’s data. 0088 0089 To switch from SQLite to MySQL database, go to :menuselection:`Settings --> Configure digiKam...` and then under **Database** section, select a database from the drop down list. 0090 0091 - **MySQL Internal**: This allows to run an internal database server on your system. digiKam uses Unix socket for the connection. 0092 0093 - **MySQL Server**: Use this if you’ve your data on remote server and you’re on a different machine trying to access the collection. 0094 0095 .. _mysql_internal: 0096 0097 The MySQL Internal Server 0098 ~~~~~~~~~~~~~~~~~~~~~~~~~ 0099 0100 While using a large collection hosted on hard drive (HDD - not SSD or NVMe device), with a size **greater than 100,000 items**, the application tends to slow down. To avoid the delay and maintain efficiency, digiKam provides option of using **MySQL Internal**. To be clear, this isn’t an actual server, or a public network. Instead, it is a server that runs only while application is running. 0101 0102 Internal server creates a separate database that can be accessed (only while application is running) using the command: 0103 0104 .. code-block:: text 0105 0106 mysql --socket=/home/[user_name]/.local/share/digikam/db_misc/mysql.socket digikam 0107 0108 Internal server uses tree MySQL Binary Tools: :file:`mysql_install_db`, :file:`mysqladmin`, and :file:`mysqld`. You can configure their locations in the configuration dialog. digiKam will try to find these binaries automatically if they’re installed on your system. 0109 0110 .. figure:: images/setup_database_mysqlinternal.webp 0111 :alt: 0112 :align: center 0113 0114 The digiKam MySQL Internal Configuration Page 0115 0116 .. _mysql_remote: 0117 0118 The MySQL Remote Server 0119 ~~~~~~~~~~~~~~~~~~~~~~~ 0120 0121 Obviously, to use digiKam with a remote MySQL, you would require a MySQL server. Or, you could also install MariaDB, which serves the purpose well. (Could be installed easily using `this link <https://www.cherryservers.com/blog/how-to-install-and-start-using-mariadb-on-ubuntu-20-04>`_.) 0122 0123 Follow the instructions below, if you don’t have a dedicated user account and a digiKam database already set up. Run the commands in MySQL server, after replacing *password* with correct one. 0124 0125 .. note:: 0126 0127 You can select any database name. (Here it is, *digikam*). Just remember to fill in the database name correctly in Core, Thumbs, Similarity, and Face database names from the dialog box shown below. 0128 0129 .. code:: sql 0130 0131 CREATE USER ''@'%' IDENTIFIED BY 'password'; 0132 GRANT ALL ON *.* TO ''@'%' IDENTIFIED BY 'password'; 0133 CREATE DATABASE digikam; 0134 GRANT ALL PRIVILEGES ON digikam.* TO ''@'%'; 0135 FLUSH PRIVILEGES; 0136 0137 .. note:: 0138 0139 If you have an enormous collection, it's recommended to start the MySQL server with `mysql --max_allowed_packet = 128M` 0140 0141 .. tip:: 0142 0143 If you have problems with a MySQL server on Ubuntu based Linux system, use the addition command in the mysql prompt to be able to create MySQL triggers. 0144 0145 .. code:: sql 0146 0147 SET global log_bin_trust_function_creators=1; 0148 0149 Now, in digiKam, go to :menuselection:`Settings --> Configure digiKam...` and then under **Database** section, select MySQL Server from the drop down list. 0150 0151 .. figure:: images/setup_database_remotemysql.webp 0152 :alt: 0153 :align: center 0154 0155 The digiKam Remote Mysql Configuration Page 0156 0157 Enter the IP address of your MySQL server in the **Host Name** field and specify the correct port in the **Host Port** field (the default port is 3306). 0158 0159 In the **Core Db Name** field, enter the name of the first database for storing photo metadata. 0160 0161 Specify the name of the second database for storing wavelets compressed thumbnails in the **Thumbs Db Name** field. 0162 0163 The third database is dedicated to store the similarity finger-prints performed by the fuzzy search engine. Use the **Similarity Db Name** field for that. 0164 0165 The last database is dedicated to store face histograms for recognition purpose. Use the **Face Db Name** field for that. 0166 0167 To be connected safety to the remote server, enter your MySQL identification using **User** and **Password** fields. 0168 0169 To check whether the database connection works properly, press the **Check 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 items to register in database. 0170 0171 There are some tips and recommendation to obtain the best results with a remote MySQL database server. 0172 0173 With slow network, digiKam hangs a lot of time especially when album contains many items **(>1,000)**. This solution relies on network performances. Problem has been reproducible using Wifi connection, for instance. Switching to Ethernet must solves the problem. 0174 0175 Also, if you have an enormous collection, you should start the MySQL server with `mysql --max_allowed_packet = 128M`. If you’re well acquainted with using MySQL, you could also change your settings in :file:`my.ini` or :file:`~/.my.cnf` files. 0176 0177 .. warning:: 0178 0179 The locale used in the Mysql server must be the same than the locale from the computer used to run digiKam to prevent problem with the double values saved in the tables of the database. 0180 0181 Database Type Criteria 0182 ---------------------- 0183 0184 See the resume below to choose the right database type depending of the use-cases. 0185 0186 ============== ============== ========== ==================================================================================== 0187 Storage Type Items Remarks 0188 ============== ============== ========== ==================================================================================== 0189 HDD SQLite < 100K **Warning: WAL is mandatory.** 0190 HDD MySQL-Internal > 100K 0191 SSD SQLite WAL is optional. 0192 SSD MySQL-Internal 0193 NVME SQLite WAL is optional. 0194 NVME MySQL-Internal 0195 Removable SQLite < 100K **Warning: WAL is mandatory. USB 3.1 minimum with NVMe drive.** 0196 Removable MySQL-Internal > 100K **Warning: USB 3.1 minimum with NVMe drive.** 0197 Network FS SQLite **Prohibited: SQLite databases must be stored on local file system.** 0198 Network FS MySQL-Internal **Prohibited: MySQL databases must be stored on local file system.** 0199 Remote MySQL-Server MariaDB server is supported. Gigabit Ethernet or higher is recommended. 0200 ============== ============== ========== ==================================================================================== 0201 0202 .. glossary:: 0203 0204 HDD 0205 Hard Disk Drive. 0206 0207 SSD 0208 Solid State Drive. 0209 0210 NVMe 0211 Non-Volatile Memory. 0212 0213 Removable 0214 External USB HDD/SSD/NVMe drive. 0215 0216 Network FS 0217 Network File System mounted locally. 0218 0219 Remote 0220 Network server as NAS (Network Attached Storage). 0221 0222 WAL 0223 Write-Ahead Lock (SQLite database only). 0224 0225 .. note:: 0226 0227 See this :ref:`Digital Asset Management chapter <storage_deterioration>` for more details about media and data protection. 0228 0229 See also this :ref:`Collection Settings chapter <collections_settings>` for more details about the way to configure your collections depending of your storage policy. 0230 0231 .. important:: 0232 0233 If you share the same **Removable** media to host databases and/or collections between different computers, you must have the same kind of operating system, the same mount paths everywhere (use symbolic links to revolve paths), and the same digiKam version everywhere to prevent conflicts with database schemes. 0234 0235 If you use a common **Remote** server to host databases and collections, you must use the same digiKam version everywhere to prevent conflicts with database schemes. Computers running digiKam cannot be used at the same time on collections. 0236 0237 If you use a common **Remote** server to host collections, as databases are located on computers, different versions of digiKam can be used and digiKam sessions can run at the same time on collections. Take a care about concurrency access on files metadata if you turned on this option on **Metadata Setup Page**. 0238 0239 .. _database_migration: 0240 0241 Database Migration 0242 ------------------ 0243 0244 The photo management application comes up with an exclusive tool named **Database Migration**, that allows users to migrate their data. Suppose, you’re using SQLite and you wish to move all data to MySQL database, migration tool will help you do so. It can help you migrate data from SQLite to MySQL and vice versa. 0245 0246 To migrate to another database, go to :menuselection:`Settings --> Database Migration...`. A dialog box appears: 0247 0248 .. figure:: images/setup_database_migration.webp 0249 :alt: 0250 :align: center 0251 0252 The digiKam Database Migration Tool 0253 0254 Now choose appropriate database types you want to convert to. Finally, click on **Migrate** button to convert the database from SQLite to MySQL (or vice versa). Depending of the database size this operation can take a while. 0255 0256 .. note:: 0257 0258 Only the digiKam **Core** database will be migrated while conversion process. All other databases needs to be rebuilt as post-processing with the :ref:`Maintenance Tools <maintenance_tools>`. The **Thumbs** and **Similarity** databases needs to be created from scratch, and the **Face** database needs the option **Rebuild the Training Data**. 0259 0260 .. _database_backup: 0261 0262 Database Backup Recommendation 0263 ------------------------------ 0264 0265 For security reasons, planing a database backup using crontab over the network can help against device dysfunctions. A NAS or an external drive can also be used for that. 0266 0267 Each database can be named with a different name, not only *digikam*. This allows to users to backup only what is needed. For instance, naming **Core** database as *digiKam_Core*, allows to isolate only this table (the most important file). **Thumbnails**, **Similarity** and **Face Recognition** databases can always be regenerated for scratch. 0268 0269 The chapter about digiKam :ref:`Maintenance Tools <maintenance_tools>` will explain how to maintain in time the database contents and how to synchronize the collections with databases information (and vice versa). 0270 0271 .. _database_stats: 0272 0273 Database Statistics 0274 ------------------- 0275 0276 digiKam provides a unique tool to show the statistics from your collections. It includes count of images, videos (including individual count by image format), tags etc. Also, includes the **Database Backend** (QSQLITE or QMYSQL) and the **Database Path** (where your collection is located). 0277 0278 You can view your statistics by going to :menuselection:`Help --> Database Statistics...`. A dialog box like this will appear: 0279 0280 .. figure:: images/setup_database_statistics.webp 0281 :alt: 0282 :align: center 0283 0284 The digiKam Database Statistics Dialog