You are viewing a plain text version of this content. The canonical link for it is here.
Posted to common-user@hadoop.apache.org by Ryan Shih <ry...@gmail.com> on 2009/02/24 02:29:55 UTC
mysql metastore problems
Hi, I'm having some problems setting up the metastore using mysql. I've
browsed the message archives, but don't see anything that helps. My
configuration files, look like:
**<<hive-site.xml>>**
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/ryan/hive/branch-0.2/install/custom/</value>
<description>where custom serdes live </description>
</property>
</configuration>
**<<jpox.properties>>**
javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
org.jpox.validateTables=false
org.jpox.validateColumns=false
org.jpox.validateConstraints=false
org.jpox.storeManagerType=rdbms
org.jpox.autoCreateSchema=true
org.jpox.autoStartMechanismMode=checked
org.jpox.transactionIsolation=read_committed
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.NontransactionalRead=true
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=hive_user
javax.jdo.option.ConnectionPassword=hive_pass
org.jpox.cache.level2=true
org.jpox.cache.level2.type=SOFT
And then with just the default hive-default.xml file. I haven't worked with
JPOX tables before, but I'm under the impression that this will
automatically get created from the autoCreateSchema flag. Has anyone had any
luck with this?
I'm getting the following error:
hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
hive> create table test_table (id INT, name STRING);
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding
class org.apache.hadoop.hive.metastore.model.MDatabase to list of
persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
at
org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
at org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
at org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
at
org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
at
org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
at
org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
at
org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
at
org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
at
org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
at
org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
at
org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
at
org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
at
org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
at
org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
at org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
at
org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
at
org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
at
org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
at
org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 61 more
Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 55 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask
Time taken: 1.883 seconds
Re: mysql metastore problems
Posted by Ryan Shih <ry...@gmail.com>.
Please ignore the last message, I must have responded to an older hive
message and it put in the Hadoop core mailing list instead. I've reposted on
the hive user list.
On Mon, Feb 23, 2009 at 5:29 PM, Ryan Shih <ry...@gmail.com> wrote:
> Hi, I'm having some problems setting up the metastore using mysql. I've
> browsed the message archives, but don't see anything that helps. My
> configuration files, look like:
> **<<hive-site.xml>>**
> <?xml version="1.0"?>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> <configuration>
> <property>
> <name>hive.metastore.local</name>
> <value>true</value>
> <description>this is local store</description>
> </property>
> <property>
> <name>hive.metastore.warehouse.dir</name>
> <value>/user/hive/warehouse</value>
> <description>default location for Hive tables</description>
> </property>
> <property>
> <name>hive.aux.jars.path</name>
> <value>/home/ryan/hive/branch-0.2/install/custom/</value>
> <description>where custom serdes live </description>
> </property>
> </configuration>
>
>
> **<<jpox.properties>>**
>
> javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
> org.jpox.validateTables=false
> org.jpox.validateColumns=false
> org.jpox.validateConstraints=false
> org.jpox.storeManagerType=rdbms
> org.jpox.autoCreateSchema=true
> org.jpox.autoStartMechanismMode=checked
> org.jpox.transactionIsolation=read_committed
> javax.jdo.option.DetachAllOnCommit=true
> javax.jdo.option.NontransactionalRead=true
> javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
>
> javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
> javax.jdo.option.ConnectionUserName=hive_user
> javax.jdo.option.ConnectionPassword=hive_pass
> org.jpox.cache.level2=true
> org.jpox.cache.level2.type=SOFT
>
> And then with just the default hive-default.xml file. I haven't worked with
> JPOX tables before, but I'm under the impression that this will
> automatically get created from the autoCreateSchema flag. Has anyone had any
> luck with this?
>
> I'm getting the following error:
> hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
> Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
> hive> create table test_table (id INT, name STRING);
> FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding
> class org.apache.hadoop.hive.metastore.model.MDatabase to list of
> persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> Source)
> at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
> at
> org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
> at
> org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
> at
> org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
> at
> org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
> at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
> at
> org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
> at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
> at
> org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
> at
> org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
> at
> org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
> at
> org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
> at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
> at
> org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
> at
> org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
> at
> org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
> at
> org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
> at
> org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
> at
> org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
> at
> org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
> at
> org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
> at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
> at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
> at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
> at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
> at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
> at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
> Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 61 more
> Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> ... 55 more
>
> NestedThrowables:
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask
> Time taken: 1.883 seconds
>
>
>
>
>
>
Re: mysql metastore problems
Posted by Prasad Chakka <pr...@facebook.com>.
I think config values in hive-default.xml override the ones in jpox.properties so if you had stale or incorrect props in hive-default but correct ones in jpox.properties then it would be a problem.
________________________________
From: Ryan Shih <ry...@gmail.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Tue, 24 Feb 2009 18:35:10 -0800
To: <hi...@hadoop.apache.org>
Subject: Re: mysql metastore problems
Ok, just for posterity, I seemed to have gotten this working now. It seems that specifying it in jpox.properties does not work. That is strange since when I run it with verbose output, it says that it found jpox.properties but I guess it doesn't use the values set there. hive-default.xml will work, but since I like to keep the defaults there, putting it in hive-site.xml will also work. Below is the exact hive-site.xml I used to get this to work for branch-0.2/hadoop 18.3. Again, this is probably just an oversight, but thought it wouldn't hurt to post in case it helps anyone.
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive_user</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive_pass</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
</configuration>
On Tue, Feb 24, 2009 at 2:42 AM, Ryan Shih <ry...@gmail.com> wrote:
Nope, all the jpox stuff is specified in jpox.properties -- all basically out of the box stuff. I had some issues getting the auxilliary jar path to work when putting it in hive-site and even directly in hive-default (I had to set the export explicitly into the hive-config) so there could be some problems there in loading in these things. Still, some ppl seem to have gotten this working, or at least gotten further, so it's likely I'm just overlooking something.
I attempted the metastore/build.xml but that didn't work either. In the end though, I'll need this to be possible from the install directory. I'm using Hadoop 18.3 with the 0.2 branch build if that is helpful.
Prasad Chakka wrote:
Are there any jpox related properties in hive-default.xml? In DEBUG mode, all the parameters that get passed to JPOX get logged. Could you send me those?
As I said, alternatively you can try to create the schema using ant target in metastore/build.xml.
Prasad
________________________________
From: Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> >
Reply-To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> >
Date: Mon, 23 Feb 2009 18:25:19 -0800
To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> >
Subject: Re: mysql metastore problems
Thanks Prasad. That is the website I got my parameters from, specifically for the local metastore. Still don't seem to have any luck at the moment even setting the conf params and doing another build install. I'll probably futz around a little more and then start looking into the code base. If anyone's been sucessful at getting this going, would love to hear how you made that happen.
Ryan
On Mon, Feb 23, 2009 at 5:37 PM, Prasad Chakka <prasad@facebook.com <ht...@facebook.com> > wrote:
Can you use the properties from here? http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin
Default JPOX options with only changes from above should work since unit tests do create the database and schema.
Also there is an ant target to create schema in metastore/build.xml that you can try to create the schema.
Prasad
From: Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> <ht...@gmail.com> >
Reply-To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> <ht...@hadoop.apache.org> >
Date: Mon, 23 Feb 2009 17:31:22 -0800
To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> <ht...@hadoop.apache.org> >
Subject: Fwd: mysql metastore problems
---------- Forwarded message ----------
From: Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> <ht...@gmail.com> >
Date: Mon, Feb 23, 2009 at 5:29 PM
Subject: mysql metastore problems
To: core-user@hadoop.apache.org <ht...@hadoop.apache.org> <ht...@hadoop.apache.org>
Hi, I'm having some problems setting up the metastore using mysql. I've browsed the message archives, but don't see anything that helps. My configuration files, look like:
**<<hive-site.xml>>**
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/ryan/hive/branch-0.2/install/custom/</value>
<description>where custom serdes live </description>
</property>
</configuration>
**<<jpox.properties>>**
javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
org.jpox.validateTables=false
org.jpox.validateColumns=false
org.jpox.validateConstraints=false
org.jpox.storeManagerType=rdbms
org.jpox.autoCreateSchema=true
org.jpox.autoStartMechanismMode=checked
org.jpox.transactionIsolation=read_committed
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.NontransactionalRead=true
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=hive_user
javax.jdo.option.ConnectionPassword=hive_pass
org.jpox.cache.level2=true
org.jpox.cache.level2.type=SOFT
And then with just the default hive-default.xml file. I haven't worked with JPOX tables before, but I'm under the impression that this will automatically get created from the autoCreateSchema flag. Has anyone had any luck with this?
I'm getting the following error:
hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
hive> create table test_table (id INT, name STRING);
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding class org.apache.hadoop.hive.metastore.model.MDatabase to list of persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
at org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
at org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
at org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
at org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
at org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
at org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
at org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
at org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
at org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
at org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
at org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
at org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
at org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
at org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
at org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
at org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
at org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 61 more
Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 55 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
Time taken: 1.883 seconds
Re: mysql metastore problems
Posted by Ryan Shih <ry...@gmail.com>.
Ok, just for posterity, I seemed to have gotten this working now. It seems
that specifying it in jpox.properties does not work. That is strange since
when I run it with verbose output, it says that it found jpox.properties but
I guess it doesn't use the values set there. hive-default.xml will work, but
since I like to keep the defaults there, putting it in hive-site.xml will
also work. Below is the exact hive-site.xml I used to get this to work for
branch-0.2/hadoop 18.3. Again, this is probably just an oversight, but
thought it wouldn't hurt to post in case it helps anyone.
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive_user</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive_pass</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
</configuration>
On Tue, Feb 24, 2009 at 2:42 AM, Ryan Shih <ry...@gmail.com> wrote:
> Nope, all the jpox stuff is specified in jpox.properties -- all basically
> out of the box stuff. I had some issues getting the auxilliary jar path to
> work when putting it in hive-site and even directly in hive-default (I had
> to set the export explicitly into the hive-config) so there could be some
> problems there in loading in these things. Still, some ppl seem to have
> gotten this working, or at least gotten further, so it's likely I'm just
> overlooking something.
>
> I attempted the metastore/build.xml but that didn't work either. In the end
> though, I'll need this to be possible from the install directory. I'm using
> Hadoop 18.3 with the 0.2 branch build if that is helpful.
>
>
> Prasad Chakka wrote:
>
> Are there any jpox related properties in hive-default.xml? In DEBUG mode,
> all the parameters that get passed to JPOX get logged. Could you send me
> those?
>
> As I said, alternatively you can try to create the schema using ant target
> in metastore/build.xml.
>
> Prasad
>
> ------------------------------
> *From: *Ryan Shih <ry...@gmail.com>
> *Reply-To: *<hi...@hadoop.apache.org>
> *Date: *Mon, 23 Feb 2009 18:25:19 -0800
> *To: *<hi...@hadoop.apache.org>
> *Subject: *Re: mysql metastore problems
>
> Thanks Prasad. That is the website I got my parameters from, specifically
> for the local metastore. Still don't seem to have any luck at the moment
> even setting the conf params and doing another build install. I'll probably
> futz around a little more and then start looking into the code base. If
> anyone's been sucessful at getting this going, would love to hear how you
> made that happen.
>
> Ryan
>
> On Mon, Feb 23, 2009 at 5:37 PM, Prasad Chakka <pr...@facebook.com>
> wrote:
>
> Can you use the properties from here?
> http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin
> Default JPOX options with only changes from above should work since unit
> tests do create the database and schema.
>
> Also there is an ant target to create schema in metastore/build.xml that
> you can try to create the schema.
>
> Prasad
>
>
> *From: *Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> >
> *Reply-To: *<hive-user@hadoop.apache.org <
> http://hive-user@hadoop.apache.org> >
> *Date: *Mon, 23 Feb 2009 17:31:22 -0800
> *To: *<hive-user@hadoop.apache.org <ht...@hadoop.apache.org> >
> *Subject: *Fwd: mysql metastore problems
>
>
>
>
> ---------- Forwarded message ----------
> From: *Ryan Shih* <ryan.shih@gmail.com <ht...@gmail.com> >
> Date: Mon, Feb 23, 2009 at 5:29 PM
> Subject: mysql metastore problems
> To: core-user@hadoop.apache.org <ht...@hadoop.apache.org>
>
>
> Hi, I'm having some problems setting up the metastore using mysql. I've
> browsed the message archives, but don't see anything that helps. My
> configuration files, look like:
> **<<hive-site.xml>>**
> <?xml version="1.0"?>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> <configuration>
> <property>
> <name>hive.metastore.local</name>
> <value>true</value>
> <description>this is local store</description>
> </property>
> <property>
> <name>hive.metastore.warehouse.dir</name>
> <value>/user/hive/warehouse</value>
> <description>default location for Hive tables</description>
> </property>
> <property>
> <name>hive.aux.jars.path</name>
> <value>/home/ryan/hive/branch-0.2/install/custom/</value>
> <description>where custom serdes live </description>
> </property>
> </configuration>
>
>
> **<<jpox.properties>>**
>
> javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
> org.jpox.validateTables=false
> org.jpox.validateColumns=false
> org.jpox.validateConstraints=false
> org.jpox.storeManagerType=rdbms
> org.jpox.autoCreateSchema=true
> org.jpox.autoStartMechanismMode=checked
> org.jpox.transactionIsolation=read_committed
> javax.jdo.option.DetachAllOnCommit=true
> javax.jdo.option.NontransactionalRead=true
> javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
>
> javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
> javax.jdo.option.ConnectionUserName=hive_user
> javax.jdo.option.ConnectionPassword=hive_pass
> org.jpox.cache.level2=true
> org.jpox.cache.level2.type=SOFT
>
> And then with just the default hive-default.xml file. I haven't worked with
> JPOX tables before, but I'm under the impression that this will
> automatically get created from the autoCreateSchema flag. Has anyone had any
> luck with this?
>
> I'm getting the following error:
> hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
> Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
> hive> create table test_table (id INT, name STRING);
> FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding
> class org.apache.hadoop.hive.metastore.model.MDatabase to list of
> persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> Source)
> at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
> at
> org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
> at
> org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
> at
> org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
> at
> org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
> at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
> at
> org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
> at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
> at
> org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
> at
> org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
> at
> org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
> at
> org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
> at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
> at
> org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
> at
> org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
> at
> org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
> at
> org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
> at
> org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
> at
> org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
> at
> org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
> at
> org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
> at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
> at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
> at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
> at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
> at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
> at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
> Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 61 more
> Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> ... 55 more
>
> NestedThrowables:
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask
> Time taken: 1.883 seconds
>
>
>
>
>
>
>
>
>
>
>
Re: mysql metastore problems
Posted by Prasad Chakka <pr...@facebook.com>.
Are there any jpox related properties in hive-default.xml? In DEBUG mode, all the parameters that get passed to JPOX get logged. Could you send me those?
As I said, alternatively you can try to create the schema using ant target in metastore/build.xml.
Prasad
________________________________
From: Ryan Shih <ry...@gmail.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Mon, 23 Feb 2009 18:25:19 -0800
To: <hi...@hadoop.apache.org>
Subject: Re: mysql metastore problems
Thanks Prasad. That is the website I got my parameters from, specifically for the local metastore. Still don't seem to have any luck at the moment even setting the conf params and doing another build install. I'll probably futz around a little more and then start looking into the code base. If anyone's been sucessful at getting this going, would love to hear how you made that happen.
Ryan
On Mon, Feb 23, 2009 at 5:37 PM, Prasad Chakka <pr...@facebook.com> wrote:
Can you use the properties from here? http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin
Default JPOX options with only changes from above should work since unit tests do create the database and schema.
Also there is an ant target to create schema in metastore/build.xml that you can try to create the schema.
Prasad
From: Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> >
Reply-To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> >
Date: Mon, 23 Feb 2009 17:31:22 -0800
To: <hive-user@hadoop.apache.org <ht...@hadoop.apache.org> >
Subject: Fwd: mysql metastore problems
---------- Forwarded message ----------
From: Ryan Shih <ryan.shih@gmail.com <ht...@gmail.com> >
Date: Mon, Feb 23, 2009 at 5:29 PM
Subject: mysql metastore problems
To: core-user@hadoop.apache.org <ht...@hadoop.apache.org>
Hi, I'm having some problems setting up the metastore using mysql. I've browsed the message archives, but don't see anything that helps. My configuration files, look like:
**<<hive-site.xml>>**
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/ryan/hive/branch-0.2/install/custom/</value>
<description>where custom serdes live </description>
</property>
</configuration>
**<<jpox.properties>>**
javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
org.jpox.validateTables=false
org.jpox.validateColumns=false
org.jpox.validateConstraints=false
org.jpox.storeManagerType=rdbms
org.jpox.autoCreateSchema=true
org.jpox.autoStartMechanismMode=checked
org.jpox.transactionIsolation=read_committed
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.NontransactionalRead=true
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=hive_user
javax.jdo.option.ConnectionPassword=hive_pass
org.jpox.cache.level2=true
org.jpox.cache.level2.type=SOFT
And then with just the default hive-default.xml file. I haven't worked with JPOX tables before, but I'm under the impression that this will automatically get created from the autoCreateSchema flag. Has anyone had any luck with this?
I'm getting the following error:
hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
hive> create table test_table (id INT, name STRING);
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding class org.apache.hadoop.hive.metastore.model.MDatabase to list of persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
at org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
at org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
at org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
at org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
at org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
at org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
at org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
at org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
at org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
at org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
at org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
at org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
at org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
at org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
at org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
at org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
at org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 61 more
Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 55 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
Time taken: 1.883 seconds
Re: mysql metastore problems
Posted by Ryan Shih <ry...@gmail.com>.
Thanks Prasad. That is the website I got my parameters from, specifically
for the local metastore. Still don't seem to have any luck at the moment
even setting the conf params and doing another build install. I'll probably
futz around a little more and then start looking into the code base. If
anyone's been sucessful at getting this going, would love to hear how you
made that happen.
Ryan
On Mon, Feb 23, 2009 at 5:37 PM, Prasad Chakka <pr...@facebook.com> wrote:
> Can you use the properties from here?
> http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin
> Default JPOX options with only changes from above should work since unit
> tests do create the database and schema.
>
> Also there is an ant target to create schema in metastore/build.xml that
> you can try to create the schema.
>
> Prasad
>
> *From: *Ryan Shih <ry...@gmail.com>
> *Reply-To: *<hi...@hadoop.apache.org>
> *Date: *Mon, 23 Feb 2009 17:31:22 -0800
> *To: *<hi...@hadoop.apache.org>
> *Subject: *Fwd: mysql metastore problems
>
>
>
>
> ---------- Forwarded message ----------
> From: *Ryan Shih* <ry...@gmail.com>
> Date: Mon, Feb 23, 2009 at 5:29 PM
> Subject: mysql metastore problems
> To: core-user@hadoop.apache.org
>
>
> Hi, I'm having some problems setting up the metastore using mysql. I've
> browsed the message archives, but don't see anything that helps. My
> configuration files, look like:
> **<<hive-site.xml>>**
> <?xml version="1.0"?>
> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
>
> <configuration>
> <property>
> <name>hive.metastore.local</name>
> <value>true</value>
> <description>this is local store</description>
> </property>
> <property>
> <name>hive.metastore.warehouse.dir</name>
> <value>/user/hive/warehouse</value>
> <description>default location for Hive tables</description>
> </property>
> <property>
> <name>hive.aux.jars.path</name>
> <value>/home/ryan/hive/branch-0.2/install/custom/</value>
> <description>where custom serdes live </description>
> </property>
> </configuration>
>
>
> **<<jpox.properties>>**
>
> javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
> org.jpox.validateTables=false
> org.jpox.validateColumns=false
> org.jpox.validateConstraints=false
> org.jpox.storeManagerType=rdbms
> org.jpox.autoCreateSchema=true
> org.jpox.autoStartMechanismMode=checked
> org.jpox.transactionIsolation=read_committed
> javax.jdo.option.DetachAllOnCommit=true
> javax.jdo.option.NontransactionalRead=true
> javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
>
> javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
> javax.jdo.option.ConnectionUserName=hive_user
> javax.jdo.option.ConnectionPassword=hive_pass
> org.jpox.cache.level2=true
> org.jpox.cache.level2.type=SOFT
>
> And then with just the default hive-default.xml file. I haven't worked with
> JPOX tables before, but I'm under the impression that this will
> automatically get created from the autoCreateSchema flag. Has anyone had any
> luck with this?
>
> I'm getting the following error:
> hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
> Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
> hive> create table test_table (id INT, name STRING);
> FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding
> class org.apache.hadoop.hive.metastore.model.MDatabase to list of
> persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> Source)
> at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
> at
> org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
> at
> org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
> at
> org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
> at
> org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
> at
> org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
> at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
> at
> org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
> at
> org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
> at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
> at
> org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
> at
> org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
> at
> org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
> at
> org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
> at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
> at
> org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
> at
> org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
> at
> org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
> at
> org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
> at
> org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
> at
> org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
> at
> org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
> at
> org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
> at
> org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
> at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
> at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
> at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
> at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
> at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
> at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
> at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
> Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ... 61 more
> Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
> Source)
> at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> ... 55 more
>
> NestedThrowables:
> java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
> FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask
> Time taken: 1.883 seconds
>
>
>
>
>
>
>
>
Re: mysql metastore problems
Posted by Prasad Chakka <pr...@facebook.com>.
Can you use the properties from here? http://wiki.apache.org/hadoop/Hive/AdminManual/MetastoreAdmin
Default JPOX options with only changes from above should work since unit tests do create the database and schema.
Also there is an ant target to create schema in metastore/build.xml that you can try to create the schema.
Prasad
From: Ryan Shih <ry...@gmail.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Mon, 23 Feb 2009 17:31:22 -0800
To: <hi...@hadoop.apache.org>
Subject: Fwd: mysql metastore problems
---------- Forwarded message ----------
From: Ryan Shih <ry...@gmail.com>
Date: Mon, Feb 23, 2009 at 5:29 PM
Subject: mysql metastore problems
To: core-user@hadoop.apache.org
Hi, I'm having some problems setting up the metastore using mysql. I've browsed the message archives, but don't see anything that helps. My configuration files, look like:
**<<hive-site.xml>>**
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/ryan/hive/branch-0.2/install/custom/</value>
<description>where custom serdes live </description>
</property>
</configuration>
**<<jpox.properties>>**
javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
org.jpox.validateTables=false
org.jpox.validateColumns=false
org.jpox.validateConstraints=false
org.jpox.storeManagerType=rdbms
org.jpox.autoCreateSchema=true
org.jpox.autoStartMechanismMode=checked
org.jpox.transactionIsolation=read_committed
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.NontransactionalRead=true
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=hive_user
javax.jdo.option.ConnectionPassword=hive_pass
org.jpox.cache.level2=true
org.jpox.cache.level2.type=SOFT
And then with just the default hive-default.xml file. I haven't worked with JPOX tables before, but I'm under the impression that this will automatically get created from the autoCreateSchema flag. Has anyone had any luck with this?
I'm getting the following error:
hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
hive> create table test_table (id INT, name STRING);
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding class org.apache.hadoop.hive.metastore.model.MDatabase to list of persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
at org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
at org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
at org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
at org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
at org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
at org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
at org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
at org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
at org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
at org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
at org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
at org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
at org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
at org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
at org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
at org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
at org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
at org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
at org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 61 more
Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 55 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
Time taken: 1.883 seconds
Fwd: mysql metastore problems
Posted by Ryan Shih <ry...@gmail.com>.
---------- Forwarded message ----------
From: Ryan Shih <ry...@gmail.com>
Date: Mon, Feb 23, 2009 at 5:29 PM
Subject: mysql metastore problems
To: core-user@hadoop.apache.org
Hi, I'm having some problems setting up the metastore using mysql. I've
browsed the message archives, but don't see anything that helps. My
configuration files, look like:
**<<hive-site.xml>>**
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>this is local store</description>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
<description>default location for Hive tables</description>
</property>
<property>
<name>hive.aux.jars.path</name>
<value>/home/ryan/hive/branch-0.2/install/custom/</value>
<description>where custom serdes live </description>
</property>
</configuration>
**<<jpox.properties>>**
javax.jdo.PersistenceManagerFactoryClass=org.jpox.PersistenceManagerFactoryImpl
org.jpox.validateTables=false
org.jpox.validateColumns=false
org.jpox.validateConstraints=false
org.jpox.storeManagerType=rdbms
org.jpox.autoCreateSchema=true
org.jpox.autoStartMechanismMode=checked
org.jpox.transactionIsolation=read_committed
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.NontransactionalRead=true
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionURL=jdbc:mysql://localhost/hive_metastore?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=hive_user
javax.jdo.option.ConnectionPassword=hive_pass
org.jpox.cache.level2=true
org.jpox.cache.level2.type=SOFT
And then with just the default hive-default.xml file. I haven't worked with
JPOX tables before, but I'm under the impression that this will
automatically get created from the autoCreateSchema flag. Has anyone had any
luck with this?
I'm getting the following error:
hive> ryan@dali:~/hive/branch-0.2/install/bin$ hive
Hive history file=/tmp/ryan/hive_job_log_ryan_200902231722_1088538316.txt
hive> create table test_table (id INT, name STRING);
FAILED: Error in metadata: javax.jdo.JDODataStoreException: Error adding
class org.apache.hadoop.hive.metastore.model.MDatabase to list of
persistence-managed classes : Table/View 'JPOX_TABLES' does not exist.
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:324)
at
org.jpox.store.rdbms.SQLController.getStatementForQuery(SQLController.java:263)
at org.jpox.store.rdbms.table.SchemaTable.hasClass(SchemaTable.java:280)
at org.jpox.store.rdbms.table.SchemaTable.addClass(SchemaTable.java:222)
at
org.jpox.store.rdbms.SchemaAutoStarter.addClass(SchemaAutoStarter.java:255)
at
org.jpox.store.AbstractStoreManager.registerStoreData(AbstractStoreManager.java:363)
at org.jpox.store.rdbms.RDBMSManager.access$3000(RDBMSManager.java:171)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTable(RDBMSManager.java:3001)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTables(RDBMSManager.java:2804)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3098)
at
org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2729)
at
org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2609)
at org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:825)
at
org.jpox.store.AbstractStoreManager.addClass(AbstractStoreManager.java:624)
at
org.jpox.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:343)
at
org.jpox.store.rdbms.RDBMSManager.getPropertiesForGenerator(RDBMSManager.java:1630)
at
org.jpox.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:945)
at org.jpox.ObjectManagerImpl.newObjectId(ObjectManagerImpl.java:2473)
at
org.jpox.state.JDOStateManagerImpl.setIdentity(JDOStateManagerImpl.java:792)
at
org.jpox.state.JDOStateManagerImpl.initialiseForPersistentNew(JDOStateManagerImpl.java:467)
at
org.jpox.state.StateManagerFactory.newStateManagerForPersistentNew(StateManagerFactory.java:151)
at
org.jpox.ObjectManagerImpl.persistObjectInternal(ObjectManagerImpl.java:1230)
at org.jpox.ObjectManagerImpl.persistObject(ObjectManagerImpl.java:1077)
at
org.jpox.jdo.AbstractPersistenceManager.jdoMakePersistent(AbstractPersistenceManager.java:666)
at
org.jpox.jdo.AbstractPersistenceManager.makePersistent(AbstractPersistenceManager.java:691)
at
org.apache.hadoop.hive.metastore.ObjectStore.createDatabase(ObjectStore.java:272)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:145)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:115)
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:100)
at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:73)
at
org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:785)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:798)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:254)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:740)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:97)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:238)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:174)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:155)
at org.apache.hadoop.mapred.JobShell.run(JobShell.java:54)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at org.apache.hadoop.mapred.JobShell.main(JobShell.java:68)
Caused by: java.sql.SQLException: Table/View 'JPOX_TABLES' does not exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 61 more
Caused by: ERROR 42X05: Table/View 'JPOX_TABLES' does not exist.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
Source)
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
Source)
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 55 more
NestedThrowables:
java.sql.SQLSyntaxErrorException: Table/View 'JPOX_TABLES' does not exist.
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask
Time taken: 1.883 seconds