sql: Vitess cannot support Grafana 10+ CTEs

[derekperkins]

We've used Vitess for Grafana persistence for several years, but after upgrading to v10, certain functionality including dashboard list/search fails due to usage of CTEs. This is a known issue, I just wanted to call this out specifically since we have tracked compatibility with major projects in the past. Feel free to close if it isn't useful, but it'll at least help anyone searching.

"Search failed" error="Error 1235: unsupported: with expression in select statement"

Related issues:

• Support new 8.0 functions #4099
• General error: 1105 unknown error: syntax error at position 5 near 'with' #9459

[systay]

@derekperkins Any chance you could share the query that is breaking vtgate?

[derekperkins]

There's a test here for recursive queries
https://github.com/grafana/grafana/blob/main/pkg/services/sqlstore/sqlstore.go#L363-L392

And a comment about how it relates to Vitess
grafana/grafana#73725 (comment)

[derekperkins]

I submitted a PR based on that comment, hopefully they accept it and it works

• SQLStore: Improve recursive CTE support detection grafana/grafana#83357

[derekperkins]

Still trying to get this to work. There has been no movement on the original PR.

This has been longer coming, but go-sql-driver/mysql just released a new version that supports a / in the db name. I also have a PR into Grafana to use that new dependency, so that I can use shard targeting to allow the recursive queries to execute, which should be more reliable long-term.

• PathEscape dbname in DSN. go-sql-driver/mysql#1432
• Cut a new v1.7.2 release go-sql-driver/mysql#1534
• Chore: upgrade go-sql-driver/mysql grafana/grafana#84131

[harshit-gangal]

Now that Vitess have CTE support, do you want to try it?

[derekperkins]

Planning on rolling out v21 this weekend, then I'll try it

[derekperkins]

v21 actually made things worse. Previously, Grafana detected that recursive CTEs weren't supported and would fallback to other mechanisms. Now that Vitess says that they are supported, it uses the regular recursive SQL, but that fails. Here is the vttablet log. This runs on initial startup and for every search, so it makes grafana unusable.

tabletserver.go:1587] Unknown column 'n' in 'field list' (errno 1054) (sqlstate 42S22) (CallerID: grafanaAdmin): Sql: "with recursive cte as (select :vtg1 /* INT64 */ from dual union all select n + :vtg1 /* INT64 */ from cte where n < :n /* INT64 */) select n from cte", BindVars: {#maxLimit: "type:INT64 value:\"50001\""n: "type:INT64 value:\"2\""vtg1: "type:INT64 value:\"1\""}

[deepthi]

That's a bummer. Can you create a separate issue with the query so that we can try to fix it?

[systay]

@derekperkins wdyt - can we close this issue now?

[derekperkins]

Sure. I'll open it again once this is available in a release if there are any other issues