You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Pradeep Kamath <pr...@yahoo-inc.com> on 2010/05/12 20:14:07 UTC
Help regarding mysql setup for metastore
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
RE: Help regarding mysql setup for metastore
Posted by Pradeep Kamath <pr...@yahoo-inc.com>.
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 [mailto:sarah@cloudera.com]
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 <pr...@yahoo-inc.com> 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 [mailto:edlinuxguru@gmail.com]
> 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 <pr...@yahoo-inc.com>
> 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
Re: Help regarding mysql setup for metastore
Posted by Sarah Sproehnle <sa...@cloudera.com>.
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 <pr...@yahoo-inc.com> 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 [mailto:edlinuxguru@gmail.com]
> 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 <pr...@yahoo-inc.com>
> 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
RE: Help regarding mysql setup for metastore
Posted by Pradeep Kamath <pr...@yahoo-inc.com>.
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 [mailto:edlinuxguru@gmail.com]
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 <pr...@yahoo-inc.com>
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
Re: Help regarding mysql setup for metastore
Posted by Edward Capriolo <ed...@gmail.com>.
On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath <pr...@yahoo-inc.com>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