Repo page returns 500 error when repo has multiple branch names that only differ in case

[cboylan]

Description

We have at least one repo that we can no longer view in gitea after the upgrade to 1.21 from 1.20. The repo in question has branches stable/kilo and stable/Kilo. Loading the top level repo page for this repo returns a 500 error. Gitea records this in the logs:

2024/02/28 17:23:30 ...ules/context/repo.go:682:RepoAssignment() [E] SyncRepoBranches: Error 1062 (23000): Duplicate entry '1057-Kilo' for key 'UQE_branch_s'
#011/go/src/code.gitea.io/gitea/modules/context/repo.go:682 (0x1bb6834)
#011/usr/local/go/src/reflect/value.go:596 (0x4f0aa6)
#011/usr/local/go/src/reflect/value.go:380 (0x4efb78)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:166 (0x1a97ddb)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:176 (0x1a97e77)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/chain.go:31 (0x1a8edc5)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:444 (0x1a91d13)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:176 (0x1a97e77)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:176 (0x1a97e77)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:176 (0x1a97e77)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/middleware/get_head.go:37 (0x24c02db)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:176 (0x1a97e77)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/context/context.go:222 (0x1bab94e)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/gitea.com/go-chi/session@v0.0.0-20230613035928-39541325faa3/session.go:257 (0x1b049b5)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:73 (0x1a8f9b5)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:316 (0x1a9129a)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:444 (0x1a91d13)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:73 (0x1a8f9b5)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:316 (0x1a9129a)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:444 (0x1a91d13)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/context/access_log.go:73 (0x1ba4421)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/routing/logger_manager.go:122 (0x1a8e5f8)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/chi-middleware/proxy@v1.1.1/middleware.go:37 (0x240b2f3)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/routers/common/middleware.go:45 (0x240c512)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/routers/common/middleware.go:37 (0x240c095)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/routers/common/middleware.go:99 (0x240b655)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/src/code.gitea.io/gitea/modules/web/handler.go:145 (0x1a98083)
#011/usr/local/go/src/net/http/server.go:2136 (0x971ca8)
#011/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:90 (0x1a8f974)
#011/go/src/code.gitea.io/gitea/modules/web/route.go:163 (0x1a994a7)
#011/usr/local/go/src/net/http/server.go:2938 (0x97498d)
#011/usr/local/go/src/net/http/server.go:2009 (0x970873)
#011/usr/local/go/src/runtime/asm_amd64.s:1650 (0x474200)
#011
2024/02/28 17:23:30 ...eb/routing/logger.go:102:func1() [I] router: completed GET /x/fuel-plugin-onos for 127.0.0.1:59198, 500 Internal Server Error in 71.0ms @ context/repo.go:425(context.RepoAssignment)

This Error 1062 is coming from MariaDB because it is trying to add non unique entries to the database. This appears to arise from case insensitive branch names in the database but case sensitive names in git (kilo vs Kilo). Gitea even comments that this is an issue here: https://github.com/go-gitea/gitea/blob/v1.21.7/models/git/branch.go#L106.

The underlying problem likely started with 6e19484 which is why this was working until semi recently. However, since 87db4a4 this may also be a problem for new pushes to gitea being rejected in addition to creating problems for existing repos that may already be in this state.

It would be nice if this could be corrected since this repo was functional in this state prior to these updates (this is a regression). Additionally gitea shouldn't reject valid git pushes or error when loading content from valid git repos. It should be possible to make this table column case sensitive and match the git behavior.

Note: I am not trying to reproduce this behavior on the gitea demo site as I suspect it will prevent the new branches from being created in the first place.

Gitea Version

1.21.7

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.39.2-1.1 from Debian Bookworm

Operating System

Debian Bookworm

How are you running Gitea?

We build our own container images based on Debian Bookworm and build gitea from scratch using the v1.21.7 tag. We then run this gitea container using docker-compose.

Database

MySQL/MariaDB

[delvh]

Please switch your DB collation to a case-sensitive one.
I think Gitea 1.22+ will automatically do it or inform you that it is necessary, but the case insensitivity is the cause of your problem.

[cboylan]

Please switch your DB collation to a case-sensitive one. I think Gitea 1.22+ will automatically do it or inform you that it is necessary, but that's the cause of your problem.

Shouldn't the database schema in gitea enforce this for fields that require case sensitive collation? I guess you are saying that this may be coming in 1.22? I know that we can change it ourselves. The problem would still exist for anyone else though.

[delvh]

Ah, the PR I had in mind was #28662.
So yes, it's only a hey, your instance might break warning.

Shouldn't the database schema in gitea enforce this for fields that require case sensitive collation

The problem is, Gitea is mainly written for case-sensitive DBs, but MySQL is also somehow supported.
The easiest way to support it is by requiring a case-sensitive DB.

[delvh]

Regarding how to fix it:
The 1.22+ doctor should be automatically able to convert the schema, so run gitea doctor convert to fix the problem.

[cboylan]

Ah, the PR I had in mind was #28662. So yes, it's only a hey, your instance might break warning.

Ya reading this PR it seems to only take action on gitea startup if the database tables haven't been created yet. Makes this statement When Gitea starts, it will try to find a better collation (`utf8mb4_0900_as_cs` or `uca1400_as_cs`) and alter the database if it is possible. ambiguous or misleading.

Shouldn't the database schema in gitea enforce this for fields that require case sensitive collation

The problem is, Gitea is mainly written for case-sensitive DBs, but MySQL is also somehow supported. The easiest way to support it is by requiring a case-sensitive DB.

It is possible to make mysql and mariadb case sensitive.

Looking at our database and tables it appears that the database default character set is latin1 and default collation is latin1_swedish_ci; however, SHOW FULL COLUMNS indicates that varchar(255) and text column types are utf8 or utf8mb4 character sets and collations in tables like comment and branch. This implies to me that gitea is enforcing utf8 at least somewhere (though maybe only 3 byte utf8 in some cases). I think it should be possible to change all (since you indicate gitea expects a fully case sensitive db) of these columns to case sensitive collations automatically? Changing the character set is a potentially error prone/lossy conversion and probably shouldn't be done automatically, but I think collations don't have this problem? I am not positive of this. Side note automatic conversion from utf8mb3 to utf8mb4 should not be lossy or error prone as utf8mb4 is a strict superset of utf8mb3. But there may still be problems doing that conversion automatically due to the time it may take if the db contents are large.

One thing that makes me hesitate doing this by hand is that #28662 seems to use some heuristic for determining the best collation. Would be best to apply that same process automatically if possible. Additionally, the override of latin1 database defaults implies gitea is doing something to choose utf8 here. Will those overrides properly select case sensitive collations for new tables or columns going forward? If not, then manually modifying the database is going to be a constant battle.

#28662 makes no mention of mysql/mariadb being unsupported. In fact the db preparation document says that mysql is one of the most used databases. It would be good to consider communicating database limitations like this more directly.

Finally if we do end up taking manual intervention is this the sort of modification you would expect for utf8(mb3|mb4) columns:

ALTER TABLE branch MODIFY name VARCHAR(255) COLLATE uca1400_as_cs;

[cboylan]

Reading #28662 more closely I think what we will likely end up doing is running the gitea doctor convert command during/after our upgrade. This should set the default on the database and convert existing table columns to the appropriate 4 byte utf8 char set as well as a case sensitive collation.

My main concern at this point is if new tables (like the new branch table) or columns will end up getting correctly created because gitea does seem to override database defaults based on what I can see in our current db content.