Redmine

Your database contains a duplicate index https://github.com/redmine/redmine/commit/331b955f47412e3b449d6ca6c5b86ffc7b708f28

1/ You manually created the index (possibly as a performance optimization?).
2/ A third-party plugin added it
3/ The database was partially restored from an already updated instance, causing a mismatch between the schema and the actual data in the database

Try removing the index and run it again, but be aware there may be additional issues. In any case, this is not a Redmine bug

Another possible cause is that the projects table may contain multiple projects with the same identifier.
If duplicates exist, you can fix the migration error by updating the identifier values with an SQL UPDATE statement.

In Redmine versions up to 6.0, duplicate identifiers were blocked at the Project model level, but there was no database constraint. This meant duplicates could still occur under certain conditions. To prevent this, Redmine 6.1.0 introduced a unique index to ensure project identifiers are always unique (see #41975).

As a result, if duplicate identifiers are already present, an error will occur during the database migration to Redmine 6.1.0. To resolve it, update the identifiers directly in the database with SQL so that each one is unique. Note that if you change a project identifier, the project's URL will also change, and any previous URLs will become invalid.

Thanks for your comments!

1/ You manually created the index (possibly as a performance optimization?).

I haven't altered the database manually.

2/ A third-party plugin added it

I do have some plugins installed, but I don’t know if any of them modified the database.

3/ The database was partially restored from an already updated instance, causing a mismatch between the schema and the actual data in the database

I’ve tried to avoid that. Each time, I started with a fresh database backup before performing the upgrade, but I may have missed something.

I don't know the internals of redmine, so please bear with me if I missed something.

To investigate, I connected to a working database with redmine version 6.0 and checked the projects table (with select identifier from projects; ).
There are 65 projects and no duplicate entries. Normally the following command should show duplicates:

mysql> select identifier, count(*) c  from projects group by identifier having c > 1;
Empty set (0.00 sec)

Am I looking in the right place?
Afterwards, I also rechecked with version 6.1 and I still get the same error.

I found the issue, and Pavel was right, but I didn’t understand it at first.
There is no problem with the project identifier itself, but rather with the key name. This is exactly what the error message indicates:

Mysql2::Error: Duplicate key name 'index_projects_on_identifier'

I checked my database dump, and in the dump from Redmine 3.3.1 (although the database was originally created some time before that, probably with a 2.x version), I already had this key name.
So I had to delete the key manually with:

ALTER TABLE projects DROP INDEX index_projects_on_identifier;

Afterwards, the migration worked fine for me.

I also investigated further to see where this index was coming from, and I found this line in the plugins repository:

redmine_git_hosting/db/migrate/20120904060609_update_multi_repo_per_project.rb:42:    add_index :projects, [:identifier]

So I suppose that this plugin created the index (it was the only reference I found in my plugin repository).

Thank you very much for your help. I can't close the issue, but it can be closed.