Bug 323062

Summary: digikam (core): Schema update to V 7 failed!
Product: digikam Reporter: Rainer Dorsch <ml>
Component: Database-SchemaAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: major CC: caulier.gilles, thomas
Priority: NOR    
Version: 3.3.0   
Target Milestone: ---   
Platform: Debian unstable   
OS: Linux   
Latest Commit: Version Fixed In: 3.4.0

Description Rainer Dorsch 2013-07-31 20:06:02 UTC
Dear Maintainer,

when starting digikam on my Debian sid system, I get

rd@blackbox:~/Downloads$ digikam 
Fontconfig warning: "/etc/fonts/conf.d/50-user.conf", line 9: reading configurations from ~/.fonts.conf is deprecated.
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 16: Having multiple values in <test> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-andika.conf", line 35: Having multiple <family> in <alias> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-droid-sans-fonts.conf", line 103: Having multiple values in <test> isn't supported and may not work as expected
Fontconfig warning: "/etc/fonts/conf.d/65-droid-sans-fonts.conf", line 138: Having multiple values in <test> isn't supported and may not work as expected
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
digikam(5648)/digikam (core): Schema update to V 7 failed! 


and the albums and tags are gone.

Digikam recommends to report the issue at bugs.kde.org, but I thought
it might be good to report to the distribution first.

Thanks,
Rainer

Reproducible: Always

Steps to Reproduce:
1.Start digikam
2.
3.
Actual Results:  
see details

Expected Results:  
see details

Please let me know if you need additional information.
Comment 1 Marcel Wiesweg 2013-08-01 17:18:47 UTC
Which version are you using, and which version have you used before?
Comment 2 Rainer Dorsch 2013-08-01 20:43:00 UTC
I use 3.1.0 but I am not aware that I did upgrade recently.

From my apt history:
-> I upgraded 10 days back from debian version 3.1.0-3 to 3.1.0-3, which should not be relevant for the scheme update and I am sure I did run digikam in between the upgrade and the failure

-> I upgraded in April from 2.6 to 3.1, but since then I run digikam many times.

Start-Date: 2013-07-20  09:52:06
Commandline: apt-get upgrade
Upgrade: digikam-private-libs:i386 (3.1.0-3, 3.1.0-4), plasma-wallpapers-addons:i386 (4.10.5-1, 4.10.5-2), kipi-plugins-common:i386 (3.1.0-3, 3.1.0-4), plasma-runners-addons:i386 (4.10.5-1, 4.10.5-2), chromium-browser:i386 (27.0.1453.110-1~deb7u1, 28.0.1500.71-1~deb7u1), kipi-plugins:i386 (3.1.0-3, 3.1.0-4), chromium-inspector:i386 (27.0.1453.110-1~deb7u1, 28.0.1500.71-1~deb7u1), libservlet2.5-java:i386 (6.0.35-6, 6.0.35-6+deb7u1), plasma-widgets-addons:i386 (4.10.5-1, 4.10.5-2), plasma-dataengines-addons:i386 (4.10.5-1, 4.10.5-2), digikam:i386 (3.1.0-3, 3.1.0-4), plasma-containments-addons:i386 (4.10.5-1, 4.10.5-2), digikam-data:i386 (3.1.0-3, 3.1.0-4), chromium:i386 (27.0.1453.110-1~deb7u1, 28.0.1500.71-1~deb7u1), kdeplasma-addons:i386 (4.10.5-1, 4.10.5-2), plasma-widget-lancelot:i386 (4.10.5-1, 4.10.5-2)
End-Date: 2013-07-20  09:52:23


Start-Date: 2013-04-12  22:29:53
Commandline: apt-get install -t experimental digikam
Install: libpgf6:i386 (6.12.24+ds1-2, automatic), libkexiv2-11:i386 (4.10.2-1, automatic), libsqlite0:i386 (2.8.17-7, automatic), digikam-private-libs:i386 
(3.1.0-3, automatic), libkdcraw22:i386 (4.10.2-1, automatic)
Upgrade: kipi-plugins:i386 (2.6.0-1+b2, 3.1.0-3), digikam:i386 (2.9.0-3+kdetrunk2, 3.1.0-3), libkexiv2-data:i386 (4.8.4-1, 4.10.2-1), digikam-data:i386 (2.9
.0-3+kdetrunk2, 3.1.0-3), kipi-plugins-common:i386 (2.6.0-1, 3.1.0-3), libkdcraw-data:i386 (4.8.4-1, 4.10.2-1)
End-Date: 2013-04-12  22:30:07

Please let me know if I can provide more information.
Comment 3 Rainer Dorsch 2013-08-03 12:26:53 UTC
Which file is affected during the schema update? I could dig into my backups to see if something special happened.

Thanks,
Rainer
Comment 4 Marcel Wiesweg 2013-08-05 19:18:42 UTC
It is the database file, digikam4.db. If you dont know the exact location (specified in the settings), it will also be shown in the first few lines of console output, if 50003 is enabled in kdebugdialog.
I recommend to open this file with the "sqlite3" command line tool and issue 
PRAGMA integrity_check; 
to rule out database corruption
Comment 5 Rainer Dorsch 2013-08-06 05:52:45 UTC
Marcel,

thank you for answering.

The database looks ok:


rd@blackbox:~/Rohdaten/digiKam$ sqlite3 digikam4.db 
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA integrity_check; 
ok
sqlite> .exit
rd@blackbox:~/Rohdaten/digiKam$ 

For me, now the main question is, why does digikam suddenly want to do the scheme update?

Where is specified in the db, that it is scheme V6? I could investigate in the backup when this changed to V6 and if it was ever before V7 (?).

Do you have any other ideas?

Thanks,
Rainer
Comment 6 Marcel Wiesweg 2013-08-11 16:51:11 UTC
The current version is stored in the Settings table, the "DBVersion" property. The value is 7 since digikam 3.0.
You have version 6 if there is an ImageHistory and ImageTagProperties table.
You have version 7 if there is a VideoMetadata table.

If you enable 50003 in kdebugdialog, you should as well be able to see what is failing exactly from the console output.
Comment 7 Rainer Dorsch 2013-08-11 17:11:13 UTC
Many thanks for your reply.

I am using digikam 3.1 since April, so it cannot be that the schema update is called for the first time.

After enabling 50003 in kdedebugdialog, I got

digikam(11831)/digikam (core) Digikam::KInotify::Private::open: Successfully opened connection to inotify: 12
digikam(11831)/digikam (core) Digikam::AlbumManager::setDatabase: DatabaseParameters: [ Type "QSQLITE", Name "/home/rd/Rohdaten/digiKam/digikam4.db" (Thumbnails Name "/home/rd/Rohdaten/digiKam/thumbnails-digikam.db"); ]
QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
digikam(11831)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: Loading SQL code from config file "/usr/share/kde4/apps/digikam/database/dbconfig.xml"
digikam(11831)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: false "1" 1 1
digikam(11831)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater update
digikam(11831)/digikam (core) Digikam::SchemaUpdater::startUpdates: Have a database structure version  6                                                    
digikam(11831)/digikam (core) Digikam::SchemaUpdater::makeUpdates: makeUpdates  6  to  7                                                                    
digikam(11831)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query:                                         
 ""                                                                                                                                                         
Error messages: "Unable to execute statement" "table VideoMetadata already exists" 1 2                                                                      
Bound values:  ()                                                                                                                                           
digikam(11831)/digikam (core) Digikam::DatabaseCoreBackend::execDBAction: Error while executing DBAction [ "UpdateSchemaFromV6ToV7" ] Statement [ " CREATE TABLE VideoMetadata                                                                                                                                          
                            (imageid INTEGER PRIMARY KEY,                                                                                                   
                            aspectRatio TEXT,                                                                                                               
                            audioBitRate TEXT,                                                                                                              
                            audioChannelType TEXT,                                                                                                          
                            audioCompressor TEXT,                                                                                                           
                            duration TEXT,                                                                                                                  
                            frameRate TEXT,                                                                                                                 
                            exposureProgram INTEGER,                                                                                                        
                            videoCodec TEXT)" ]                                                                                                             
digikam(11831)/digikam (core): Schema update to V 7 failed!                                                                                                 
digikam(11831)/kdeui (KNotification) KNotificationManager::notificationClosed: 2919   

Seems digikam tries to create VideoMetadata, but it already exists (?).

In that context I might have tried to add GPS coordinates to a video (using Image->Geo-location, which failed. But not sure if this is related...

Can you tell why digikam tries to create VideoMetadata?

Many thanks again,
Rainer
Comment 8 Rainer Dorsch 2013-08-11 19:06:52 UTC
> You have version 6 if there is an ImageHistory and ImageTagProperties table.
> You have version 7 if there is a VideoMetadata table.

sqlite> .tables
AlbumRoots          ImageHistory        ImageTagProperties  Tags              
Albums              ImageInformation    ImageTags           TagsTree          
DownloadHistory     ImageMetadata       Images              VideoMetadata     
ImageComments       ImagePositions      Searches          
ImageCopyright      ImageProperties     Settings          
ImageHaarMatrix     ImageRelations      TagProperties     
sqlite> 

Should I have ImageHistory and ImageTagProperties tables AND and VideoMetadata in version 7? Or should there be no ImageHistory and ImageTagProperties tables in version 7?

Many thanks,
Rainer
Comment 9 Rainer Dorsch 2013-08-11 19:25:31 UTC
Seems the database contains inconsistent data:
-> DBVersion|6

but 

sqlite> .tables VideoMetadata
VideoMetadata
sqlite> 

Can you imagine any scenario, which results in such an inconsistent database?

Does it make sense to change DBVersion manually to 7?

Here are the full settings:

sqlite> select * from Settings;
Locale|UTF-8
UpgradedFromSqlite2|yes
databaseImageFormats|jpg;jpeg;jpe;jp2;j2k;jpx;jpc;pgx;tif;tiff;png;xpm;ppm;pnm;pgf;gif;bmp;xcf;pcx;bay;bmq;cr2;crw;cs1;dc2;dcr;dng;erf;fff;hdr;k25;kdc;mdc;mos;mrw;nef;orf;pef;pxn;raf;raw;rdc;sr2;srf;x3f;arw;3fr;cine;ia;kc2;mef;nrw;qtk;rw2;sti;rwl;srw;
databaseVideoFormats|mpeg;mpg;mpo;mpe;avi;divx;wmv;wmf;asf;mp4;3gp;mov;3g2;m4v;m2v;mkv;webm
databaseAudioFormats|ogg;mp3;wma;wav
FilterSettingsVersion|4
DcrawFilterSettingsVersion|4
databaseUserImageFormats|dps
databaseUserVideoFormats|
databaseUserAudioFormats|
RemovedItemsTime|2013-07-31T20:55:51
DeleteRemovedCompleteScanCount|1
Scanned|2013-07-31T20:56:06
preAlpha010Update1|true
preAlpha010Update2|true
preAlpha010Update3|true
beta010Update1|true
beta010Update2|true
databaseUUID|{5f83ecaf-ffb7-4858-9a74-ed0507c6940b}
DBVersion|6
DBVersionRequired|5
sqlite> 

Thanks,
Rainer
Comment 10 Rainer Dorsch 2013-08-11 20:04:30 UTC
I looked through my backup and found a digikam4.db from July 31st in the morning which contains DBVersion=7, i.e. somehow the DBVersion got downgraded from 7 to 6 (!).

sqlite> SELECT * FROM Settings;
UpgradedFromSqlite2|yes
preAlpha010Update1|true
preAlpha010Update2|true
preAlpha010Update3|true
beta010Update1|true
beta010Update2|true
databaseUUID|{3596a888-3c47-4720-bf06-7848dff56aff}
Locale|UTF-8
InitialSyncDigikamToNepomuk-1|yes
databaseUserImageFormats|dps
databaseUserVideoFormats|
databaseUserAudioFormats|
DeleteRemovedTime|2013-02-03T15:57:50
DeleteRemovedCompleteScanCount|1
Scanned|2013-02-10T19:11:30
databaseImageFormats|jpg;jpeg;jpe;jp2;j2k;jpx;jpc;pgx;tif;tiff;png;xpm;ppm;pnm;pgf;gif;bmp;xcf;pcx;bay;bmq;cr2;crw;cs1;dc2;dcr;dng;erf;fff;hdr;k25;kdc;mdc;mos;mrw;nef;orf;pef;pxn;raf;raw;rdc;sr2;srf;x3f;arw;3fr;cine;ia;kc2;mef;nrw;qtk;rw2;sti;rwl;srw;
databaseVideoFormats|mpeg;mpg;mpo;mpe;avi;divx;wmv;wmf;asf;mp4;3gp;mov;3g2;m4v;m2v;mkv;webm
databaseAudioFormats|ogg;mp3;wma;wav
FilterSettingsVersion|4
DcrawFilterSettingsVersion|4
RemovedItemsTime|2013-07-28T21:39:54
DBVersion|7
DBVersionRequired|5
sqlite> 

With the old digikam4.db, digikam works again.

Needless to say that I cannot reproduce this downgrade from DBVersion 7 to DBVersion6, currently.

Do you have an idea how that could happen?

I do have a copy of the "inconsistent" digikam4.db file. Do you want to have a look into it to better understand what happened?
Comment 11 Marcel Wiesweg 2013-08-12 20:10:33 UTC
This is very strange indeed.
Did you use an old (3.0) digikam in between? Still cannot think that it would cause the weird behavior.
Comment 12 Rainer Dorsch 2013-08-12 21:07:14 UTC
No, I upgraded at 2013-04-12 from digikam 2.9 to 3.1 (see Comment 2 above).

I tried to add coordinates and tags to movies again, but that did (at least this time) not trigger the problem.

I have no idea, how I (or better digikam) managed to get DBVersion=6 back. The only chance I see is to compare the two digikam4.db files and check what else changed. As I said, if you are interested in that, I am happy to provide the db files. If you think it is not worth, because there are more severe issues, in which your time is better invested, also fine with me.

Rainer
Comment 13 TSa 2013-08-18 14:08:51 UTC
HaloO,

I am not sure if I should hijack this bug or open a new one. Sorry for any inconvenience.

Recently i tried to upgrade from digikam 0.93 on kubuntu 8.04 to digikam 3.1 on kubuntu 13.04.
At startup digikam tried to convert the database version. While this happened I saw a progressbar
and the message "throwing away V3 tables" then the error that the upgrade from V6 to V7 failed
has occurred. I confirm this with OK and the message box disappears. Thereafter all my albums, photos and tags are visible. However when I restart digikam I get just the V6 to V7 error and no photos and tags are loaded.

I can reproduce the initial behaviour by deleting the digikam4.db file and restarting with the original digikam3.db file.

I have upgraded digikam to version 3.2 following a tutorial. This gave me a core dump on the update dialog. For investigating this I have tried to build the newest version from GIT but the configure failed on my kubuntu 13.04 environment for not being new enough. So I have built
version 3.2 for debugging and found the reason for the core dump:

void CollectionScanner::scanFileNormal(const QFileInfo& fi, const ItemScanInfo& scanInfo)
{
    bool hasAnyHint = d->hints && d->hints->hasAnyNormalHint(scanInfo.id);

    // if the date is null, this signals a full rescan
    if (scanInfo.modificationDate.isNull() || 
        (hasAnyHint && d->hints->hasRescanHint(scanInfo.id)) )
    {
      if (d->hints) // TSa added: could be null
        {
            QWriteLocker locker(&d->hints->lock);
            d->hints->rescanItemHints.remove(scanInfo.id);
        }
        rescanFile(fi, scanInfo);
        return;
    }

I have fixed it by checking d->hints for null. Then 3.2 behaves exactly as 3.1: I see all my tags
when starting from the digikam3.db file but not from the digikam4.db file.

Unfortunately I am not a database developer but I am willing to debug this as needed. What I could find out easily is the offending DBAction. For printing it in gdb I´ve copied the QString into a temporary const char *sm that can be printed. Here is the debug  result in
DatabaseCoreBackend::QueryState DatabaseCoreBackend::execDBAction(
     const DatabaseAction& action, const QMap<QString, 
         QVariant>& bindingMap, QList<QVariant>* const values, 
    QVariant* const lastInsertId)

(gdb) p sm
$1 = 0x7fffcc00c188 " CREATE TABLE VideoMetadata\n", ' ' <repeats 28 times>, "(imageid INTEGER PRIMARY KEY,\n", ' ' <repeats 28 times>, "aspectRatio TEXT,\n", ' ' <repeats 28 times>, "audioBitRate TEXT,\n", ' ' <repeats 21 times>...
(gdb) n
660                 result = execDirectSql(actionElement.statement);
(gdb) 
663             if (result != DatabaseCoreBackend::NoErrors)
(gdb) p result
$2 = {value = Digikam::DatabaseCoreBackend::SQLError}

OK, this is where I´m standing right now. What should I do next to investigate the problem?

Regards TSa.
Comment 14 Marcel Wiesweg 2013-08-18 18:57:31 UTC
TSa, thanks for your investigations.
You can use the "sqlite3" command line tool to investigate your failing digikam4.db file.
SELECT * FROM Settings;
What is your "DBVersion" setting?
.schema VideoMetadata
Is there already a table VideoMetadata? Creating it is the statement that fails.
Comment 15 Marcel Wiesweg 2013-08-18 19:33:04 UTC
Git commit 09e9718996170f1882b51fe869c5b343f9a78fea by Marcel Wiesweg.
Committed on 18/08/2013 at 19:25.
Pushed by mwiesweg into branch 'master'.

Fix null pointer access of d->hints, thanks to TSa

M  +1    -0    libs/database/collectionscanner.cpp

http://commits.kde.org/digikam/09e9718996170f1882b51fe869c5b343f9a78fea
Comment 16 TSa 2013-08-19 21:12:32 UTC
HaloO,

I have analyzed the sequence of events when converting my digkam3.db V3 database to V7.
The SchemaUpdater::updateV4toV6() calls SchemaUpdater::createTables() which retrieves
the CreateDB action from the dbconfig.xml file. This contains the creation of the VideoMetadata
table. So it exists when the performUpdateToVersion("UpdateSchemaFromV6ToV7", 7, 5) call
is made in SchemaUpdater::updateToVersion(int targetVersion) because the dbconfig.xml contains
a simple CREATE TABLE VideoMetadata for the SQLite version of UpdateSchemaFromV6ToV7.
In the MySql case it contains a CREATE TABLE  IF NOT EXISTS.

When I add the IF NOT EXISTS in the SQLite case than the update of the database works and
I see all my tags. So please check the contents of dbconfig.xml for consistency between the MySql
and the SQLite backends.

Regards, TSa.
Comment 17 TSa 2013-08-20 19:55:26 UTC
HaloO,

in which version of digikam will my hints fix be released? I guess the 3.3 release still
has the problem and I wonder if the bugfix release 3.4 will make it into kubuntu 13.10.

I also consider the IF NOT EXISTS patch a workaround. From a conceptual point of view the actual error is the fact that updateV4ToV6() calls the createTables() method that creates the most recent set of tables instead of the proper V6 set. So I think there should be a CreateDBV6 database action in dbconfig.xml and updateV4ToV6() should execute that instead of CreateDB. Than future implementations of createTables() won´t collide with subsequent incremental updates like the one from V6 to V7 or a future V7 to V8 etc. The same applies to the corresponding index generation function even though it has worked for me. In general the result of a database upgrade step should be a proper database layout of that version.

So what are your plans to proceed with this bug? I hope the intention is to provide update paths from all older versions. And of course the original reporters problem is different than
mine.

 I fear I can not be of further assistance.

Regards TSa.
Comment 18 Marcel Wiesweg 2013-08-25 12:32:27 UTC
I agree. upgradeV4toV6 is essentially like creating a fresh database and doing some importing into the empty tables, so it ends up at the latest version. I would not like to have another large a historic createTablesV6 for this, as far as I see it's easily fixed by recognizing the updateV4toV6 is properly named updateV4toV7
Comment 19 Marcel Wiesweg 2013-08-25 12:32:45 UTC
Git commit b4f815763e7233ee84aac9532fd903a4e10427a5 by Marcel Wiesweg.
Committed on 25/08/2013 at 12:31.
Pushed by mwiesweg into branch 'master'.

Fix schema updates from old versions: updatev4tov6 is updatev4tov7

M  +6    -4    libs/database/schemaupdater.cpp
M  +1    -1    libs/database/schemaupdater.h

http://commits.kde.org/digikam/b4f815763e7233ee84aac9532fd903a4e10427a5
Comment 20 TSa 2013-08-25 13:16:04 UTC
HaloO Marcel,

great to hear that the bug is fixed! And I hope you keep an eye on it for
future schema updates. Because then you might need to rename the function
to updateV4toV8 if that still fits the bill or do something more sophisticated
depending on the changes in the schema.

I can live with my patched 3.2 on kubuntu 13.04 for now and I think at the time
of an update to 13.10 which currently has digikam 3.3 my schema will be already
on V7. So the bug will be irrelevant to me.

Should I keep my old digikam file for regression testing or do you have an environment
where you can check the version transitions? BTW, is it sufficient to keep the digikam3.db
file or do I need all the photos in the right place?

Regards TSa.
Comment 21 Marcel Wiesweg 2013-08-26 18:34:50 UTC
 As you see with this bug, I did not do regression test with combinations of old versions. And I have no special environment set up.
I would think it better to have the photos in the old place, did not try what happens if not.
Comment 22 Marcel Wiesweg 2013-08-27 17:56:19 UTC
Short guidance for anyone wishing to repair his database:

Open the digikam4.db file with the sqlite3 command line tool:
sqlite3 <path>/digikam4.db

Make sure this bug is for you:
SELECT * FROM Settings WHERE keyword='DBVersion';
DBVersion|6
SELECT COUNT(*) FROM VideoMetadata;
0

The latter statement should give any number, and not the error "no such table: VideoMetadata"

If that applies, fix your DBVersion: 

REPLACE INTO Settings (keyword, value) VALUES ('DBVersion', 7);
.exit