Azure AD/Oauth2 - Link External Account operation fails due to MS SQL database error.

[abjoseph]

Description

When attempting to Link an external account as shown in the screenshot below (1st screenshot), I'm getting a HTTP 500 error (2nd screenshot). Upon looking at the logs, I found the following:

May 23 23:04:22 gitea gitea[1975042]: 2023/05/23 23:04:22 ...rs/web/auth/oauth.go:929:SignInOAuthCallback() [E] [646d4676-2] UserLinkAccount: mssql: String or binary data would be truncated.

Edit/Update: I tested the exact same setup with a MySQL and SQLite database, and it worked as expected. The issue has been narrowed down to some configuration issue when Gitea is backed by a MS SQL database.

Gitea Version

1.19.3

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

1st Screenshot

2nd Screenshot

Git Version

2.40.0

Operating System

Ubuntu 20.04.6 LTS

How are you running Gitea?

Gitea is ran as a service per the docs:

[Service]
# Modify these two values and uncomment them if you have
# repos with lots of files and get an HTTP error 500 because
# of that
###
#LimitMEMLOCK=infinity
#LimitNOFILE=65535
RestartSec=5s
Type=simple
User=git
Group=git
WorkingDirectory=/var/lib/gitea/
# If using unix socket: Tells Systemd to create /run/gitea folder to home gitea.sock
# Manual cration would vanish after reboot.
#RuntimeDirectory=gitea
ExecStart=/usr/local/bin/gitea web -c /etc/gitea/app.ini -p 3000
Restart=always
Environment=USER=git HOME=/home/git GITEA_WORK_DIR=/var/lib/gitea
# If you want to bind Gitea to a port below 1024, uncomment
# the two values below, or use socket activation to pass Gitea its ports as above
###
#CapabilityBoundingSet=CAP_NET_BIND_SERVICE
#AmbientCapabilities=CAP_NET_BIND_SERVICE
###

[Install]
WantedBy=multi-user.target

Database

MSSQL

[abjoseph]

@GiteaBot Is anyone available to confirm my report and look into this?

[wxiaoguang]

GiteaBot doesn't have ability to do that ....

---

Could it be caused by some of these information from your external account is too long?

[abjoseph]

Hi @wxiaoguang, that is entirely possible, but I also tested the exact same setup with both SQLite and MySQL and both worked as-is without any issue. Given that the setup works with other supported databases, shouldn't we be adjusting MSSQL configuration to match Gitea's requirements that is already present in other DBMS? If not, then I don't think we can honestly list MSSQL as a fully supported database.

[wxiaoguang]

I just would like to confirm that it's caused by the "length limitation".

If there is a clear clue for the bug, then there could be a fix for it. Otherwise I am not using MSSQL, so I haven't spent time on reproducing it .....

[abjoseph]

@wxiaoguang, understood. Which table are these fields in? I'll take a look and experiment with what might be causing the error. Also, do you know if there is a way to have the logs show more information other than what I included in the initial post?

[wxiaoguang]

The table name is external_login_user.

To get more logs: https://docs.gitea.com/help/seek-help , section 2 : logs.

[abjoseph]

@wxiaoguang. Ok, so I found the discrepancy and the source of the issue. As your screenshot above shows, the access_token, access_token_secret and refresh_token are each configured with the text datatype which in mysql which gives a maximum length of 65,535 characters. However, in MSSQL those same fields are configured with varchar(255) as shown below and that size limitation causes an issue for the access_token, which appears to be a JWT token and is much larger than 255 characters.

P.S - Also, we might want to consider improving or at the very least, adding a line entry from the functions that initiate the DB calls. I say this because, despite enabling "LOG_SQL=true", I didn't get any additional output or info. This was confirmed when I was digging through the code for the location of the db call that writes to this table, I noticed that it simply returns the error. I know this is more or less idiomatic go coding style but it sometimes makes for harder debugging.

[wxiaoguang]

Can you confirm that if you alter these columns manually, then the OAuth2 could work?

And ...are you running an old instance (upgraded from an old instance) ? I can see that these fields have been changed to TEXT by #8554 (v1.11)

[abjoseph]

Hi @wxiaoguang, I can confirm that altering the columns manually to varchar(max) does resolve the OAuth2 account linking issue. Also, I can confirm that I am running the latest, 1.19.3 (2nd screenshot)

1st Screenhot

2nd Screenhot

[abjoseph]

@wxiaoguang, do you know if that Issue you linked was just for MySQL DB engine or for all. I ask because, TEXT datatype does not exist in MSSQL and the reference to TEXT datatype would imply this issue was focused on MYSQL.

[wxiaoguang]

The Gitea's database system is supported by XORM, so maybe there are some tricks. Could @lunny help to confirm the TEXT behavior?