Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink.ddl-generation : drop-and-create-tables don't drop tables
Eclipselink.ddl-generation : drop-and-create-tables don't drop tables [message #691879] Sat, 02 July 2011 13:32 Go to next message
Xavier  is currently offline Xavier
Messages: 3
Registered: March 2011
Junior Member
Hi,

I'm trying to implement a persistence layer and get stuck without being able to understand why.

I'm using Eclipselink 2.3.0 and try to write on a MySQL DB (I got the same proble when I tried on Derby, so *I suppose the DB doesn't matter), with the Eclipselink drop-and-create-tables option in the persistence.xml.

This works once, and then produces this kind of error :

[EL Warning]: 2011-07-02 15:11:32.669--ServerSession(31248093)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'PROJECT' already exists
Error Code: 1050
Call: CREATE TABLE PROJECT ...


I thought it could be qomething related to cache, googled and found some things like this for example :

<shared-cache-mode>NONE</shared-cache-mode>


And some variants that I tried in my persistence.xml, but nothing worked. The tests run fine once in a while, without any apparent reason. For example, they can pass just after a minor modification of the persistence.xml, but fail if I try to run them again. And the opposite happens, too.

This kind of problem didn't happen when I used Eclipselink 1.1.0. But it happens with every Eclipselink 2.x.

Here's my persistence.xml :

<?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="azerty" transaction-type="RESOURCE_LOCAL">
		<shared-cache-mode>NONE</shared-cache-mode>
		<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
 		<class>...</class>
		<properties>
			<property name="eclipselink.target-database" value="MYSQL" />
			<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:mysql://azerty.net:3306/azerty_test" />
			<property name="javax.persistence.jdbc.user" value="azerty_test" />
			<property name="javax.persistence.jdbc.password" value="password" />
			<property name="eclipselink.ddl-generation" value="drop-and-create-tables" />
			<property name="eclipselink.ddl-generation.output-mode" value="both" />
			<property name="eclipselink.logging.level" value="FINE" />
		</properties>
	</persistence-unit>
</persistence>


I'm ready to test every idea you may have, because I've been stuck for several hours now...

Thanks in advance.
Re: Eclipselink.ddl-generation : drop-and-create-tables don't drop tables [message #692448 is a reply to message #691879] Mon, 04 July 2011 09:04 Go to previous messageGo to next message
Gordon Yorke is currently offline Gordon Yorke
Messages: 77
Registered: July 2009
Member
This appears to be a DDL generation issue and has nothing to do with Cache settings. Is there an error raised when the 'PROJECT' table is dropped? Are you adding mappings between re-deployments? The Tables are dropped based on the current structure of the Persistence Unit not the structure of the previous deployment. It is possible the drop is failing because you have changed the mapping or the classes in the Persistence Unit and the drop of a related table is missing and the 'PROJECT' table then fails to drop.

The properties in the persistence.xml file suggest you are generating scripts. You may want to use the drop script from the previous deployment to remove the tables.
(no subject) [message #692451 is a reply to message #691879] Mon, 04 July 2011 09:04 Go to previous messageGo to next message
Gordon Yorke is currently offline Gordon Yorke
Messages: 77
Registered: July 2009
Member
This appears to be a DDL generation issue and has nothing to do with Cache settings. Is there an error raised when the 'PROJECT' table is dropped? Are you adding mappings between re-deployments? The Tables are dropped based on the current structure of the Persistence Unit not the structure of the previous deployment. It is possible the drop is failing because you have changed the mapping or the classes in the Persistence Unit and the drop of a related table is missing and the 'PROJECT' table then fails to drop.

The properties in the persistence.xml file suggest you are generating scripts. You may want to use the drop script from the previous deployment to remove the tables.
Re: (no subject) [message #694047 is a reply to message #692451] Thu, 07 July 2011 13:27 Go to previous messageGo to next message
Xavier  is currently offline Xavier
Messages: 3
Registered: March 2011
Junior Member
Hi,

Thanks for replying, and sorry for the late reply.

Here is the trace for the one of the tables (all of them generate the same trace).


[EL Fine]: 2011-07-07 19:11:27.165--ServerSession(27978063)--Connection(3916302)--Thread(Thread[main,5,main])--DROP TABLE PROJECT
[EL Fine]: 2011-07-07 19:11:27.166--ServerSession(27978063)--Connection(3916302)--Thread(Thread[main,5,main])--CREATE TABLE PROJECT (ID BIGINT NOT NULL, CLOSUREDATE DATETIME, OFFERSELECTIONDATE DATETIME, STATUS VARCHAR(255) NOT NULL, TENDERDATE DATETIME, PRIMARY KEY (ID))
... Some other DB actions
[EL Warning]: 2011-07-07 19:11:27.408--ServerSession(9170930)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'PROJECT' already exists
Error Code: 1050
Call: CREATE TABLE PROJECT (ID BIGINT NOT NULL, CLOSUREDATE DATETIME, OFFERSELECTIONDATE DATETIME, STATUS VARCHAR(255) NOT NULL, TENDERDATE DATETIME, PRIMARY KEY (ID))
Query: DataModifyQuery(sql="CREATE TABLE PROJECT (ID BIGINT NOT NULL, CLOSUREDATE DATETIME, OFFERSELECTIONDATE DATETIME, STATUS VARCHAR(255) NOT NULL, TENDERDATE DATETIME, PRIMARY KEY (ID))")


I ended up using Eclipselink 1.2 (implementing JPA 2), which does what I want it to do. That doesn't answer the 'why', but it works.

Thanks anyway !

X
Re: (no subject) [message #694371 is a reply to message #694047] Fri, 08 July 2011 08:49 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
Hello,

From your description, it looks like the initial create table Project statement is succeeding with other statements occuring before another create table Project statement occurs again and fails. If so, then it is working - the table is being dropped and then recreated. The problem is that later on there is another create table project statement. This is only a warning that can be ignored, but feel free to file a bug if you can create a small test project that reproduces the issue to have the reason for the second create statement looked into - the smaller the test case the better.

Best Regards,
Chris
Re: (no subject) [message #694739 is a reply to message #694371] Sat, 09 July 2011 10:12 Go to previous messageGo to next message
Xavier  is currently offline Xavier
Messages: 3
Registered: March 2011
Junior Member
Hi Chris,

Yes, it's only a warning, but my tests still don't run.

The problem isn't that the table gets created once or twice, but that I try to insert test data (always the same from one test session to another), designed to never allow duplicate entries, and that those insertions work with eclipselink 1.2 (the tables get dropped, recreated, and data gets inserted without problem), but not with eclipselink 2.3.

Given the error messages, I thought at first that the DB didn't drop the tables, but as you say, it seems that they're dropped fine, but recreated twice. So is it possible that the test data gets inserted twice too, thus the integrity constraint violation ?

I haven't looked further lately, but I will, and try to submit runnable short tests illustrating this.

Thanks !
(no subject) [message #694755 is a reply to message #694371] Sat, 09 July 2011 10:12 Go to previous messageGo to next message
Xavier is currently offline Xavier
Messages: 6
Registered: July 2009
Junior Member
Hi Chris,

Yes, it's only a warning, but my tests still don't run.

The problem isn't that the table gets created once or twice, but that I try to insert test data (always the same from one test session to another), designed to never allow duplicate entries, and that those insertions work with eclipselink 1.2 (the tables get dropped, recreated, and data gets inserted without problem), but not with eclipselink 2.3.

Given the error messages, I thought at first that the DB didn't drop the tables, but as you say, it seems that they're dropped fine, but recreated twice. So is it possible that the test data gets inserted twice too, thus the integrity constraint violation ?

I haven't looked further lately, but I will, and try to submit runnable short tests illustrating this.

Thanks !
Re: (no subject) [message #696175 is a reply to message #694755] Wed, 13 July 2011 08:31 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 995
Registered: July 2009
Senior Member
Hello,

If the problem with your test is the test data, you should see the SQL being executed (if through EclipseLink) in the log. Is it showing up twice?

It is strange that create tables is being called twice. Could there be two persistence units, one with drop-create and another with just create that are using the same classpath?

Best Regards,
Chris
Re: (no subject) [message #698556 is a reply to message #696175] Tue, 19 July 2011 13:20 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Set the log level on finest. The drop is failing most likely of a constraint from another table. Include the full log with all of the constraints and errors from the drop.


James : Wiki : Book : Blog : Twitter
Previous Topic:@GeneratedValue(strategy=GenerationType.AUTO)
Next Topic:how to automatically refresh a cached namedquery after object insertion/deletion ?
Goto Forum:
  


Current Time: Fri Apr 18 19:26:52 EDT 2014

Powered by FUDForum. Page generated in 0.01869 seconds