Eclipse Community Forums: EclipseLink » Eclipselink.ddl-generation : drop-and-create-tables don't drop tables

Help

Home

Home » Eclipse Projects » EclipseLink » Eclipselink.ddl-generation : drop-and-create-tables don't drop tables

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

Eclipselink.ddl-generation : drop-and-create-tables don't drop tables [message #691879]

Sat, 02 July 2011 17:32

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.

Report message to a moderator

Re: Eclipselink.ddl-generation : drop-and-create-tables don't drop tables [message #692448 is a reply to message #691879]

Mon, 04 July 2011 13:04

Gordon Yorke

Messages: 78
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.

Report message to a moderator

(no subject) [message #692451 is a reply to message #691879]

Mon, 04 July 2011 13:04

Gordon Yorke

Messages: 78
Registered: July 2009

Member

Report message to a moderator

Re: (no subject) [message #694047 is a reply to message #692451]

Thu, 07 July 2011 17:27

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

Report message to a moderator

Re: (no subject) [message #694371 is a reply to message #694047]

Fri, 08 July 2011 12:49

Chris Delahunt

Messages: 1389
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

Report message to a moderator

Re: (no subject) [message #694739 is a reply to message #694371]

Sat, 09 July 2011 14:12

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 !

Report message to a moderator

(no subject) [message #694755 is a reply to message #694371]

Sat, 09 July 2011 14:12

Xavier

Messages: 6
Registered: July 2009

Junior Member

Report message to a moderator

Re: (no subject) [message #696175 is a reply to message #694755]

Wed, 13 July 2011 12:31

Chris Delahunt

Messages: 1389
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

Report message to a moderator

Re: (no subject) [message #698556 is a reply to message #696175]

Tue, 19 July 2011 17:20

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

Report message to a moderator

Previous Topic:	@GeneratedValue(strategy=GenerationType.AUTO)
Next Topic:	how to automatically refresh a cached namedquery after object insertion/deletion ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:33:40 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter