routers/init.go:86:GlobalInit() [F] ORM engine initialization failed: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists

[SomeStrangeName]

• Gitea version (or commit ref):
• Git version:
• Operating system:
• Database (use [x]):
  • PostgreSQL
  • MySQL (MariaDB)
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • [ x] Not relevant
• Log gist:

Description

I tried to upgrade from gitea-1.4.3-linux-amd64 to gitea-1.9.2-linux-amd64 by replacing the binary. Now I got the message:

routers/init.go:86:GlobalInit() [F] ORM engine initialization failed: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists

I noticed this ticket: #3056 but in my case there is an empty string instead of index.

Adding the index to the database as described in the other ticket was also not working:
ALTER TABLE repo_unit ADD COLUMN index INT;

So currently I can't run gitea.

[guillep2k]

Please try enabling the most detailed possible log in app.ini and paste the latest 20~30 lines here.
Make sure you at least have:

[Database]
LOG_SQL = true
[Log]
LEVEL = Trace

But before pasting the text here, please remove any sensitive information that it may contain (e.g. user or password information).

[SomeStrangeName]

Meanwhile I switched the binary back to gitea-1.4.3-linux-amd64 at least this seems working again. Anyhow replacing it with gitea-1.9.2-linux-amd64 will fail.

If I see it correct start tries 10 times and fails always.

Beside the mentioned message I see some warnings:

# grep '\[W\]' gitea.log | cut -d' ' -f3- | sort | uniq -c 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column assignee_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column commit_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column commit_sha db default is 'NULL', struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column content db default is 'NULL', struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column created_unix db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column issue_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column label_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column line db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column milestone_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column new_title db default is 'NULL', struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column old_assignee_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column old_milestone_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column old_title db default is 'NULL', struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column poster_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column removed_assignee db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column type db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table comment Column updated_unix db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table issue_assignees Column assignee_id db default is NULL, struct default is 
     33 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table issue_assignees Column issue_id db default is NULL, struct default is 

One example:

 ...orm/dialect_mysql.go:466:GetIndexes() [I] [SQL] SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? [gitea
 gpg_key]
 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table issue_assignees Column assignee_id db default is NULL, struct default is 
 ...-xorm/xorm/engine.go:1307:Sync2() [W] Table issue_assignees Column issue_id db default is NULL, struct default is 
 ...orm/dialect_mysql.go:437:GetTables() [I] [SQL] SELECT `TABLE_NAME`, `ENGINE`, `TABLE_ROWS`, `AUTO_INCREMENT`, `TABLE_COMMENT` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [gitea]

Searching for fail shows this

# grep fail gitea.log | cut -d' ' -f3- | sort | uniq -c
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #1/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #2/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #3/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #4/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #5/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #6/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #7/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #8/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #9/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
      6 routers/init.go:86:GlobalInit() [F] ORM engine initialization failed: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists

Potentially an issue was introduced by an older try to upgrade to gitea-1.5.3-linux-amd64 which failed as well.

[guillep2k]

This looks like a missing migration step. Gitea has an array of migration procedures that updates the database according to the content of version.version. Could you please check that value? Perhaps it's misconfigured.

SELECT * from `version`;

[SomeStrangeName]

MariaDB [gitea]> SELECT * from `version`;
+----+---------+
| id | version |
+----+---------+
|  1 |      64 |
+----+---------+

[guillep2k]

Can you please check the following?

SHOW INDEX FROM `issue`;
SHOW INDEX FROM `issue_user`;

[SomeStrangeName]

MariaDB [gitea]> SHOW INDEX FROM `issue`;
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| issue |          0 | PRIMARY                 |            1 | id            | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| issue |          0 | UQE_issue_repo_index    |            1 | repo_id       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          0 | UQE_issue_repo_index    |            2 | index         | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_updated_unix  |            1 | updated_unix  | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_milestone_id  |            1 | milestone_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_is_closed     |            1 | is_closed     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_is_pull       |            1 | is_pull       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_deadline_unix |            1 | deadline_unix | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_created_unix  |            1 | created_unix  | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_closed_unix   |            1 | closed_unix   | A         |          24 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_repo_id       |            1 | repo_id       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_poster_id     |            1 | poster_id     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| issue |          1 | IDX_issue_assignee_id   |            1 | assignee_id   | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.001 sec)

MariaDB [gitea]> SHOW INDEX FROM `issue_user`;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| issue_user |          0 | PRIMARY            |            1 | id          | A         |          47 |     NULL | NULL   |      | BTREE      |         |               |
| issue_user |          1 | IDX_issue_user_uid |            1 | uid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)

[guillep2k]

Oh, I see. Try adding the following indexes:

CREATE INDEX `IDX_issue_assignee_id` ON `issue` (`assignee_id`);
CREATE INDEX `issue_user_is_assigned` ON `issue_user` (`is_assigned`);

Those are the indexes that the code is trying to drop. In theory, if the index is missing the function should not attempt to drop it, but then again, perhaps this works.

Otherwise, if the columns are missing from the tables (why should they be missing before migrating?), try creating them and the indexes:

ALTER TABLE `issue` ADD (`assignee_id` bigint(20));
ALTER TABLE `issue_user` ADD (`is_assigned` tinyint(1));
CREATE INDEX `IDX_issue_assignee_id` ON `issue` (`assignee_id`);
CREATE INDEX `issue_user_is_assigned` ON `issue_user` (`is_assigned`);

Try migrating after that. But!! Please perform a backup first and be prepared to use it, because it looks like your database is not entirely in one defined version. It will be a nightmare to fix if the ensuing migration steps fail too. I'm not that knowledgeable in Gitea's history or database to help you out if that's the case.

[SomeStrangeName]

Thanks for your support, but adding the keys seems already not working.

MariaDB [gitea]> CREATE INDEX `IDX_issue_assignee_id` ON `issue` (`assignee_id`);
ERROR 1061 (42000): Duplicate key name 'IDX_issue_assignee_id'
MariaDB [gitea]> CREATE INDEX `issue_user_is_assigned` ON `issue_user` (`is_assigned`);
Query OK, 0 rows affected (0.065 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [gitea]> ALTER TABLE `issue` ADD (`assignee_id` bigint(20));
ERROR 1060 (42S21): Duplicate column name 'assignee_id'
MariaDB [gitea]> ALTER TABLE `issue_user` ADD (`is_assigned` tinyint(1));
ERROR 1060 (42S21): Duplicate column name 'is_assigned'
MariaDB [gitea]> CREATE INDEX `IDX_issue_assignee_id` ON `issue` (`assignee_id`);
ERROR 1061 (42000): Duplicate key name 'IDX_issue_assignee_id'
MariaDB [gitea]> CREATE INDEX `issue_user_is_assigned` ON `issue_user` (`is_assigned`);
ERROR 1061 (42000): Duplicate key name 'issue_user_is_assigned'
MariaDB [gitea]> 

Looks like this does not help.

[guillep2k]

This one did!:

MariaDB [gitea]> CREATE INDEX `issue_user_is_assigned` ON `issue_user` (`is_assigned`);
Query OK, 0 rows affected (0.065 sec)
Records: 0  Duplicates: 0  Warnings: 0

[SomeStrangeName]

But still the same logs. I will try tomorrow again :) What looks strange is the empty string:

2019/09/03 20:00:29 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #1/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:32 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #2/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:36 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #3/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:39 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #4/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:43 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #5/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:46 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #6/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:49 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #7/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:53 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #8/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists
2019/09/03 20:00:56 routers/init.go:57:initDBEngine() [D] ORM engine initialization attempt #9/10 failed. Error: migrate: do migrate: Error 1091: Can't DROP INDEX ``; check that it exists

Maybe a new version could show more details in the logs?
Is there a way to export the data into a specific format and import it again? So that a new database could be filled?

[guillep2k]

I don't know, sorry. As I've said before, the latest version (1.9.2) allows for a better log configuration, so you could set it to log everything (=Trace), including each and every SQL. But I'm not quite sure migration is affected by that configuration. Your goal is to at least see the actual DROP INDEX statement the moment xorm attempts to do it (not the error message!).

This is an example from another log (not a DROP INDEX, I know):

2019/09/03 12:35:19 ...go-xorm/xorm/rows.go:50:newRows() [I] [SQL] SELECT "id", "repo_id", "interval", "enable_prune", "updated_unix", "next_update_unix" FROM "mirror" WHERE (next_update_unix<=$1) AND (next_update_unix!=0) []interface {}{1567524919}

As you can see, the whole statement is logged and thus you can deduce what's happening. You should also see the "SHOW INDEX" commands. But that's only if the migration steps do care about log settings, which I am not sure of.

[guillep2k]

My suggestion is to seek help in Gitea's discourse channel, as this doesn't seem to be an issue with Gitea itself but something with your particular server.

[jajm]

Had the same issue minutes ago, while trying to upgrade directly from 1.4.0-rc1 to 1.9.2.
I tried to upgrade one minor version at a time (1.4.0-rc1 -> 1.4.0 -> 1.5.2 -> 1.6.4 -> 1.7.6 -> 1.8.3 -> 1.9.2), running gitea web after each upgrade (to run db migrations), and surprisingly it worked.

[guillep2k]

This seems like a regression problem. Tracking and solving it seems very problematic because many libraries (internal and external) are involved. We should consider updating:

gitea/models/migrations/migrations.go

Line 30 in 4cb1bdd

const minDBVersion = 4

[SomeStrangeName]

Then I will try it step by step. Replacing the binary with version 1.5.2 and 1.5.3 was working. At least I could start gitea and connect to the service over the browser (with my messed up database :)).