Linking issue dependencies fails (API returns 500 error)

[fnetX]

Description

Okay, we're really lost in debugging this for hours now on Codeberg. A live meeting with 5 people cannot figure out what's exactly going on. Let's start from the beginning:

On Codeberg.org, you can no longer link issue dependencies. The API call returns an error 500 with the error message in the gitea.log:

2022/04/10 18:38:42 ...api/v1/repo/issue.go:276:SearchIssues() [E] CountIssues: unable to get one row result when CountIssues, row count=0

Now of course we thought of a recent regression and reverted to pre-1.16.5: still happening. We tried to reproduce on try.gitea.io and on codeberg-test: not happening.

We tried moving a whole database snapshot into our air gapped staging instance: Not happening, not reproducible, everything fine it seems (debugging there is a bit complicated, but we tried

"https://codeberg.org/api/v1/repos/issues/search?q=smtp&priority_repo_id=453&type=all&_=164961674595"

and got many results, whereas on Codeberg we still get 500.

Accepting that we can probably only approach the issue on our real production server, @Gusted worked out some patches for us to get more information.

Printing the generated SQL query for the function by XORM, and executing this ourselves in MySQL, worked fine and returned the correct information (about 9000 in our case).

We then checked the length of the return value of models.Issues here to see if it's an issue with countIssues or if it's an underlying issue of the options query. And the return value here was zero (0), so it indicates an issue with the query options that was being passed down.

This indicates that the query is correct, but when XORM is executing it, it will not return anything.
So this seems to be a scaling issue to us, maybe because this passes the id's of all the public repos + the further issues a user has access to, which is, 17000+ on our instance. (Maybe a point for further optimization, e.g. extend the query to allow all repos with is_private = false + explicitly private repos a user has access to?)

Also very relevant according to blame: #19244, and especially this comment, as we can't explain why this would ever return something else than 1 row from the `SELECT COUNT`` call.
Please note that our tested version includes that pull request, because @6543 pushed a cherry-pick to our branch. Of course we also tried reverting it, but - still broken.

That's where we currently are. The SQL log contains many question marks, about 17k, for each repository. That's about how many question marks we see when thinking about how to further approach this. We appreciate any help. Thank you!

Gitea Version

https://codeberg.org/Codeberg/gitea/commit/99e133b593fef813188e1e6469c65c9a11ec258a

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

git version 2.30.2

Operating System

Debian GNU/Linux 11

How are you running Gitea?

custom fork / deployment, see commit ref above

Database

MySQL

[wxiaoguang]

I think it's still a bug, maybe caused by GROUP BY, or some aggr SQL else.
SELECT COUNT FROM ... => always 1 rows
SELECT COUNT FROM ... GROUP BY ... => maybe 0 or many rows.
The key problem is that if it is the case, then the result of the old CountIssues (row[0]) is still incorrect and need to be fiexed ......

---

updated by comment below: the SQL only returns one row, indeed.

[wxiaoguang]

Printing the generated SQL query for [the function by XORM](https://github.com/go-gitea/gitea/blob/ee769f7459ffba310563cd735f503d3d7576da60/models/issue.go#L1520), and executing this ourselves in MySQL, worked fine and returned the correct information (about 9000 in our case).

Can you share the SQL for CountIssues (with the patch from #19244) ?

ps: I think there might be something incorrect for the comment and the COUNT SQL. The SQL error says row count=0, then it's impossible for the real SQL from log to get 9000 rows .... can you double check that the SQL you tried is a COUNT SQL?

[fnetX]

Please see http://paste.debian.net/hidden/73165403/ for the SQL query we ran (manually interpolating the values).
Please ignore the line wraps, I just added them for easier readability. The use gitea; was also added by me.

Running it results in

count
9966

so I'm sorry if I was unclear before: Of course it returned one row with the value of about 9k (or 10k actually), and not that many rows.

[wxiaoguang]

The SQL looks correct. And the result is correct too.

Since everything seems correct, I can not understand how the row count=0 failure occurs, either ........

I suspect there are something missing or inconsistent. And according to the comment "We tried moving a whole database snapshot into our air gapped staging instance: Not happening, not reproducible, everything fine it seems", there must be something different.

Another question is that is the code (CountIssues related) exactly the same between Codeberg Gitea and Official Gitea?

[fnetX]

Another question is that is the code (CountIssues related) exactly the same between Codeberg Gitea and Official Gitea?

I can't see any difference. Codeberg commits are always prefixed with CB/. The blame shows that nothing is touching these functions:
https://codeberg.org/Codeberg/gitea/blame/branch/codeberg-1.16/models/issue.go

Since we can't reproduce in our staging instance with the Codeberg binary yet, we don't really have a way to hunt this down with the upstream code. We can't just deploy the plain Gitea on production I fear ...

[wxiaoguang]

I have an idea about how to debug the problem.

Maybe we can build Codeberg-Gitea with a special flag (eg: ./gitea-codeberg my-test-issue), and use the special flag to run SearchIssues / CountIssues directly with the specialized options to trigger the bug and see output. Then we just put this test binary to production server, then test with the production database without any downtime.

If the special flag can trigger the bug, then it gives us the chance to find the root case.

If the special flag can not trigger the bug either ..... it must be impossible, no reason to explain it .....

[fnetX]

I like this idea, in fact I had a similar idea about trying to produce a minimal reproducible thing (e.g. a fresh binary with only the XORM call).

I think we can do this, provided that the binary does not modify any data or execute stuff (we already considered spawning a second Gitea instance, but we'd have to make sure it doesn't e.g. trigger Matrix hooks, run queues etc).

[wxiaoguang]

https://stackoverflow.com/questions/32973835/mysql-sometimes-erroneously-returns-0-for-count

And someone said that it could also be a mysql bug 😂 (although I don't want to suspect that it's mysql's problem, and that post is quite old, the described bug is not the same)

[wxiaoguang]

I like this idea, in fact I had a similar idea about trying to produce a minimal reproducible thing (e.g. a fresh binary with only the XORM call).

I think we can do this, provided that the binary does not modify any data or execute stuff (we already considered spawning a second Gitea instance, but we'd have to make sure it doesn't e.g. trigger Matrix hooks, run queues etc).

IIRC there are some Gitea sub commands like admin or doctor, it only do some database work.

[luwol03]

You may can also add a db user with read-only permissions to be sure nothing is changed?

[fnetX]

Notable in the stackoverflow thread is that count() can return 0 when nothing matches. I wasn't aware of that, but we should keep that in mind for further debugging.

a db user with read-only permissions

Yeah, but I also feared that it would read some queue and see it would need to fire some webhooks etc. Using a subcommand that only runs the affected query appears to be safe, though (not starting the normal Gitea app itself).

[lunny]

The SQL is too long. What's the 500 error log?

[fnetX]

The error log line that appears on the 500 error is the one from the initial comment:

2022/04/10 18:38:42 ...api/v1/repo/issue.go:276:SearchIssues() [E] CountIssues: unable to get one row result when CountIssues, row count=0