[digiKam-users] DB migration error: "Error while creating the database schema."

Tue Feb 9 11:44:01 GMT 2021

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.cc,
> 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;