[digiKam-users] internal SQL Server error after Update to 7.10.0

[Gilles Caulier]

Le mer. 15 mars 2023 à 17:33, Peter Orth <planetorth at gmail.com> a écrit :
>
> Hi Gilles,
>
> I’m trying to understand this problem before upgrading from DK 7.9 on an M1 Mac OS 13.1
>
> Here is my layperson’s understanding:  In finding a work-around for the problems that users experienced connecting to an external database on OS 13 Mac, some database handling changes were made in 7.10.
> Now, in order to continue to use Mysql Internal database on DK 7.10, I’ll need to follow the steps that you outlined below, migrating to SQLite in DK 7.9, then opening that database in 7.10 and migrating back to Mysql.
> Does that sound roughly correct?

Yes. By this way the internal mysql database structure will be
downgraded from version 10 to 5. This does not decrease the database
performances.

>
> Questions:
>
> 1. I have an internal database of 335K images. Will SQLite be able to handle a database of that size? I remember that SQLite is not recommended beyond a certain number of assets (100K)?

Yes. Here using SSD/nvme hardware, Sqlite works like a charm. My
collection is around 260 K.

Note : with next 8.0.0, WAL sqlite option will be available to
increase the database structure, safety, and performances. It's
explained in online documentation:

https://docs.digikam.org/en/setup_application/database_settings.html#database-type-criteria

>
> 2. Will this workaround be necessary if I skip DK 7.10 and go straight to 8.0 (future) release?  I’m not currently using a remote database.

Not at all. If the fix introduced in 7.10.0 does not concern your
workflow, stay in 7.9.0 until the 8.0 release.

>
> 3. Clarification on step 3 below: "Rename the ".mysql.digikam" folder with ".bak" in your collection.”  What exactly is the resulting name, where does the “.bak” go?

".mysql.digikam.bak"

> When performing the inverse operation in DK 7.10, this folder's name needs to revert to ".mysql.digikam”, or will a new folder be written?

No. A new one will be created instead. The older one is still here as
backup in case of.

>
> 4. I assume that switching databases Mysql>SQLite>Mysql will result in a very long (hours/days) rebuilds of the database. Is this the case?
>

Yes, it can be long, but it's a conversion of database contents, not a
re-scan of the collection.

> 5. If SQLite can handle the 330K images, is there a reason to go back to MariaDB/Mysql in 7.10 ?

I think no. Read well the online documentation about the setup database.

Best

Gilles Caulier