[digiKam-users] DB migration error: "Error while creating the database schema."
Tomer Altman
me at tomeraltman.net
Fri Feb 12 07:50:08 GMT 2021
Hi Maik,
Thank you for your reply.
In debugging my problem, I realized that the FreeBSD jail that I was
using for hosting the MySQL server had some serious library
inconsistencies, which lead to those low-level mysqld crashes that I
reported. I set up a Linux VM running MariaDB, followed the same
procedures that I reported previously, and now the migration is
progressing. It hasn't finished yet, so I can't yet declare victory. But
it seems that the empty database wasn't the issue.
Thanks for your help,
~Tomer
On 2/10/21 10:31 AM, Maik Qualmann wrote:
> 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;
>
>
>
--
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