【JAVA】若依（ruoyi）框架配置多数据源后，报错 execute error. SELECT 1 FROM DUAL，DUAL doesn‘t exist. (UNKNOWN_TABLE)

ruoyi框架配置多数据源后，再进行编译时候，报错execute error. SELECT 1 FROM DUAL，DUAL doesn’t exist. (UNKNOWN_TABLE)，如下为解决方案

一、错误信息

多数据源配置后，提示如下错误：
12:42:54.292 [Druid-ConnectionPool-Create-1858454754] ERROR druid.sql.Statement - [statementLogError,148] - {conn-310003, stmt-320002} execute error. SELECT 1 FROM DUAL
ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: 192.168.0.203, port: 8123; Code: 60. DB::Exception: Table integration_shandong.DUAL doesn’t exist. (UNKNOWN_TABLE) (version 22.3.2.1)

二、解决方案

• 在nacos配置文件中进行如下修改:
  将
  validationQuery: SELECT 1 FROM DUAL
  修改为
  validationQuery: SELECT 1

• 原内容

dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
          # 主库数据源
          master:
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.0.208:3306/integration_shandong?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: zykj@mcs123#Z
          # 从库数据源
          # slave:
            # username: 
            # password: 
            # url: 
            # driver-class-name: 
          # clickhouse数据源
          clickhouse:
            driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
            url: jdbc:clickhouse://192.168.0.203:8123/integration_shandong
            username: default
            password: gjgwsjk
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

• 修改后

datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
          # 主库数据源
          master:
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.0.208:3306/integration_shandong?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: zykj@mcs123#Z
          # 从库数据源
          # slave:
            # username: 
            # password: 
            # url: 
            # driver-class-name: 
          # clickhouse数据源
          clickhouse:
            driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
            url: jdbc:clickhouse://192.168.0.203:8123/integration_shandong
            username: default
            password: gjgwsjk
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

修改完成后，编译通过。