1

I am using EclipseLink 2.4.1 with Glassfish and a MySQL database for persisting entities.
I added a field to an entity, and when I try to persist this entity it says the new field is 'unknown'.
How are we supposed to use the new 'create-or-extend-tables' feature? I would have thought it would create a new column for this field.

Some relevant information is below, let me know if you want more.

Thanks in advance!

Stack Trace

...
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003 ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 't0.TESTFIELD' in 'field list'
Error Code: 1054
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
...

persistence.xml:

<persistence-unit name="myApp" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/mysql</jta-data-source>
<mapping-file>META-INF/orm.xml</mapping-file>
<properties>
  <property name="eclipselink.ddl-generation.output-mode" value="database"/>
  <property name="eclipselink.jdbc.batch-writing" value="Buffered"/>
  <property name="eclipselink.logging.level" value="INFO"/>
  <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
</properties>

Extra Info

drop-and-create-tables does work.
I am using merge(entity) to persist new entities (as they have lots of many to one fields which causes duplicate primary id issues otherwise) - I feel this may be the issue.
Looking at the MySQL log, and the finest log level from EclipseLink, EclipseLink first tries to select the entity from the database as follows:
mysql_log:

121115 10:49:03 9 Query SELECT t0.LISTINGID, t0.DTYPE, ... , t0.TESTFIELD FROM the_entity...

This is the last mysql log entry, meaning that it crashes here, it never tries to drop the table etc. Does this mean you cannot use merge with drop-and-create or create-or-extend? I just did a google and didn't find any info on this.

EclipseLink Logging:

FINER: client acquired: 64279491
FINER: TX binding to tx mgr, status=STATUS_ACTIVE
FINER: acquire unit of work: 161130796
FINEST: Merge clone with references nz.co.site.api.v1.ListedItemDetail@8d88ca1
FINEST: Execute query ReadObjectQuery(referenceClass=ListedItemDetail )
FINEST: Connection acquired from connection pool [read].
FINEST: reconnecting to external connection pool
FINE: SELECT t0.LISTINGID, t0.DTYPE, ... , t0.TESTFIELD, ... , FROM ITEM t0, LISTEDITEMDETAIL t1 WHERE ((t0.LISTINGID = ?) AND ((t1.LISTINGID = t0.LISTINGID) AND (t0.DTYPE = ?)))
bind => [2 parameters bound]
FINE: SELECT 1
FINEST: Connection released to connection pool [read].
WARNING: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException

I use bean transaction management to merge new entities:

userTransaction.begin();
entityManager.merge(entity);
entityManager.flush();
userTransaction.commit();
Tim
  • 514
  • 8
  • 17
  • Can you post the new mapping and set logging to finest and show the DDL that gets generated? Does drop-and-create-tables work instead? – Chris Nov 14 '12 at 05:59
  • No, drop-and-create-tables does not work either! Sorry I'm not sure what you mean by the new mapping? Do you mean the new entity class? I've added some extra info to the bottom of the question. – Tim Nov 14 '12 at 21:54
  • You need to turn logging to a higher level to see the DDL that is executed in the database to be sure what is going wrong, as DDL exceptions are not fatal, and not writen out in the INFO level. Try setting it to FINE or FINEST: – Chris Nov 14 '12 at 21:56

1 Answers1

3

Glassfish intercepts the eclipselink.ddl-generation.output-mode property and forces it write out to file as described in an answer here: eclipselink does not generate tables from annotated JPA classes So you will need to check that you've enabled java2db in Glassfish for "drop-and-create-tables" to work.

"create-or-extend-tables" though requires database connections to see what is in the database, and so does not currently work with the sql-script output mode. I am not sure how to force Glassfish to not overwrite the output-mode property, but if it cannot be done, you will need to run your persistence unit side glassfish to use this feature.

A workaround in glassfish would be to try something like this:

Map properties = new HashMap();
properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.CREATE_OR_EXTEND);
//create-or-extend only works on the database
properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
//this causes DDL generation to occur on refreshMetadata rather than wait until an em is obtained
properties.put(PersistenceUnitProperties.DEPLOY_ON_STARTUP, "true");
JpaHelper.getEntityManagerFactory(em).refreshMetadata(properties);
Community
  • 1
  • 1
Chris
  • 20,138
  • 2
  • 29
  • 43
  • Thanks for the quick answer! A bit too quick, it took me longer to add the extra info than it took for you to reply, so you may want to look at that at the bottom of my question. I think the issue may be related to the fact that I am merging, and hence it does a select statement first, which fails as the field is not in the database. Would you come to the same conclusion & if so do you know a way around this? – Tim Nov 14 '12 at 22:21
  • Just to clarify, drop-and-create-tables works if I do not add a new field. On deployment, it drops all the tables and recreates them. – Tim Nov 14 '12 at 22:25
  • The error states the field isnt there, so it's not a merge problem. I'm not sure what you mean by drop+create work if not adding a new field though. It works on deployment, so how are you adding a field to your entity that it wouldn't work? Is it not redeployed? – Chris Nov 14 '12 at 22:55
  • Sorry for the long delay in replying, I sort of gave up on it as it was taking up time. I've just gone back to looking at it and, at the moment at least, it appears that 'create-or-extend-tables' just doesn't do anything, like it isn't supported. It doesn't drop, create, or extend any tables on deployment. Changing to create-tables or drop-and-create-tables works fine. EclipseLink version is 2.4.1 as per log. This would explain all the issues I've been getting (though I would have thought I would have noticed), as it would try to select a field during merge that doesn't exist. – Tim Nov 21 '12 at 06:43
  • As mentioned in the answer, Glassfish injects eclipselink properties based on its java2db feature. If its on, it sets the ddl-generation mode to drop-and-create-tables, and if off, it sets it to none. It completely overwrites user specified values for the property. The only way I see getting "create-or-extend-tables" to work is to run the persistence unit outside glassfish. Please file a glassfish bug/enhancement as there should be a way to let EclipseLink handle DDL. – Chris Nov 21 '12 at 14:07
  • Okay, am I correct in my understanding: Glassfish uses the eclipselink properties as a 'guide' for its own java2db DDL generation (by sending the eclipselink output to a sql-script somewhere)? So, because the java2db does not support create-or-extend-tables it will just default to doing nothing? Also, is there a reason you keep mentioning drop-and-create-tables but not create-tables, both appear to work correctly to me? Thanks a lot for your help so far. – Tim Nov 22 '12 at 06:05
  • Glassfish java2DB overrides the ddl-generation with either "drop-and-create-tables" or "none", so it doesn't matter what value you set for the property. 'drop-and-create-tables' is used to create two DDL files, one for dropping, the other for creating. So if creat-tables works without dropping, it is because glassfish java2db is configured to ignore the drop table script. A feature/bug will need to be raised with Glassfish java2db to support allowing EclipseLink to handle DDL, or to implement its own create-or-extend-tables feature. – Chris Nov 22 '12 at 16:46