deploy risingwave on mysql 8.0.47 with error This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'

[hpphpp]

Describe the bug

I encountered an exception when trying to use docker-compose to deploy risingwave2.0.1 on mysql 8.0.x.

Error message/log

server errorLog
2024-11-01T07:13:18.950275075Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovering mview progress
2024-11-01T07:13:18.952177262Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovered mview progress
2024-11-01T07:13:18.963829005Z  WARN      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: scale actors failed error=Query Error: error returned from database: 1235 (42000): This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'
2024-11-01T07:13:19.070333503Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovering mview progress
2024-11-01T07:13:19.072653859Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovered mview progress
2024-11-01T07:13:19.080984742Z  WARN      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: scale actors failed error=Query Error: error returned from database: 1235 (42000): This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'
2024-11-01T07:13:22.212433177Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovering mview progress
2024-11-01T07:13:22.214251291Z  INFO      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: recovered mview progress
2024-11-01T07:13:22.222035769Z  WARN      rw-standalone-meta bootstrap_recovery{prev_epoch=0}:recovery_attempt: risingwave_meta::barrier::recovery: scale actors failed error=Query Error: error returned from database: 1235 (42000): This version of MySQL doesn't yet support 'ORDER BY / LIMIT / SELECT DISTINCT in recursive query block of Common Table Expression'

jdbc client error
 ERROR: Failed to execute the statement

Caused by these errors (recent errors listed first):
  1: gRPC request to meta service failed: Internal error
  2: The cluster is bootstrapping

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

docker-compose command，i can deploy some config on postgres，it seems only happens when deploy on mysql
command: "standalone --meta-opts="
--listen-addr 0.0.0.0:5690
--advertise-addr 0.0.0.0:5690
--dashboard-host 0.0.0.0:5691
--backend mysql
--sql-endpoint 192.168.40.226:3306
--sql-username root
--sql-password xxx
--sql-database xxx
--state-store hummock+minio://xxx:[email protected]:9000/xxx
--data-directory flowengine001
--config-path /risingwave.toml"
--compute-opts="
--config-path /risingwave.toml
--listen-addr 0.0.0.0:5688
--advertise-addr 0.0.0.0:5688
--async-stack-trace verbose
--role both
--meta-address http://0.0.0.0:5690\"
--frontend-opts="
--config-path /risingwave.toml
--listen-addr 0.0.0.0:4566
--advertise-addr 0.0.0.0:4566
--meta-addr http://0.0.0.0:5690\"
--compactor-opts="
--listen-addr 0.0.0.0:6660
--advertise-addr 0.0.0.0:6660
--meta-address http://0.0.0.0:5690\""

The version of RisingWave

risingwavelabs/risingwave:v2.0.1

Additional context

[shanicky]

Hello, could you please upgrade to version v2.0.2? This issue should have been resolved with patch #18825

[hpphpp]

Hello, could you please upgrade to version v2.0.2? This issue should have been resolved with patch #18825

thanks you~