hive初始化数据库mysql时出现Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061)

1. Hive初始化mysql数据库

[root@sam02 hive1.2]# schematool -initSchema -dbType mysql

2. 出错

Metastore connection URL:	 jdbc:mysql://sam03:3306/hive1?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 root
Starting metastore schema initialization to 1.2.0
Initialization script hive-schema-1.2.0.mysql.sql
Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***

---

3. 解决方法

3.1 查看mysql数据库

---

如上图，发现mysql已经存在hive数据库，之前已经初始化过mysql数据库

3.2 方法1:删除存在mysql的hive数据库

mysql >drop database if exists hive;

3.3 方法2:修改hive的hive-site.sh的ConnectionURL

---

将jdbc:mysql://sam03:3306/hive?createDatabaseIfNotExist=true的hive改成其它名称，如hive1

---