Database migration fails upgrading from v3.20.18 to v4.5.5 / v4.5.8 (duplicate foreign keys)

[goldtom]

Environment

• Source version: Chevereto Paid v3.20.18
• Target versions tested: Chevereto Pro v4.5.5 and v4.5.8
• OS: Ubuntu 22.04.5 LTS
• Web server: OpenLiteSpeed 1.8.3
• PHP: 8.2.35 (LSPHP)
• Database: MariaDB 10.6.23
• Database table prefix: chv_
• Installation type: Existing paid v3 installation, upgrading to v4 Pro

Problem​

I'm attempting to upgrade an existing v3.20.18 installation to Chevereto Pro v4.5.8.

Executing the CLI migration:

php app/bin/cli -C database-migrate

fails with exit code 255 and the following generic exception:

LogicException in app/legacy/install/installer.php

Error executing the Chevereto update query.
>>>
Try running each of the following statements
in the database console to find the conflict.

The migration partially modifies the database but leaves the installed-version marker at 3.20.18.

Reproduction​

To eliminate possible effects from a previous unsuccessful upgrade attempt, I created a separate test database and restored a January 2026 SQL backup of my working v3.20.18 installation.

The restored database contains:

• 10,751 image records
• 129 albums
• 4 users
• chevereto_version_installed = 3.20.18
• No existing chv_tags_* tables or chv_variables table

I used a separate copy of the official v4 package, configured to connect only to the test database.

The unmodified database-migrate command failed under both v4.5.5 and v4.5.8.

I then generated the migration SQL with:

php app/bin/cli -C database-migrate -d

and tested the generated statements against freshly restored copies of the original v3 database to identify the underlying SQL errors.

Finding 1: Duplicate foreign key constraints​

The generated migration SQL creates these tables:

chv_tags_albums

chv_tags_files

chv_tags_users

Their CREATE TABLE definitions already contain foreign key relationships.

Later in the same generated migration, seven additional statements attempt to add those same foreign keys.

For example:

ALTER TABLE `chv_tags_albums`
ADD CONSTRAINT `chv_tags_albums_ibfk_1`
FOREIGN KEY (tag_album_tag_id)
REFERENCES `chv_tags` (tag_id)
ON DELETE CASCADE;

MariaDB reports:

ERROR 1005 (HY000):
Can't create table chevereto_recovery.chv_tags_albums
(errno: 121 "Duplicate key on write or update")

I confirmed using information_schema.REFERENTIAL_CONSTRAINTS and SHOW CREATE TABLE that the foreign keys already existed after the table creation.

This behavior is present in the SQL generated by both v4.5.5 and v4.5.8 from the restored v3 database.

Finding 2: Redundant primary-key migration in v4.5.8​

As a diagnostic test only, I removed the seven duplicate foreign-key additions from a copy of the v4.5.8 migration SQL.

That allowed execution to proceed further, where it failed on:

ALTER TABLE `chv_tags_albums`
DROP INDEX tag_album_UNIQUE,
ADD PRIMARY KEY (
tag_album_tag_id,
tag_album_album_id,
tag_album_user_id
);

With:

ERROR 1091 (42000):
Can't DROP INDEX `tag_album_UNIQUE`;
check that it exists

The v4.5.8 schema already creates chv_tags_albums with that composite primary key, so the old named unique index does not exist.

There are corresponding migration statements for chv_tags_files and chv_tags_users.

Comparing the official v4.5.5, v4.5.7, and v4.5.8 packages:

• v4.5.5 defines named UNIQUE indexes on these three tables.
• v4.5.7 and v4.5.8 instead define composite primary keys.

This appears potentially related to the tag-table primary-key changes noted in the v4.5.6 release.

Expected behavior​

A direct upgrade from v3.20.18 should generate a migration sequence that applies the necessary schema changes without recreating existing constraints or attempting to drop nonexistent indexes.

Additional context​

The source installation (a small, personal project/website installed and mantained by an idiot; that is, me) is 5+ years old and may have had historical DB customizations (I don't think I would have pushed any novice SQL changes to production, but it remains a nagging possibility). However, the same errors occur using a restored backup that predates my recent upgrade attempts.

I have not verified that the original v3 database schema is identical to a pristine v3.20.18 installation, so I can't conclusively rule out historical schema differences.

Questions:

1. Is this a known issue with direct v3 → v4 migrations, particularly under MariaDB 10.6?
2. Are the duplicate foreign-key statements and tag-table primary-key alterations expected in the same generated migration?
3. Is there a recommended intermediate upgrade path, or a supported workaround?
4. Would it help if I provided redacted migration SQL, the relevant schema definitions, or additional CLI output?

I have retained the original databases, backups, and unmodified release packages for further testing. No changes have been made to the production database as a result of these isolated tests. I am just going to roll everything back to the working v3 installation in the meantime, while I await input and investigate further.

 

[Rodolfo]

Thank you for bringing this to my attention. Indeed, a bug in the update process is affecting versions prior to 4.2.0. It happens because the chv_tags_* related queries run twice.

At this time I'm checking v3.X database migration against generated v4.5 update SQL.

Is this a known issue with direct v3 → v4 migrations, particularly under MariaDB 10.6?

I got a conflicting installation on December 2025 but I didn't notice it was caused by repeated queries. For what I can tell it affects anything below 4.2.0 and the problem impacts MySQL and MariaDB in a similar manner.

Are the duplicate foreign-key statements and tag-table primary-key alterations expected in the same generated migration?

No, the system should detect that it doesn't need to run these extra queries. The issue is happening because the migration is not checking if the conflicting tables exist.

Is there a recommended intermediate upgrade path, or a supported workaround?

I've prepared a patch already, give it a try and let me know if it goes. It should work whatever you are on v3.20.X and carry the upgrade or if you attempted the upgrade and the database got into the broken state. https://gist.github.com/rodber/a57b8a6a7216b55ec96a06fca6d9595b

If running from an instance where the v3.20.x -> v4.5.8 failed, you may need to run this in the MySQL console:

UPDATE chv_settings SET setting_value = 0 WHERE setting_name = 'maintenance';

Would it help if I provided redacted migration SQL, the relevant schema definitions, or additional CLI output?

If the patch above didn't work I would like to take a peek at the migration SQL. Hope it works.