[Bug][lake] can not edit scope config

[tim-goto]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

error when clicking on 'edit scope config'

lake logs

lake time="2025-01-29 14:19:47" level=info msg="\x1b[31;1m/app/impls/dalgorm/dalgorm.go:209 \x1b[35;1mError 1064 (42000): You have an error in your SQL syntax; check th
e manual that corresponds to your MySQL server version for the right syntax to use near 'varchar) = bps.scope_id) WHERE bps.plugin_name = ? AND bps.connection_id = ?' a
t line 1\n\x1b[0m\x1b[33m[0.531ms] \x1b[34;1m[rows:0]\x1b[0m SELECT bp.id AS blueprint_id, bp.project_name, bps.scope_id, _tool_github_repos.* FROM _devlake_blueprint_s
copes bps LEFT JOIN _devlake_blueprints bp ON (bp.id = bps.blueprint_id) LEFT JOIN _tool_github_repos ON (_tool_github_repos.connection_id = bps.connection_id AND cast(
_tool_github_repos.github_id as varchar) = bps.scope_id) WHERE bps.plugin_name = 'github' AND bps.connection_id = 5"
lake [GIN] 2025/01/29 - 14:19:47 | 500 |     6.36026ms |    10.232.70.46 | GET      "/plugins/github/scope-config/6/projects"
lake
lake
lake 2025/01/29 14:19:47 [Recovery] 2025/01/29 - 14:19:47 panic recovered:
lake GET /plugins/github/scope-config/6/projects HTTP/1.1
lake Host: REDACTED
lake Accept: application/json, text/plain, */*
lake Accept-Encoding: gzip, deflate, br, zstd
lake Accept-Language: en-US
lake Dnt: 1
lake Priority: u=0
lake Referer: REDACTED/connections/github/5
lake Sec-Fetch-Dest: empty
lake Sec-Fetch-Mode: cors
lake Sec-Fetch-Site: same-origin
lake User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:134.0) Gecko/20100101 Firefox/134.0
lake X-Forwarded-For: 10.232.70.46
lake X-Forwarded-Host: REDACTED
lake X-Forwarded-Port: 443
lake X-Forwarded-Proto: https
lake X-Forwarded-Scheme: https
lake X-Real-Ip: REDACTED
lake X-Request-Id: fa0480cfe6909e343b5751e1eb8b9cd6
lake X-Scheme: https

What do you expect to happen

no error, I can edit the scope config

How to reproduce

click on 'edit scope config'

Anything else

No response

Version

release-v1.0_250120_1118_6e14cfa

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[dosubot]

@tim-goto Hello there! I'm here to help you with bug fixes, answer questions, and guide you to become a contributor. Let's squash those bugs together! 🤖

I found a similar closed issue titled "[Bug][Config UI] Errors when update project connection's scopes](#6097)" which was resolved by a pull request that reset the scope API to differentiate plugins ^[1].

The error you're encountering seems to be related to an SQL syntax issue, specifically with casting a column to varchar. This might be similar to the issue that was previously resolved. You might want to check if the SQL syntax used in your query is compatible with the MySQL version used by DevLake.

If the issue persists, reviewing the changes made in the pull request that resolved the similar issue might provide insights into how to address the current problem.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[kostas-petrakis]

This looks related to the fix I submitted. Yet I did test this against a MySQL database.
Could you provide the version of the MySQL DB you are using?
I will check if the cast to char fixes it.

[tim-goto]

Thanks for getting back to me, I am using MySQL Server 8.4.4-1.el9 as part of the official devlake helmchart

[tim-goto]

Hey @kostas-petrakis did you get a chance to look into this? And thank you for your help :)

[kostas-petrakis]

Hello @tim-goto unfortunately I didn't find the time past weeks due to workload, I plan to check it this Friday, together with another issue I am experiencing.

[kostas-petrakis]

@tim-goto I've confirmed the regression in a dev container. A new PR is up that uses my initial suggestion of checking the database dialect to dynamically format the condition, addressing the compatibility issue for both PostgreSQL and MySQL.

@klesh I have opened a followup PR fixing this

[dougludlow]

Ran into the same error when attempting edit the scope config, using v1.0.2-beta6 of the helm chart. Log from the lake container:

time="2025-02-26 10:12:02" level=info msg="\x1b[31;1m/app/impls/dalgorm/dalgorm.go:209 \x1b[35;1mError 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar) = bps.scope_id) WHERE bps.plugin_name = ? AND bps.connection_id = ?' at line 1\n\x1b[0m\x1b[33m[0.660ms] \x1b[34;1m[rows:0]\x1b[0m SELECT bp.id AS blueprint_id, bp.project_name, bps.scope_id, _tool_github_repos.* FROM _devlake_blueprint_scopes bps LEFT JOIN _devlake_blueprints bp ON (bp.id = bps.blueprint_id) LEFT JOIN _tool_github_repos ON (_tool_github_repos.connection_id = bps.connection_id AND cast(_tool_github_repos.github_id as varchar) = bps.scope_id) WHERE bps.plugin_name = 'github' AND bps.connection_id = 1"
[GIN] 2025/02/26 - 10:12:02 | 500 |    8.215121ms |    10.16.216.98 | GET      "/plugins/github/scope-config/1/projects"


2025/02/26 10:12:02 [Recovery] 2025/02/26 - 10:12:02 panic recovered:
GET /plugins/github/scope-config/1/projects HTTP/1.1
Host: devlake-9a3cc686-lake.devlake.svc.cluster.local:8080
Accept: application/json, text/plain, */*
Accept-Encoding: gzip, deflate, br, zstd
Accept-Language: en-US,en;q=0.9
Referer: http://localhost:4000/projects/Swell/github-1
Sec-Ch-Ua: "Not(A:Brand";v="99", "Google Chrome";v="133", "Chromium";v="133"
Sec-Ch-Ua-Mobile: ?0
Sec-Ch-Ua-Platform: "macOS"
Sec-Fetch-Dest: empty
Sec-Fetch-Mode: cors
Sec-Fetch-Site: same-origin
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36


Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar) = bps.scope_id) WHERE bps.plugin_name = ? AND bps.connection_id = ?' at line 1 (500)
Wraps: (2) Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar) = bps.scope_id) WHERE bps.plugin_name = ? AND bps.connection_id = ?' at line 1
Error types: (1) *hintdetail.withDetail (2) *mysql.MySQLError
/app/core/errors/util.go:34 (0x7f1c3535a0ca)
/app/helpers/srvhelper/scope_config_service_helper.go:80 (0x7f1c353596e9)
/app/helpers/pluginhelper/api/ds_scope_config_api_helper.go:66 (0x7f1c35357fc5)
/app/plugins/github/api/scope_config_api.go:96 (0x7f1c35355b13)
/app/server/api/router.go:141 (0x1613e6e)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/context.go:174 (0x1611c82)
/app/server/api/middlewares.go:95 (0x1611c66)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/context.go:174 (0x1612127)
/app/server/api/middlewares.go:117 (0x1611fc2)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/context.go:174 (0x12532a1)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/recovery.go:102 (0x125328c)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/context.go:174 (0x12523c6)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/logger.go:240 (0x12523a9)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/context.go:174 (0x125124a)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/gin.go:620 (0x1250ed1)
/go/pkg/mod/github.com/gin-gonic/gin@v1.9.1/gin.go:576 (0x12509fc)
/usr/local/go/src/net/http/server.go:2936 (0xce25b5)
/usr/local/go/src/net/http/server.go:1995 (0xcdd751)
/usr/local/go/src/runtime/asm_amd64.s:1598 (0x9ccf40)