JPA2: create-or-extend-tables is not extending existing table with new column

Question

I am using JPA2, EclipseLink. I am trying to add a new column to the existing database and table. I added a new field in POJO class MyTable. Inspite of having create-or-extend-tables properties, the new column is not getting added up.

Here is my persistence.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="idpersistance">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

<class>com.id.service.db.pojo.AppTable</class>
<class>com.id.service.db.pojo.MyTable</class>

<properties>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"></property>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:2705/mydb"></property>
<property name="javax.persistence.jdbc.user" value="user"></property>
<property name="javax.persistence.jdbc.password" value="password"></property>

<property name="eclipselink.ddl-generation" value="create-or-extend-tables" /> 
<property name="eclipselink.ddl-generation.output-mode" value="database" />
<property name="eclipselink.id-validation" value="NULL"></property>
<property name="eclipselink.logging.level" value="FINE"/>
<property name="javax.persistence.lock.timeout" value="1000"/>
<property name="eclipselink.target-database" value="MySQL"/>

</properties>

</persistence-unit>
</persistence>

I am not sure why the new column is not getting added. I am getting back following error:

 Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
 Internal Exception: java.sql.SQLException: null,  message from server: "Unknown column 'newcol' in 'field list'"
 Error Code: 1054

The jar file that I am using:

 eclipselink.jar (2.5.2)
 javax.persistence_2.1.0.v201304241213.jar

Please let me know if any thing missing in my persistence.xml file.

Any help is Appreciated.

Update: I had debugged into EclipseLink code and found that it is not attempting to generate new columns, it throws an error at extendDefaultTables() method in EntityManagerFactoryProvider class and says table already exists and returns from that method.

There is a field called useExternalConnectionPooling in incrementCallCount() method of DatasourceAccessor class, if I set this field to true manually. Then new columns are getting generated successfully. Because of the field is false, EclipseLink is not triggering extend. I am not sure, if this is correct field to go with.

see http://stackoverflow.com/questions/13372213/eclipselink-create-or-extend-tables-not-working-unknown-column — Scary Wombat, Nov 06 '14 at 08:42
@Chris, I did not get you what you mean by. The tables are already created and now I am trying to add new column. — User12111111, Nov 07 '14 at 10:15
table creation is the process where EclipseLink will attempt to create the tables or extend them during deployment. Check the SQL that is generated when the persistence unit logs into the database and it should show you what might be happening — Chris, Nov 07 '14 at 15:01
I have debugged into EclipseLink code and found that it is not attempting to generate new columns, it throws an error at extendDefaultTables() method in EntityManagerFactoryProvider class and says table already exists and it doesn't attempt to create new column. There is a field called `useExternalConnectionPooling` in `incrementCallCount()` method of DatasourceAccessor class, if I set this field to true manually. Then new columns are gets generated successfully. Because of the field is set to false, EclipseLink is not triggering extending. I am not sure, what I am missing. — User12111111, Nov 08 '14 at 08:51
@Chris As you said to look at SQL persistence log, I see error w.r.t Table already exists. — User12111111, Nov 08 '14 at 08:52
If you are stepping into the code, step down into the TableCreator extendTables method. If the static CHECK_EXISTENCE boolean is true (default) then it will query to see if the table exists or just create it if false. If the table exists, then a DatabaseException is expected, but some problem must be occuring that isn't expected. If you are able to catch it in a debugging environment, it will tell you what is going wrong, though I suspect it has to do with your driver and setup parameters - the useExternalConnectionPooling flag should not need to be set. — Chris, Nov 13 '14 at 15:07
Please show the log at the finest log level, and an entity that isn't working. — Chris, Nov 13 '14 at 15:17
Did you fix this issue? I'm having the same issue with eclipselink 2.7.0. — javydreamercsw, Nov 03 '17 at 15:41

Hartmut Pfarr · Answer 1 · 2015-11-11T15:52:28.737

Set eclipselink.logging.level.sql to FINE, 1) by adjusting configuration files, or 2) programmatically:

Map<String, Object> x = new HashMap<>();
...
x.put("eclipselink.logging.level.sql", "FINE");
...
Persistence.createEntityManagerFactory("MyPersistenceUnit", x);

Then You'll get the SQL logs, where You can find statements like: ALTER TABLE myTab ADD myColumn ...

[EL Fine]: sql: 2015-11-11 16:24:14.042--ServerSession(667237426)--Connection(1844518545)--ALTER TABLE bm_picinfo ADD account BIGINT NOT NULL 
[EL Fine]: sql: 2015-11-11 16:24:14.066--ServerSession(667237426)--SELECT 1
[EL Warning]: 2015-11-11 16:24:14.092--ServerSession(667237426)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "account" contains null values

In the example above, the new field was defined as

   @ManyToOne(targetEntity = BmAccountE.class)
   @JoinColumn(name = "account", nullable = false, insertable = false, updatable = false)
   public BmAccountE account;

In this case, removing nullable = false was the solution.

JPA2: create-or-extend-tables is not extending existing table with new column

1 Answers1