search

apache / iceberg

Apache Iceberg
https://iceberg.apache.org/
Apache License 2.0
5.86k stars 2.06k forks source link

UncheckedSQLException: Failed to execute exists query: SELECT table_namespace FROM iceberg_tables WHERE catalog_name = ? AND (table_namespace = ? OR table_namespace LIKE ? ESCAPE '\') LIMIT 1 #10056

Open Zelin1203 opened 3 months ago

Zelin1203 commented 3 months ago

Apache Iceberg version

1.5.0 (latest release)

Query engine

Flink

Please describe the bug 🐞

I use jdbc catalog. When I update flink from 1.17.1 to 1.18.1 and iceberg from 1.4.2 to 1.5.0, the exception is throwed.

Caused by: org.apache.iceberg.jdbc.UncheckedSQLException: Failed to execute exists query: SELECT table_namespace FROM iceberg_tables WHERE catalog_name = ? AND (table_namespace = ? OR tablenamespace LIKE ? ESCAPE '\') LIMIT 1 at org.apache.iceberg.jdbc.JdbcUtil.exists(JdbcUtil.java:793) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcUtil.namespaceExists(JdbcUtil.java:761) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcCatalog.namespaceExists(JdbcCatalog.java:591) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcCatalog.loadNamespaceMetadata(JdbcCatalog.java:475) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.flink.FlinkCatalog.getDatabase(FlinkCatalog.java:192) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.flink.FlinkCatalog.databaseExists(FlinkCatalog.java:204) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.flink.table.catalog.CatalogManager.lambda$permanentDatabaseExists$14(CatalogManager.java:891) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at java.util.Optional.map(Unknown Source) ~[?:?] at org.apache.flink.table.catalog.CatalogManager.permanentDatabaseExists(CatalogManager.java:891) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.table.catalog.CatalogManager.schemaExists(CatalogManager.java:879) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.table.planner.catalog.CatalogCalciteSchema.getSubSchema(CatalogCalciteSchema.java:61) ~[?:?] at org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitSubSchema(SimpleCalciteSchema.java:111) ~[?:?] at org.apache.calcite.jdbc.CalciteSchema.getSubSchema(CalciteSchema.java:271) ~[?:?] at org.apache.calcite.sql.validate.EmptyScope.resolve(EmptyScope.java:135) ~[?:?] at org.apache.calcite.sql.validate.EmptyScope.resolveTable(EmptyScope.java:101) ~[?:?] at org.apache.calcite.sql.validate.DelegatingScope.resolveTable(DelegatingScope.java:214) ~[?:?] at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:116) ~[?:?] at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:188) ~[?:?] at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1050) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1025) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3294) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3276) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3603) ~[?:?] at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64) ~[?:?] at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1050) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1025) ~[?:?] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1000) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:749) ~[?:?] at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:196) ~[?:?] at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:117) ~[?:?] at org.apache.flink.table.planner.operations.SqlNodeToOperationConversion.convert(SqlNodeToOperationConversion.java:261) ~[?:?] at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:106) ~[?:?] at org.apache.flink.table.api.internal.TableEnvironmentImpl.sqlQuery(TableEnvironmentImpl.java:708) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at com.transsion.trancare.realtime.ads.ADSStabilityAppCrashWarningHourly$.main(ADSStabilityAppCrashWarningHourly.scala:121) ~[?:?] at com.transsion.trancare.realtime.ads.ADSStabilityAppCrashWarningHourly.main(ADSStabilityAppCrashWarningHourly.scala) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:?] at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?] at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?] at org.apache.flink.client.program.PackagedProgram.callMainMethod(PackagedProgram.java:355) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.program.PackagedProgram.invokeInteractiveModeForExecution(PackagedProgram.java:222) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.ClientUtils.executeProgram(ClientUtils.java:105) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.deployment.application.ApplicationDispatcherBootstrap.runApplicationEntryPoint(ApplicationDispatcherBootstrap.java:301) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.deployment.application.ApplicationDispatcherBootstrap.lambda$runApplicationAsync$2(ApplicationDispatcherBootstrap.java:254) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) ~[?:?] at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:?] at org.apache.flink.runtime.concurrent.pekko.ActorSystemScheduledExecutorAdapter$ScheduledFutureTask.run(ActorSystemScheduledExecutorAdapter.java:172) ~[?:?] at org.apache.flink.runtime.concurrent.ClassLoadingUtils.runWithContextClassLoader(ClassLoadingUtils.java:68) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.runtime.concurrent.ClassLoadingUtils.lambda$withContextClassLoader$0(ClassLoadingUtils.java:41) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] ... 7 more Caused by: java.sql.SQLSyntaxErrorException: 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 ''\') LIMIT 1' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-8.0.33.jar:8.0.33] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.33.jar:8.0.33] at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.33.jar:8.0.33] at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972) ~[mysql-connector-j-8.0.33.jar:8.0.33] at org.apache.iceberg.jdbc.JdbcUtil.lambda$exists$4(JdbcUtil.java:783) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:58) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:51) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcUtil.exists(JdbcUtil.java:776) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcUtil.namespaceExists(JdbcUtil.java:761) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcCatalog.namespaceExists(JdbcCatalog.java:591) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.jdbc.JdbcCatalog.loadNamespaceMetadata(JdbcCatalog.java:475) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.flink.FlinkCatalog.getDatabase(FlinkCatalog.java:192) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.iceberg.flink.FlinkCatalog.databaseExists(FlinkCatalog.java:204) ~[iceberg-flink-runtime-1.18-1.5.0.jar:?] at org.apache.flink.table.catalog.CatalogManager.lambda$permanentDatabaseExists$14(CatalogManager.java:891) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at java.util.Optional.map(Unknown Source) ~[?:?] at org.apache.flink.table.catalog.CatalogManager.permanentDatabaseExists(CatalogManager.java:891) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.table.catalog.CatalogManager.schemaExists(CatalogManager.java:879) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.table.planner.catalog.CatalogCalciteSchema.getSubSchema(CatalogCalciteSchema.java:61) ~[?:?] at org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitSubSchema(SimpleCalciteSchema.java:111) ~[?:?] at org.apache.calcite.jdbc.CalciteSchema.getSubSchema(CalciteSchema.java:271) ~[?:?] at org.apache.calcite.sql.validate.EmptyScope.resolve_(EmptyScope.java:135) ~[?:?] at org.apache.calcite.sql.validate.EmptyScope.resolveTable(EmptyScope.java:101) ~[?:?] at org.apache.calcite.sql.validate.DelegatingScope.resolveTable(DelegatingScope.java:214) ~[?:?] at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:116) ~[?:?] at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:188) ~[?:?] at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1050) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1025) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3294) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3276) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3603) ~[?:?] at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64) ~[?:?] at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1050) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1025) ~[?:?] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1000) ~[?:?] at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:749) ~[?:?] at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.org$apache$flink$table$planner$calcite$FlinkPlannerImpl$$validate(FlinkPlannerImpl.scala:196) ~[?:?] at org.apache.flink.table.planner.calcite.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:117) ~[?:?] at org.apache.flink.table.planner.operations.SqlNodeToOperationConversion.convert(SqlNodeToOperationConversion.java:261) ~[?:?] at org.apache.flink.table.planner.delegation.ParserImpl.parse(ParserImpl.java:106) ~[?:?] at org.apache.flink.table.api.internal.TableEnvironmentImpl.sqlQuery(TableEnvironmentImpl.java:708) ~[flink-table-api-java-uber-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at com.transsion.trancare.realtime.ads.ADSStabilityAppCrashWarningHourly$.main(ADSStabilityAppCrashWarningHourly.scala:121) ~[?:?] at com.transsion.trancare.realtime.ads.ADSStabilityAppCrashWarningHourly.main(ADSStabilityAppCrashWarningHourly.scala) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:?] at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:?] at java.lang.reflect.Method.invoke(Unknown Source) ~[?:?] at org.apache.flink.client.program.PackagedProgram.callMainMethod(PackagedProgram.java:355) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.program.PackagedProgram.invokeInteractiveModeForExecution(PackagedProgram.java:222) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.ClientUtils.executeProgram(ClientUtils.java:105) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.deployment.application.ApplicationDispatcherBootstrap.runApplicationEntryPoint(ApplicationDispatcherBootstrap.java:301) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.client.deployment.application.ApplicationDispatcherBootstrap.lambda$runApplicationAsync$2(ApplicationDispatcherBootstrap.java:254) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) ~[?:?] at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:?] at org.apache.flink.runtime.concurrent.pekko.ActorSystemScheduledExecutorAdapter$ScheduledFutureTask.run(ActorSystemScheduledExecutorAdapter.java:172) ~[?:?] at org.apache.flink.runtime.concurrent.ClassLoadingUtils.runWithContextClassLoader(ClassLoadingUtils.java:68) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] at org.apache.flink.runtime.concurrent.ClassLoadingUtils.lambda$withContextClassLoader$0(ClassLoadingUtils.java:41) ~[flink-dist-1.18-SNAPSHOT.jar:1.18-SNAPSHOT] ... 7 more

nastra commented 3 months ago

@Zelin1203 your stack trace indicates that you're using MySql, can you confirm that and also please share your full catalog configuration? /cc @jbonofre

jbonofre commented 3 months ago

You are using MySQL according to Caused by: java.sql.SQLSyntaxErrorException: 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 ''') LIMIT 1' at line

MySQL is not yet supported as backend (PostgreSQL is OK). I'm working on an improvement to support more backends smoothly.

Zelin1203 commented 3 months ago

@Zelin1203 your stack trace indicates that you're using MySql, can you confirm that and also please share your full catalog configuration? /cc @jbonofre

Yes, i am using MySql.The catalog is as follow 'type'='iceberg', 'catalog-impl'='org.apache.iceberg.jdbc.JdbcCatalog', 'warehouse' = 'oss://%s', 'io-impl'='org.apache.iceberg.aliyun.oss.OSSFileIO', 'oss.endpoint' = 'oss-eu-central-1-internal.aliyuncs.com', 'client.access-key-id' = '%s', 'client.access-key-secret' = '%s', 'uri' = 'jdbc:mysql://%s:3306/iceberg', 'jdbc.user' = '%s', 'jdbc.password' = '%s',

Zelin1203 commented 3 months ago

You are using MySQL according to Caused by: java.sql.SQLSyntaxErrorException: 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 ''') LIMIT 1' at line

MySQL is not yet supported as backend (PostgreSQL is OK). I'm working on an improvement to support more backends smoothly.

Thanks.

nastra commented 2 months ago

@jbonofre I believe JDBC + MySql used to work as there have been users using it in the past that reported other issues, which we then fixed as part of https://github.com/apache/iceberg/pull/6338

jbonofre commented 2 months ago

Before my changes, I remember to have issue with MySQL (whereas it was working fine with PostgreSQL). I will double check.

nastra commented 2 months ago

I confirmed that the breaking change has been introduced by https://github.com/apache/iceberg/pull/8340 (the ESCAPE). Once I removed the ESCAPE from the two SQL statements, it started to work with MySQL, so I think we should fix this.

jbonofre commented 2 months ago

@nastra yes, I agree. Let me fix that and verify with MySQL.

jbonofre commented 2 months ago

@nastra @amogh-jahagirdar can you guys assign this issue to me ?

I have two works in progress on that:

  1. I'm testing if defining the charset on MySQL can help (without change to Iceberg JDBC Catalog). I remember that MySQL charsets have an impact on SQL statements, especially using LIKE and ESCAPE.
  2. If (1) doesn't help, I will move forward with the dialect support I started, allowing to have SQL statements specific to backends (like MySQL).