Grokbase Groups Hive user May 2010
FAQ
Hi,

I am trying to use mysql_server-5.1.41.0 as the local db for hive
following instructions at
http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin#Local_Meta
store



I get the following exception:

FAILED: Error in metadata: MetaException(message:Got exception:
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.MySQLSyntaxErrorException: Specified key was
too long; max key length is 767 bytes

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)

..

at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:242)

at
org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:
572)

at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMet
aStore.java:1295)

at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMet
aStore.java:1292)

at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRet
ry(HiveMetaStore.java:229)

at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(Hiv
eMetaStore.java:1292)

at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaS
toreClient.java:533)

..

Initially my hunch was that the server was configured to use utf8 as its
default charset and reading in some web forums that had known to cause
the above error.

However even after chaning the character set to latin1 (settings below),
I still see the error - any pointers to what I should change on my mysql
setup would be greatly appreciated.



Thanks,

Pradeep





===============================================

mysql settings related to charset:



grep -i latin1 /etc/my.cnf

character_set_server = latin1

collation_server = latin1_swedish_ci

default_character_set = latin1

Search Discussions

  • Edward Capriolo at May 12, 2010 at 6:56 pm

    On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath wrote:

    Hi,

    I am trying to use mysql_server-5.1.41.0 as the local db for hive following
    instructions at
    http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin#Local_Metastore



    I get the following exception:

    FAILED: Error in metadata: MetaException(message:Got exception:
    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.MySQLSyntaxErrorException: Specified key was too
    long; max key length is 767 bytes

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)

    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)

    ..

    at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:242)

    at
    org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:572)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1295)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRetry(HiveMetaStore.java:229)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaStoreClient.java:533)

    ..

    Initially my hunch was that the server was configured to use utf8 as its
    default charset and reading in some web forums that had known to cause the
    above error.

    However even after chaning the character set to latin1 (settings below), I
    still see the error - any pointers to what I should change on my mysql setup
    would be greatly appreciated.



    Thanks,

    Pradeep





    ===============================================

    mysql settings related to charset:



    grep -i latin1 /etc/my.cnf

    character_set_server = latin1

    collation_server = latin1_swedish_ci

    default_character_set = latin1
    If you Google search this one it comes up often.

    I just built mysql from source 5.1.46. It worked fine. I would suggest
    running 'show variables' and making sure that latin1 is your character set.
    Drop the database just to be safe. If you did build mysql from source, watch
    out, innodb is now a plugin not on by default. ./configure
    --with-plugins=max

    Mysql only fires a warning if you try to create an innodb table and innodb
    is not built in.

    Edward
  • Pradeep Kamath at May 12, 2010 at 11:29 pm
    Here are the settings I see - I am not familiar with how to set mysql
    settings - any help on how I could set the character_set_system setting
    would be appreciated (if this indeed will fix the issue I am seeing):

    mysql -e "show variables" | grep char

    character_set_client latin1

    character_set_connection latin1

    character_set_database latin1

    character_set_filesystem binary

    character_set_results latin1

    character_set_server latin1

    character_set_system utf8 -> this seems to be one seting which is
    utf8 based - how do I change this?

    character_sets_dir /home/y/share/mysql/charsets/



    grep char /etc/my.cnf

    # Default server character set name

    character_set_server = latin1

    default_character_set = latin1



    ________________________________

    From: Edward Capriolo
    Sent: Wednesday, May 12, 2010 11:56 AM
    To: hive-user@hadoop.apache.org
    Subject: Re: Help regarding mysql setup for metastore





    On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath wrote:

    Hi,

    I am trying to use mysql_server-5.1.41.0 as the local db for hive
    following instructions at
    http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin#Local_Meta
    store



    I get the following exception:

    FAILED: Error in metadata: MetaException(message:Got exception:
    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.MySQLSyntaxErrorException: Specified key was
    too long; max key length is 767 bytes

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)

    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)

    ..

    at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:242)

    at
    org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:
    572)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMet
    aStore.java:1295)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMet
    aStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRet
    ry(HiveMetaStore.java:229)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(Hiv
    eMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaS
    toreClient.java:533)

    ..

    Initially my hunch was that the server was configured to use utf8 as its
    default charset and reading in some web forums that had known to cause
    the above error.

    However even after chaning the character set to latin1 (settings below),
    I still see the error - any pointers to what I should change on my mysql
    setup would be greatly appreciated.



    Thanks,

    Pradeep





    ===============================================

    mysql settings related to charset:



    grep -i latin1 /etc/my.cnf

    character_set_server = latin1

    collation_server = latin1_swedish_ci

    default_character_set = latin1

    If you Google search this one it comes up often.

    I just built mysql from source 5.1.46. It worked fine. I would suggest
    running 'show variables' and making sure that latin1 is your character
    set. Drop the database just to be safe. If you did build mysql from
    source, watch out, innodb is now a plugin not on by default. ./configure
    --with-plugins=max

    Mysql only fires a warning if you try to create an innodb table and
    innodb is not built in.

    Edward
  • Sarah Sproehnle at May 12, 2010 at 11:59 pm
    Hi Pradeep,

    Changing the MySQL settings does not change existing tables. You may
    need to convert them:
    http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html
    On Wed, May 12, 2010 at 4:27 PM, Pradeep Kamath wrote:
    Here are the settings I see – I am not familiar with how to set mysql
    settings – any help on how I could set the character_set_system setting
    would be appreciated (if this indeed will fix the issue I am seeing):

    mysql -e "show variables" | grep char

    character_set_client    latin1

    character_set_connection        latin1

    character_set_database  latin1

    character_set_filesystem        binary

    character_set_results   latin1

    character_set_server    latin1

    character_set_system    utf8 -> this seems to be one seting which is utf8
    based – how do I change this?

    character_sets_dir      /home/y/share/mysql/charsets/



    grep char /etc/my.cnf

    # Default server character set name

    character_set_server                = latin1

    default_character_set               = latin1



    ________________________________

    From: Edward Capriolo
    Sent: Wednesday, May 12, 2010 11:56 AM
    To: hive-user@hadoop.apache.org
    Subject: Re: Help regarding mysql setup for metastore





    On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath wrote:

    Hi,

    I am trying to use mysql_server-5.1.41.0 as the local db for hive following
    instructions at
    http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin#Local_Metastore



    I get the following exception:

    FAILED: Error in metadata: MetaException(message:Got exception:
    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.MySQLSyntaxErrorException: Specified key was too
    long; max key length is 767 bytes

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)

    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)

    ..

    at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:242)

    at
    org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:572)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1295)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRetry(HiveMetaStore.java:229)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaStoreClient.java:533)

    ..

    Initially my hunch was that the server was configured to use utf8 as its
    default charset and reading in some web forums that had known to cause the
    above error.

    However even after chaning the character set to latin1 (settings below), I
    still see the error - any pointers to what I should change on my mysql setup
    would be greatly appreciated.



    Thanks,

    Pradeep





    ===============================================

    mysql settings related to charset:



    grep -i latin1 /etc/my.cnf

    character_set_server                = latin1

    collation_server                    = latin1_swedish_ci

    default_character_set               = latin1

    If you Google search this one it comes up often.

    I just built mysql from source 5.1.46. It worked fine. I would suggest
    running 'show variables' and making sure that latin1 is your character set.
    Drop the database just to be safe. If you did build mysql from source, watch
    out, innodb is now a plugin not on by default. ./configure
    --with-plugins=max

    Mysql only fires a warning if you try to create an innodb table and innodb
    is not built in.

    Edward


    --
    get hadoop: cloudera.com/hadoop
    online training: cloudera.com/hadoop-training
    blog: cloudera.com/blog
    twitter: twitter.com/cloudera
  • Pradeep Kamath at May 13, 2010 at 12:38 am
    Thanks Sarah! - you were right - the db was already created prob. with utf8 settings - I dropped it and tried hive again and it works now.

    -----Original Message-----
    From: Sarah Sproehnle
    Sent: Wednesday, May 12, 2010 4:59 PM
    To: hive-user@hadoop.apache.org
    Subject: Re: Help regarding mysql setup for metastore

    Hi Pradeep,

    Changing the MySQL settings does not change existing tables. You may
    need to convert them:
    http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html
    On Wed, May 12, 2010 at 4:27 PM, Pradeep Kamath wrote:
    Here are the settings I see - I am not familiar with how to set mysql
    settings - any help on how I could set the character_set_system setting
    would be appreciated (if this indeed will fix the issue I am seeing):

    mysql -e "show variables" | grep char

    character_set_client    latin1

    character_set_connection        latin1

    character_set_database  latin1

    character_set_filesystem        binary

    character_set_results   latin1

    character_set_server    latin1

    character_set_system    utf8 -> this seems to be one seting which is utf8
    based - how do I change this?

    character_sets_dir      /home/y/share/mysql/charsets/



    grep char /etc/my.cnf

    # Default server character set name

    character_set_server                = latin1

    default_character_set               = latin1



    ________________________________

    From: Edward Capriolo
    Sent: Wednesday, May 12, 2010 11:56 AM
    To: hive-user@hadoop.apache.org
    Subject: Re: Help regarding mysql setup for metastore





    On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath wrote:

    Hi,

    I am trying to use mysql_server-5.1.41.0 as the local db for hive following
    instructions at
    http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin#Local_Metastore



    I get the following exception:

    FAILED: Error in metadata: MetaException(message:Got exception:
    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.MySQLSyntaxErrorException: Specified key was too
    long; max key length is 767 bytes

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)

    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)

    ..

    at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:242)

    at
    org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:572)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1295)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler$24.run(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.executeWithRetry(HiveMetaStore.java:229)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables(HiveMetaStore.java:1292)

    at
    org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaStoreClient.java:533)

    ..

    Initially my hunch was that the server was configured to use utf8 as its
    default charset and reading in some web forums that had known to cause the
    above error.

    However even after chaning the character set to latin1 (settings below), I
    still see the error - any pointers to what I should change on my mysql setup
    would be greatly appreciated.



    Thanks,

    Pradeep





    ===============================================

    mysql settings related to charset:



    grep -i latin1 /etc/my.cnf

    character_set_server                = latin1

    collation_server                    = latin1_swedish_ci

    default_character_set               = latin1

    If you Google search this one it comes up often.

    I just built mysql from source 5.1.46. It worked fine. I would suggest
    running 'show variables' and making sure that latin1 is your character set.
    Drop the database just to be safe. If you did build mysql from source, watch
    out, innodb is now a plugin not on by default. ./configure
    --with-plugins=max

    Mysql only fires a warning if you try to create an innodb table and innodb
    is not built in.

    Edward


    --
    get hadoop: cloudera.com/hadoop
    online training: cloudera.com/hadoop-training
    blog: cloudera.com/blog
    twitter: twitter.com/cloudera

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedMay 12, '10 at 6:16p
activeMay 13, '10 at 12:38a
posts5
users3
websitehive.apache.org

People

Translate

site design / logo © 2021 Grokbase