hive create table: Specified key was too long; max key length is 767 bytes

今天下午参照着资料安装Hive，安装大体是这样的：

下载hive源文件(http://mirrors.cnnic.cn/apache/hive/ )
 解压hive文件【tar -zxvf apache-hive-0.14.0-bin.tar.gz】——【mv apache-hive-0.14.0-bin hive-0.14.0 】——【bin/hive】

 

进入$HIVE_HOME/conf/修改文件
cp  hive-env.sh.template  hive-env.sh
cp  hive-default.xml.template  hive-site.xml

修改$HIVE_HOME/bin的hive-env.sh，增加以下三行
export JAVA_HOME=/usr/local/jdk
export HIVE_HOME=/usr/local/hive-0.14.0
export HADOOP_HOME=/usr/local/hadoop-2.6.0

 

配置MySQL的metastore

修改$HIVE_HOME/conf/hive-site.xml

<property>

  <name>javax.jdo.option.ConnectionURL</name>

  <value>jdbc:mysql://192.168.1.100:3306/myTestDb?

      createDatabaseIfNotExist=true</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>admin</value>

</property>

 

————————————————————————————————————————————————————————————————

安装完毕之后bin/hive也能正常运作，也授权了

grant all PRIVILEGES on *.* to root@'cxzdy100'  identified by 'root';

flush privileges;

*.*，第一个*是数据库名，这里不限制数据库名，
第二个*是表名，也不限制表名
%是访问机器IP

但是在create table t1(name string);的时候老是报错，提示什么 Specified key was too long; max key length is 767 bytes  长度的问题。

hive> create table t1 ( name string ) ;
Moved: 'hdfs://cxzdy:9000/user/hive/warehouse/t1' to trash at: hdfs://cxzdy:9000/user/root/.Trash/Current
Moved: 'hdfs://cxzdy:9000/user/hive/warehouse/t1' to trash at: hdfs://cxzdy:9000/user/root/.Trash/Current
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)省略.......

在linux上 more hive.log文件发现是长度的问题。

2015-04-28 01:08:38,273 ERROR [main]: DataNucleus.Datastore (Log4JLogger.java:error(115)) - Error thrown executing CREATE TABLE `TABLE_PARAMS`
(
`TBL_ID` BIGINT NOT NULL,
`PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
`PARAM_VALUE` VARCHAR(4000) BINARY NULL,
CONSTRAINT `TABLE_PARAMS_PK` PRIMARY KEY (`TBL_ID`,`PARAM_KEY`)
) ENGINE=INNODB : 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)

度娘上的解决方案大多都是：

　　在mysql机器的上运行:　　alter database hive character set latin1;

起初还以为这是BUG的最终解决方案，后来在windows的mysql上执行提示

错误码: 1
Can't create/write to file '.\hive\db.opt' (Errcode: 2 - No such file or directory)

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

之后就是一顿神烦DeBug。网上什么驱动太旧啊，什么修改hive-site.xml的 auth</value>结尾啊....

最后不知从哪冒出的想法，绝望地输入了alter database myTestDb character set latin1;

结果，整个下午都在忙的问题居然是这么简单的答案。

不由想起了这样的题。呵呵。。

（），（），2，4，6，7，8 请问括号里该填什么？