Project

General

Profile

Actions

Defect #43278

closed

Error on db:migrate from version 6.0 to version 6.1

Added by Georg Hof 2 days ago. Updated about 5 hours ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Invalid
Affected version:

Description

When I try to migrate my data from docker "redmine:6.0" to "redmine:6.1" (official docker images), I get an error on db:migrate: Mysql2::Error: Duplicate key name 'index_projects_on_identifier' (Mysql2::Error)

Used docker versions:
  • nginx:latest
  • redmine:6.0 => redmine:6.1
  • mysql:8.1

I have done the following upgrades without issue:
3.3.1 -> 4.2
4.2 -> 5.1
5.1 -> 6.0


Files

Actions #1

Updated by Pavel Rosický 1 day ago

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

Actions #2

Updated by Go MAEDA 1 day ago

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.

Actions #3

Updated by Georg Hof about 22 hours ago

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.

Actions #4

Updated by Georg Hof about 21 hours ago

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.

Actions #5

Updated by Holger Just about 5 hours ago

  • Status changed from New to Closed
  • Resolution set to Invalid

Thank you for your feedback. I'm closing this issue as invalid as the issue was not caused by Redmine but by an external plugin.

Actions

Also available in: Atom PDF