com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

[San Luo]

hi guys,

     when I am trying to use mysql to manager hive metastore，I got a
problem like below:

 

[root@master hive]# hive

 

Logging initialized using configuration in
jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in
[jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/o
rg/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in
[jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/S
taticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

hive> show tables;

OK

invites

pokes

test

Time taken: 2.573 seconds, Fetched: 3 row(s)

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE
invites PARTITION (ds='2008-08-15');

Loading data to table default.invites partition (ds=2008-08-15)

Interrupting... Be patient, this might take some time.

Press Ctrl+C again to kill JVM

Failed with exception 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.GeneratedConstructorAccessor32.newInstance(Unknown
Source)

        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstruc
torAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

        at com.mysql.jdbc.Util.getInstance(Util.java:386)

…….

 

 

I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to
modify mysql charcter set of hive database to latin1 and utf8 but it doesn’
t work.

 

Any idea will be appreciated.

 


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?><!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
--><configuration>
<property>  
        <name>javax.jdo.option.ConnectionURL</name>  
        <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>  
        <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>  
        <name>javax.jdo.option.ConnectionDriverName</name>  
        <value>com.mysql.jdbc.Driver</value>  
        <description>Driver class name for a JDBC metastore</description>
</property>

<property>  
        <name>javax.jdo.option.ConnectionUserName</name>  
        <value>hive</value>  
        <description>username to use against metastore database</description>
</property>

<property>  
        <name>javax.jdo.option.ConnectionPassword</name>  
        <value>123456</value>  
        <description>password to use against metastore database</description>
</property>
<property> 
   <name>hive.hwi.listen.port </name> 
   <value>9999 </value> 
   <description>This is the port the Hive Web Interface will listen on </description> 
</property> 

<property> 
  <name>datanucleus.autoCreateSchema</name> 
  <value>true</value> 
</property> 

<property>
  <name>datanucleus.autoCreateTables</name>
  <value>true</value>
</property>

<property>
  <name>datanucleus.autoCreateColumns</name>
  <value>true</value>
</property>

<property>
  <name>datanucleus.readOnlyDatastore</name>
  <value>false</value>
</property>

<property> 
  <name>datanucleus.fixedDatastore</name> 
  <value>false</value> 
</property>
<!--
<property>
  <name>hive.support.concurrency</name>
  <value>true</value>
</property>
-->
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
  </property>
</configuration>