溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
1.今天遇到一個(gè)錯(cuò)誤，在連接到hive之后，無(wú)法執(zhí)行任何命令。
$ beeline -u "jdbc:hive2://hostname.domain.cn:10000/default;principal=hive/_HOST@domain.com;" Connecting to jdbc:hive2://hostname.domain.cn:10000/default;principal=hive/_HOST@domain.com; Connected to: Apache Hive (version release-1.2.1-EDH-1.1.2) Driver: Hive JDBC (version release-1.2.1-EDH-1.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ Beeline version release-1.2.1-EDH-1.1.2 by Apache Hive 0: jdbc:hive2://hostname.domain.cn:10000> show databases;
返回錯(cuò)誤如下
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException (message:For direct MetaStore DB connections, we don't support retries at the client level.) (state=08S01,code=1)
2. 百度這個(gè)錯(cuò)誤，很多文檔都提及是字符集的問(wèn)題，需要保證mysql的字符集是latin1.
mysql> show variables like '%collation_%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+
3. 如果字符集不是latin1，需要手動(dòng)執(zhí)行(本次問(wèn)題，不是這個(gè)，我的字符集，原本就是latin1)
mysql>use hive mysql>alter database hive character set latin1;
4. 后來(lái)嘗試重啟一下hive，結(jié)果，metastore竟然啟動(dòng)失敗。查看日志，說(shuō)創(chuàng)建表失敗。這個(gè)hive已經(jīng)運(yùn)行一段時(shí)間了，一直沒(méi)有問(wèn)題。對(duì)應(yīng)的表也都是存在的，沒(méi)有道理重建的。
5.日志輸出如下：
Traceback (most recent call last): File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 259, in <module> HiveMetastore().execute() File "/usr/lib/python2.6/site-packages/resource_management/libraries/script/script.py", line 280, in execute method(env) File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 59, in start self.configure(env) File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive_metastore.py", line 73, in configure hive(name = 'metastore') File "/usr/lib/python2.6/site-packages/ambari_commons/os_family_impl.py", line 89, in thunk return fn(*args, **kwargs) File "/var/lib/ambari-agent/cache/common-services/HIVE/0.12.0.2.0/package/scripts/hive.py", line 320, in hive user = params.hive_user File "/usr/lib/python2.6/site-packages/resource_management/core/base.py", line 155, in __init__ self.env.run() File "/usr/lib/python2.6/site-packages/resource_management/core/environment.py", line 160, in run self.run_action(resource, action) File "/usr/lib/python2.6/site-packages/resource_management/core/environment.py", line 124, in run_action provider_action() File "/usr/lib/python2.6/site-packages/resource_management/core/providers/system.py", line 273, in action_run tries=self.resource.tries, try_sleep=self.resource.try_sleep) File "/usr/lib/python2.6/site-packages/resource_management/core/shell.py", line 71, in inner result = function(command, **kwargs) File "/usr/lib/python2.6/site-packages/resource_management/core/shell.py", line 93, in checked_call tries=tries, try_sleep=try_sleep) File "/usr/lib/python2.6/site-packages/resource_management/core/shell.py", line 141, in _call_wrapper result = _call(command, **kwargs_copy) File "/usr/lib/python2.6/site-packages/resource_management/core/shell.py", line 294, in _call raise Fail(err_msg) resource_management.core.exceptions.Fail: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-metastore/bin/schematool -initSchema -dbType mysql -userName hive -passWord [PROTECTED] -verbose' returned 1. Metastore connection URL: jdbc:mysql://hostname.mysql.cn/hivemeta Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: hive Starting metastore schema initialization to 1.2.0 Initialization script hive-schema-1.2.0.mysql.sql Connecting to jdbc:mysql://hostname.mysql.cn/hivemeta Connected to: MySQL (version 5.1.73) Driver: MySQL Connector Java (version mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 )) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:mysql://hostname.mysql.cn/hivem> !autocommit on Autocommit status: true 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET @OLD_CHARACTER_SET_C LIENT=@@CHARACTER_SET_CLIENT */ No rows affected (0.003 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET @OLD_CHARACTER_SET_R ESULTS=@@CHARACTER_SET_RESULTS */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET @OLD_COLLATION_CONNE CTION=@@COLLATION_CONNECTION */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET NAMES utf8 */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40103 SET @OLD_TIME_ZONE=@@TIM E_ZONE */ No rows affected (0.002 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40103 SET TIME_ZONE='+00:00' * / No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40014 SET @OLD_UNIQUE_CHECKS=@ @UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40014 SET @OLD_FOREIGN_KEY_CHE CKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ No rows affected (0.001 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET @OLD_SQL_MODE=@@SQL_ MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40111 SET @OLD_SQL_NOTES=@@SQL _NOTES, SQL_NOTES=0 */ No rows affected (0.001 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> /*!40101 SET character_set_client = utf8 */ No rows affected (0.001 seconds) 0: jdbc:mysql://hostname.mysql.cn/hivem> CREATE TABLE IF NOT EXISTS `BUCKE TING_COLS` ( `SD_ID` bigint(20) NOT NULL, `BUCKET_COL_NAME` varchar(256) CHARACT ER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, P RIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `BUCKETING_COLS_N49` (`SD_ID`), CONSTRAI NT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGIN E=InnoDB DEFAULT CHARSET=latin1 Error: Can't create table 'hivemeta.bucketing_cols' (errno: 121) (state=HY000,code=1005) Closing: 0: jdbc:mysql://hostname.mysql.cn/hivemeta org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:270) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:243) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:473) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:358) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:326) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:266) ... 8 more *** schemaTool failed ***
6.根據(jù)關(guān)鍵字“Schema initialization FAILED! Metastore state would be inconsistent !!”，查詢到hdp的官方論壇。
解釋說(shuō)，這個(gè)是一個(gè)已知的問(wèn)題。需要修改ambari的sql腳本，刪除mysql的一些index等。
https://community.hortonworks.com/questions/113748/hive-metastore-start.html
7. 官方建議的步驟繁多。個(gè)人比較懶。而且這個(gè)hive庫(kù)，前一天還是正常的。這個(gè)問(wèn)題是突然出現(xiàn)的，我不傾向與，這個(gè)是bug的可能。
繼續(xù)分析，查看hivemetastore的log，還是有必要的。
發(fā)現(xiàn)問(wèn)題，表不存在
2018-03-07 15:34:43,871 INFO [pool-8-thread-200]: metastore.HiveMetaStore (HiveMetaStore.java:logInfo(746)) - 200: get_all_databases 2018-03-07 15:34:43,871 INFO [pool-8-thread-200]: HiveMetaStore.audit (HiveMetaStore.java:logAuditEvent(371)) - ugi=ambari-qa-hbjt_hadoop@domain.com ip=/172.18.98.57 cmd=get_all_databases 2018-03-07 15:34:43,873 ERROR [pool-8-thread-200]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:invoke(165)) - HMSHandler Fatal error: javax.jdo.JDOException: Exception thrown when executing query at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:596) ...... NestedThrowablesStackTrace: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'hivemeta.dbs' doesn't exist at sun.reflect.GeneratedConstructorAccessor60.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
8.現(xiàn)在要從mysql入手分析，逐步檢查。
mysql> use hive mysql> show tables; +---------------------------+ | Tables_in_hivemeta | +---------------------------+ | BUCKETING_COLS | | CDS | | COLUMNS_V2 | | COMPACTION_QUEUE | | COMPLETED_TXN_COMPONENTS | | DATABASE_PARAMS | | DBS | | DB_PRIVS | | DELEGATION_TOKENS |
9. 問(wèn)題來(lái)了，表不存在。為什么不存在呢？其實(shí)我已經(jīng)知道問(wèn)題了。Mysql表名大小寫(xiě)敏感問(wèn)題。
mysql>select count(*) from `DBS`; ERROR 1146 (42S02): Table 'hivemeta.DBS' doesn't exist
10. 由于很多Oracle數(shù)據(jù)庫(kù)的表名是大小寫(xiě)不敏感的。但是Mysql卻是表名大小寫(xiě)敏感的。所以，很多開(kāi)發(fā)人員，都喜歡將mysql設(shè)置為表名大小寫(xiě)不敏感。
而show tables之后，我就懷疑這個(gè)可能了。
11. 經(jīng)過(guò)檢查/etc/my.cnf，確實(shí)已經(jīng)設(shè)置lower_case_table_names=1
12. Hive庫(kù)創(chuàng)建的時(shí)候，mysql是大小寫(xiě)敏感的。所以，表名都是大寫(xiě)的。而后期，有人修改了mysql參數(shù)，取消大小寫(xiě)敏感。這樣，問(wèn)題就來(lái)了。Mysql并不是完全不敏感，而是在設(shè)置參數(shù)lower_case_table_names=1 之后，創(chuàng)建和查詢的sql語(yǔ)句中的表名，默認(rèn)修改為小寫(xiě)的。而已經(jīng)創(chuàng)建的表名，還是大寫(xiě)的顯示。但是你無(wú)論用大寫(xiě)表名，還是小寫(xiě)表名，都無(wú)法正確查詢到了。
13. 這個(gè)問(wèn)題出現(xiàn)的場(chǎng)景：
只有是在hive庫(kù)創(chuàng)建完成之后，又修改/etc/my.cnf，添加參數(shù)lower_case_table_names=1導(dǎo)致的。
14. 問(wèn)題找到之后，有兩個(gè)解決方案：
a. 取消參數(shù)lower_case_table_names=1
b. 如果mysql的hive庫(kù)中沒(méi)有什么數(shù)據(jù)，刪除hive庫(kù)表，重新創(chuàng)建hive meta表，直接就創(chuàng)建出小寫(xiě)的表名了。
輸出就是小寫(xiě)的了：
mysql> show tables; +---------------------------+ | Tables_in_hive_beta | +---------------------------+ | bucketing_cols | | cds | | columns_v2 | | compaction_queue | | completed_txn_components | | database_params | | db_privs | | dbs | | delegation_tokens | | func_ru |
15. 我最后選擇取消參數(shù)lower_case_table_names=1，并重啟mysql。查詢正常了。
mysql> select count(*) from `DBS`; +----------+ | count(*) | +----------+ | 2 |
16. Ambari啟動(dòng)hive metastore也成功了。
問(wèn)題解決，一切恢復(fù)正常
$ beeline -u "jdbc:hive2://hostname.domain.cn:10000/default;principal=hive/_HOST@domain.com;" Connecting to jdbc:hive2://hostname.domain.cn:10000/default;principal=hive/_HOST@domain.com; Connected to: Apache Hive (version release-1.2.1-EDH-1.1.2) Driver: Hive JDBC (version release-1.2.1-EDH-1.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ Beeline version release-1.2.1-EDH-1.1.2 by Apache Hive 0: jdbc:hive2://hostname.domain.cn:10000> show databases; +----------------+--+ | database_name | +----------------+--+ | default | | hbjt | +----------------+--+ 2 rows selected (1.206 seconds)
最終的原因，就是有人修改的/etc/my.cnf，導(dǎo)致的本次問(wèn)題。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
