[Bug] table exists check error with two schemas in a database

[CzyerChen]

Search before asking

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

Apache SkyWalking Component

OAP server (apache/skywalking)

What happened

storage:
  selector: postgresql
  postgresql:
    properties:
      jdbcUrl: ${SW_JDBC_URL:"jdbc:postgresql://localhost:5432/skywalking?currentSchema=test1"} 

test1 in skywalking: check tables not exist and create tables
and switch schema :

storage:
  selector: postgresql
  postgresql:
    properties:
      jdbcUrl: ${SW_JDBC_URL:"jdbc:postgresql://localhost:5432/skywalking?currentSchema=test2"} 

test2 in skywalking: check tables exist in information_schema.tables without filter schemaPattern (default: public)

 public boolean tableExists(final String table) throws SQLException {
        try (final var conn = getConnection();
             final var result = conn.getMetaData().getTables(conn.getCatalog(), null, table, null)) {
            return result.next();
        }
    }

What you expected to happen

Checking and creating tables works with different schemas in one db.

How to reproduce

• step one

oap server config:

storage:
  selector: postgresql
  postgresql:
    properties:
      jdbcUrl: ${SW_JDBC_URL:"jdbc:postgresql://localhost:5432/skywalking?currentSchema=test1"} 

start up

• step two:

oap server config:

storage:
  selector: postgresql
  postgresql:
    properties:
      jdbcUrl: ${SW_JDBC_URL:"jdbc:postgresql://localhost:5432/skywalking?currentSchema=test2"} 

start up with error:

com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.1.0.jar:?]
	at org.apache.skywalking.oap.server.library.client.jdbc.hikaricp.JDBCClient.executeQuery(JDBCClient.java:118) [classes/:?]
	at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCUITemplateManagementDAO.getAllTemplates(JDBCUITemplateManagementDAO.java:101) [classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateManagementService.getAllTemplates(UITemplateManagementService.java:56) [classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.verifyNameConflict(UITemplateInitializer.java:124) [classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.initTemplate(UITemplateInitializer.java:114) [classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.initAll(UITemplateInitializer.java:94) [classes/:?]
	at org.apache.skywalking.oap.server.core.CoreModuleProvider.notifyAfterCompleted(CoreModuleProvider.java:446) [classes/:?]
	at org.apache.skywalking.oap.server.library.module.BootstrapFlow.notifyAfterCompleted(BootstrapFlow.java:52) [classes/:?]
	at org.apache.skywalking.oap.server.library.module.ModuleManager.init(ModuleManager.java:76) [classes/:?]
	at org.apache.skywalking.oap.server.starter.OAPServerBootstrap.start(OAPServerBootstrap.java:52) [classes/:?]
	at org.apache.skywalking.oap.server.starter.OAPServerStartUp.main(OAPServerStartUp.java:23) [classes/:?]
2024-02-19 18:17:21,469 org.apache.skywalking.oap.server.starter.OAPServerBootstrap 64 [main] ERROR [] - [10.0.0-SNAPSHOT-a471b85] ERROR: relation "ui_template" does not exist
  位置：15
org.postgresql.util.PSQLException: ERROR: relation "ui_template" does not exist
  位置：15
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181) ~[postgresql-42.4.1.jar:42.4.1]
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:133) ~[postgresql-42.4.1.jar:42.4.1]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.1.0.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.1.0.jar:?]
	at org.apache.skywalking.oap.server.library.client.jdbc.hikaricp.JDBCClient.executeQuery(JDBCClient.java:118) ~[classes/:?]
	at org.apache.skywalking.oap.server.storage.plugin.jdbc.common.dao.JDBCUITemplateManagementDAO.getAllTemplates(JDBCUITemplateManagementDAO.java:101) ~[classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateManagementService.getAllTemplates(UITemplateManagementService.java:56) ~[classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.verifyNameConflict(UITemplateInitializer.java:124) ~[classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.initTemplate(UITemplateInitializer.java:114) ~[classes/:?]
	at org.apache.skywalking.oap.server.core.management.ui.template.UITemplateInitializer.initAll(UITemplateInitializer.java:94) ~[classes/:?]
	at org.apache.skywalking.oap.server.core.CoreModuleProvider.notifyAfterCompleted(CoreModuleProvider.java:446) ~[classes/:?]
	at org.apache.skywalking.oap.server.library.module.BootstrapFlow.notifyAfterCompleted(BootstrapFlow.java:52) ~[classes/:?]
	at org.apache.skywalking.oap.server.library.module.ModuleManager.init(ModuleManager.java:76) ~[classes/:?]
	at org.apache.skywalking.oap.server.starter.OAPServerBootstrap.start(OAPServerBootstrap.java:52) [classes/:?]
	at org.apache.skywalking.oap.server.starter.OAPServerStartUp.main(OAPServerStartUp.java:23) [classes/:?]
2024-02-19 18:17:21,470 pool-1-thread-1 DEBUG Stopping LoggerContext[name=251a69d7, org.apache.logging.log4j.core.LoggerContext@1750fbeb]
2024-02-19 18:17:21,470 pool-1-thread-1 DEBUG Stopping LoggerContext[name=251a69d7, org.apache.logging.log4j.core.LoggerContext@1750fbeb]...
2024-02-19 18:17:21,473 pool-1-thread-1 DEBUG Shutting down OutputStreamManager SYSTEM_OUT.false.false
2024-02-19 18:17:21,473 pool-1-thread-1 DEBUG OutputStream closed
2024-02-19 18:17:21,473 pool-1-thread-1 DEBUG Shut down OutputStreamManager SYSTEM_OUT.false.false, all resources released: true
2024-02-19 18:17:21,474 pool-1-thread-1 DEBUG Appender Console stopped with status true
2024-02-19 18:17:21,474 pool-1-thread-1 DEBUG Stopped OapConfiguration[location=/Users/chenzy/files/gitFile/skywalking-server/skywalking/oap-server/server-starter/target/classes/log4j2.xml] OK
2024-02-19 18:17:21,475 pool-1-thread-1 DEBUG Stopped LoggerContext[name=251a69d7, org.apache.logging.log4j.core.LoggerContext@1750fbeb] with status true
Disconnected from the target VM, address: '127.0.0.1:56546', transport: 'socket'

Process finished with exit code 1

Anything else

No response

Are you willing to submit a pull request to fix on your own?

• Yes I am willing to submit a pull request on my own!

Code of Conduct

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

[wu-sheng]

FYI @kezhenxu94