[digiKam-users] DB migration error: "Error while creating the database schema."
Tomer Altman
me at tomeraltman.net
Wed Feb 10 03:53:49 GMT 2021
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.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;
>>
>>
--
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!
More information about the Digikam-users
mailing list