[digiKam-users] DB migration error: "Error while creating the database schema."
Maik Qualmann
metzpinguin at gmail.com
Wed Feb 10 18:31:49 GMT 2021
With this commit it is now also possible to start the migration into an empty
database.
https://invent.kde.org/graphics/digikam/-/commit/
6aeb907ffdc246a4ad9a0f13e0f9b5c55b13ee8f
Maik
Am Mittwoch, 10. Februar 2021, 08:03:33 CET schrieb Maik Qualmann:
> I think I now know what your problem is. The migration does not work on an
> empty digiKam database because the existing tables, triggers, indexes etc.
> are deleted. The right way is to set up MySQL in digiKam setup. If you are
> asked about a collection, enter an empty folder. Done, tables are created
> and you have an empty digiKam albums view. Now you open the migration and
> run it, restart digiKam and you're done.
>
> Maik
>
> Am Mittwoch, 10. Februar 2021, 04:53:49 CET schrieb Tomer Altman:
> > I've been looking at the Digikam source code dealing with MySQL as found
> > here:
> >
> > https://github.com/KDE/digikam/blob/33d0457e20adda97c003f3dee652a1749406ff
> > 9f /core/data/database/dbconfig.xml.cmake.in
> >
> > I see that the SQL code chunk which is implicated in the MySQL server
> > crash is embedded within the `create_index_if_not_exists` stored
> > procedure.
> >
> > Using the MySQL command-line client, loading in the stored procedure
> > doesn't trigger any errors, but calling the stored procedure to load the
> > first index does:
> >
> > CALL create_index_if_not_exists('Images','dir_index','album');
> >
> > Now that it's reproducible without using Digikam, I've filed a bug
> > report with MySQL:
> >
> > https://bugs.mysql.com/bug.php?id=102550&thanks=4
> >
> > I'm surprised that no one else has hit this bug, given that I'm using
> > the most recent version of MySQL (8.0.23). Perhaps it's platform-specific?
> >
> > Are most people here using MariaDB, or an older version of MySQL (like
> > v5.7) ?
> >
> > I'll try using one of those next, and see if that avoids this particular
> > bug.
> >
> > Thanks,
> >
> > ~Tomer
> >
> > On 2/9/21 11:00 AM, Tomer Altman wrote:
> > > Hi Maik,
> > >
> > > Thanks for your reply. This is what the `digikam_core` and `digikam`
> > > databases look like right now:
> > >
> > > ```
> > > root at localhost [(none)]> use digikam_core;
> > > Database changed
> > > root at localhost [digikam_core]> show tables;
> > > +------------------------+
> > >
> > > | Tables_in_digikam_core |
> > >
> > > +------------------------+
> > >
> > > | albumroots |
> > > | albums |
> > > | downloadhistory |
> > > | imagecomments |
> > > | imagecopyright |
> > > | imagehistory |
> > > | imageinformation |
> > > | imagemetadata |
> > > | imagepositions |
> > > | imageproperties |
> > > | imagerelations |
> > > | images |
> > > | imagetagproperties |
> > > | imagetags |
> > > | searches |
> > > | settings |
> > > | tagproperties |
> > > | tags |
> > > | tagstree |
> > > | videometadata |
> > >
> > > +------------------------+
> > > 20 rows in set (0.05 sec)
> > >
> > > root at localhost [digikam_core]> use digikam;
> > > Database changed
> > > root at localhost [digikam]> show tables;
> > > Empty set (0.00 sec)
> > > ```
> > >
> > > I'm dropping & recreating the databases after each error as follows:
> > >
> > > ```
> > > drop database digikam_core;
> > > drop database digikam;
> > >
> > > CREATE DATABASE digikam;
> > > CREATE DATABASE digikam_core;
> > > GRANT ALL PRIVILEGES ON digikam.* TO 'digikam'@'%';
> > > GRANT ALL PRIVILEGES ON digikam_core.* TO 'digikam'@'%';
> > > FLUSH PRIVILEGES;
> > >
> > > ```
> > > I then restart digikam, and attempt the migration again. The "Check
> > > Connection" button results in a pop-up saying it was successful. I then
> > > click on "Migrate", and an again get the 'Error while creating the
> > > database schema' pop-up.
> > >
> > > Now, here's what my databases look like immediately after:
> > >
> > > ```
> > > root at localhost [information_schema]> use digikam;
> > > No connection. Trying to reconnect...
> > > Connection id: 8
> > > Current database: *** NONE ***
> > >
> > > Database changed
> > > root at localhost [digikam]> show tables;
> > > Empty set (0.01 sec)
> > >
> > > root at localhost [digikam]> use digikam_core;
> > > Database changed
> > > root at localhost [digikam_core]> show tables;
> > > +------------------------+
> > >
> > > | Tables_in_digikam_core |
> > >
> > > +------------------------+
> > >
> > > | albumroots |
> > > | albums |
> > > | downloadhistory |
> > > | imagecomments |
> > > | imagecopyright |
> > > | imagehistory |
> > > | imageinformation |
> > > | imagemetadata |
> > > | imagepositions |
> > > | imageproperties |
> > > | imagerelations |
> > > | images |
> > > | imagetagproperties |
> > > | imagetags |
> > > | searches |
> > > | settings |
> > > | tagproperties |
> > > | tags |
> > > | tagstree |
> > > | videometadata |
> > >
> > > +------------------------+
> > > ```
> > >
> > > So, indeed, Digikam is creating an 'albums' directory. I don't know if
> > > this what you're referring to, but at least in my MySQL server
> > > configuration, it is case-insensitive (I found some statements in the
> > > Digikam source code that also seems to indicate that the default MySQL
> > > server configuration is also case insensitive):
> > >
> > > ```
> > > root at localhost [digikam_core]> describe albums;
> > > +--------------+----------+------+-----+---------+----------------+
> > >
> > > | Field | Type | Null | Key | Default | Extra |
> > >
> > > +--------------+----------+------+-----+---------+----------------+
> > >
> > > | id | int | NO | PRI | NULL | auto_increment |
> > > | albumRoot | int | NO | MUL | NULL | |
> > > | relativePath | longtext | NO | | NULL | |
> > > | date | date | YES | | NULL | |
> > > | caption | longtext | YES | | NULL | |
> > > | collection | longtext | YES | | NULL | |
> > > | icon | bigint | YES | MUL | NULL | |
> > >
> > > +--------------+----------+------+-----+---------+----------------+
> > > 7 rows in set (0.00 sec)
> > >
> > > root at localhost [digikam_core]> describe Albums;
> > > +--------------+----------+------+-----+---------+----------------+
> > >
> > > | Field | Type | Null | Key | Default | Extra |
> > >
> > > +--------------+----------+------+-----+---------+----------------+
> > >
> > > | id | int | NO | PRI | NULL | auto_increment |
> > > | albumRoot | int | NO | MUL | NULL | |
> > > | relativePath | longtext | NO | | NULL | |
> > > | date | date | YES | | NULL | |
> > > | caption | longtext | YES | | NULL | |
> > > | collection | longtext | YES | | NULL | |
> > > | icon | bigint | YES | MUL | NULL | |
> > >
> > > +--------------+----------+------+-----+---------+----------------+
> > > ```
> > >
> > > I know this is long, but hopefully it was informative. Please let me
> > > know if I can send along any further debugging information.
> > >
> > > Thanks,
> > >
> > > ~Tomer
> > >
> > > On 2/9/21 3:44 AM, Maik Qualmann wrote:
> > >> The core database does not contain an "Albums" table. So something went
> > >> wrong with the first creation. You have to start with a new database
> > >> and
> > >> if so, post the error message that occurs when the database was
> > >> created.
> > >> DigiKam does not change any file or table limits in an external MySQL
> > >> database.
> > >>
> > >> Maik
> > >>
> > >> Am Dienstag, 9. Februar 2021, 09:36:38 CET schrieb Tomer Altman:
> > >>> I upgraded to digiKam 7.2.0 as advised. I am still encountering the
> > >>> same
> > >>> error.
> > >>>
> > >>> Enabling the logging, I have included below the terminal output.
> > >>>
> > >>> Looking at the MySQL server error log, I see a recurring crash that
> > >>> seems to be happening when a migration is attempted, and a
> > >>> possibly-related SQL query that might be triggering the crash. I'm
> > >>> using
> > >>> MySQL v8.0 on FreeBSD:
> > >>>
> > >>> /usr/local/libexec/mysqld Ver 8.0.23 for FreeBSD11.4 on amd64 (Source
> > >>> distribution)
> > >>>
> > >>> Any advice about how to figure out what is going wrong would be
> > >>> greatly
> > >>> appreciated!
> > >>>
> > >>> Thanks,
> > >>>
> > >>> ~Tomer
> > >>>
> > >>> ---
> > >>>
> > >>> Terminal output:
> > >>>
> > >>> digikam.database: Testing DB connection ( "ConnectionTest" ) with
> > >>> these
> > >>> settings:
> > >>>
> > >>> digikam.database: Database Parameters:
> > >>> Type: "QMYSQL"
> > >>> DB Core Name: "digikam_core"
> > >>> DB Thumbs Name: "digikam"
> > >>> DB Face Name: "digikam"
> > >>> DB Similarity Name: "digikam"
> > >>> Connect Options: ""
> > >>> Host Name: "10.37.220.13"
> > >>> Host port: 3306
> > >>> Internal Server: false
> > >>> Internal Server Path: ""
> > >>> Internal Server Serv Cmd: ""
> > >>> Internal Server Init Cmd: ""
> > >>> Username: "digikam"
> > >>> Password: "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
> > >>>
> > >>> digikam.database: items to tag ()
> > >>> digikam.database: Complete scan took: 65378 msecs.
> > >>> digikam.general: Event is dispatched to OSX desktop notifier
> > >>>
> > >>> digikam.dbengine: Failure executing query:
> > >>> ""
> > >>>
> > >>> Error messages: "QMYSQL: Unable to execute query" "Table
> > >>> 'digikam_core.albums' doesn't exist" "1146" 2
> > >>> Bound values: ()
> > >>> digikam.dbengine: Error while executing DBAction [
> > >>> "Migrate_Cleanup_Prepare" ] Statement [ "ALTER TABLE Albums DROP
> > >>> FOREIGN
> > >>> KEY Albums_Images;" ]
> > >>> digikam.coredb: Core database: running schema update
> > >>> digikam.coredb: Core database: no database file available
> > >>>
> > >>> digikam.dbengine: Failure executing query:
> > >>> ""
> > >>>
> > >>> Error messages: "QMYSQL: Unable to execute query" "Lost connection to
> > >>> MySQL server during query" "2013" 2
> > >>> Bound values: ()
> > >>> digikam.dbengine: Error while executing DBAction [ "CreateIndices" ]
> > >>> Statement [ "CALL
> > >>> create_index_if_not_exists('Images','dir_index','album');" ]
> > >>>
> > >>> digikam.dbengine: Failure executing transaction. Error messages:
> > >>> "QMYSQL: Unable to commit transaction" "MySQL server has gone away"
> > >>>
> > >>> "2006" 2
> > >>> digikam.dbengine: Failed to commit transaction. Starting rollback.
> > >>>
> > >>> ---
> > >>>
> > >>> MySQL error log:
> > >>>
> > >>> Assertion failed: (m_sp == current_thd->sp_runtime_ctx->sp), function
> > >>> this_item, file
> > >>> /wrkdirs/usr/ports/databases/mysql80-server/work/mysql-8.0.23/sql/item
> > >>> .c
> > >>> c,
> > >>> line 1730.
> > >>> 20:12:53 UTC - mysqld got signal 6 ;
> > >>> Most likely, you have hit a bug, but this error can also be caused by
> > >>> malfunctioning hardware.
> > >>> Thread pointer: 0x86e356000
> > >>> Attempting backtrace. You can use the following information to find
> > >>> out
> > >>> where mysqld died. If you see no messages after this, something went
> > >>> terribly wrong...
> > >>> [0x109c9e6] unsigned char+0x1c6
> > >>>
> > >>> <snip>
> > >>>
> > >>> 0x8076a8cf0 <pthread_sigmask+0x530> at /lib/libthr.so.3
> > >>>
> > >>> Trying to get some variables.
> > >>> Some pointers may be invalid and cause the dump to abort.
> > >>> Query (86e3a5828): SET @Index_cnt = (
> > >>>
> > >>> SELECT COUNT(1) cnt
> > >>> FROM INFORMATION_SCHEMA.STATISTICS
> > >>> WHERE CONVERT(DATABASE() USING latin1) =
> > >>>
> > >>> CONVERT(TABLE_SCHEMA USING latin1)
> > >>>
> > >>> AND CONVERT(table_name USING latin1) =
> > >>>
> > >>> CONVERT(table_name_vc USING latin1)
> > >>>
> > >>> AND CONVERT(index_name USING latin1) =
> > >>>
> > >>> CONVERT(index_name_vc USING latin1)
> > >>>
> > >>> )
> > >>>
> > >>> Connection ID (thread ID): 11
> > >>> Status: NOT_KILLED
> > >>>
> > >>> The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html
> > >>> contains
> > >>> information that should help you find out what is causing the crash.
> > >>> 2021-02-07T20:12:58.935675Z 0 [Warning] [MY-010140] [Server] Could not
> > >>> increase number of max_open_files to more than 32768 (request: 32929)
> > >>> 2021-02-07T20:12:58.935695Z 0 [Warning] [MY-010142] [Server] Changed
> > >>> limits: table_open_cache: 16303 (requested 16384)
> > >>>
> > >>> On 2/7/21 10:53 PM, Maik Qualmann wrote:
> > >>>> First of all, digiKam-7.1.0 is too old for MacOS. Use the current
> > >>>> release
> > >>>> candidate digiKam-7.2.0-RC from here:
> > >>>>
> > >>>> https://files.kde.org/digikam/
> > >>>>
> > >>>> Then we need the debug output from the terminal as described here,
> > >>>> don't
> > >>>> forget to set the debug environment variable:
> > >>>>
> > >>>> https://www.digikam.org/contribute/
> > >>>>
> > >>>> Maik
> > >>>>
> > >>>> Am Montag, 8. Februar 2021, 06:10:20 CET schrieb Tomer Altman:
> > >>>>> To the digikam community:
> > >>>>>
> > >>>>> I hope that this finds you well. I am a new digikam user and have
> > >>>>> been
> > >>>>> very happy using it to merge several large sets of photos & videos
> > >>>>> into
> > >>>>> a single collection. Until now I have used the SQLite DB back-end,
> > >>>>> but
> > >>>>> my intention has been to move on to using the external MySQL server
> > >>>>> DB
> > >>>>> back-end to allow me to edit the collection from multiple machines.
> > >>>>>
> > >>>>> I am using Digikam v.7.1.0
> > >>>>> (commits.kde.org/digikam/d52927ebc98ee24c9fc3a3a34f527d4d21b09fb3)
> > >>>>> on
> > >>>>> Mac OS X v10.13.6. I have been using the following documentation to
> > >>>>> guide me:
> > >>>>>
> > >>>>> https://docs.kde.org/trunk5/en/extragear-graphics/digikam/using-setu
> > >>>>> p.
> > >>>>> htm
> > >>>>> l
> > >>>>>
> > >>>>> I installed MySQL v8.0, and executed the following SQL commands to
> > >>>>> set
> > >>>>> up the database (see below). Note that I had to go beyond the
> > >>>>> documentation and add the "WITH mysql_native_password" clause in
> > >>>>> order
> > >>>>> for MySQL to use a password plugin that is supported by Digikam (at
> > >>>>> least the Mac version).
> > >>>>>
> > >>>>> When using the DB migration tool, I am able to connect to the MySQL
> > >>>>> server, but when I try to initiate the migration, I get a pop up
> > >>>>> window
> > >>>>> saying "Error while creating the database schema".
> > >>>>>
> > >>>>> I'm not sure what the error is. I do see on the server side that the
> > >>>>> digikam_core database does get populated with tables. The digikam
> > >>>>> database does not.
> > >>>>>
> > >>>>> I have some DBA experience, so I'm sure that I could work past any
> > >>>>> mis-configuration issues on the MySQL side, if only I could see a
> > >>>>> more
> > >>>>> detailed error message. Any suggestions on how to get past this
> > >>>>> uninformative error message would be greatly appreciated. Thanks!
> > >>>>>
> > >>>>> Cheers,
> > >>>>>
> > >>>>> ~Tomer
> > >>>>>
> > >>>>> ---
> > >>>>>
> > >>>>> CREATE USER 'digikam'@'%' IDENTIFIED WITH mysql_native_password BY
> > >>>>> <password>;
> > >>>>>
> > >>>>> GRANT ALL ON *.* to 'digikam'@'%';
> > >>>>>
> > >>>>> CREATE DATABASE digikam;
> > >>>>>
> > >>>>> GRANT ALL PRIVILEGES ON digikam.* TO 'digikam'@'%';
> > >>>>>
> > >>>>> CREATE DATABASE digikam_core;
> > >>>>>
> > >>>>> GRANT ALL PRIVILEGES ON digikam_core.* TO 'digikam'@'%';
> > >>>>>
> > >>>>> FLUSH PRIVILEGES;
More information about the Digikam-users
mailing list