Cannot create database with Hive

Question

I'm using Hive with MySQL as meta store but somehow cannot create database. Right after Hive installation, I was able to create a database but not any more. I get following error but does anyone have any idea what the cause of this issue is?

hive> create database testingyou;
FAILED: Error in metadata: MetaException(message:javax.jdo.JDOUserException: 
Could not create "increment"/"table" value-generation container `SEQUENCE_TABLE` 
since autoCreate flags do not allow it. 

NestedThrowables:
org.datanucleus.exceptions.NucleusUserException: Could not create 
"increment"/"table" value-generation container `SEQUENCE_TABLE` 
since autoCreate flags do not allow it. )
FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask

Jeroen Vannevel · Accepted Answer · 2014-04-16 11:26:02Z

1

I had the same problem and found out I forgot to uncomment a line. Full steps:

Find mysql config file my.cnf (/etc/my.cnf)
Search binlog_formt, you probably find it was commented with #
Open it and try your hive again

edited Apr 16, 2014 at 11:26

Jeroen Vannevel

44.6k25 gold badges114 silver badges174 bronze badges

answered Apr 16, 2014 at 10:06

o0ops

911 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeroen Vannevel Over a year ago

@DaImTo: why shouldn't this be considered an answer?

Jason H · Accepted Answer · 2016-02-24 21:26:21Z

One possible cause is a known bug in Hive where Data Nucleus forgets to escape the database name such as 'db-name' or [db-name]. When the database catalog name has dashes as my example does, HiveServer2 won't be able to connect to it because of a SQL syntax error when issuing a critical command used to start hive:

Hive metastore logging shows

2016-01-16 02:14:10,228 DEBUG [main]: Datastore.Native (Log4JLogger.java:debug(58)) - SELECT NEXT_VAL FROM db-name.dbo.SEQUENCE_TABLE WHERE SEQUENCE_NAME=<'org.apache.hadoop.hive.metastore.model.MDatabase'>

2016-01-16 02:14:10,233 INFO  [main]: DataNucleus.ValueGeneration (Log4JLogger.java:info(77)) - Error encountered allocating block of IDs : Couldnt obtain a new sequence (unique id) : Incorrect syntax near '-'.

2016-01-16 02:14:10,233 INFO  [main]: DataNucleus.ValueGeneration (Log4JLogger.java:info(77)) - Error encountered allocating block of IDs : Couldnt obtain a new sequence (unique id) : Incorrect syntax near '-'.

That issue is is being tracked in https://issues.apache.org/jira/browse/HIVE-6113. A fix will be available as part of Hive 2.0, which is not out for all at the moment.

Until then, make sure you avoid Dashes or Hyphens in the database catalog name.

usomillp · Accepted Answer · 2014-03-12 04:58:20Z

0

check your mysql settings.

binlog_format

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

binlog_format=STATEMENT was same error

answered Mar 12, 2014 at 4:58

usomillp

1

Collectives™ on Stack Overflow

Cannot create database with Hive

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related