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

Tue Feb 9 08:36:38 GMT 2021

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.html
>>
>> 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;
>
>
>

-- 
Please do not send me sensitive information (e.g., passwords, personal details, financial/health information) via email, as it is not secure.
Please contact me to determine the best way to transfer sensitive information safely. Thank you!