Hive执行某些命令时报错Specified key was too long; max key length is 767 bytes

hive> create table user(name string,password string);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:782)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:625)

......

 

登录mysql，修改元数据表的字符类型。

mysql> show variables like '%char%';

执行： alter database hivemeta（在mysql上的元数据表） character set latin1;

 

如果还是不行：

/etc/my.cnf

--在 [mysqld] 标签下加上
default-character-set = latin1
character_set_server = latin1

--在 [mysql]  标签下加上

default-character-set = latin1

--在 [mysql.server]标签下加上

default-character-set = latin1

--在 [mysqld_safe]标签下加上
default-character-set = latin1

--在 [client]标签下加上
default-character-set = latin1

重新启动MySql服务

mysql> show variables like '%char%';

 

如果确定了字符集已经都是latin1，还是报这个错，那就是你的元数据库里存在脏数据（比如分区用了中文、表名用了中文等等），检查hive在mysql里的元数据表信息。（可以先指定一个新的元数据库，试试是否还会报以上错误）