[Digikam-users] Mysql/MariaDb database expert needs...

Gilles Caulier caulier.gilles at gmail.com
Tue Nov 17 17:21:28 GMT 2015


Hi all,

I back in this thread, with some fresh news :

The database schema for Faces Recognition database integration to digiKam
core is now complete. With Mysql, the face database is now integrated as
well. There is a new setting in DB setup to specify a Face Schema Name on
your server.

I fixed also some typo in SQL statements for the database creation. After
few days to try to understand how it's work, i can said that Mysql code is
really more complex than SQlite. For ex, about the famous functions to
create Indexes which require grant privileges, i don't yet found an
alternative, to be able to prevent databases creation with these settings
and to restore internal server.

Any way, i review ALL database code, renamed, tried, cleaned, factored,
simplified, documented implementations when it's possible. I found some
glitches in code but I'm not sure if this will really fix serious problems.

As now the code is better to ready and maintain, i will start to hack
really MySql support. The bugs are already well identified in bugzilla and
some entries are reproducible on my computer.

So, if i need help, it's about to test with all possible user cases.

There are 3 configurations :

1/ using a server (local or remote) with 3 separated databases ==> It's
definitively the best configuration. All is well separated and clear. it's
already set on my computer and this will be the first stage to hack.

2/ using a server (local or remote) with a single and common database ==>
second stage to hack

3/ using internal server : do not work due to functions in DB. ==> last
stage to hack, later...

So if there are some volunteers to help me to test, no need to report new
entries in bugzilla, but please review existing files and check if problems
still reproducible with the current implementation from git/master (digiKam
5.0.0 ported to Qt5)

Thanks in advance

PS : for information, the case 1/ give me the DB content below on my server
:

[root at localhost mysql]# pwd
/var/lib/mysql
[root at localhost mysql]# ls -al
total 176184
drwxr-xr-x  8 mysql mysql     4096 Nov 17 17:18 ./
drwxr-xr-x 42 root  root      4096 Nov 16 23:48 ../
-rw-rw----  1 mysql mysql    16384 Nov 17 00:05 aria_log.00000001
-rw-rw----  1 mysql mysql       52 Nov 17 00:05 aria_log_control
drwx------  2 mysql mysql     4096 Nov 17 17:51 digikamcoredb/
drwx------  2 mysql mysql     4096 Nov 16 23:52 digikamfacedb/
drwx------  2 mysql mysql     4096 Nov 16 23:50 digikamthumbsdb/
-rw-rw----  1 mysql mysql 79691776 Nov 17 17:54 ibdata1
-rw-rw----  1 mysql mysql 50331648 Nov 17 17:54 ib_logfile0
-rw-rw----  1 mysql mysql 50331648 Nov 16 23:48 ib_logfile1
-rw-rw----  1 mysql mysql        0 Nov 16 23:48 multi-master.info
drwx------  2 mysql mysql     4096 Nov 16 23:48 mysql/
srwxrwxrwx  1 mysql mysql        0 Nov 17 17:18 mysql.sock=
drwx------  2 mysql mysql     4096 Nov 16 23:48 performance_schema/
-rw-rw----  1 mysql mysql     2760 Nov 17 17:56 sql_errors.log
drwx------  2 mysql mysql     4096 Nov 16 23:48 test/
[root at localhost mysql]# ls -al digikamcoredb/
total 4244
drwx------ 2 mysql mysql   4096 Nov 17 17:51 ./
drwxr-xr-x 8 mysql mysql   4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql   1582 Nov 16 23:50 AlbumRoots.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 AlbumRoots.ibd
-rw-rw---- 1 mysql mysql   1612 Nov 16 23:50 Albums.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:51 Albums.ibd
-rw-rw---- 1 mysql mysql     65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql     38 Nov 16 23:50 delete_image.TRN
-rw-rw---- 1 mysql mysql     36 Nov 16 23:50 delete_tag.TRN
-rw-rw---- 1 mysql mysql   1553 Nov 16 23:50 DownloadHistory.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:50 DownloadHistory.ibd
-rw-rw---- 1 mysql mysql   2470 Nov 16 23:50 ImageComments.frm
-rw-rw---- 1 mysql mysql 180224 Nov 16 23:51 ImageComments.ibd
-rw-rw---- 1 mysql mysql   2042 Nov 16 23:50 ImageCopyright.frm
-rw-rw---- 1 mysql mysql 360448 Nov 16 23:51 ImageCopyright.ibd
-rw-rw---- 1 mysql mysql   1045 Nov 16 23:50 ImageHaarMatrix.frm
-rw-rw---- 1 mysql mysql  98304 Nov 16 23:50 ImageHaarMatrix.ibd


-rw-rw---- 1 mysql mysql   1604 Nov 16 23:50 ImageHistory.frm


-rw-rw---- 1 mysql mysql 573440 Nov 16 23:51 ImageHistory.ibd

                                                -rw-rw---- 1 mysql mysql
1713 Nov 16 23:50 ImageInformation.frm

                    -rw-rw---- 1 mysql mysql 311296 Nov 16 23:51
ImageInformation.ibd

-rw-rw---- 1 mysql mysql   1514 Nov 16 23:50 ImageMetadata.frm


-rw-rw---- 1 mysql mysql 311296 Nov 16 23:51 ImageMetadata.ibd

                                              -rw-rw---- 1 mysql mysql
1292 Nov 16 23:50 ImagePositions.frm

                      -rw-rw---- 1 mysql mysql 196608 Nov 16 23:51
ImagePositions.ibd

  -rw-rw---- 1 mysql mysql    999 Nov 16 23:50 ImageProperties.frm

                                                  -rw-rw---- 1 mysql mysql
114688 Nov 16 23:51 ImageProperties.ibd

                    -rw-rw---- 1 mysql mysql   1959 Nov 16 23:50
ImageRelations.frm


-rw-rw---- 1 mysql mysql 147456 Nov 16 23:51 ImageRelations.ibd

                                              -rw-rw---- 1 mysql mysql
3226 Nov 16 23:50 Images.frm


-rw-rw---- 1 mysql mysql 655360 Nov 16 23:51 Images.ibd


-rw-rw---- 1 mysql mysql   1408 Nov 16 23:50 Images.TRG

                                                  -rw-rw---- 1 mysql mysql
  2003 Nov 16 23:50 ImageTagProperties.frm

                    -rw-rw---- 1 mysql mysql 147456 Nov 17 17:54
ImageTagProperties.ibd

-rw-rw---- 1 mysql mysql   1932 Nov 16 23:50 ImageTags.frm

                                                  -rw-rw---- 1 mysql mysql
262144 Nov 17 17:53 ImageTags.ibd


-rw-rw---- 1 mysql mysql   1017 Nov 16 23:50 Searches.frm


-rw-rw---- 1 mysql mysql  98304 Nov 16 23:50 Searches.ibd


-rw-rw---- 1 mysql mysql    970 Nov 16 23:50 Settings.frm

                                                      -rw-rw---- 1 mysql
mysql 114688 Nov 17 17:52 Settings.ibd

                                  -rw-rw---- 1 mysql mysql    996 Nov 16
23:50 TagProperties.frm


-rw-rw---- 1 mysql mysql 114688 Nov 17 17:53 TagProperties.ibd

                                                -rw-rw---- 1 mysql mysql
1094 Nov 16 23:50 Tags.frm
-rw-rw---- 1 mysql mysql  98304 Nov 17 17:53 Tags.ibd
-rw-rw---- 1 mysql mysql    567 Nov 16 23:50 TagsTree.frm
-rw-rw---- 1 mysql mysql    489 Nov 16 23:50 Tags.TRG
-rw-rw---- 1 mysql mysql   1241 Nov 16 23:50 VideoMetadata.frm
-rw-rw---- 1 mysql mysql  98304 Nov 16 23:50 VideoMetadata.ibd
[root at localhost mysql]# ls -al digikamthumbsdb/
total 19120
drwx------ 2 mysql mysql     4096 Nov 16 23:50 ./
drwxr-xr-x 8 mysql mysql     4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql     1456 Nov 16 23:50 CustomIdentifiers.frm
-rw-rw---- 1 mysql mysql   131072 Nov 17 17:53 CustomIdentifiers.ibd
-rw-rw---- 1 mysql mysql       65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql     1450 Nov 16 23:50 FilePaths.frm
-rw-rw---- 1 mysql mysql   212992 Nov 17 17:53 FilePaths.ibd
-rw-rw---- 1 mysql mysql      970 Nov 16 23:50 Settings.frm
-rw-rw---- 1 mysql mysql   114688 Nov 17 17:52 Settings.ibd
-rw-rw---- 1 mysql mysql     1061 Nov 16 23:50 Thumbnails.frm
-rw-rw---- 1 mysql mysql 18874368 Nov 17 17:53 Thumbnails.ibd
-rw-rw---- 1 mysql mysql     1603 Nov 16 23:50 UniqueHashes.frm
-rw-rw---- 1 mysql mysql   212992 Nov 17 17:53 UniqueHashes.ibd
[root at localhost mysql]# ls -al digikamfacedb/
total 552
drwx------ 2 mysql mysql   4096 Nov 16 23:52 ./
drwxr-xr-x 8 mysql mysql   4096 Nov 17 17:18 ../
-rw-rw---- 1 mysql mysql     65 Nov 16 23:49 db.opt
-rw-rw---- 1 mysql mysql     42 Nov 16 23:52 delete_identities.TRN
-rw-rw---- 1 mysql mysql    948 Nov 16 23:52 Identities.frm
-rw-rw---- 1 mysql mysql  98304 Nov 16 23:52 Identities.ibd
-rw-rw---- 1 mysql mysql    375 Nov 16 23:52 Identities.TRG
-rw-rw---- 1 mysql mysql   1018 Nov 16 23:52 IdentityAttributes.frm
-rw-rw---- 1 mysql mysql 114688 Nov 16 23:52 IdentityAttributes.ibd
-rw-rw---- 1 mysql mysql   1131 Nov 16 23:52 OpenCVLBPHistograms.frm
-rw-rw---- 1 mysql mysql  98304 Nov 16 23:52 OpenCVLBPHistograms.ibd
-rw-rw---- 1 mysql mysql   1066 Nov 16 23:52 OpenCVLBPHRecognizer.frm
-rw-rw---- 1 mysql mysql  98304 Nov 16 23:52 OpenCVLBPHRecognizer.ibd
-rw-rw---- 1 mysql mysql    970 Nov 16 23:52 Settings.frm
-rw-rw---- 1 mysql mysql 114688 Nov 17 17:52 Settings.ibd

Gilles Caulier
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151117/070ce442/attachment.html>


More information about the Digikam-users mailing list