[jira] [Commented] (HIVE-8824) Exception happened in case set hive.metastore.try.direct.sql to true when running 'show table stats xxx' on impala with hive metastore db is mysql
[ https://issues.apache.org/jira/browse/HIVE-8824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14257583#comment-14257583 ] Chaoyu Tang commented on HIVE-8824: --- [~lixuebin] It is a defect in MetaStoreDirectSql related to MySQL sql_mode which should be set to ANSI_QUOTES. Exception happened in case set hive.metastore.try.direct.sql to true when running 'show table stats xxx' on impala with hive metastore db is mysql --- Key: HIVE-8824 URL: https://issues.apache.org/jira/browse/HIVE-8824 Project: Hive Issue Type: Bug Components: Metastore Affects Versions: 0.13.1 Reporter: lixuebin Hive Metstore Database is MySQL. First: set hive-site.xml hive.metastore.try.direct.sql true second: On impala show table stats x; Remark: MYSQL does not support table name and column name with double quotes, such as : select * from testtb where id='1234'; should be: select * from testtb where id='1234'; MetaStoreDirectSql.java should be checked. The Exception: metastore.ObjectStore (ObjectStore.java:handleDirectSqlError(2243)) - Direct SQL failed, falling back to ORM javax.jdo.JDODataStoreException: Error executing SQL query select COLUMN_NAME, COLUMN_TYPE, LONG_LOW_VALUE, LONG_HIGH_VALUE, DOUBLE_LOW_VALUE, DOUBLE_HIGH_VALUE, BIG_DECIMAL_LOW_VALUE, BIG_DECIMAL_HIGH_VALUE, NUM_NULLS, NUM_DISTINCTS, AVG_COL_LEN, MAX_COL_LEN, NUM_TRUES, NUM_FALSES, LAST_ANALYZED from TAB_COL_STATS where DB_NAME = ? and TABLE_NAME = ? and COLUMN_NAME in (?). at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451) at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getTableStats(MetaStoreDirectSql.java:879) at org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:5749) at org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:5746) at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2208) at org.apache.hadoop.hive.metastore.ObjectStore.getTableColumnStatisticsInternal(ObjectStore.java:5746) at org.apache.hadoop.hive.metastore.ObjectStore.getTableColumnStatistics(ObjectStore.java:5740) at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108) at com.sun.proxy.$Proxy0.getTableColumnStatistics(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_statistics_req(HiveMetaStore.java:3559) at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105) at com.sun.proxy.$Proxy1.get_table_statistics_req(Unknown Source) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table_statistics_req.getResult(ThriftHiveMetastore.java:9528) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table_statistics_req.getResult(ThriftHiveMetastore.java:9512) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hadoop.hive.metastore.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:48) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744) NestedThrowablesStackTrace: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'TAB_COL_STATS where DB_NAME = 'tpcds' and TABLE_NAME = 'et_date_dim' and ' at line 1 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-8824) Exception happened in case set hive.metastore.try.direct.sql to true when running 'show table stats xxx' on impala with hive metastore db is mysql
[ https://issues.apache.org/jira/browse/HIVE-8824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14246568#comment-14246568 ] Binglin Chang commented on HIVE-8824: - We get this error too with hive-0.13.1 and impala-1.4.1-cdh5.1.2, maybe it should be report to impala user list as well. Exception happened in case set hive.metastore.try.direct.sql to true when running 'show table stats xxx' on impala with hive metastore db is mysql --- Key: HIVE-8824 URL: https://issues.apache.org/jira/browse/HIVE-8824 Project: Hive Issue Type: Bug Components: Metastore Affects Versions: 0.13.1 Reporter: lixuebin Hive Metstore Database is MySQL. First: set hive-site.xml hive.metastore.try.direct.sql true second: On impala show table stats x; Remark: MYSQL does not support table name and column name with double quotes, such as : select * from testtb where id='1234'; should be: select * from testtb where id='1234'; MetaStoreDirectSql.java should be checked. The Exception: metastore.ObjectStore (ObjectStore.java:handleDirectSqlError(2243)) - Direct SQL failed, falling back to ORM javax.jdo.JDODataStoreException: Error executing SQL query select COLUMN_NAME, COLUMN_TYPE, LONG_LOW_VALUE, LONG_HIGH_VALUE, DOUBLE_LOW_VALUE, DOUBLE_HIGH_VALUE, BIG_DECIMAL_LOW_VALUE, BIG_DECIMAL_HIGH_VALUE, NUM_NULLS, NUM_DISTINCTS, AVG_COL_LEN, MAX_COL_LEN, NUM_TRUES, NUM_FALSES, LAST_ANALYZED from TAB_COL_STATS where DB_NAME = ? and TABLE_NAME = ? and COLUMN_NAME in (?). at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451) at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321) at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getTableStats(MetaStoreDirectSql.java:879) at org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:5749) at org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:5746) at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2208) at org.apache.hadoop.hive.metastore.ObjectStore.getTableColumnStatisticsInternal(ObjectStore.java:5746) at org.apache.hadoop.hive.metastore.ObjectStore.getTableColumnStatistics(ObjectStore.java:5740) at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108) at com.sun.proxy.$Proxy0.getTableColumnStatistics(Unknown Source) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table_statistics_req(HiveMetaStore.java:3559) at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105) at com.sun.proxy.$Proxy1.get_table_statistics_req(Unknown Source) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table_statistics_req.getResult(ThriftHiveMetastore.java:9528) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_table_statistics_req.getResult(ThriftHiveMetastore.java:9512) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hadoop.hive.metastore.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:48) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744) NestedThrowablesStackTrace: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'TAB_COL_STATS where DB_NAME = 'tpcds' and TABLE_NAME = 'et_date_dim' and ' at line 1 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
