OK not surprising the script does not have a means to drop the object before creating it so the script is recreating the object again!
So your best bet it to drop metastoreDB and recreate it as empty and run your script again. HTH Mich Talebzadeh http://talebzadehmich.wordpress.com Publications due shortly: Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. -----Original Message----- From: Amith sha [mailto:amithsh...@gmail.com] Sent: 10 March 2015 08:29 To: user@hive.apache.org Subject: Re: hive 1.1.0 Error while Metastore starting after executing this command mysql>use metastoreDB mysql>source hive-schema--0.14.0.mysql.sql i am getting this ERROR 1061 (42000): Duplicate key name 'PCS_STATS_IDX' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 1050 (42S01): Table 'TXNS' already exists ERROR 1050 (42S01): Table 'TXN_COMPONENTS' already exists ERROR 1050 (42S01): Table 'COMPLETED_TXN_COMPONENTS' already exists ERROR 1050 (42S01): Table 'NEXT_TXN_ID' already exists Query OK, 1 row affected (0.17 sec) ERROR 1050 (42S01): Table 'HIVE_LOCKS' already exists ERROR 1061 (42000): Duplicate key name 'HL_TXNID_IDX' ERROR 1050 (42S01): Table 'NEXT_LOCK_ID' already exists Query OK, 1 row affected (0.14 sec) ERROR 1050 (42S01): Table 'COMPACTION_QUEUE' already exists ERROR 1050 (42S01): Table 'NEXT_COMPACTION_QUEUE_ID' already exists Query OK, 1 row affected (0.13 sec) ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Thanks & Regards Amithsha On Tue, Mar 10, 2015 at 1:53 PM, Mich Talebzadeh <m...@peridale.co.uk> wrote: > From the sql code do you know which table and primary key it is complaining? > > I have used the script for Oracle 11g and modified one work with SAP Sybase > ASE 15.7 and they both worked fine. > > > HTH > > > Mich Talebzadeh > > http://talebzadehmich.wordpress.com > > Publications due shortly: > Creating in-memory Data Grid for Trading Systems with Oracle TimesTen > and Coherence Cache > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this message > shall not be understood as given or endorsed by Peridale Ltd, its > subsidiaries or their employees, unless expressly so stated. It is the > responsibility of the recipient to ensure that this email is virus free, > therefore neither Peridale Ltd, its subsidiaries nor their employees accept > any responsibility. > > > -----Original Message----- > From: Amith sha [mailto:amithsh...@gmail.com] > Sent: 10 March 2015 07:36 > To: user@hive.apache.org > Subject: Re: hive 1.1.0 Error while Metastore starting > > Hi Srinivas > > Have used hive-schema-0.14.0 but same error found > > > 2015-03-10 13:04:03,829 ERROR [main]: DataNucleus.Datastore > (Log4JLogger.java:error(115)) - An exception was thrown while > adding/validating class(es) : Specified key was too long; max key > length is 767 bytes > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was > too long; max key length is 767 bytes > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:526) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) > > > > Thanks & Regards > Amithsha > > > On Tue, Mar 10, 2015 at 1:02 PM, Amith sha <amithsh...@gmail.com> wrote: >> ok thank you Srinivas wil try it and reply now Thanks & Regards >> Amithsha >> >> >> On Tue, Mar 10, 2015 at 12:59 PM, Srinivas Thunga >> <srinivas.thu...@gmail.com> wrote: >>> i am thinking to run hive-schema-0.14.0.mysql.sql. it will have 54 tables. >>> >>> Thanks & Regards, >>> >>> Srinivas T >>> >>> On Tue, Mar 10, 2015 at 12:55 PM, Amith sha <amithsh...@gmail.com> wrote: >>>> >>>> so can u suggest the solution >>>> Thanks & Regards >>>> Amithsha >>>> >>>> >>>> On Tue, Mar 10, 2015 at 12:53 PM, Srinivas Thunga >>>> <srinivas.thu...@gmail.com> wrote: >>>> > Hi, >>>> > >>>> > I guess you have the sql as >>>> > >>>> > hive-schema-1.1.0.mysql.sql >>>> > >>>> > for this you will get only 45 tables only as Nucleus will not be there. >>>> > >>>> > I am also faced the same problem >>>> > >>>> > Thanks & Regards, >>>> > >>>> > Srinivas T >>>> > >>>> > On Tue, Mar 10, 2015 at 12:46 PM, Amith sha >>>> > <amithsh...@gmail.com> >>>> > wrote: >>>> >> >>>> >> Now i am able to create a metastore database after exporting the >>>> >> hive in .bashrc But the same mysql error is found Thanks & >>>> >> Regards Amithsha >>>> >> >>>> >> >>>> >> On Tue, Mar 10, 2015 at 12:03 PM, Amith sha >>>> >> <amithsh...@gmail.com> >>>> >> wrote: >>>> >> > Hi all, >>>> >> > >>>> >> > I have Configured Hive 1.1.0 in Hadoop 2.4.1 successfully.Have >>>> >> > started the metastore by [ hive --service metastore -p 7099 ] >>>> >> > >>>> >> > and got the log as >>>> >> > >>>> >> > 2015-03-10 11:32:42,649 ERROR [main]: DataNucleus.Datastore >>>> >> > (Log4JLogger.java:error(115)) - An exception was thrown while >>>> >> > adding/validating class(es) : Specified key was too long; max >>>> >> > key length is 767 bytes >>>> >> > com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: >>>> >> > Specified key was too long; max key length is 767 bytes >>>> >> > at >>>> >> > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native >>>> >> > Method) >>>> >> > at >>>> >> > >>>> >> > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) >>>> >> > at >>>> >> > >>>> >> > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) >>>> >> > at >>>> >> > java.lang.reflect.Constructor.newInstance(Constructor.java:526) >>>> >> > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) >>>> >> > at com.mysql.jdbc.Util.getInstance(Util.java:386) >>>> >> > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) >>>> >> > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) >>>> >> > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) >>>> >> > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617) >>>> >> > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) >>>> >> > at >>>> >> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819) >>>> >> > at >>>> >> > com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768) >>>> >> > at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949) >>>> >> > at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795) >>>> >> > at >>>> >> > com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:760) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.table.TableImpl.createIndices(TableImpl.java:648) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.table.TableImpl.validateIndices(TableImpl.java:593) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.table.TableImpl.validateConstraints(TableImpl.java:390) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.table.ClassTable.validateConstraints(ClassTable.java:3463) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3464) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:3190) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2841) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:122) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:1605) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:954) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:679) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getStatementForCandidates(RDBMSQueryUtils.java:408) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.query.JDOQLQuery.compileQueryFull(JDOQLQuery.java:947) >>>> >> > at >>>> >> > >>>> >> > org.datanucleus.store.rdbms.query.JDOQLQuery.compileInternal(JDOQLQuery.java:370) >>>> >> > at >>>> >> > org.datanucleus.store.query.Query.executeQuery(Query.java:1744) >>>> >> > at >>>> >> > org.datanucleus.store.query.Query.executeWithArray(Query.java:1672) >>>> >> > at org.datanucleus.store.query.Query.execute(Query.java:1654) >>>> >> > at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:221) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.MetaStoreDirectSql.ensureDbInit(MetaStoreDirectSql.java:172) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.MetaStoreDirectSql.<init>(MetaStoreDirectSql.java:130) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:275) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:238) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.RawStoreProxy.<init>(RawStoreProxy.java:56) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:65) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:579) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:557) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:606) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:448) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:66) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:72) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5570) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5565) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore.startMetaStore(HiveMetaStore.java:5798) >>>> >> > at >>>> >> > >>>> >> > org.apache.hadoop.hive.metastore.HiveMetaStore.main(HiveMetaStore.java:5723) >>>> >> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >>>> >> > at >>>> >> > >>>> >> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) >>>> >> > at >>>> >> > >>>> >> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >>>> >> > at java.lang.reflect.Method.invoke(Method.java:606) >>>> >> > at org.apache.hadoop.util.RunJar.main(RunJar.java:212) >>>> >> > >>>> >> > >>>> >> > >>>> >> > *Changed the metastore DB name but the db is not created in >>>> >> > MYSQL >>>> >> > >>>> >> > Can anyone suggest it >>>> >> > >>>> >> > >>>> >> > >>>> >> > >>>> >> > >>>> >> > >>>> >> > Thanks & Regards >>>> >> > Amithsha >>>> > >>>> > >>> >>> >
