Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 364809

Summary:

[DB] Store with Postgres fail to recover from crash

Product:

[Modeling] EMF

Reporter:

Federico Tomassetti <f.tomassetti>

Component:

cdo.db

Assignee:

Eike Stepper <stepper>

Status:

CLOSED FIXED

QA Contact:

Eike Stepper <stepper>

Severity:

normal

Priority:

CC:

dzonekl, stepper, vincenzo.caselli

Version:

4.2

Target Milestone:

---

Hardware:

OS:

Windows 7

Whiteboard:

Attachments:

Description	Flags
Build a database schema	none
Output and stacktrace obtained	none
Test case for the bug	none
Avoid table creation when using a Postgresql DB if the table exists already and it has the right schema.	none

Description Federico Tomassetti

2011-11-25 05:35:08 EST

Build Identifier: 20110916-0149

After a crash CDO does not recover using DBStore with postgres. It fails because of previous errors which are not specified.

This bugzilla is related to discussion (http://www.eclipse.org/forums/index.php/mv/msg/261423/754361/#msg_754361)

Reproducible: Always

Steps to Reproduce:
1. Shutdown not properly the repository
2. Launch it again, it try to recover, it fails

Comment 1 Federico Tomassetti

2011-11-25 05:38:17 EST

Created attachment 207522 [details]
Build a database schema

Part of the problem could be that CDO try to create tables without checking if they are existing. As first step CDO could try to compare the current Database schema with the desired one and avoid creating already existing tables.

Postgres in particular seems to react badly at these tentatives...

Comment 2 Federico Tomassetti

2011-11-25 05:40:22 EST

Created attachment 207523 [details]
Output and stacktrace obtained

Comment 3 Federico Tomassetti

2011-11-25 05:41:40 EST

Created attachment 207524 [details]
Test case for the bug

You should run the test named LockingTest_RUNME.

You need a Postgres installation...

Comment 4 Federico Tomassetti

2011-12-15 07:04:48 EST

One related issue:
should not the MappingStrategy take care of veryfing the table it needs are available and correct?

For example I created a repository without branching support and I am trying to use it now with branching. The table CDOResourceFolder should have a column (cdo_branch) which it has not. I think also the mapping strategy should verify if the schema is correct because it is the only piece of code which knows about some DB details (like the presence of the cdo_branch column in this case).

Comment 5 Federico Tomassetti

2012-01-10 04:13:11 EST

Created attachment 209248 [details]
Avoid table creation when using a Postgresql DB if the table exists already and it has the right schema.

Comment 6 Eike Stepper

2012-08-14 22:52:49 EDT

Moving all open issues to 4.2. Open bugs can be ported to 4.1 maintenance after they've been fixed in master.

Comment 7 Eike Stepper

2012-10-18 10:18:35 EDT

This bug and bug 365602 are basically duplicates but they seem to suggest different solutions.

Comment 8 Vincenzo Caselli

2012-10-30 08:53:40 EDT

(In reply to comment #7)
> This bug and bug 365602 are basically duplicates but they seem to suggest
> different solutions.

Hi Eike!
Here we have the same situation: when CDO Server with Postgres crashes then it's impossible to re-connect, even if we stop/restart the Postgres service!
For what we know now, we can just drop and create a new database, but then loosing all data.
Below is the stacktrace.
Can you suggest some solution? 
Thank you

Vincenzo





!ENTRY org.eclipse.emf.cdo.server 1 0 2012-10-30 13:47:40.787
!MESSAGE CDO server starting
[WARN] Detected crash of repository proteo-server

!ENTRY org.eclipse.emf.cdo.server.db 2 0 2012-10-30 13:47:41.432
!MESSAGE Detected crash of repository proteo-server
[ERROR] org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
org.eclipse.net4j.db.DBException: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:185)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.DelegatingObjectTypeMapper.getMaxID(DelegatingObjectTypeMapper.java:85)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.repairAfterCrash(AbstractHorizontalMappingStrategy.java:94)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.repairAfterCrash(HorizontalMappingStrategy.java:204)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.repairAfterCrash(DBStore.java:679)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.reStart(DBStore.java:661)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:556)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1846)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:111)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:174)
	... 20 more

!ENTRY org.eclipse.net4j.util 4 0 2012-10-30 13:47:41.646
!MESSAGE org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
!STACK 0
org.eclipse.net4j.db.DBException: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:185)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.DelegatingObjectTypeMapper.getMaxID(DelegatingObjectTypeMapper.java:85)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.repairAfterCrash(AbstractHorizontalMappingStrategy.java:94)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.repairAfterCrash(HorizontalMappingStrategy.java:204)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.repairAfterCrash(DBStore.java:679)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.reStart(DBStore.java:661)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:556)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1846)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:111)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:174)
	... 20 more

!ENTRY org.eclipse.equinox.app 4 0 2012-10-30 13:47:41.650
!MESSAGE org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
!STACK 0
org.eclipse.net4j.db.DBException: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:185)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.DelegatingObjectTypeMapper.getMaxID(DelegatingObjectTypeMapper.java:85)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.repairAfterCrash(AbstractHorizontalMappingStrategy.java:94)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.repairAfterCrash(HorizontalMappingStrategy.java:204)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.repairAfterCrash(DBStore.java:679)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.reStart(DBStore.java:661)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:556)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1846)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:111)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.ObjectTypeTable.getMaxID(ObjectTypeTable.java:174)
	... 20 more

Comment 9 Eike Stepper

2012-10-31 05:54:19 EDT

This article explains the root cause:

http://stackoverflow.com/questions/10399727/current-transaction-is-aborted-commands-ignored-until-end-of-transaction-block

Unfortunately it's not particularly easy to address this issue. The exception occurs in CDO mechansims that visit all tables and just try to execute some SQL without knowing the exact structure of the tables. This is because at those times the package registry is not yet populated/populatable. Examples are: 

   DBStore.repairAfterCrash()
   DBStore.migrateSchema()

Even more dangerous (because more reusable):

   DBAdapter.validateTable(IDBTable, Statement)

But that is handled in PostgreSQLAdapter.createTable()!

Thinking...

Comment 10 Eike Stepper

2012-10-31 06:03:45 EDT

Okay, I've changes visitAllTables to always commit/rollback after one table has been visited:

  public void visitAllTables(Connection connection, IDBStore.TableVisitor visitor)
  {
    for (String name : DBUtil.getAllTableNames(connection, getRepository().getName()))
    {
      try
      {
        visitor.visitTable(connection, name);
        connection.commit();
      }
      catch (SQLException ex)
      {
        try
        {
          connection.rollback();
        }
        catch (SQLException ex1)
        {
          throw new DBException(ex1);
        }

        if (!COLUMN_NOT_FOUND.equalsIgnoreCase(ex.getSQLState()))
        {
          throw new DBException(ex);
        }
      }
    }
  }

That changes the atomicity  in the case of schema migrators (repairAfterCrash is not impacted as it only reads), but it happens at startup time and in very rare cases. That's acceptable.

Comment 11 Eike Stepper

2012-10-31 06:04:19 EDT

commit 4037005461a8af61010d6fa024d1816d782e74b7

Comment 12 Eike Stepper

2012-10-31 06:05:05 EDT

I have no PostgreSQL. Can you please retest your scenario and report here whether it's really fixed?

Comment 13 Eike Stepper

2012-10-31 06:22:16 EDT

*** Bug 365602 has been marked as a duplicate of this bug. ***

Comment 14 Vincenzo Caselli

2012-10-31 06:31:57 EDT

(In reply to comment #12)
> I have no PostgreSQL. Can you please retest your scenario and report here
> whether it's really fixed?

Eike: great!
Thank you for the quick response!
Yes, we will try and let you know here.

Vincenzo

Comment 15 Vincenzo Caselli

2012-11-02 05:58:58 EDT

Hi Eike,
we tried the head version with Postgres, both downloading the sources of CDO and Net4j and (alternatively) using the following update site:
http://download.eclipse.org/modeling/emf/cdo/drops/I20121031-1041/
which should correspond (at least your latest changes are into the sources of this drop) to your commit
http://git.eclipse.org/c/cdo/cdo.git/commit/?id=4037005461a8af61010d6fa024d1816d782e74b7

In both cases, after a crash, we get the same error reported below ("column t.cdo_id does not exist").
Indeed it seems that in this case, inside the method 
AbstractHorizontalMappingStrategy...visitTable(Connection, String)
the following query is executed

SELECT MIN("t".cdo_id) FROM cdo_objects "o", cdo_branches "t" 
WHERE "t".cdo_branch<0 AND "t".cdo_id="o".cdo_id AND "t".cdo_created="o".cdo_created

but the "cdo_branches" table has only [id,name,base_id,base_time] columns.
Therefore the query is referring to missing columns [cdo_id,cdo_branch,cdo_created] in this table.

We tried also the same code with MySQL (which we are using in parallel to Postgres and that has always recovered successfully after crashes) and here, in the same situation, the problem seems to be twofold:
1) the character " seems not to be accepted for table aliases
2) removing the character " from aliases brings to the same error shown in Postgres (Unknown column 't.cdo_id' in 'field list')

========================= MySQL ===============================
[ERROR] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.cdo_id) FROM cdo_objects "o", cdo_branches "t" WHERE "t".cdo_branch<0 AND "t".c' at line 1
org.eclipse.net4j.db.DBException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.cdo_id) FROM cdo_objects "o", cdo_branches "t" WHERE "t".cdo_branch<0 AND "t".c' at line 1
	at org.eclipse.emf.cdo.server.internal.db.DBStore.visitAllTables(DBStore.java:277)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.getMinLocalID(AbstractHorizontalMappingStrategy.java:461)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.repairAfterCrash(AbstractHorizontalMappingStrategy.java:96)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.repairAfterCrash(HorizontalMappingStrategy.java:204)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.repairAfterCrash(DBStore.java:741)
=================================================================	
	


========================= Postgres ===============================

[ERROR] org.postgresql.util.PSQLException: ERROR: column t.cdo_id does not exist
  Position: 12
org.eclipse.net4j.db.DBException: org.postgresql.util.PSQLException: ERROR: column t.cdo_id does not exist
  Position: 12
	at org.eclipse.emf.cdo.server.internal.db.DBStore.visitAllTables(DBStore.java:277)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.getMinLocalID(AbstractHorizontalMappingStrategy.java:461)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.repairAfterCrash(AbstractHorizontalMappingStrategy.java:96)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.repairAfterCrash(HorizontalMappingStrategy.java:204)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.repairAfterCrash(DBStore.java:741)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.reStart(DBStore.java:717)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:607)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1874)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.ShareableLifecycle.internalActivate(ShareableLifecycle.java:43)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:113)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
=================================================================

Comment 16 Eike Stepper

2012-11-02 06:31:29 EDT

Darned! This is all because silly Oracle wants double quotes around table alias names (according to another user) ;-(

In cases where (a) tables or (b) columns are not found, can you please (for all DB types you have access to) set a breakpoint and write down the value of ex.getSQLState()? I wonder if they use non-standard codes.

I check for these codes because they're expected exceptions in these particular scenarios. But it may also be that the DB (or the driver) itself logs out these exceptions. In that case they can just be ignored.

Comment 17 Eike Stepper

2012-11-02 06:32:35 EDT

BTW. This morning I corrected a few mistakes I made with the double quotes and one with the new schema migration. A new I-build will be available later today...

Comment 18 Eike Stepper

2012-11-02 06:34:23 EDT

In the worst case I'll have to make the double quoting DB-specific ;-(

But waiting for your feedback regarding the getSQLState() values of the exceptions...

Comment 19 Christophe Bouhier

2012-11-02 07:45:12 EDT

"the new schema migration." 

Does this affect: https://bugs.eclipse.org/bugs/show_bug.cgi?id=351068
?

I still see objects with negative rev. in the DB. 
See some emails I have send to your mailbox this morning..

Comment 20 Eike Stepper

2012-11-02 09:56:56 EDT

(In reply to comment #19)
> "the new schema migration." 
> 
> Does this affect: https://bugs.eclipse.org/bugs/show_bug.cgi?id=351068
> ?
> 
> I still see objects with negative rev. in the DB. 
> See some emails I have send to your mailbox this morning..

Yes, that can be ;-(

I wrote if (0 < schemaVersion && ...).
But it must be if (0 <= schemaVersion && ...).

That prevented first time migration, e.g. v0 -> v2 from happening. It's fixed and available in the newest I-build : http://www.eclipse.org/cdo/downloads/#I20121102_0646

Sorry for the inconvenience!

Comment 21 Vincenzo Caselli

2012-11-02 12:12:21 EDT

(In reply to comment #16)
> Darned! This is all because silly Oracle wants double quotes around table
> alias names (according to another user) ;-(

I have OracleXE 12 installed and can assure that double quotes are not mandatory for table alias names (unless one is using aliases with spaces inside the name - always a bad practice - but here this can be avoided)

> 
> In cases where (a) tables or (b) columns are not found, can you please (for
> all DB types you have access to) set a breakpoint and write down the value
> of ex.getSQLState()? I wonder if they use non-standard codes.
> 
> I check for these codes because they're expected exceptions in these
> particular scenarios. But it may also be that the DB (or the driver) itself
> logs out these exceptions. In that case they can just be ignored.

I updated to the commit 625c2b6e8de8af0d6781a1085e430ae99bd3039a and now the issue is the following (with Postgres, starting with a brand new empty database):

the SQLState in this case is: 42P01

!MESSAGE CDO server starting
[ERROR] org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
org.eclipse.net4j.util.lifecycle.LifecycleException: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:104)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1874)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.ShareableLifecycle.internalActivate(ShareableLifecycle.java:43)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:113)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)

Comment 22 Vincenzo Caselli

2012-11-05 10:43:34 EST

Sorry, the stack trace was truncated in last message.
Here is it complete:

[INFO] CDO server starting
!SESSION 2012-11-05 16:42:38.402 -----------------------------------------------
eclipse.buildId=unknown
java.version=1.6.0_33
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Framework arguments:  -product org.eclipse.emf.cdo.server.product.tcp_h2
Command-line arguments:  -product org.eclipse.emf.cdo.server.product.tcp_h2 -data E:\_hd\workspaces\_Clienti\TS_proteo-dev/../runtime-CDO-Server.product -dev file:E:/_hd/workspaces/_Clienti/TS_proteo-dev/.metadata/.plugins/org.eclipse.pde.core/CDO-Server.product/dev.properties -os win32 -ws win32 -arch x86 -consoleLog

!ENTRY org.eclipse.emf.cdo.server 1 0 2012-11-05 16:42:39.829
!MESSAGE CDO server starting
[ERROR] org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
org.eclipse.net4j.util.lifecycle.LifecycleException: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:104)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1874)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.ShareableLifecycle.internalActivate(ShareableLifecycle.java:43)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:113)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
!ENTRY org.eclipse.net4j.util 4 0 2012-11-05 16:42:49.229
!MESSAGE org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
!STACK 0
org.eclipse.net4j.util.lifecycle.LifecycleException: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:104)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1874)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.ShareableLifecycle.internalActivate(ShareableLifecycle.java:43)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:113)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.selectSchemaVersion(DBStore.java:812)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:586)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	... 16 more

!ENTRY org.eclipse.equinox.app 4 0 2012-11-05 16:42:49.234
!MESSAGE org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
!STACK 0
org.eclipse.net4j.util.lifecycle.LifecycleException: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:104)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:1874)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	at org.eclipse.net4j.util.lifecycle.ShareableLifecycle.internalActivate(ShareableLifecycle.java:43)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.activate(Lifecycle.java:162)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:114)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:104)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:208)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:113)
	at org.eclipse.emf.cdo.internal.server.bundle.CDOServerApplication.doStart(CDOServerApplication.java:61)
	at org.eclipse.net4j.util.om.OSGiApplication.start(OSGiApplication.java:63)
	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
	at org.eclipse.equinox.internal.app.AnyThreadAppLauncher.run(AnyThreadAppLauncher.java:26)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "cdo_properties" does not exist
  Position: 19
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.selectSchemaVersion(DBStore.java:812)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:586)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	... 16 more

	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.selectSchemaVersion(DBStore.java:812)
	at org.eclipse.emf.cdo.server.internal.db.DBStore.doActivate(DBStore.java:586)
	at org.eclipse.net4j.util.lifecycle.Lifecycle.internalActivate(Lifecycle.java:76)
	... 16 more

Comment 23 Vincenzo Caselli

2012-11-05 17:20:23 EST

I've set a breakpoint in org.eclipse.emf.cdo.server.internal.db.DBStore.selectSchemaVersion(Connection)
at lines
    ...
  catch (SQLException ex)
    {
      if (TABLE_NOT_FOUND.equals(ex.getSQLState()))

and with Postgres ex.getSQLState() has the value "42P01", where MySQL shows instead a "42S02"

Comment 24 Eike Stepper

2012-11-06 02:31:02 EST

I've spent 5 hours now on the installation of Postgres 9.2.1 to my Windows 7 64bit box. And I'm sick of it. It fails, fails, fails. Uninstall doesn't work either, so I must clean my registry manually each time. It's crap!

I'm not going to support Postgres anymore. The existing adapter will be built/published as usually. If someone needs fixes in there, he/she will have to provide the full patches which I will try to review then.

The last thing I'll do right now is making this stupid Postgres-specific error code for "table not found" be recognized by the DB framework. Have to add API for this. Stupid!

Comment 25 Eike Stepper

2012-11-06 02:52:29 EST

I've added the new API and implemented it as follows:

  /**
   * See <a href="http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html">Appendix A. PostgreSQL Error Codes</a>.
   */
  @Override
  public boolean isDuplicateKeyException(SQLException ex)
  {
    // RESTRICT VIOLATION || UNIQUE VIOLATION
    return super.isDuplicateKeyException(ex) || "23505".equals(ex.getSQLState());
  }

  /**
   * See <a href="http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html">Appendix A. PostgreSQL Error Codes</a>.
   */
  @Override
  public boolean isTableNotFoundException(SQLException ex)
  {
    // UNDEFINED TABLE
    return "42P01".equals(ex.getSQLState());
  }

  /**
   * See <a href="http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html">Appendix A. PostgreSQL Error Codes</a>.
   */
  @Override
  public boolean isColumnNotFoundException(SQLException ex)
  {
    // UNDEFINED COLUMN
    return "42703".equals(ex.getSQLState());
  }

commit aa797f40e69af58abc6740706486290514e19de0

Comment 26 Vincenzo Caselli

2012-11-12 06:14:01 EST

(In reply to comment #25)
> I've added the new API and implemented it as follows:
...
> commit aa797f40e69af58abc6740706486290514e19de0

Hi Eike,
we tried your modification with Postgres and it is working great!!!

For us using Postgres with CDO is very important and so we wanted to thank you for your effort, especially having to deal with the non-standard SQL error codes of this database.

Thank you again!

Vincenzo
RCP Vision

Comment 27 Eike Stepper

2012-11-16 04:09:36 EST

Thanks for the feedback!

Comment 28 Eike Stepper

2013-06-27 03:32:38 EDT

Available in R20130613-1157 (4.2)