Eclipse Community Forums: EMF » [CDO] Usage of MySQL adapter with InnoDB

Home » Modeling » EMF » [CDO] Usage of MySQL adapter with InnoDB(java.sql.SQLException: Table definition has changed)

Show: Today's Messages :: Show Polls :: Message Navigator

[CDO] Usage of MySQL adapter with InnoDB [message #1715409]

Mon, 23 November 2015 11:52

Thorsten Schlathölter

Messages: 312
Registered: February 2012
Location: Düsseldorf

Senior Member

Hi,
I am trying to use CDO together with the MySQL adapter using a MySQL Server Version 5.6. I experience problems during initialization of the store.

The schema is created and then in the process of initialization of the root resource i get the following exception:

[ERROR] Rollback in DBStore: org.eclipse.net4j.db.DBException: java.sql.SQLException: Table definition has changed, please retry transaction
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.queryResources(AbstractHorizontalMappingStrategy.java:474)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalMappingStrategy.queryResources(AbstractHorizontalMappingStrategy.java:126)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalMappingStrategy.queryResources(HorizontalMappingStrategy.java:184)
	at org.eclipse.emf.cdo.server.internal.db.DBStoreAccessor.queryResources(DBStoreAccessor.java:331)
	at org.eclipse.emf.cdo.spi.server.StoreAccessorBase.readResourceID(StoreAccessorBase.java:215)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.AbstractHorizontalClassMapping.checkDuplicateResources(AbstractHorizontalClassMapping.java:469)
	at org.eclipse.emf.cdo.server.internal.db.mapping.horizontal.HorizontalBranchingClassMapping.writeRevision(HorizontalBranchingClassMapping.java:747)
	at org.eclipse.emf.cdo.server.internal.db.DBStoreAccessor.writeRevision(DBStoreAccessor.java:591)
	at org.eclipse.emf.cdo.server.internal.db.DBStoreAccessor.writeRevisions(DBStoreAccessor.java:571)
	at org.eclipse.emf.cdo.spi.server.StoreAccessor.doWrite(StoreAccessor.java:98)
	at org.eclipse.emf.cdo.server.internal.db.DBStoreAccessor.doWrite(DBStoreAccessor.java:831)
	at org.eclipse.emf.cdo.spi.server.StoreAccessorBase.write(StoreAccessorBase.java:152)
	at org.eclipse.emf.cdo.internal.server.TransactionCommitContext.write(TransactionCommitContext.java:651)
	at org.eclipse.emf.cdo.internal.server.Repository.initRootResource(Repository.java:2060)
	at org.eclipse.emf.cdo.internal.server.Repository.doActivate(Repository.java:2213)
	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:127)
	at org.eclipse.net4j.util.lifecycle.LifecycleUtil.activate(LifecycleUtil.java:117)
	at org.eclipse.emf.cdo.server.CDOServerUtil.addRepository(CDOServerUtil.java:292)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:141)
	at org.eclipse.emf.cdo.spi.server.RepositoryConfigurator.configure(RepositoryConfigurator.java:108)
	at enco.sox2.workspace.cdo.server.CDOApplication.doStart(CDOApplication.java:62)
	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)

I only found this hint on this subject:
https://bugs.mysql.com/bug.php?id=65378

Are there any known issues running CDO with MySQL?

Thanks in advance

Thorsten

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715474 is a reply to message #1715409]

Mon, 23 November 2015 22:14

Thorsten Schlathölter

Messages: 312
Registered: February 2012
Location: Düsseldorf

Senior Member

I have played around a little and have replaced the DBStoreAccessor with an own imlementation that performs a commit on the current connection prior to queryResources

public void queryResources(QueryResourcesContext context) {
		try {
			getConnection().commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		super.queryResources(context);
	}

This solves the problem which proves that it indeed has to do something with the transaction not yet being committed.

http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html

Of course this is not the right place. Actually for MySQL it seems that a commit needs to be performed in

Repository.initRootResource()

between

commitContext.setNewPackageUnits(getNewPackageUnitsForRootResource(commitContext.getPackageRegistry()));

and

commitContext.write(new Monitor());

As the later performs a query on a table that has been created in the setNewPackageUnits method call. As no one seems to have these problems of course it is still possible that I have misconfigured either my MySQL database or CDO?

Regards,
Thorsten

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715499 is a reply to message #1715474]

Tue, 24 November 2015 06:45

Thorsten Schlathölter

Messages: 312
Registered: February 2012
Location: Düsseldorf

Senior Member

I have to correct my above statement. The relevant tables and indizes are written when

TransactionCommitContext.write()

is called.

A solution would be to commit after packages have been written

	public void writePackageUnits(InternalCDOPackageUnit[] packageUnits, OMMonitor monitor) {
		super.writePackageUnits(packageUnits, monitor);

		try {
			getConnection().commit();
		} catch (SQLException e) {
			OM.LOG.error(e);
		}
	}

But then again a clean rollback would not be possible anymore.

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715501 is a reply to message #1715499]

Tue, 24 November 2015 07:37

Eike Stepper

Messages: 6678
Registered: July 2009

Senior Member

Am 24.11.2015 um 07:45 schrieb Thorsten Schlathölter:
> I have to correct my above statement. The relevant tables and indizes are written when
>
> TransactionCommitContext.write()
>
>
> is called.
>
> A solution would be to commit after packages have been written
>
>
> public void writePackageUnits(InternalCDOPackageUnit[] packageUnits, OMMonitor monitor) {
> super.writePackageUnits(packageUnits, monitor);
>
> try {
> getConnection().commit();
> } catch (SQLException e) {
> OM.LOG.error(e);
> }
> }
>
>
>
> But then again a clean rollback would not be possible anymore.
Yes, that's the problem. On the other hand the worst effect in case of a later rollback would probably be that empty
tables are left in the schema. Not sure how a new attempt to create these tables would behave, but in theory CDO's
schema reconcilation mechanism should handle that case gracefully. My main problem is that only very few DBs seem to
want/need this extra commit after schema modification. If we issue this commit for all DBs that probably creates more
harm than benfit. Maybe we could delegate this behavior to the DBAdapter or make it configurable via cdo-server.xml...

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715504 is a reply to message #1715409]

Tue, 24 November 2015 07:51

Thorsten Schlathölter

Messages: 312
Registered: February 2012
Location: Düsseldorf

Senior Member

Yes. That's exactly what I did. Since I have my own implementation for the adapters if have simply added a new Interface which is implemented by the MyOwnMySQLAdapter:

So currently it looks like this:

public void writePackageUnits(InternalCDOPackageUnit[] packageUnits, OMMonitor monitor) {
	super.writePackageUnits(packageUnits, monitor);

	IDBAdapter dbAdapter = getStore().getDBAdapter();
	if (dbAdapter instanceof IDbAdapter2 && ((IDbAdapter2) dbAdapter).commitAfterNewPackages()) {
		try {
			getConnection().commit();
		} catch (SQLException e) {
			OM.LOG.error(e);
		}
	}
}

Since I intend to preregister all packages anyway I also think that the rollback issue is not so harmfull.

Regards,
Thorsten

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715516 is a reply to message #1715504]

Tue, 24 November 2015 08:57

Eike Stepper

Messages: 6678
Registered: July 2009

Senior Member

Am 24.11.2015 um 08:51 schrieb Thorsten Schlathölter:
> Yes. That's exactly what I did. Since I have my own implementation for the adapters if have simply added a new
> Interface which is implemented by the MyOwnMySQLAdapter:
>
> So currently it looks like this:
>
>
> public void writePackageUnits(InternalCDOPackageUnit[] packageUnits, OMMonitor monitor) {
> super.writePackageUnits(packageUnits, monitor);
>
> IDBAdapter dbAdapter = getStore().getDBAdapter();
> if (dbAdapter instanceof IDbAdapter2 && ((IDbAdapter2) dbAdapter).commitAfterNewPackages()) {
> try {
> getConnection().commit();
> } catch (SQLException e) {
> OM.LOG.error(e);
> }
> }
> }

This approach may work for your custom case, but as the net4j.db layer doesn't know anything about CDO it's hard to
imagine how we could generalize this for everybody.

What I had in mind is more along the lines of adding a general boolean property (getter) to IDBAdapter, e.g.,
needsCommitAfterSchemaModification() and then conditionally call commit from the original writePackageUnits() method. I
don#t have the time right now to play with it, but something like that. What do you think?

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

>
> Since I intend to preregister all packages anyway I also think that the rollback issue is not so harmfull.
>
> Regards,
> Thorsten

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715522 is a reply to message #1715516]

Tue, 24 November 2015 09:12

Thorsten Schlathölter

Messages: 312
Registered: February 2012
Location: Düsseldorf

Senior Member

Well I think that is exactly what I did besides the fact that I implemented it in my own classes\interfaces to avoid messing up with original CDO bundles. The method from my interface could easily be integrated in the standard IDBAdapter interface and the conditional commit could be pushed into the original writePackageUnits method.

Then the Default DBAdapter implementation just returns false for the method needsCommitAfterSchemaModification and the MySQLAdapter returns true. This should solve the issue with no side effects.

Regards,
Thorsten

Report message to a moderator

Re: [CDO] Usage of MySQL adapter with InnoDB [message #1715525 is a reply to message #1715522]

Tue, 24 November 2015 09:16

Eike Stepper

Messages: 6678
Registered: July 2009

Senior Member

Am 24.11.2015 um 10:12 schrieb Thorsten Schlathölter:
> Well I think that is exactly what I did besides the fact that I implemented it in my own classes\interfaces to avoid
> messing up with original CDO bundles. The method from my interface could easily be integrated in the standard
> IDBAdapter interface and the conditional commit could be pushed into the original writePackageUnits method.
>
> Then the Default DBAdapter implementation just returns false for the method needsCommitAfterSchemaModification and the
> MySQLAdapter returns true. This should solve the issue with no side effects.
Exactly. Please submit a bugzilla as a reminder.

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Report message to a moderator