digiKam › digikam-users

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

Classic

List

Threaded

12 messages Options

Tomer Altman

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

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!

Tomer Altman

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

Maik Qualmann

Re: DB migration error: "Error while creating the database schema."

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;

Tomer Altman

Re: DB migration error: "Error while creating the database schema."

Hi Maik,

Thank you very much for your fast reply.

At the time that I installed Digikam a few weeks ago, 7.1.0 was the release version. The 7.2.0 version on the site that you linked is described as:

```
IMPORTANT: these bundle files, are built weekly and can be used to test new features quickly.
           Do not use these versions in production, unless you know what are you doing.
```

I guess after spending a few weeks merging in 50k+ media files, I'm a bit nervous about using a bleeding edge version. In your opinion, is the 7.2.0 release candidate stable enough for neophyte end-users like myself?

Thanks for pointing out the debugging settings. I'll try that tomorrow and post the results to the list.

Cheers,

~Tomer

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!

Gilles Caulier-4

Re: DB migration error: "Error while creating the database schema."

Hi Tomer,

The digiKam for macOS version 7.2.0 release candidate is near of final
release. The step between 7.1.0 and 7.2.0 for Mac support is huge and
so far better.

I use myself a MacBook pro 2015 at home plus a few VM with macOS
bigSur for the continuous Deployment...

Best

Gilles Caulier

If you have already have tagged your collection, just make a database
backup before to update.

Le lun. 8 févr. 2021 à 02:59, Tomer Altman <[hidden email]> a écrit :

>
> Hi Maik,
>
> Thank you very much for your fast reply.
>
> At the time that I installed Digikam a few weeks ago, 7.1.0 was the release version. The 7.2.0 version on the site that you linked is described as:
>
> ```
> IMPORTANT: these bundle files, are built weekly and can be used to test new features quickly.
> Do not use these versions in production, unless you know what are you doing.
> ```
>
> I guess after spending a few weeks merging in 50k+ media files, I'm a bit nervous about using a bleeding edge version. In your opinion, is the 7.2.0 release candidate stable enough for neophyte end-users like myself?
>
> Thanks for pointing out the debugging settings. I'll try that tomorrow and post the results to the list.
>
> Cheers,
>
> ~Tomer
>
>
> 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!

Tomer Altman

Re: DB migration error: "Error while creating the database schema."

In reply to this post by Maik Qualmann

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!

Maik Qualmann

Re: DB migration error: "Error while creating the database schema."

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;

Tomer Altman

Re: DB migration error: "Error while creating the database schema."

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

Tomer Altman

Re: DB migration error: "Error while creating the database schema."

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@localhost [(none)]> use digikam_core;
> Database changed
> root@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@localhost [digikam_core]> use digikam;
> Database changed
> root@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@localhost [information_schema]> use digikam;
> No connection. Trying to reconnect...
> Connection id:    8
> Current database: *** NONE ***
>
> Database changed
> root@localhost [digikam]> show tables;
> Empty set (0.01 sec)
>
> root@localhost [digikam]> use digikam_core;
> Database changed
> root@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@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@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;
>>
>>

Maik Qualmann

Re: DB migration error: "Error while creating the database schema."

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/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@localhost [(none)]> use digikam_core;
> > Database changed
> > root@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@localhost [digikam_core]> use digikam;
> > Database changed
> > root@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@localhost [information_schema]> use digikam;
> > No connection. Trying to reconnect...
> > Connection id: 8
> > Current database: *** NONE ***
> >
> > Database changed
> > root@localhost [digikam]> show tables;
> > Empty set (0.01 sec)
> >
> > root@localhost [digikam]> use digikam_core;
> > Database changed
> > root@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@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@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-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;

Maik Qualmann

Re: DB migration error: "Error while creating the database schema."

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@localhost [(none)]> use digikam_core;
> > > Database changed
> > > root@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@localhost [digikam_core]> use digikam;
> > > Database changed
> > > root@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@localhost [information_schema]> use digikam;
> > > No connection. Trying to reconnect...
> > > Connection id: 8
> > > Current database: *** NONE ***
> > >
> > > Database changed
> > > root@localhost [digikam]> show tables;
> > > Empty set (0.01 sec)
> > >
> > > root@localhost [digikam]> use digikam_core;
> > > Database changed
> > > root@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@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@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;

Tomer Altman

Re: DB migration error: "Error while creating the database schema."

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@localhost [(none)]> use digikam_core;
>>>> Database changed
>>>> root@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@localhost [digikam_core]> use digikam;
>>>> Database changed
>>>> root@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@localhost [information_schema]> use digikam;
>>>> No connection. Trying to reconnect...
>>>> Connection id: 8
>>>> Current database: *** NONE ***
>>>>
>>>> Database changed
>>>> root@localhost [digikam]> show tables;
>>>> Empty set (0.01 sec)
>>>>
>>>> root@localhost [digikam]> use digikam_core;
>>>> Database changed
>>>> root@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@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@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;
>
>
>