Cloudera Manager 5.9 离线安装 启动Hive异常：Error: Unknown error 1813 (state=HY000,code=1813)

Connected to: MySQL (version 5.7.18-log)
Driver: MySQL Connector Java (version mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ))
Transaction isolation: TRANSACTION_READ_COMMITTED
Autocommit status: true
No rows affected (0.007 seconds)
No rows affected (0.004 seconds)
No rows affected (0.001 seconds)
No rows affected (0.004 seconds)
No rows affected (0.002 seconds)
No rows affected (0.001 seconds)
No rows affected (0.002 seconds)
No rows affected (0.004 seconds)
No rows affected (0 seconds)
No rows affected (0.003 seconds)
No rows affected (0.002 seconds)
No rows affected (0.002 seconds)
Error: Unknown error 1813 (state=HY000,code=1813)
Closing: 0: jdbc:mysql://zj-hadoop-01.xxxx.xxx:3306/hive?useUnicode=true&characterEncoding=UTF-8
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:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    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 ***

 

 

1、修改 mysql 配置文件 /etc/my.cnf ，添加配置项

default-storage-engine=innodb

2、重启 mysql 服务

service mysqld restart 

3、如果 mysql 已经创建 hive、oozie、hue 数据库，需要删除重新创建

--hive数据库
drop database hive;
create database hive DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
--oozie数据库
drop database oozie;
create database oozie DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
--hue数据库
drop database hue;
create database hue DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 

接着又出现新的异常问题

 

 然后按照各种方法排查问题：

1、配置 mysql 连接信息到 /etc/hive/conf/hive-site.xml 末尾

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost:3306/hive?useUnicode=true&amp;characterEncoding=UTF-8</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>root</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>123456</value>
</property>

2、上传 mysql-connector-java-5.1.48-bin.jar 包到$HIVE_HOME/lib/ 目录下

3、删除mysql hive 库 重新创建并重启 mysql 服务

drop database hive;
create database hive DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

service mysqld restart

 

经过反复安装 hive 服务，最终安装成功，但没有确认是哪一步引起的问题。