Error 500 on new issue (not for all repositories)

[doppioandante]

Gitea Version

1.15.2

Git Version

2.30.2

Operating System

Debian 11, kernel 5.10.0-8-amd64

How are you running Gitea?

Self hosted gitea, binary service through systemd

Database

MySQL

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Description

I recently upgraded from 1.14.6 to 1.15.2, one of my repositories is getting error 500 when opening a new issue. It works on other repositories.
I'm using MariaDB 15.1
The relevant log is

2021/09/16 16:13:19 ...vices/issue/issue.go:16:NewIssue() [I] [SQL] COMMIT [] - 1.287557ms
2021/09/16 16:13:19 modules/web/route.go:64:func1() [I] [SQL] BEGIN TRANSACTION [] - 305.286µs
2021/09/16 16:13:19 models/issue.go:1025:NewIssue() [I] [SQL] INSERT INTO `issue` (`repo_id`,`index`,`poster_id`,`original_author`,`original_author_id`,`name`,`content`,`milestone_id`,`priority`,`is_closed`,`is_pull`,`num_comments`,`ref`,`deadline_unix`,`created_unix`,`updated_unix`,`closed_unix`,`is_locked`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [2 24 6  0 Prova prova 0 0 false false 0  0 1631801599 1631801599 0 false] - 1.299408ms
2021/09/16 16:13:19 ...rs/web/repo/issue.go:975:NewIssuePost() [I] [SQL] ROLLBACK [] - 936.385µs
2021/09/16 16:13:19 ...rs/web/repo/issue.go:980:NewIssuePost() [E] NewIssue: newIssue: Error 1062: Duplicate entry '2-24' for key 'UQE_issue_repo_index'

Screenshots

No response

[artu72]

I have a simillar issue #17067

[doppioandante]

I have a simillar issue #17067

Indeed, even though the error message is different, maybe due to database differences?

[artu72]

I have a simillar issue #17067

Indeed, even though the error message is different, maybe due to database differences?

Perhaps, I am not experienced on this argument.

[wxiaoguang]

They are different problems.

For the MySQL/MariaDB error Duplicate entry '2-24' for key 'UQE_issue_repo_index', a quick fix can be:

-- backup your database first!!!
replace into issue_index (group_id, max_index) select repo_id, max(`index`) from issue group by repo_id;

I think this bug may be caused by the repository migrated by an old gitea, or some legacy data, which doesn't set issue_index correctly. related issue: #16683 , fixed by: #16687

[doppioandante]

They are different problems.

For the MySQL/MariaDB error Duplicate entry '2-24' for key 'UQE_issue_repo_index', a quick fix can be:

-- backup your database first!!!
replace into issue_index (group_id, max_index) select repo_id, max(`index`) from issue group by repo_id;

I think this bug may be caused by the repository migrated by an old gitea, or some legacy data, which doesn't set issue_index correctly. related issue: #16683 , fixed by: #16687

Thanks.. apparently, the migration code that you linked to is begin triggered just before the new issue with error 500 is created:

```2021/09/16 15:28:26 ...vices/issue/issue.go:16:NewIssue() [I] [SQL] BEGIN TRANSACTION [] - 488.227µs
2021/09/16 15:28:26 models/index.go:92:getNextResourceIndex() [I] [SQL] SELECT max_index FROM issue_index WHERE group_id = ? [2] - 1.29299ms
2021/09/16 15:28:26 models/index.go:66:GetNextResourceIndex() [I] [SQL] INSERT INTO issue_index (group_id, max_index) VALUES (?,1) ON DUPLICATE KEY UPDATE max_index = max_index+1 [2] - 1.011125ms
2021/09/16 15:28:26 models/index.go:102:getNextResourceIndex() [I] [SQL] SELECT max_index FROM issue_index WHERE group_id = ? AND max_index=? [2 1] - 449.567µs

So apparently that code doesn't fix everything already?
I'll try the workaround for now.

[zeripath]

How did you migrate to 1.15.2? Did you go via 1.15.0? (Others reading this - ALWAYS GO STRAIGHT TO THE LATEST MINOR RELEASE.)

It would be helpful to check the contents of the issue_index table.

[doppioandante]

How did you migrate to 1.15.2? Did you go via 1.15.0? (Others reading this - ALWAYS GO STRAIGHT TO THE LATEST MINOR RELEASE.)

It would be helpful to check the contents of the issue_index table.

I jumped straight from 1.14.6 to 1.15.2. Tomorrow I'll look into the tables and I'll let you know

[lunny]

Could you paste the logs before this line 2021/09/16 16:13:19 ...vices/issue/issue.go:16:NewIssue() [I] [SQL] COMMIT [] - 1.287557ms? It should print SQL to generate an index number for issue.

[doppioandante]

Sure,

2021/09/18 11:13:33 ...vices/issue/issue.go:16:NewIssue() [I] [SQL] BEGIN TRANSACTION [] - 397.277µs
2021/09/18 11:13:33 models/index.go:92:getNextResourceIndex() [I] [SQL] SELECT max_index FROM issue_index WHERE group_id = ? [2] - 873.02µs
2021/09/18 11:13:33 models/index.go:66:GetNextResourceIndex() [I] [SQL] INSERT INTO issue_index (group_id, max_index) VALUES (?,1) ON DUPLICATE KEY UPDATE max_index = max_index+1 [2] - 811.393µs
2021/09/18 11:13:33 models/index.go:102:getNextResourceIndex() [I] [SQL] SELECT max_index FROM issue_index WHERE group_id = ? AND max_index=? [2 29] - 412.131µs
2021/09/18 11:13:33 ...vices/issue/issue.go:16:NewIssue() [I] [SQL] COMMIT [] - 667.758µs
2021/09/18 11:13:33 modules/web/route.go:64:func1() [I] [SQL] BEGIN TRANSACTION [] - 120.531µs
2021/09/18 11:13:33 models/issue.go:1025:NewIssue() [I] [SQL] INSERT INTO `issue` (`repo_id`,`index`,`poster_id`,`original_author`,`original_author_id`,`name`,`content`,`milestone_id`,`priority`,`is_closed`,`is_pull`,`num_comments`,`ref`,`deadline_unix`,`created_unix`,`updated_unix`,`closed_unix`,`is_locked`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [2 29 6  0 test  0 0 false false 0  0 1631956413 1631956413 0 false] - 1.202113ms
2021/09/18 11:13:33 ...rs/web/repo/issue.go:975:NewIssuePost() [I] [SQL] ROLLBACK [] - 673.288µs
2021/09/18 11:13:33 ...rs/web/repo/issue.go:980:NewIssuePost() [E] NewIssue: newIssue: Error 1062: Duplicate entry '2-29' for key 'UQE_issue_repo_index'

after this query, the content of the issue_index table as requested from @zeripath is

MariaDB [giteadb]> select * from issue_index;
+----------+-----------+
| group_id | max_index |
+----------+-----------+
|       48 |         0 |
|       36 |         1 |
|       46 |         1 |
|       49 |         1 |
|       47 |         9 |
|        2 |        29 |
+----------+-----------+
6 rows in set (0.001 sec)

I haven't executed the workaround by @wxiaoguang yet, but the relevant part of their query returns the following result:

MariaDB [giteadb]> select repo_id, max(`index`) from issue group by repo_id;
+---------+--------------+
| repo_id | max(`index`) |
+---------+--------------+
|       2 |           36 |
|      29 |           40 |
|      36 |            1 |
|      46 |            1 |
|      47 |            9 |
|      49 |            1 |
+---------+--------------+
6 rows in set (0.001 sec)

[zeripath]

So it looks like your issue_index table is completely out of sync. I don't understand how this has happened.

The contents of issue_index should be the same as those of the query.

[lunny]

migrations v182 should update the table correctly. But it's not. It's wired.

[wxiaoguang]

It seems that this issue has been fixed. So we close this one.

[doppioandante]

Not fixed, but since then I have verified with gitea doctor that the database is unfortunately somewhat corrupted, probably after some wrong migration (the version doesn't match and some other things). Therefore this is probably a fallout of that :(

[wxiaoguang]

I have provided a SQL to fix.

[doppioandante]

For any future reader: the sql fix works.