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

Answer 1

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

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

Answer 2

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.