[digiKam-users] DB migration error: "Error while creating the database schema."
Tomer Altman
me at tomeraltman.net
Tue Feb 9 19:00:14 GMT 2021
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