Bug 277242

This error message is reproducible under Linux as OSX ?

Gilles Caulier

This is in fact reproducible on Linux. Now that i have it building my machine at work, here is the path to reproduce. 

I stared with a fresh database with some sample photos. Use the Database Migration Tool, to convert the database from SQLite to MySQL (NOTE: i used an external MySQL database, not the internal version as the 'Internal Database' option gave an error). The conversion succeeded. Then, go to Configure Digikam and update the database information to use the newly created MySQL database, at which point i got an error saying, 
  "Error message: Failed to create tables in database." 
where the title of the dialog was: 
  "Failed to initialize thumbnail database".

Relevant konsole output follows,
  digikam(11486)/digikam (core): Call initializeThumbnailDatabase at
  application start. There are already thumbnail loading threads created,
  and these will not be switched to use the database.

Then after these steps, every start of digikam will yield the messages outlined in the initial bug report. Also Note, that using the command line mysql client i can create tables within the thumbnails database using the same username/pass as digikam. 

I hope this helps a little bit, please let me know if you need more information.

I think I can explain a bit of it, and how to make it work the temporary
way:

If you have a separate Mysql DB for thumbs (say 'digikam' for images and
'digikam_thumbs' for the thumbnails), then a 'settings' table is written
into 'digikam_thumbs'. During the migration from sqlite, nothing gets
written into into that table, although the sqlite corresponding table has
DBThumbnailsVersionRequired:1, DBThumbnailsVersion:2 in it.

I added the two records to 'digikam_thumbs'.settings, but digikam still
throws the same error. Then I added the 2 records to  'digikam'.settings and
it worked, no error message anymore, and digikam finds all its thumbnails
from the DB again.


Gerhard

On Thu, Jul 7, 2011 at 10:38 PM, brad <bkn@ithryn.net> wrote:

> https://bugs.kde.org/show_bug.cgi?id=277242
>
>
>
>
>
> --- Comment #2 from brad <bkn ithryn net>  2011-07-07 20:38:21 ---
> This is in fact reproducible on Linux. Now that i have it building my
> machine
> at work, here is the path to reproduce.
>
> I stared with a fresh database with some sample photos. Use the Database
> Migration Tool, to convert the database from SQLite to MySQL (NOTE: i used
> an
> external MySQL database, not the internal version as the 'Internal
> Database'
> option gave an error). The conversion succeeded. Then, go to Configure
> Digikam
> and update the database information to use the newly created MySQL
> database, at
> which point i got an error saying,
>  "Error message: Failed to create tables in database."
> where the title of the dialog was:
>  "Failed to initialize thumbnail database".
>
> Relevant konsole output follows,
>  digikam(11486)/digikam (core): Call initializeThumbnailDatabase at
>  application start. There are already thumbnail loading threads created,
>  and these will not be switched to use the database.
>
> Then after these steps, every start of digikam will yield the messages
> outlined
> in the initial bug report. Also Note, that using the command line mysql
> client
> i can create tables within the thumbnails database using the same
> username/pass
> as digikam.
>
> I hope this helps a little bit, please let me know if you need more
> information.
>
> --
> Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
> ------- You are receiving this mail because: -------
> You are the assignee for the bug.
> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel@kde.org
> https://mail.kde.org/mailman/listinfo/digikam-devel
>

I also did some testing and i will describe my findings in brief here in hopes someone who is more familiar with the database code will understand the problem.

Consider the line of code in core/libs/database/thumbnailschemaupdater.cpp:92

    QStringList tables = m_access->backend()->tables();                             

If one looks at the list returned, it is in fact a list of tables; however it is a list of ALL tables in ALL databases that the particular mysql user can read. So in that list there is a 'Settings' table for the 'digikam' database, as well as a 'Settings' table for the 'digikam_thumbs' database. When the code asks for the 'Settings' table (which i am assuming line 97 of the same file does) is ambiguous, and probably returns the first Settings table that occurs alphabetically; in the example above that would be 'digikam.settings' rather than 'digikam_thumbs.settings'.

This explains the behavior in the above comment. I am still sifting through the code to figure out why there is no database specified in this particular database code. I am not familiar with this code; so perhaps someone familiar with the code will find a fix first. 

Cheers, 
 -- brad

(In reply to comment #3)
> I think I can explain a bit of it, and how to make it work the temporary
> way:
> 
> If you have a separate Mysql DB for thumbs (say 'digikam' for images and
> 'digikam_thumbs' for the thumbnails), then a 'settings' table is written
> into 'digikam_thumbs'. During the migration from sqlite, nothing gets
> written into into that table, although the sqlite corresponding table has
> DBThumbnailsVersionRequired:1, DBThumbnailsVersion:2 in it.
> 
> I added the two records to 'digikam_thumbs'.settings, but digikam still
> throws the same error. Then I added the 2 records to  'digikam'.settings and
> it worked, no error message anymore, and digikam finds all its thumbnails
> from the DB again.
> 
> 
> Gerhard

I'll test using separate databases for images and thumbnails, in the meantime could you explain the rationale for using different databases in mysql for tables and thumbs?

Because I was thinking on this the last week and made no sense to me, either it should be possible to have different types of databases i.e. images on mysql and thumbs on sqlite, or it should be all in one database, so I'm curious of this one



(In reply to comment #4)
> I also did some testing and i will describe my findings in brief here in hopes
> someone who is more familiar with the database code will understand the
> problem.
> 
> Consider the line of code in core/libs/database/thumbnailschemaupdater.cpp:92
> 
>     QStringList tables = m_access->backend()->tables();    

this call
- QStringList QSqlDatabase::tables ( QSql::TableType type = QSql::Tables ) const
- Returns a list of the database's tables, system tables and views, as specified by the - parameter type.
if it return tables of other mysql databases it's a QT bug

> 
> If one looks at the list returned, it is in fact a list of tables; however it
> is a list of ALL tables in ALL databases that the particular mysql user can
> read. So in that list there is a 'Settings' table for the 'digikam' database,
> as well as a 'Settings' table for the 'digikam_thumbs' database. When the code
> asks for the 'Settings' table (which i am assuming line 97 of the same file
> does) is ambiguous, and probably returns the first Settings table that occurs
> alphabetically; in the example above that would be 'digikam.settings' rather
> than 'digikam_thumbs.settings'.
> 
> This explains the behavior in the above comment. I am still sifting through the
> code to figure out why there is no database specified in this particular
> database code. I am not familiar with this code; so perhaps someone familiar
> with the code will find a fix first. 
>

Francesco,

The reason why thumbs are stored in a separated DB file is file size, which can be huge with large collection. 

Users what to be able to manage where thumb DB will be stored to not fill a lots the hard drive. 

In this DB we use wavelets compression to optimize size. Thumbs size is 256x256. Imagine the DB size for 200K, or 400K items ?

Gilles Caulier

(In reply to comment #6)
> Francesco,
> 
> The reason why thumbs are stored in a separated DB file is file size, which can
> be huge with large collection. 
> 
> Users what to be able to manage where thumb DB will be stored to not fill a
> lots the hard drive. 
> 
> In this DB we use wavelets compression to optimize size. Thumbs size is
> 256x256. Imagine the DB size for 200K, or 400K items ?
> 
> Gilles Caulier

This is correct for sqlite for many reasons, but a mysql database is quite a different beast, has I've said before it can be useful to use different _types_ of databases for images and thumbs (also a filesystem based one came in mind), but using different schemas in mysql give no performaces or safety gains

But the size of DB to store thumbs still a problem with Mysql. No ?

The way to be able to setup a specific place to store thumbs DB is very important (it's an old report from bugzilla in fact). Currently, with Mysql, it's not possible to do it...

Gilles Caulier

(In reply to comment #8)
> But the size of DB to store thumbs still a problem with Mysql. No ?

Either you have a table per file (myisam and innodb with "innodb_file_per_table") or you have all databases in one place (innodb and other). So in short no, it's not a problem with mysql.
Also Mysql is adapt meant to be used with huge amount of data, many GBytes or even TeraBytes

> 
> The way to be able to setup a specific place to store thumbs DB is very
> important (it's an old report from bugzilla in fact). Currently, with Mysql,
> it's not possible to do it...

yep it is ... kinda possible, you have "partitioning" and "symlinking" but that's stuff for advanced users not for us to manage
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
http://dev.mysql.com/doc/refman/5.1/en/symbolic-links-to-tables.html

>
>
> I'll test using separate databases for images and thumbnails, in the
> meantime
> could you explain the rationale for using different databases in mysql for
> tables and thumbs?
>
> Because I was thinking on this the last week and made no sense to me,
> either it
> should be possible to have different types of databases i.e. images on
> mysql
> and thumbs on sqlite, or it should be all in one database, so I'm curious
> of
> this one
>

I like to keep thumbnails separate for the sole reason of slim backups.
Explanation:
I run a sql backup every week, and having 1.3GB as thumbnails, it is a bit
heavy. The image DB has 130MB only (contains 56k images at the moment,
interestingly, the bulk data is copyrights). So I don't do backups on
thumbnails. Should I loose them, digikam will rebuild them in 2 hours.
Digikam foresees the dual DB system in the settings: you can specify two
schema names. And, by the way, when using sqlite it uses to DBs as well.

Gerhard


> _______________________________________________
> Digikam-devel mailing list
> Digikam-devel@kde.org
> https://mail.kde.org/mailman/listinfo/digikam-devel
>

(In reply to comment #10)
> >
> >
> > I'll test using separate databases for images and thumbnails, in the
> > meantime
> > could you explain the rationale for using different databases in mysql for
> > tables and thumbs?

still todo but soon (TM) ;)

> >
> > Because I was thinking on this the last week and made no sense to me,
> > either it
> > should be possible to have different types of databases i.e. images on
> > mysql
> > and thumbs on sqlite, or it should be all in one database, so I'm curious
> > of
> > this one
> >
> 
> I like to keep thumbnails separate for the sole reason of slim backups.
> Explanation:
> I run a sql backup every week, and having 1.3GB as thumbnails, it is a bit
> heavy. The image DB has 130MB only (contains 56k images at the moment,
> interestingly, the bulk data is copyrights). So I don't do backups on
> thumbnails. Should I loose them, digikam will rebuild them in 2 hours.
> Digikam foresees the dual DB system in the settings: you can specify two
> schema names. And, by the way, when using sqlite it uses to DBs as well.
> 
> Gerhard

I do understund, but please permit me to suggest a script that does the dump of the wanted data, and append only the structure of thumbnail database, it use the the standard `mysqldump` program


TIMESTAMP=$( date -u +%Y-%m-%dT%H-%M-%S )
DIGIKAM_DATABASE=digikam
u=digikam
p=digikam

mysqldump \
  --opt --routines \
  -u"${u}" -p"${p}" \
  --ignore-table="${DIGIKAM_DATABASE}.Thumbnails" \
  --ignore-table="${DIGIKAM_DATABASE}.UniqueHashes" \
  --ignore-table="${DIGIKAM_DATABASE}.FilePaths" \
  --ignore-table="${DIGIKAM_DATABASE}.CustomIdentifiers" \
  ${DIGIKAM_DATABASE} \
  > digikam-${TIMESTAMP}

mysqldump \
  --opt \
  --no-data \
  -u"${u}" -p"${p}" \
  "${DIGIKAM_DATABASE}" \
    Thumbnails \
    UniqueHashes \
    FilePaths \
    CustomIdentifiers \
  >> digikam-${TIMESTAMP}

gzip digikam-${TIMESTAMP}

## restore with:
# gzcat digikam-${TIMESTAMP}.gz | mysql -u"${u}" -p"${p}" "${DIGIKAM_DATABASE}"

-- regards, Francesco

I don't know much about all of this, but might i suggest that however the tables/databases are organized, that perhaps the tables that are meant to be for thumbnails all be have a prefix, e,g, ThumbnailDBSettings, ThumbnailDBUniqueHashes, ThumbnailDBFIlePaths .... etc.

This way, there are no name space clashes, which we're seeing above, and if the two databases are to be merged in the future, minimal effort will be needed to alter the sql code. 

I played around with this idea in my local code base, however in the hour or two i was testing, i wasn't able to get a full understanding of all the instances of database queries.

(In reply to comment #12)
> I don't know much about all of this, but might i suggest that however the
> tables/databases are organized, that perhaps the tables that are meant to be
> for thumbnails all be have a prefix, e,g, ThumbnailDBSettings,
> ThumbnailDBUniqueHashes, ThumbnailDBFIlePaths .... etc.
> 
> This way, there are no name space clashes, which we're seeing above, and if the
> two databases are to be merged in the future, minimal effort will be needed to
> alter the sql code. 

let me precise that there will be always the possibility to have different databases for thumbnails and images data, the digikam code also will always be totally structured to support this, the "merging" I was thinking is more something like - hardcopy the database name used for images to that of thumbnails" _only_ for databases like mysql or postgres.

> 
> I played around with this idea in my local code base, however in the hour or
> two i was testing, i wasn't able to get a full understanding of all the
> instances of database queries.

it's a very good idea, but it require another migration and another version of the database, it will not be done in 2.0

I don't think I'll be able to fix this for 2.0, could we live with this bug until 2.1?

Changes should be simple but have yet to be investigated and after that tested in deep.

:(

> 
> 
> --- Comment #14 from Francesco Riosa <francesco+kde pnpitalia it>  2011-07-25 12:13:01 ---
> I don't think I'll be able to fix this for 2.0, could we live with this bug
> until 2.1?
> 
if this is the case, would it make sense to temporarily comment out the Database Migration feature? I image anyone trying this feature will likely run into the same problem, file the same bug report, or simply give up on the software from a bad experience which will happen every time the start digikam. 


> Changes should be simple but have yet to be investigated and after that tested
> in deep.
> 
understood. if a patch needs testing in the future for this bug, post it here and i'll offer some feedback.

(In reply to comment #15)
> > 
> > 
> > --- Comment #14 from Francesco Riosa <francesco+kde pnpitalia it>  2011-07-25 12:13:01 ---
> > I don't think I'll be able to fix this for 2.0, could we live with this bug
> > until 2.1?
> > 
> if this is the case, would it make sense to temporarily comment out the
> Database Migration feature? I image anyone trying this feature will likely run
> into the same problem, file the same bug report, or simply give up on the
> software from a bad experience which will happen every time the start digikam. 

Well, the migration is needed to fix various other bugs making the answer "no it's no possible", there is s still the possibility to disable a splitted mysql database, because that's a simply interface change but it can give problem to people who have already a splitted mysql database (there are two databases but I see only one in the settings) not wonderfull either

> > Changes should be simple but have yet to be investigated and after that tested
> > in deep.
> > 
> understood. if a patch needs testing in the future for this bug, post it here
> and i'll offer some feedback.

Sure will be done

Git commit d5eae51de889ba326b3d33771e7888cd97492932 by Francesco Riosa.
Committed on 31/07/2011 at 18:31.
Pushed by riosa into branch 'master'.

Migration: splitted mysql database fixes

Migration of a sqlite database to a mysql one didn't create
create_index_if_not_exists() stored procedure in the thumbnail database,
leading to failed update if index already existed.

Data of the thumbnail database is not copyed at all, but the content of
the "Settings" table are needed and checked. Workaround the problem
checking/inserting the two needed records at creation time.

CCBUG: 277242
CCBUG: 276052

M  +77   -32   data/database/dbconfig.xml.cmake

http://commits.kde.org/digikam/d5eae51de889ba326b3d33771e7888cd97492932

(In reply to comment #17)
> Migration: splitted mysql database fixes

I've discovered with some surprise that the content of the thumbnail database is not migrated at all from the procedure.

Now thinking at it may make sense, dump and reload of that heavy data is slow and asking the user to re-generate the database could make sense, she/he/them will also benefit from a clean database without thumbnails for deleted images.

RFC

should be migrate the data, or should be warn the user that re-building all the thumbnails will be needed?

after the decision I'll fix and close definitely this bug

I guess regenerating my collection of 27k images over slow Wifi and NFS would be substantially slower than just migrating the data in the database. OTOH it would probably be less error prone to force a regeneration of the thumbnail DB.

(In reply to comment #19)
> I guess regenerating my collection of 27k images over slow Wifi and NFS would
> be substantially slower than just migrating the data in the database. OTOH it
> would probably be less error prone to force a regeneration of the thumbnail DB.

I guess there is a lot of people in this situation, if nothing else because the need to a mysql database arise with big collections.

If your thumbnail db is empty, In the meantime could you test the following?

sqlite3 thumbnails-digikam.db .dump\
| sed -n -e 's:^INSERT INTO "\([^"]*\)":INSERT IGNORE INTO `\1`:p' \
| mysql -u"$digikam_user" -p"$digikam_pass" "$digikam_database"

Git commit d9afaa0084b2a35bd2baa2ab365a54cd4a19ab4c by Francesco Riosa.
Committed on 02/08/2011 at 01:37.
Pushed by riosa into branch 'sql/2.0'.

Split of settings/migration for images/thumbs DB

This is the first step in really managing the Image metadata database
and the Thumbnail cache database as different entities.

Only changes in the gui are included and as is the code is not
functional, still need to be done:

1) change the structure of digikamrc and it's migration
2) define interaction between widgets in the new migration dialog
3) port and possibly unify "Settings -> database migration" with
"Settings -> configure digikam -> database settings"
4) add a "Backup" button

CCBUG: 277242
GUI: some new strings, work in progress

M  +2    -2    digikam/utils/albumsettings.h
M  +141  -38   libs/dialogs/migrationdlg.cpp
M  +2    -2    utilities/setup/setupdatabase.cpp
M  +4    -4    libs/widgets/common/databasewidget.h
M  +3    -3    libs/database/databaseparameters.cpp
M  +4    -1    libs/dialogs/migrationdlg.h
M  +8    -8    digikam/utils/albumsettings.cpp
M  +38   -35   libs/widgets/common/databasewidget.cpp

http://commits.kde.org/digikam/d9afaa0084b2a35bd2baa2ab365a54cd4a19ab4c

here there is another one:

http://quickgit.kde.org/?p=digikam.git&a=commit&h=9fa4b617ec1b195b9f966bb8fc5f712d6bb0b515

Don't try this at home, is broken ATM

hello, 
I'm testing out digikam 2.0.0 using the fedora 15 kde-unstable packages and ran into this problem. 

Failed to initialize thumbnail  ..
Error Message: The database is not valid: the "DBThumbnailsVersion" setting does not exist. The current database schema version cannot be verified. Try to start with an empty database.

The error message was displayed, but the program appeared to operate okay.   I also did get an error after initial load of images, but didn't write that down.  This was with a new mysql database and not using the migration assistant. 

Following the guidance above the following sql statements into mysql seem to fix the problem.  

insert into digikamthumb.Settings(keyword,value) VALUES('DBThumbnailsVersionRequired','1');
insert into digikamthumb.Settings(keyword,value) VALUES('DBThumbnailsVersion','2');

Looks like you already have seen this, but thought I'd mention it anyway..

Geoff

Git commit 37f5f5d59f174bcedc86438365f9a7270a1bd677 by Francesco Riosa.
Committed on 06/09/2011 at 00:29.
Pushed by riosa into branch 'master'.

Split "images" and "thumbnails" databases.

This commit splits the configure options of the images and thumbnail
databases, will then be possible use different database servers.
Also the migration dialog is now more flexible.

GUI: migrationdlg.cpp databasewidget.cpp
CCBUG: 277242

M  +1    -1    databaseserver/databaseserver.cpp
M  +35   -22   digikam/album/albummanager.cpp
M  +6    -1    digikam/album/albummanager.h
M  +7    -7    digikam/database/databaseguierrorhandler.cpp
M  +1    -1    digikam/database/dbstatdlg.cpp
M  +18   -3    digikam/main/digikamapp.cpp
M  +110  -39   digikam/utils/albumsettings.cpp
M  +39   -18   digikam/utils/albumsettings.h
M  +1    -1    digikam/utils/componentsinfo.h
M  +3    -0    digikam/utils/config-digikam.h.cmake
M  +14   -14   libs/database/databasecorebackend.cpp
M  +1    -1    libs/database/databaseoperationgroup.cpp
M  +422  -200  libs/database/databaseparameters.cpp
M  +44   -30   libs/database/databaseparameters.h
M  +5    -5    libs/database/schemaupdater.cpp
M  +1    -1    libs/database/thumbnailschemaupdater.cpp
M  +2    -0    libs/dialogs/migrationdlg.cpp
M  +1    -0    libs/dialogs/migrationdlg.h
M  +363  -152  libs/widgets/common/databasewidget.cpp
M  +40   -19   libs/widgets/common/databasewidget.h
M  +2    -2    utilities/nepomuk/digikamnepomukservice.cpp
M  +65   -34   utilities/setup/setupdatabase.cpp

http://commits.kde.org/digikam/37f5f5d59f174bcedc86438365f9a7270a1bd677

This bug appears to have been fixed (Thanks Francesco) as of code sync from Oct 3rd. if no one else objects i will close this bug as fixed in the coming days.

(In reply to comment #25)
> This bug appears to have been fixed (Thanks Francesco) as of code sync from Oct
> 3rd. if no one else objects i will close this bug as fixed in the coming days.

Hi, there are other changes needed to have all properly working, some of them were considered not yet ready for release and reverted, so please leave this bug open.
I've not yet an exact ETA for this bug, being rather under pressure at work, but this is high in priority.

Hi,
I am running Digikam 2.5.0, with the same DB schemata for Thumbnails and Images. I also ran into this bug ... I dont know whether it should have been fixed already.
BTW even though I have a couple of thousands images at stage, the Thumbnails table is empty...

New digiKam 4.11.0 is available with official PKG installer for OSX.

https://www.digikam.org/node/740

Can you reproduce the problem with this release ?

Gilles Caulier

On my installation the thumbnails databases populated is functioning correctly.