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:[email protected]] Sent: Wednesday, May 12, 2010 4:59 PM To: [email protected] 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 <[email protected]> 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:[email protected]] > Sent: Wednesday, May 12, 2010 11:56 AM > To: [email protected] > Subject: Re: Help regarding mysql setup for metastore > > > > > > On Wed, May 12, 2010 at 2:14 PM, Pradeep Kamath <[email protected]> > 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
