Eclipselink update existing tables

Question

Maybe I got it wrong but i though that JPA was able to update an existing table (model changed adding a column) but is not working in my case.

I can see in the logs eclipselink attempting to create it but failing because it already exists. Instead of trying an update to add the column it keeps going.

<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jwrestling"/>
<property name="javax.persistence.jdbc.password" value="password"/>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.user" value="user"/>
<property name="eclipselink.ddl-generation" value="create-tables"/>
<property name="eclipselink.logging.logger" value="org.eclipse.persistence.logging.DefaultSessionLog"/>
<property name="eclipselink.logging.level" value="INFO"/>

And here's the table with the change (online column added)

[EL Warning]: 2010-05-31 14:39:06.044--ServerSession(16053322)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100517-r7246): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'account' already exists
Error Code: 1050
Call: CREATE TABLE account (ID INTEGER NOT NULL, USERNAME VARCHAR(32) NOT NULL, SECURITY_KEY VARCHAR(255) NOT NULL, EMAIL VARCHAR(64) NOT NULL, STATUS VARCHAR(8) NOT NULL, TIMEDATE DATETIME NOT NULL, PASSWORD VARCHAR(255) NOT NULL, ONLINE TINYINT(1) default 0 NOT NULL, PRIMARY KEY (ID))
Query: DataModifyQuery(sql="CREATE TABLE account (ID INTEGER NOT NULL, USERNAME VARCHAR(32) NOT NULL, SECURITY_KEY VARCHAR(255) NOT NULL, EMAIL VARCHAR(64) NOT NULL, STATUS VARCHAR(8) NOT NULL, TIMEDATE DATETIME NOT NULL, PASSWORD VARCHAR(255) NOT NULL, ONLINE TINYINT(1) default 0 NOT NULL, PRIMARY KEY (ID))")
[EL Warning]: 2010-05-31 14:39:06.074--ServerSession(16053322)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100517-r7246): org.eclipse.persistence.exceptions.DatabaseException

After this it continues with the following.

Am I doing something wrong or is a bug?

Answer 1

This is the expected behavior when using the create-tables value. From the documentation about the eclipselink.ddl-generation property:

Using EclipseLink JPA Extensions for Schema Generation

The following are the valid values for the use in a persistence.xml file:

• none – EclipseLink does not generate DDL; no schema is generated.
• create-tables – EclipseLink will attempt to execute a CREATE TABLE SQL for each table. If the table already exists, EclipseLink will follow the default behavior of your specific database and JDBC driver combination (when a CREATE TABLE SQL is issued for an already existing table). In most cases an exception is thrown and the table is not created. EclipseLink will then continue with the next statement. (See also eclipselink.create-ddl-jdbc-file-name.)
• drop-and-create-tables – EclipseLink will attempt to DROP all tables, then CREATE all tables. If any issues are encountered, EclipseLink will follow the default behavior of your specific database and JDBC driver combination, then continue with the next statement. (See also eclipselink.create-ddl-jdbc-file-name and eclipselink.drop-ddl-jdbc-file-name.)

So you might want drop-and-create-tables instead.