bug(analytics): channel dimension query uses unsupported CAST AS TEXT on MySQL

[lihaojiee]

Summary

The channel dimension query used by analyticsDimensionStats generates CAST(... AS TEXT). MySQL does not support TEXT as a CAST target type, so the Analytics query fails with error 1064.

The same code is currently present on the unstable branch.

Environment

• AxonHub: v1.0.0-beta6
• Package: windows_amd64
• Database: MySQL
• Deployment: standalone binary with automatic migrations enabled

Steps to reproduce

1. Run AxonHub v1.0.0-beta6 with a MySQL database.
2. Open the Analytics page or execute the analyticsDimensionStats GraphQL query.
3. Request dimension statistics that include channels.

Actual result

{
  "errors": [
    {
      "message": "failed to get analytics stats by channel: 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 'TEXT) AS \`id\`, \`t1\`.\`name\` AS \`name\`, COUNT(\`usage_logs\`.\`id\`) AS \`request_count\`' at line 1",
      "path": ["analyticsDimensionStats"]
    }
  ],
  "data": null
}

Root cause

internal/server/gql/analytics_helpers.go builds the channel ID projection with:

sql.As(fmt.Sprintf("CAST(%s AS TEXT)", s.C(usagelog.FieldChannelID)), "id")

MySQL accepts targets such as CHAR, but not TEXT, in this form. The SQL fails during parsing before table data or schema can be evaluated.

Source:
https://github.com/looplj/axonhub/blob/v1.0.0-beta6/internal/server/gql/analytics_helpers.go#L180

Expected result

analyticsDimensionStats should return channel statistics successfully on every supported database dialect.

Suggested fix

For a cross-dialect fix, avoid the SQL cast: scan channel_id into an integer field and convert it to the GraphQL string ID in Go. A MySQL-only change to CAST(... AS CHAR) would fix MySQL but would retain dialect-specific behavior.

A regression test should cover the generated/executed channel dimension query for MySQL.

[lihaojiee]

Hi @ChancenJ, this line was introduced with the Analytics feature in #2005:

sql.As(fmt.Sprintf("CAST(%s AS TEXT)", s.C(usagelog.FieldChannelID)), "id")

On MySQL, TEXT is not a valid CAST target, so the channel dimension query fails with error 1064. The same line is still present on unstable.

Could you please take a look? A dialect-neutral fix would be to scan channel_id as an integer and convert it to the GraphQL string ID in Go, avoiding a database-specific cast.