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

[bugzilla_noreply]

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

            Bug ID: 372312
           Summary: database upgrade v7 to v8 failed
           Product: digikam
           Version: 5.2.0
          Platform: openSUSE RPMs
                OS: Linux
            Status: UNCONFIRMED
          Severity: critical
          Priority: NOR
         Component: Database-Mysql
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

I cannot update the database version v7 (DK 4.14) to v8 (DK 5.2). I'm using
Opensuse Leap 42.2 with mariadb 10.0.27


$ digikam
digikam.general: AlbumWatch use QFileSystemWatcher
QFileSystemWatcher::removePaths: list is empty
digikam.general: Database Parameters:
   Type:                     "QMYSQL"
   DB Core Name:             "digikamdb"
   DB Thumbs Name:           "digikamthumbs"
   DB Face Name:             "digikamfaces"
   Connect Options:          ""
   Host Name:                "localhost"
   Host port:                3306
   Internal Server:          false
   Internal Server Path:     ""
   Internal Server Serv Cmd: ""
   Internal Server Init Cmd: ""
   Username:                 "digikamuser"
   Password:                 "XXXXXXXXX"

digikam.dbengine: Loading SQL code from config file
"/usr/share/digikam/database/dbconfig.xml"
digikam.dbengine: Checking XML version ID => expected:  3  found:  3
digikam.coredb: Core database: running schema update
digikam.coredb: Core database: have a structure version  7
digikam.coredb: Core database: makeUpdates  7  to  8
digikam.dbengine: Failure executing query:
 ""
Error messages: "QMYSQL: Unable to execute query" "Specified key was too long;
max key length is 767 bytes" 1071 2
Bound values:  ()
digikam.dbengine: Error while executing DBAction [ "UpdateSchemaFromV7ToV8" ]
Statement [ "ALTER TABLE Albums\n                                        ADD
CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id)
ON DELETE CASCADE ON UPDATE CASCADE,\n                                      
ADD UNIQUE (albumRoot, relativePath(255)),\n                                  
    ENGINE InnoDB;" ]
digikam.coredb: Core database: schema update to V 8 failed!
digikam.coredb: Core database: cannot process schema initialization

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #1 from Kusi <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #2 from Kusi <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #3 from Kusi <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

boospy <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #4 from boospy <[hidden email]> ---
Affected too. Kubuntu 16.04 with ppa

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #5 from boospy <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #6 from [hidden email] ---
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 the assignee for the bug.

[bugzilla_noreply]

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

Maik Qualmann <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #7 from Maik Qualmann <[hidden email]> ---
Created attachment 102330
  --> https://bugs.kde.org/attachment.cgi?id=102330&action=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 the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #8 from Kusi <[hidden email]> ---
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;

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

Maik Qualmann <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
 Attachment #102330|0                           |1
        is obsolete|                            |

--- Comment #9 from Maik Qualmann <[hidden email]> ---
Created attachment 102340
  --> https://bugs.kde.org/attachment.cgi?id=102340&action=edit
dbconfig.xml

Thanks for testing, new try.

Maik

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #10 from Kusi <[hidden email]> ---
Created attachment 102342
  --> https://bugs.kde.org/attachment.cgi?id=102342&action=edit
my dbconfig.xml

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #11 from boospy <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #12 from Kusi <[hidden email]> ---
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 the assignee for the bug.

[bugzilla_noreply]

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

Maik Qualmann <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
 Attachment #102340|0                           |1
        is obsolete|                            |
 Attachment #102342|0                           |1
        is obsolete|                            |

--- Comment #13 from Maik Qualmann <[hidden email]> ---
Created attachment 102345
  --> https://bugs.kde.org/attachment.cgi?id=102345&action=edit
dbconfig.xml

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

Maik

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

Maik Qualmann <[hidden email]> 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 <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.

[bugzilla_noreply]

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

--- Comment #15 from Kusi <[hidden email]> ---
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 the assignee for the bug.