Gitea: Migration failure 1.7.6->1.8.0: No such index: IDX_user_"idx_user_IDX_user_is_active"

[tadly]

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

2019/04/23 11:48:27 [...itea/routers/init.go:82 GlobalInit()] [E] ORM engine initialization failed: migrate: do migrate: no such index: IDX_user_"idx_user_IDX_user_is_active"
2019/04/23 11:48:27 [I] Log Mode: File(Info)
2019/04/23 11:48:27 [I] XORM Log Mode: File(Info)
2019/04/23 11:48:27 [I] Cache Service Enabled
2019/04/23 11:48:27 [I] Session Service Enabled
2019/04/23 11:48:27 [I] Mail Service Enabled
2019/04/23 11:48:27 [I] Notify Mail Service Enabled
2019/04/23 11:48:27 [I] Beginning ORM engine initialization.
2019/04/23 11:48:27 [I] ORM engine initialization attempt #1/10...
2019/04/23 11:48:27 [I] Migration: add theme to users
2019/04/23 11:48:27 [I] Backing off for 3 seconds
2019/04/23 11:48:30 [I] ORM engine initialization attempt #2/10...
2019/04/23 11:48:30 [I] Migration: add theme to users
2019/04/23 11:48:30 [I] Backing off for 3 seconds
2019/04/23 11:48:33 [I] ORM engine initialization attempt #3/10...
2019/04/23 11:48:33 [I] Migration: add theme to users
2019/04/23 11:48:33 [I] Backing off for 3 seconds
...

Description

I made sure every version from 1.7.0 up to 1.7.6 was working.
As soon as I upgrade from 1.7.6 to 1.8.0 I get the above error.

Indexes do exist and there's also one for idx_user_IDX_user_is_active

$ sqlite3 gitea.db .indexes | grep user_is_active
idx_user_IDX_user_is_active

[adelowo]

Looks like this is https://github.com/go-gitea/gitea/pull/5681 but it was fixed not sure why this is happening ..

$ sqlite3 gitea.db
$ UPDATE users SET theme=""

[tadly]

Nope, not it especially as the error says: no such index: IDX_user_"idx_user_IDX_user_is_active"
I did try your suggestion anyways but again, didn't work.

The first IDX_user_ prefix seems to much. No idea where this one is coming from though.

[adelowo]

The log you pasted doesn’t contain anything related to indexes though

[tadly]

Uhm the very first line?

2019/04/23 11:48:27 [...itea/routers/init.go:82 GlobalInit()] [E] ORM engine initialization failed: migrate: do migrate: no such index: IDX_user_"idx_user_IDX_user_is_active"

specifically: no such index

Anyways, the fact it says "idx_user_IDX_user_is_active" is wrong already which I've manually fixed but it still prefixes it with an additional IDX_user_.

So currently I'm stuck at

ORM engine initialization failed: migrate: do migrate: no such index: IDX_user_"IDX_user_created_unix"

while it should be just IDX_user_created_unix

Will have to investigate more some time later as I'm rather busy right now :(

[zeripath]

OK let's try to figure out which migration is doing this. What's the version number of your database?

SELECT * from Version;

[tadly]

Sorry for the delay. First of, I'm using the official docker image and am curious how the upgrade strategy for gitea usually is. Do you have to make sure you hit every version or could you (in theory) go from 1.7.0 straight to 1.8.0

As for the version, 77 is what I get.
Seems like I'm missing quite a bit... 78 - 84 ._.
As to how to apply them I'm not quite sure 🙄

[Cellebyte]

Same here from 1.7.3 to 1.8 Docker image.

2019/04/24 07:50:00 [...itea/routers/init.go:82 GlobalInit()] [E] ORM engine initialization failed: migrate: do migrate: Drop column failed: mssql: ALTER TABLE DROP COLUMN is_bare failed because one or more objects access this column

I will try to upgrade to 1.7.latest and then to 1.8 but i think the migrate is not working from 1.7 to 1.8

Tried it is not working.

[adelowo]

Oh sorry, I was on mobile and didn't see that :)

[ChriFo]

I am running v1.7.6 from Docker image with PostgreSQL.
Currently I am at version 77.

And I think I should wait with upgrade to 1.8 :smiley:

[xor-gate]

Same for me with Version 78.

2019/04/24 10:14:10 [I] Backing off for 3 seconds
2019/04/24 10:14:13 [I] ORM engine initialization attempt #10/10...
2019/04/24 10:14:13 [I] Migration: rename repo is_bare to repo is_empty
2019/04/24 10:14:13 [...itea/routers/init.go:82 GlobalInit()] [E] ORM engine initialization failed: migrate: do migrate: Drop index failed: Error 1091: Can't DROP INDEX `IDX_repository_is_ba
re`; check that it exists

https://github.com/go-gitea/gitea/blob/b80d6490f31d2dde2e12aeb7a68236bae5f6809d/models/migrations/v78.go#L37

[xor-gate]

Fixed for me with:

MariaDB [gitea]> CREATE INDEX IDX_repository_is_bare ON repository(is_bare);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [gitea]> quit
Bye
root@src02:/home/git/gitea# tail -f log/gitea.log
2019/04/24 10:27:06 [I] Migration: update U2F counter type
2019/04/24 10:27:06 [I] Migration: hot fix for wrong release sha1 on release table
2019/04/24 10:27:06 [I] ORM engine initialization successful!
2019/04/24 10:27:07 [I] Git Version: 2.1.4
2019/04/24 10:27:07 [I] SQLite3 Supported
2019/04/24 10:27:07 [I] Run Mode: Production
2019/04/24 10:27:07 [...gitea/models/pull.go:1464 TestPullRequests()] [E] testPatch: git read-tree --index-output=/tmp/gitea-am-45990841 release/1.24.0: exec(5:testPatch (git read-tree): 23) failed: exit status 128(<nil>) stdout:  stderr: fatal: Not a valid object name release/1.24.0
 - fatal: Not a valid object name release/1.24.0

2019/04/24 10:27:07 [I] Listen: http://0.0.0.0:3000/git

[adelowo]

Will send a fix after lunch

[ChriFo]

I am running v1.7.6 from Docker image with PostgreSQL.
Currently I am at version 77.

And I think I should wait with upgrade to 1.8 😃

Update to 1.8 was successful. Current version is 83.

[Cellebyte]

Same here for MSSQL from 1.7.6 to 1.8 Docker image.

2019/04/24 07:50:00 [...itea/routers/init.go:82 GlobalInit()] [E] ORM engine initialization failed: migrate: do migrate: Drop column failed: mssql: ALTER TABLE DROP COLUMN is_bare failed because one or more objects access this column

Cannot upgrade.

[adelowo]

@lafriks Since my pr doesn't fix everything, can we reopen this? The original post says ORM engine initialization failed: migrate: do migrate: no such index: IDX_user_"idx_user_IDX_user_is_active"

[tadly]

I do suspect that this is just my db being borked and not a general issue (the IDX_user_"idx_user_IDX_user_is_active" thing)

Still would appreciate it if anyone could help me fix this as doing a fresh setup would be rather tedious and time-consuming :sweat_smile:

[zeripath]

Try setting your version to 78 to skip the v78 migration - but do the work of the migration by hand.

[tadly]

@zeripath Actually did that already but now gitea isn't doing anything on startup.
Nothing in the logs (even if I set the level to Debug) and the version stays at 78 :/

I ended up doing the most stupid thing I could think of (Spoiler, it's working now):

1. Manually drop all indexes (So everything from .indexes including the unique ones even though they would fail but I didn't care :D)
2. Select all indexes from a fresh setup (SELECT sql FROM sqlite_master WHERE type == 'index';)
3. Recreate all indexes using the previous commands
4. database is locked (_WhatTheHeckWhyNowAllOfASuddon.jpg_)
5. _indexes where recreated automatically_
6. DB-Version is 85 now

I have this bad feeling that this isn't the last of it and that my db is quite messed up but that's not your guys fault.

Thanks for the help. I think we can close the issue again unless there are any objections?

[zeripath]

@tadly so it looks like you had a particularly broken db, I'm not sure how. I don't think models/migrations/v78.go could be responsible for that. Especially if the next migration in line fails too.

Dropping all the indexes and recreating them should be fine though.

Therefore I'm going to close this issue - I'm also going to change the title as I don't want people to refer to this unless they actually have a similar index problem - in which case we can reopen and reinvestigate.