Running the recover tool was successfully like if it had fixed the problem, but then, when I tried again to access the db the error persisted. This was only in a test db so there was no harm, I am worried this could happen in one of our production databases when they exectue one of those queries with 'case'.
On Wed, Sep 17, 2014 at 8:54 AM, Thomas Mueller-6 [via H2 Database] < [email protected]> wrote: > Hi, > > I'm afraid I don't know what the problem could be. > > You wrote you tried to use the Recover tool and it didn't work. What was > the problem? > > Regards, > Thomas > > > On Thursday, September 11, 2014, Dani Fernandez <[hidden email] > <http://user/SendEmail.jtp?type=node&node=4029985&i=0>> wrote: > >> This morning I tried to open my test database to make some tests there >> and I couldn't open it anymore; even tried the recover tool. When I try to >> open it using the h2 console I am getting this exception: >> >> Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; >> SQL statement: >> ALTER TABLE ADDRESS_SPACE.TABLE_A ADD CONSTRAINT >> ADDRESS_SPACE.FK_VAR_OBJECT FOREIGN KEY(OBJECT_ID) REFERENCES >> ADDRESS_SPACE.OBJECT_NODE(OBJECT_ID) ON DELETE CASCADE ON UPDATE CASCADE >> NOCHECK [23505-176] >> at >> org.h2.message.DbException.getJdbcSQLException(DbException.java:344) >> at org.h2.message.DbException.get(DbException.java:178) >> at org.h2.message.DbException.get(DbException.java:154) >> at >> org.h2.index.PageDataIndex.getNewDuplicateKeyException(PageDataIndex.java:166) >> >> at org.h2.index.PageDataIndex.add(PageDataIndex.java:144) >> at org.h2.store.PageStore.addMeta(PageStore.java:1808) >> at org.h2.index.PageBtreeIndex.<init>(PageBtreeIndex.java:64) >> at org.h2.table.RegularTable.addIndex(RegularTable.java:235) >> at >> org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:280) >> >> at >> org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:205) >> >> at >> org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:73) >> >> at org.h2.engine.MetaRecord.execute(MetaRecord.java:59) >> at org.h2.engine.Database.open(Database.java:719) >> at org.h2.engine.Database.openDatabase(Database.java:260) >> at org.h2.engine.Database.<init>(Database.java:254) >> at org.h2.engine.Engine.openSession(Engine.java:57) >> at org.h2.engine.Engine.openSession(Engine.java:164) >> at org.h2.engine.Engine.createSessionAndValidate(Engine.java:142) >> at org.h2.engine.Engine.createSession(Engine.java:125) >> at org.h2.engine.Engine.createSession(Engine.java:27) >> at >> org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:331) >> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:107) >> at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:91) >> at org.h2.Driver.connect(Driver.java:74) >> at org.h2.server.web.WebServer.getConnection(WebServer.java:684) >> at org.h2.server.web.WebApp.login(WebApp.java:958) >> at org.h2.server.web.WebApp.process(WebApp.java:212) >> at org.h2.server.web.WebApp.processRequest(WebApp.java:171) >> at org.h2.server.web.WebThread.process(WebThread.java:138) >> at org.h2.server.web.WebThread.run(WebThread.java:94) >> >> Yesterday the last thing I did was executing some queries with CASE that >> I have ported from mysql: >> >> UPDATE MODBUS_INDEX_MAP SET >> VALUE = CASE >> WHEN INDEXO = 2 THEN 99 >> ELSE VALUE >> END, >> FLAG = CASE >> WHEN INDEXO<>2 AND (SELECT FLAG FROM ( SELECT FLAG FROM >> MODBUS_INDEX_MAP WHERE INDEXO = 2) AS PIP)=1 THEN 0 >> WHEN INDEXO = 2 THEN 1 >> ELSE FLAG >> END >> WHERE SOURCEID IN (SELECT SOURCEID FROM ( SELECT SOURCEID FROM >> MODBUS_INDEX_MAP WHERE INDEXO = 2) AS PEPE) >> AND (ACK = (SELECT ACK FROM ( SELECT ACK FROM MODBUS_INDEX_MAP WHERE >> INDEXO = 2) AS PIPO) >> OR (ACK IS NULL AND ((SELECT ACK FROM ( SELECT ACK FROM >> UA_MODBUS_INDEX_MAP WHERE INDEXO = 2) AS PIPO2) IS NULL))) >> AND (EFB = (SELECT EFB FROM ( SELECT EFB FROM MODBUS_INDEX_MAP WHERE >> INDEXO = 2) AS PIPO3) >> OR (EFB IS NULL AND ((SELECT EFB FROM ( SELECT EFB FROM MODBUS_INDEX_MAP >> WHERE INDEXO = 2) AS PIPO4) IS NULL))); >> >> I am stuck trying to open it... Any ideas? >> It looks like if the primary key is violated the db cannot be opened >> anymore? >> >> Tks, >> Dani. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to <a href="javascript:_e(%7B%7D,'cvml',&# >> 39;h2-database%[email protected]');" >> target="_blank">h2-database+unsubscribe@.... >> To post to this group, send email to <a >> href="javascript:_e(%7B%7D,'cvml',&# >> 39;[email protected]');" target="_blank">h2-database@.... >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [hidden email] > <http://user/SendEmail.jtp?type=node&node=4029985&i=1>. > To post to this group, send email to [hidden email] > <http://user/SendEmail.jtp?type=node&node=4029985&i=2>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > > http://h2-database.66688.n3.nabble.com/Cannot-open-database-tp4029937p4029985.html > To start a new topic under H2 Database, email > [email protected] > To unsubscribe from H2 Database, click here > <http://h2-database.66688.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=66688&code=Ym9hZGFkZkBnbWFpbC5jb218NjY2ODh8MTU5NjUyOTk0NA==> > . > NAML > <http://h2-database.66688.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://h2-database.66688.n3.nabble.com/Cannot-open-database-tp4029937p4030023.html Sent from the H2 Database mailing list archive at Nabble.com. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
