As mentioned on digikam-users mailing list on 2016-08-29, I tried the alternative dbconfig.xml provided by Maik. No success. I've also tried innodb-large-prefix=true in my global my.cfg. No success either. How can I help to fix the issue?

The sql command in question

  ALTER TABLE Albums 
  ADD CONSTRAINT Albums_AlbumRoots 
  FOREIGN KEY (albumRoot) 
  REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE (albumRoot, relativePath(250)), ENGINE InnoDB;

runs fine without specifying the engine, that is without "ENGINE InnoDB". On my db, according to

  SHOW TABLE STATUS FROM digikamdb;

the "Albums" table is of engine type MyISAM, not InnoDB. Is that new db engine format wanted? Is it ok to change the engine of an existing table? It looks like I have inconsistent engine types over all my tables (mixture between MyISAM and InnoDB).

I deleted all but one row from the Albums table and tried to convert the engine. Whats going on here?


MariaDB [digikamdb]> select * from Albums;
+------+-----------+--------------+------------+---------+------------+------+
| id   | albumRoot | relativePath | date       | caption | collection | icon |
+------+-----------+--------------+------------+---------+------------+------+
| 7847 |         7 | /2012        | 2012-01-22 | NULL    | NULL       | NULL |
+------+-----------+--------------+------------+---------+------------+------+
1 row in set (0.00 sec)

MariaDB [digikamdb]> ALTER TABLE Albums ENGINE = InnoDB;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Affected too. Kubuntu 16.04 with ppa

Migration to SQLite is also not possible. The idea was first migrate to sqlite and then back to mysql.

Hello,

You should maybe first write data from Digikam previous version to XMP files using maintenance tools and then import them in updated Digikam.

That's the way I do it to get datas from Digikam 3.5 + mysql to digikam 5.3 + mysql.

Eric

Created attachment 102330 [details]
dbconfig.xml

Please try this dbconfig.xml to update the DB from version 7 to 8. Replace the file under /usr/share/digikam/database/

Maik

the new dbconfig.xml brings me quite a bit further, but no success yet. Unfortunately, I've got a 10 years old sql db which probably degenerated a bit. I need to resolve all foreign key violations (from which I have a bunch) myself. Hopefully that doesn't happen anymore with the added constraints. Thanks for that, btw!

As for your new xml: The following sequence cannot work, can it?

<statement mode="plain">
    ALTER TABLE Albums
    DROP FOREIGN KEY Albums_Images;
</statement>
<statement mode="plain">
    ALTER TABLE Albums
    ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE;
</statement>

You drop Albums_Images which didn't exist on a DK 4.14, right? At least for me, I need DROP FOREIGN KEY IF EXISTS Albums_Images;

Created attachment 102340 [details]
dbconfig.xml

Thanks for testing, new try.

Maik

Created attachment 102342 [details]
my dbconfig.xml

In the meantime it has worked for me too. What have i done. First i migrated do sqlite. That seems to be not worked. No Pictures no data. Than i've created new database and migrated the datas back and after this everything was there again.

I've added my dbconfig.xml with which I was successful. In addition to the modified dbconfig.xml, the following changes to the db were needed

- Since I have many Umlaute, accents etc in my db, I first had to fix the encoding. You have the utf encoding in your dbconfig.xml, but for unknown reasons I had to do that explicitly first before running DK. I assume it comes with the ENGINE change.

  ALTER TABLE digikamdb.Albums 
  DEFAULT CHARSET=utf8;

  ALTER TABLE digikamdb.Images 
  DEFAULT CHARSET=utf8;

- During creation of the Albums_AlbumRoots constraint, you limit the column "relativePath" to 255 chars, but that didn't do the trick (neither did it for other users on digikam-user mailing list, as you remember). I don't know why (again I assume its because of the ENGINE change to InnoDB), though. I had to change the datatype from LONGTEXT to VARCHAR(255) in the ALTER command of the v7 to v8 upgrade statement

For me, the issue is resolved, but I don't think the solution to my issues is applicable for everybody. If needed, you can have my db to experiment (in an anonymized form), let me know.

Am I the only one with plenty of FK constraint violations? If needed by someone, I have a (risky) script which resolves them.

Created attachment 102345 [details]
dbconfig.xml

Thanks, can you try this file before I commit the patch?

Maik

Git commit c3784b1a8ae634dbd5488828a9e29c180d660a0f by Maik Qualmann.
Committed on 21/11/2016 at 17:42.
Pushed by mqualmann into branch 'master'.

fix MySQL database upgrade from v7 to v8
FIXED-IN: 5.4.0

M  +2    -1    NEWS
M  +30   -21   data/database/dbconfig.xml.cmake.in

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

update procedure looks now more robust, thanks!

It doesn't address yet the issue about having a too long key as reported initially and confirmed here: https://mail.kde.org/pipermail/digikam-users/2016-August/022581.html

Does it make sense to have datatype LONGTEXT for column Albums.relativePath when you crop it anyways with UNIQUE (albumRoot, relativePath(255))? If your path is indeed longer than 255 (which is anyways not supported by many fs), you could miss the unique constraint. datatype varchar(255) would prevent that (and solves the initially reported issue for me)