[ https://issues.apache.org/jira/browse/HIVE-26503?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17597721#comment-17597721 ]
zhangbutao commented on HIVE-26503: ----------------------------------- Double check this issue. Myabe this is not bug, and I could create jdbc table successfully with {*}_hive.sql.query_{*}, but the precondition is that there is no *_hive.sql.table_* in TBLPROPERTIES: {code:java} CREATE EXTERNAL TABLE jdbc_testmysql ( id int ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( "hive.sql.database.type" = "MYSQL", "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver", "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/testdb", "hive.sql.dbcp.username" = "user", "hive.sql.dbcp.password" = "passwd", "hive.sql.dbcp.maxActive" = "1", "hive.sql.query" = "SELECT id from testmysql" ); {code} Anyway, after HIVE-20652, this will mislead user when creating jdbc table in case of *_hive.sql.query_* and _*hive.sql.table*_ exist together. Maybe we shoud optimise this using a good way... > Hive JDBC Storage Handler: Failed to create jdbc table with hive.sql.query > -------------------------------------------------------------------------- > > Key: HIVE-26503 > URL: https://issues.apache.org/jira/browse/HIVE-26503 > Project: Hive > Issue Type: Bug > Components: JDBC storage handler > Affects Versions: 4.0.0-alpha-1, 4.0.0-alpha-2 > Reporter: zhangbutao > Assignee: zhangbutao > Priority: Major > > I found this problem when I was testing HIVE-26046, and This problem maybe a > regression from HIVE-20652. > HIVE-23709 has disabled {*}jdbc_handler.q{*}, so we didn't catch and fix the > problem in time, and maybe we should reopen the qtest in the future. > > Step to repro: > *1. Create table in mysql:* > {code:java} > create table testmysql(id int);{code} > > *2. Create jdbc-map table with _hive.sql.query_ in hive:* > {code:java} > CREATE EXTERNAL TABLE jdbc_testmysql > ( > id int > ) > STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' > TBLPROPERTIES ( > "hive.sql.database.type" = "MYSQL", > "hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver", > "hive.sql.jdbc.url" = "jdbc:mysql://localhost:3306/testdb", > "hive.sql.dbcp.username" = "user", > "hive.sql.dbcp.password" = "passwd", > "hive.sql.table" = "testmysql", > "hive.sql.dbcp.maxActive" = "1", > "hive.sql.query" = "SELECT id from testmysql" > ); {code} > *3. Failed create jdbc table in hive with following exception:* > {code:java} > 2022-08-30T15:45:46,977 ERROR [HiveServer2-Background-Pool: Thread-71] > metadata.Table: Unable to get field from serde: > org.apache.hive.storage.jdbc.JdbcSerDe > java.lang.RuntimeException: > MetaException(message:org.apache.hadoop.hive.serde2.SerDeException Caught > exception while initializing the SqlSerDe) > at > org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:352) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.metadata.Table.getDeserializer(Table.java:332) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.metadata.Table.getColsInternal(Table.java:742) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.metadata.Table.getCols(Table.java:725) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.ddl.table.create.CreateTableDesc.toTable(CreateTableDesc.java:949) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.ddl.table.create.CreateTableOperation.execute(CreateTableOperation.java:76) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.ddl.DDLTask.execute(DDLTask.java:84) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:212) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:105) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Executor.launchTask(Executor.java:354) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Executor.launchTasks(Executor.java:327) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Executor.runTasks(Executor.java:244) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Executor.execute(Executor.java:105) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:370) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:205) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Driver.run(Driver.java:154) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at org.apache.hadoop.hive.ql.Driver.run(Driver.java:149) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:185) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:233) > ~[hive-service-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hive.service.cli.operation.SQLOperation.access$500(SQLOperation.java:88) > ~[hive-service-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork$1.run(SQLOperation.java:336) > ~[hive-service-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at java.security.AccessController.doPrivileged(Native Method) > ~[?:1.8.0_112] > at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_112] > at > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) > ~[hadoop-common-3.3.1.jar:?] > at > org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork.run(SQLOperation.java:356) > ~[hive-service-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > ~[?:1.8.0_112] > at java.util.concurrent.FutureTask.run(FutureTask.java:266) > ~[?:1.8.0_112] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > ~[?:1.8.0_112] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > ~[?:1.8.0_112] > at java.lang.Thread.run(Thread.java:745) [?:1.8.0_112] > Caused by: org.apache.hadoop.hive.metastore.api.MetaException: > org.apache.hadoop.hive.serde2.SerDeException Caught exception while > initializing the SqlSerDe > at > org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:101) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:80) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > at > org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:350) > ~[hive-exec-4.0.0-alpha-2-SNAPSHOT.jar:4.0.0-alpha-2-SNAPSHOT] > ... 29 more > {code} > -- This message was sent by Atlassian Jira (v8.20.10#820010)