Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
RE: [eclipselink-users] Alter table doesn't find table previously created during DDL - autocommit==true
Eric,
    Good question on using the same datasource for ddl-generation and runtime operations.
    The ddl-generation functionality should really have it's own persistence unit and jdbc connection - I would use another datasource for the runtime app persistence unit.
    Why? ddl-generation is really only meant for demo's or to setup your database, when your app is in production I would turn off the two ddl-generation flags in your persistence unit so you have fully control over your schema.
 
    2 Persistence Units:
    For some of my applications I have a persistence.xml file that contains 2 persistence units - one for setup of a new DB that is RESOURCE_LOCAL, the other one JTA for running the app - you can then fully utilize the transaction API on the container.
 
    Not being an expert but we must assume that autocommit=true during ddl-generation as in some cases like for Oracle there is an implicit (hidden) commit that occurs when you do a TABLE CREATE or DROP - which i have run into.
 
    thank you
    /michael
-----Original Message-----
From: Eric Gulatee [mailto:eric.gulatee@xxxxxxxxx]
Sent: Thursday, September 04, 2008 11:45
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] Alter table doesn't find table previously created during DDL

Michael, Chris,

Thanks guys.  You're right.  My Pooled Connection did have auto commit turned off.  My bad, I appologize, Especially since you mentioned it to begin with.

I do have a concern about auto-commit = ture however. 
If you're going to use the same DS for DDL generation (And thus autocommit = true) as well as the application.

That would mean you can't have a transaction with more db operation unless the app manually sets the autocommit to false.  [From the stuff I've read after you mentionned autocommit needs to be true]

This being a pain, it would be much nicer if the eclipselink DDL export/generation would enable autocommit on the ds it uses and then sets it back to whatever setting it was at.  [Instead of requiring it set to autocommit = true prior to eclipse link using it]

Any objections?  Opinions?

Cheers,
Eric G.

On Wed, Sep 3, 2008 at 8:50 PM, <MICHAEL.OBRIEN@xxxxxxxxxx> wrote:
Eric,
    That is very good.
    Your non-pooled datasource uses the default auto-commit setting of true.
    EclipseLink should work with the dbcp pooled library.
    Either of your datasources should work, If you want to stll use a pooled datasource - try changing the 6th parameter (index 5) from false to true to enable auto-commit.
 
    thank you
    /michael
-----Original Message-----
From: Eric Gulatee [mailto:eric.gulatee@xxxxxxxxx]
Sent: Wednesday, September 03, 2008 16:57
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] Alter table doesn't find table previously created during DDL

Michael,

Well I got my solution. 

I changed the DS to a non pooled data source and... voila!  It works!  Tables are created and my app starts. 
Just to make certain it wasn't another change, I put it back to pooled, wiped out the the db tables and it broke trying to create them

FYI.
I was using a
org.apache.commons.dbcp.PoolingDataSource

Now am using a
org.apache.commons.dbcp.BasicDataSource

I am guessing eclipselink does pooling.  I guess an RFE might be in order to allow external pooling libraries like dbcp.

Cheers,
Eric G.



On Wed, Sep 3, 2008 at 2:13 PM, Eric Gulatee <eric.gulatee@xxxxxxxxx> wrote:
Michael,

Thanks again for your interest & help.

Well, the other property settings are being picked up when configured in the spring xml file.  [I will attach]

I'll attach my complete logs.  (Just in case I have missed sometrhing, but I'd expect to see some sort of exception or error).
[Aside from the non reweavable messages]

The DB configuration is done in spring and is a pooled data source.

Cheers,
Eric Gulatee



On Wed, Sep 3, 2008 at 2:01 PM, Eric Gulatee <eric.gulatee@xxxxxxxxx> wrote:
Michael,

Thank you for the quick response.

Well I do have resource_local transaction type in my persistence.xml.  (See below) 

The datasource is of type:  org.apache.commons.dbcp.PoolingDataSource

    derbyurl= "jdbc:derby://localhost:1527/digitalart;create=true";
derbydriver = org.apache.derby.jdbc.ClientDriver

Maybe I should not have a pooled connection as a datasource?  Would that have any impact?

The SQL generated is good when I execute through an SQL Tool.  [I added spring config  to generate the create & drop tables]

Thank you for your interest. 

    <bean id="bds" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" ref="derbydriver" />
        <property name="url" ref="derbyurl" />
    </bean>
    <bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
        <constructor-arg>
            <ref bean="bds" />
        </constructor-arg>
    </bean>
    <bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
        <constructor-arg index="0">
            <ref bean="dsConnectionFactory" />
        </constructor-arg>
        <constructor-arg index="1">
            <ref bean="pool" />
        </constructor-arg>
        <constructor-arg index="2">
            <null />
        </constructor-arg>
        <constructor-arg index="3">
            <value>SELECT 1</value>
        </constructor-arg>
        <constructor-arg index="4">
            <value>false</value>
        </constructor-arg>
        <constructor-arg index="5">
            <value>false</value>
        </constructor-arg>
    </bean>
    <bean id="daDataSource" class="org.apache.commons.dbcp.PoolingDataSource"
        depends-on="poolableConnectionFactory">
        <constructor-arg>
            <ref bean="pool" />
        </constructor-arg>
    </bean></beans>




<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    version="1.0">
    <persistence-unit name="DigitalArtJPA"
        transaction-type="RESOURCE_LOCAL">
        <class>da.db.model.ArtCategory</class>
        <class>da.db.model.ArtMovement</class>
        <class>da.db.model.AuditLog</class>
        <class>da.db.model.Buyer</class>
        <class>da.db.model.CartItem</class>
        <class>da.db.model.Commission</class>
        <class>da.db.model.DigitalContent</class>
        <class>da.db.model.DigitalItem</class>
        <class>da.db.model.DigitalItemTag</class>
        <class>da.db.model.Forum</class>
        <class>da.db.model.Gallery</class>
        <class>da.db.model.GalleryTag</class>
        <class>da.db.model.Group</class>
        <class>da.db.model.Post</class>
        <class>da.db.model.Price</class>
        <class>da.db.model.Rating</class>
        <class>da.db.model.RatingAggregate</class>
        <class>da.db.model.RatingContainer</class>
        <class>da.db.model.RememberMe</class>
        <class>da.db.model.Seller</class>
        <class>da.db.model.User</class>
       
    </persistence-unit>
</persistence>




On Wed, Sep 3, 2008 at 1:34 PM, <MICHAEL.OBRIEN@xxxxxxxxxx> wrote:
Eric,
    Hi, The cause should be due to no auto-commit set, or ddl-generation is running in a JTA transaction.
    I noticed that your RESOURCE_LOCAL jdbc settings were not set in persistence.xml - but they evidently get picked up by the spring config or in properties set in your app.
 
    Since the spring containter framework is another layer on top of EclipseLink - i would like to adapt an example scenario for the combination Tomcat/Derby with/without Spring.
    This is an important proof-of-concept app and I will get back to you on my results and post the example to the following JPA examples page.
 
   
    thank you
    /michael
-----Original Message-----
From: Eric Gulatee [mailto:eric.gulatee@xxxxxxxxx]
Sent: Wednesday, September 03, 2008 12:34
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] Alter table doesn't find table previously created during DDL

All,

I vaguely remember having configured properties for hibernate in the EntityManagerFactory. So I moved the settings you indicated in the spring config.  Things seem to behave slightly differently when I configuring the properties you mention to put  in persistence.xml in the spring configuration.  (More logging (finest), so the settings take only if I put in string config )  However I still have a failure on altering tables and the tables DO NOT exist.

I realize the tomcat logs don't show the info stuff I see on the console.
Anyhow, I will attach my spring config. 

Has anyone gotten spring & eclipselink & ddl generation working together?  Any examples or other ideas?  Likely something silly I am not doing. 


[EL Info]: 2008.09.03 12:22:20.217--ServerSession(18560421)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 1.1 (B
ild SNAPSHOT-20080901)
[EL Config]: 2008.09.03 12:22:21.842--ServerSession(18560421)--Connection(5262695)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
        platform=>DerbyPlatform
        user name=> ""
        connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2008.09.03 12:22:26.061--ServerSession(18560421)--Connection(4052150)--Thread(Thread[main,5,main])--Connected: jdbc:derby://localhost:15
7/digitalart;create=true
        User: APP
        Database: Apache Derby  Version: 10.4.1.3 - (648739)
        Driver: Apache Derby Network Client JDBC Driver  Version: 10.4.1.3 - (648739)
[EL Config]: 2008.09.03 12:22:26.061--ServerSession(18560421)--Connection(22549381)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
        platform=>DerbyPlatform
        user name=> ""
        connector=>JNDIConnector datasource name=>null
))
[EL Config]: 2008.09.03 12:22:26.061--ServerSession(18560421)--Connection(23700826)--Thread(Thread[main,5,main])--Connected: jdbc:derby://localhost:1
27/digitalart;create=true
        User: APP
        Database: Apache Derby  Version: 10.4.1.3 - (648739)
        Driver: Apache Derby Network Client JDBC Driver  Version: 10.4.1.3 - (648739)
[EL Finest]: 2008.09.03 12:22:26.170--ServerSession(18560421)--Thread(Thread[main,5,main])--sequencing connected, state is Preallocation_Transaction_
oAccessor_State
[EL Finest]: 2008.09.03 12:22:26.170--ServerSession(18560421)--Thread(Thread[main,5,main])--sequence : preallocation size 50
[EL Finest]: 2008.09.03 12:22:26.170--ServerSession(18560421)--Thread(Thread[main,5,main])--sequence SEQ_GEN: preallocation size 50
[EL Info]: 2008.09.03 12:22:26.327--ServerSession(18560421)--Thread(Thread[main,5,main])--file:/C:/development/apache-tomcat-6.0.14/webapps/DigitalAr
Web/WEB-INF/lib/da-db-0.0.1-SNAPSHOT.jar-DigitalArtJPA login successful
[EL Finest]: 2008.09.03 12:22:26.577--ServerSession(18560421)--Thread(Thread[main,5,main])--Execute query DataModifyQuery()
[EL Finest]: 2008.09.03 12:22:26.592--ServerSession(18560421)--Thread(Thread[main,5,main])--reconnecting to external connection pool
[EL Fine]: 2008.09.03 12:22:26.592--ServerSession(18560421)--Connection(23310932)--Thread(Thread[main,5,main])--CREATE TABLE AUDITLOG (ID BIGINT NOT
ULL, USERNAME VARCHAR(255), SESSIONID VARCHAR(255), ACTION VARCHAR(255), MESSAGE VARCHAR(255), IP VARCHAR(255), USERAGENT VARCHAR(255), TIMESTAMP TIM
STAMP, PRIMARY KEY (ID))
[EL Finest]: 2008.09.03 12:22:27.030--Thread(Thread[main,5,main])--The table (AUDITLOG) is created.

[EL Fine]: 2008.09.03 12:22:39.733--ServerSession(18560421)--Connection(16233020)--Thread(Thread[main,5,main])--CREATE TABLE RATING (ID BIGINT NOT NUL
L, VALUE INTEGER, CONTAINER_ID BIGINT, USERWHOVOTED_ID BIGINT, PRIMARY KEY (ID))
[EL Finest]: 2008.09.03 12:22:40.170--Thread(Thread[main,5,main])--The table (RATING) is created.
[EL Finest]: 2008.09.03 12:22:40.170--ServerSession(18560421)--Thread(Thread[main,5,main])--Execute query DataModifyQuery()
[EL Finest]: 2008.09.03 12:22:40.170--ServerSession(18560421)--Thread(Thread[main,5,main])--reconnecting to external connection pool
[EL Fine]: 2008.09.03 12:22:40.170--ServerSession(18560421)--Connection(25626423)--Thread(Thread[main,5,main])--CREATE TABLE FORUM (ID BIGINT NOT NULL
, NAME VARCHAR(255), GALLERY_ID BIGINT, IMAGE_ID BIGINT, PRIMARY KEY (ID))
[EL Finest]: 2008.09.03 12:22:40.498--Thread(Thread[main,5,main])--The table (FORUM) is created.
[EL Finest]: 2008.09.03 12:22:40.498--ServerSession(18560421)--Thread(Thread[main,5,main])--Execute query DataModifyQuery()
[EL Finest]: 2008.09.03 12:22:40.498--ServerSession(18560421)--Thread(Thread[main,5,main])--reconnecting to external connection pool
[EL Fine]: 2008.09.03 12:22:40.498--ServerSession(18560421)--Connection(26133207)--Thread(Thread[main,5,main])--ALTER TABLE GROUPS_GROUPS ADD CONSTRAI
NT GRUPSGROUPSGroupID FOREIGN KEY (Group_ID) REFERENCES GROUPS (ID)
[EL Fine]: 2008.09.03 12:22:40.733--ServerSession(18560421)--Thread(Thread[main,5,main])--VALUES(1)
[EL Warning]: 2008.09.03 12:22:40.811--ServerSession(18560421)--Thread(Thread[main,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Service
s - 1.1 (Build SNAPSHOT-20080901)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: 'ALTER TABLE' cannot be performed on 'GROUPS_GROUPS' because it does not exist.
Error Code: -1
Call: ALTER TABLE GROUPS_GROUPS ADD CONSTRAINT GRUPSGROUPSGroupID FOREIGN KEY (Group_ID) REFERENCES GROUPS (ID)
Query: DataModifyQuery()
[EL Finest]: 2008.09.03 12:22:40.842--ServerSession(18560421)--Thread(Thread[main,5,main])--Execute query DataModifyQuery()
[EL Finest]: 2008.09.03 12:22:40.842--ServerSession(18560421)--Thread(Thread[main,5,main])--reconnecting to external connection pool




_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users





_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


Back to the top