Using Debezium and Hibernate. When connecting using URI with database, it tries to connect to a different catalog.database.

[alberttwong]

Using Debezium and Hibernate. When connecting using URI with database, it tries to connect to a different catalog.database.

[alberttwong]

I am also using an additional setting of hibernate.connection.init_sql="use default_catalog.demo". It doesn't matter if I use this additional setting or not.

[alberttwong]

debezium/debezium-connector-jdbc#71

[alberttwong]

chat with debezium - https://debezium.zulipchat.com/#narrow/stream/383534-community-jdbc/topic/Unknown.20table.20'_statistics_.2E_statistics_.2Ecustomer'

[alberttwong]

2024-04-26 20:58:09,202 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select $$, error: Getting analyzing error. Detail message: Column '$$' cannot be resolved.
2024-04-26 20:59:18,484 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: SHOW KEYS FROM `customer` FROM `_statistics_`, error: Unknown table '_statistics_._statistics_.customer'

root@starrocks-fe:/opt/starrocks/fe/log# cat fe.log |grep execute
2024-04-26 20:56:52,938 INFO (starrocks-mysql-nio-pool-0|125) [StmtExecutor.execute():710] execute Exception, sql: select @@version_comment limit 1, error: Backend node not found. Check if any backend node is down.backend: 
2024-04-26 20:56:52,973 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select @@version_comment limit 1, error: Backend node not found. Check if any backend node is down.backend: 
2024-04-26 20:56:53,109 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select @@version_comment limit 1, error: Backend node not found. Check if any backend node is down.backend: 
2024-04-26 20:56:53,125 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select @@version_comment limit 1, error: Backend node not found. Check if any backend node is down.backend: 
2024-04-26 20:56:57,950 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select @@version_comment limit 1, error: Backend node not found. Check if any backend node is down.backend: 
2024-04-26 20:58:09,202 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: select $$, error: Getting analyzing error. Detail message: Column '$$' cannot be resolved.
2024-04-26 20:59:18,484 INFO (starrocks-mysql-nio-pool-1|129) [StmtExecutor.execute():710] execute Exception, sql: SHOW KEYS FROM `customer` FROM `_statistics_`, error: Unknown table '_statistics_._statistics_.customer'

root@starrocks-fe:/opt/starrocks/fe/log# cat fe.log |grep customer    
2024-04-26 20:29:10,087 INFO (starrocks-mysql-nio-pool-2|167) [StmtExecutor.execute():710] execute Exception, sql: CREATE TABLE `customer` (
2024-04-26 20:29:17,377 INFO (starrocks-mysql-nio-pool-2|167) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table demo.customer with 2 replicas
2024-04-26 20:29:17,380 INFO (starrocks-mysql-nio-pool-2|167) [LocalMetastore.onCreate():2284] Successfully create table: customer-10119, in database: demo-10004
2024-04-26 20:30:31,326 INFO (starrocks-mysql-nio-pool-1|139) [NativeAccessController.checkAnyActionOnObject():347] Object not found when checking any action on TABLE default_catalog._statistics_.customer, message: cannot find table customer in db _statistics_
2024-04-26 20:30:31,327 INFO (starrocks-mysql-nio-pool-1|139) [StmtExecutor.execute():710] execute Exception, sql: SHOW KEYS FROM `customer` FROM `_statistics_`, error: Unknown table '_statistics_._statistics_.customer'

root@starrocks-fe:/opt/starrocks/fe/log# cat fe.log |grep demo        
2024-04-26 20:27:58,758 INFO (starrocks-mysql-nio-pool-0|125) [LocalMetastore.createDb():431] createDb dbName = demo, id = 10004
2024-04-26 20:29:17,376 INFO (starrocks-mysql-nio-pool-2|167) [Database.checkDataSizeQuota():442] database[demo] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:29:17,376 INFO (starrocks-mysql-nio-pool-2|167) [Database.checkReplicaQuota():453] database[demo] replica quota: left number: 9223372036854775807 / total: 9223372036854775807
2024-04-26 20:29:17,377 INFO (starrocks-mysql-nio-pool-2|167) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table demo.customer with 2 replicas
2024-04-26 20:29:17,380 INFO (starrocks-mysql-nio-pool-2|167) [LocalMetastore.onCreate():2284] Successfully create table: customer-10119, in database: demo-10004

root@starrocks-fe:/opt/starrocks/fe/log# cat fe.log |grep _statistics_
2024-04-26 20:28:55,333 INFO (statistics meta manager|26) [LocalMetastore.createDb():431] createDb dbName = _statistics_, id = 10006
2024-04-26 20:28:55,356 INFO (statistics meta manager|26) [Database.checkDataSizeQuota():442] database[_statistics_] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:28:55,356 INFO (statistics meta manager|26) [Database.checkReplicaQuota():453] database[_statistics_] replica quota: left number: 9223372036854775807 / total: 9223372036854775807
2024-04-26 20:28:55,380 INFO (statistics meta manager|26) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table _statistics_.table_statistic_v1 with 10 replicas
2024-04-26 20:28:55,413 INFO (statistics meta manager|26) [LocalMetastore.onCreate():2284] Successfully create table: table_statistic_v1-10008, in database: _statistics_-10006
2024-04-26 20:28:55,445 INFO (statistics meta manager|26) [Database.checkDataSizeQuota():442] database[_statistics_] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:28:55,445 INFO (statistics meta manager|26) [Database.checkReplicaQuota():453] database[_statistics_] replica quota: left number: 9223372036854775797 / total: 9223372036854775807
2024-04-26 20:28:55,445 INFO (statistics meta manager|26) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table _statistics_.column_statistics with 10 replicas
2024-04-26 20:28:55,448 INFO (statistics meta manager|26) [LocalMetastore.onCreate():2284] Successfully create table: column_statistics-10031, in database: _statistics_-10006
2024-04-26 20:28:55,452 INFO (statistics meta manager|26) [Database.checkDataSizeQuota():442] database[_statistics_] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:28:55,452 INFO (statistics meta manager|26) [Database.checkReplicaQuota():453] database[_statistics_] replica quota: left number: 9223372036854775787 / total: 9223372036854775807
2024-04-26 20:28:55,452 INFO (statistics meta manager|26) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table _statistics_.histogram_statistics with 10 replicas
2024-04-26 20:28:55,455 INFO (statistics meta manager|26) [LocalMetastore.onCreate():2284] Successfully create table: histogram_statistics-10054, in database: _statistics_-10006
2024-04-26 20:28:55,458 INFO (statistics meta manager|26) [Database.checkDataSizeQuota():442] database[_statistics_] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:28:55,458 INFO (statistics meta manager|26) [Database.checkReplicaQuota():453] database[_statistics_] replica quota: left number: 9223372036854775777 / total: 9223372036854775807
2024-04-26 20:28:55,459 INFO (statistics meta manager|26) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table _statistics_.external_column_statistics with 10 replicas
2024-04-26 20:28:55,461 INFO (statistics meta manager|26) [LocalMetastore.onCreate():2284] Successfully create table: external_column_statistics-10077, in database: _statistics_-10006
2024-04-26 20:28:55,477 INFO (statistics meta manager|26) [Database.checkDataSizeQuota():442] database[_statistics_] data quota: left bytes: 8388608.000 TB / total: 8388608.000 TB
2024-04-26 20:28:55,477 INFO (statistics meta manager|26) [Database.checkReplicaQuota():453] database[_statistics_] replica quota: left number: 9223372036854775767 / total: 9223372036854775807
2024-04-26 20:28:55,478 INFO (statistics meta manager|26) [LocalMetastore.buildPartitions():1890] start to build 1 partitions sequentially for table _statistics_.pipe_file_list with 8 replicas
2024-04-26 20:28:55,480 INFO (statistics meta manager|26) [LocalMetastore.onCreate():2284] Successfully create table: pipe_file_list-10100, in database: _statistics_-10006
2024-04-26 20:28:55,483 INFO (statistics meta manager|26) [RepoCreator.correctTable():83] table _statistics_.pipe_file_list already has 1 replicas, no need to alter replication_num
2024-04-26 20:30:31,326 INFO (starrocks-mysql-nio-pool-1|139) [NativeAccessController.checkAnyActionOnObject():347] Object not found when checking any action on TABLE default_catalog._statistics_.customer, message: cannot find table customer in db _statistics_
2024-04-26 20:30:31,327 INFO (starrocks-mysql-nio-pool-1|139) [StmtExecutor.execute():710] execute Exception, sql: SHOW KEYS FROM `customer` FROM `_statistics_`, error: Unknown table '_statistics_._statistics_.customer'

[alberttwong]

https://debezium.zulipchat.com/#narrow/stream/383534-community-jdbc/topic/Unknown.20table.20'_statistics_.2E_statistics_.2Ecustomer'/near/436093404

Hi @Albert Wong so after looking at this most of the morning, I don't believe StarRocks is feasible without changes that go beyond just setting the catalog name. For example, with the PR build, I actually get this error:

debezium     | Caused by: java.sql.SQLSyntaxErrorException: Access denied; you need (at least one of) the CREATE TABLE privilege(s) on DATABASE test for this operation. Please ask the admin to grant permission(s) or try activating existing roles using <set [default] role>. Current role(s): NONE. Inactivated role(s): NONE.
debezium     |  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
^ NOTE I granted all priviledges to my user, so no idea why this is the case :/

So this tells me the catalog feature works; however, if a table has a primary key or certain types of data like long text, this creates an issue because we hit datatype mismatches because the JDBC sink is using the MySQL dialect but StarRocks does not support all of MySQL's data types. So I truly think we need a StarRocks dialect implementation that handles these nuances before you can use the JDBC sink with this target system.