Database migration fails when attempting to upgrade from version 1.20.5

[snoopydo]

Description

When attempting to upgrade from version 1.20.5 (windows gogit x64), the migrations fail and service stops.
In the log there are 10x

2023/11/28 13:13:58 routers/common/db.go:36:InitDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: migrate: get: mssql: Invalid usage of the option NEXT in the FETCH statement.

enabling logsql and verbose logging i see

2023/11/28 13:29:55 [email protected]/engine.go:1246:Get() [I] [SQL] SELECT [id], [version] FROM [version] WHERE [id]=? ORDER BY [id] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY [1]

That syntax isn't supported by MSSQL until version 2012 at least. Documentation says 2008 R2 SP3 is supported.

Gitea Version

windows gogit x64 1.20.5

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.42.0.windows.2

Operating System

Windows Server 2012

How are you running Gitea?

Running as a service, using prebuilt binaries downloaded from releases tab from github

Database

MSSQL

[lng2020]

https://gitea.com/xorm/xorm/pulls/2188 xorm changed the implementation 2 months ago. We better update the docs.

[snoopydo]

Gitea 1.20.5 was only released a month ago.
Looking at the query, it appears to have been unnecessarily over complicated, a simple select top 1 sql query is all that is need. MS docs say to use TOP and only use OFFSET and FETCH in paging queries.

[wxiaoguang]

-> Require MySQL 8.0, PostgreSQL 12, MSSQL 2012 #27337

Well, not for 1.21/1.20 .... but MSSQL 2008 is quite too old .... https://endoflife.date/mssqlserver , so it's still better to upgrade the database .....

[GiteaBot]

We close issues that need feedback from the author if there were no new comments for a month. 🍵

[snoopydo]

Documentation needs fixing.

https://docs.gitea.com/installation/database-prep

[wxiaoguang]

-> Improve 1.22 document for Database Preparation #28643
-> Improve 1.21 document for Database Preparation (#28643) #28644