CODEWITHSUNDEEP
mysqlhadoophiveintegration
scarcer
scarcer

Reputation: 233

Creating a Hive table automatically changes charset settings of mysql metastore

I configured Hive to use a mysql metastore. It works fine.

But when I try to drop a table, I get some errors like below.

FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error(s) were found while auto-creating/validating the datastore for classes. The errors are printed in the log, and are attached to this exception.
NestedThrowables:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

I did some research, google and official faq tell me that changing mysql character set settings will fix this.

My mysql settings as below 

mysql> show variables like '%char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.02 sec)

I changed all character set settings from utf8 to latin1, except character_set_filesystem, character_set_system, character_sets_dir.

Even do so, I still get the same errors when I try to drop tables. And worse, whenever I create a new table, the mysql character set settings will be reset to utf-8.

Anyone can help please?

Upvotes: 2

Views: 3501

Answers (1)

user2331700
user2331700

Reputation: 51

the problem is that in hive-site.xml it use an other database in creation 'hive' in the bellow

 <property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
 </property>

change the database name to 'metastore' that you hade already create in mysql like this

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true</value>
</property>

hope that this regle the problem I had the same problem and I fixed it like this. freindly Matouk

Upvotes: 5

Related Questions