[digiKam-users] DB migration error: "Error while creating the database schema."
Maik Qualmann
metzpinguin at gmail.com
Wed Feb 10 07:03:33 GMT 2021
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/33d0457e20adda97c003f3dee652a1749406ff9f
> /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-setup.
> >>>>> 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