[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #15 from Kusi  ---
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)

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=372312

Maik Qualmann  changed:

   What|Removed |Added

 Status|UNCONFIRMED |RESOLVED
  Latest Commit||http://commits.kde.org/digi
   ||kam/c3784b1a8ae634dbd548882
   ||8a9e29c180d660a0f
 Resolution|--- |FIXED
   Version Fixed In||5.4.0

--- Comment #14 from Maik Qualmann  ---
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-1NEWS
M  +30   -21   data/database/dbconfig.xml.cmake.in

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

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=372312

Maik Qualmann  changed:

   What|Removed |Added

 Attachment #102340|0   |1
is obsolete||
 Attachment #102342|0   |1
is obsolete||

--- Comment #13 from Maik Qualmann  ---
Created attachment 102345
  --> https://bugs.kde.org/attachment.cgi?id=102345=edit
dbconfig.xml

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

Maik

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #12 from Kusi  ---
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.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[boospy]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #11 from boospy  ---
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.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #10 from Kusi  ---
Created attachment 102342
  --> https://bugs.kde.org/attachment.cgi?id=102342=edit
my dbconfig.xml

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=372312

Maik Qualmann  changed:

   What|Removed |Added

 Attachment #102330|0   |1
is obsolete||

--- Comment #9 from Maik Qualmann  ---
Created attachment 102340
  --> https://bugs.kde.org/attachment.cgi?id=102340=edit
dbconfig.xml

Thanks for testing, new try.

Maik

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #8 from Kusi  ---
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?


ALTER TABLE Albums
DROP FOREIGN KEY Albums_Images;


ALTER TABLE Albums
ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON
DELETE SET NULL ON UPDATE CASCADE;


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;

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Maik Qualmann]

https://bugs.kde.org/show_bug.cgi?id=372312

Maik Qualmann  changed:

   What|Removed |Added

 CC||metzping...@gmail.com

--- Comment #7 from Maik Qualmann  ---
Created attachment 102330
  --> https://bugs.kde.org/attachment.cgi?id=102330=edit
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

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=372312

e.longuem...@laposte.net changed:

   What|Removed |Added

 CC||e.longuem...@laposte.net

--- Comment #6 from e.longuem...@laposte.net ---
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

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[boospy]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #5 from boospy  ---
Migration to SQLite is also not possible. The idea was first migrate to sqlite
and then back to mysql.

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[boospy]

https://bugs.kde.org/show_bug.cgi?id=372312

boospy  changed:

   What|Removed |Added

 CC||li...@osit.cc

--- Comment #4 from boospy  ---
Affected too. Kubuntu 16.04 with ppa

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

Kusi  changed:

   What|Removed |Added

 CC||k...@forum.titlis.org

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #3 from Kusi  ---
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

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #2 from Kusi  ---
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).

-- 
You are receiving this mail because:
You are watching all bug changes.

[digikam] [Bug 372312] database upgrade v7 to v8 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=372312

--- Comment #1 from Kusi  ---
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?

-- 
You are receiving this mail because:
You are watching all bug changes.