Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Failure create-and-extend-tables, foreign key creation, postgreSQL(create or extends tables fails if foreign key already exists)
Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1801022] Fri, 11 January 2019 09:46 Go to next message
Andreas B is currently offline Andreas BFriend
Messages: 2
Registered: January 2019
Junior Member
Hi,

I'm using eclipselink as my JPA provider. I use the property eclipselink.ddl-generation with the value create-or-extend-tables together with a postgreSQL database. Whenever my database did not contain any table, everything is fine and the tables are created as expected. But, if the tables are already existing within my postgreSQL database, a failure message "Constraint FK already exists" will thrown.

The FK is already existing, that is true but this is not a failure message for me. It sounds more or less as an warning message. I changed the logging level eclipselink.logging.level.sql to different states without any changes.

For my local environment I can ignore this failure message. But I using a payara-micro server and try to start my payara instance within an open-shift private cloud. Whenever I start my container, the container start fails related to the eclipselink failure message.

I using the latest eclipse link version 2.7.3.v20180807-4be1041. Within my database (postgreSQL) all permissions are set to the related database user.

My questions:
I there any additional configuration required to avoid this failure / eclipse link behaviour (create FK relation only if not already existing)? Is there any opportunity to avoid the output of this specific failure?

Thanks,
Andreas

Report message to a moderator

Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835013 is a reply to message #1801022] Mon, 23 November 2020 11:19 Go to previous messageGo to next message
Luigi Annuzzi is currently offline Luigi AnnuzziFriend
Messages: 3
Registered: November 2020
Junior Member
Hello!

Would need one an annotation for esclude this Entity Class from other.

If i'ts possibile eclude the "extend" of this class. the problem is partial solved.

Report message to a moderator

Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835043 is a reply to message #1835013] Mon, 23 November 2020 21:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
There is no way to avoid this issue as foreign key handling was left out of the original feature development - Determining if a constraint exists is platform specific and error prone, as many are created as generic unnamed restrictions. Extending schemas was aimed at an easy way to add columns, not complex foreign key or constraint additions - it tries to add them on a per table basis, which works if is creating the table on this iterative change, but won't if it already exists. You can file features to extend this support (within the TableCreator.java class), but I haven't seen much call for the behaviour, and as everything gets left behind as a schema develops, you are better off using a drop-and-create-tables option for these types of changes occasionally anyway.

Output should not be logging stack traces, and there should be no errors 'thrown' so what ever issue you are hitting seems to be a logging one. Please show the messages you are getting you wish to avoid, and show the log settings, and maybe we can determine why you are seeing them if you've already turned the appropriate logging off.

Report message to a moderator

Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835077 is a reply to message #1835043] Tue, 24 November 2020 11:44 Go to previous messageGo to next message
Luigi Annuzzi is currently offline Luigi AnnuzziFriend
Messages: 3
Registered: November 2020
Junior Member
Hello Chris,
thanks for the thorough answer!

I explain the problem better (with error in MySQL).

I have 2 Entities;

@Entity
@Table(name = "users")
public class User implements Serializable {

    // ...

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    @Column(name = "username", nullable = false, unique = true)
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    
    @Column(name = "user_password", nullable = false)
    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }

}// User

@Entity
@Table(name = "qualities")
public class Quality implements Serializable {

    // ...

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }

    @Column(name = "description", nullable = false)
    public String getDescription() { return description; }
    public void setDescription(String description) { this.description = description; }
    
    
    /* 
    private String newColumn;    // I wanna add ...
    
    @Column(name = "new_column", nullable = true)
    public String getNewColumn() { return newColumn; }
    public void setNewColumn(String newColumn) { this.newColumn = newColumn; }
    */
    	
    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)	
    public User getUser() { return user; }
    public void setUser(User user) { this.user = user; }

}// Quality



with this configuration:
<property name="eclipselink.target-database" value="MySQL"/>
<property name="eclipselink.logging.level" value="WARNING"/>
<property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="database"/>


if schema doesn't exist, this is created correctly

at second run, because the schema exist, i have this error:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-f94_7b'
Error Code: 1022
Call: ALTER TABLE qualities ADD CONSTRAINT FK_qualities_user_id FOREIGN KEY (user_id) REFERENCES users (id)
Query: DataModifyQuery(sql="ALTER TABLE qualities ADD CONSTRAINT FK_qualities_user_id FOREIGN KEY (user_id) REFERENCES users (id)")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:908)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:970)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:640)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2096)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:304)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:284)
	at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:87)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914)
	at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:3349)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1827)
	at org.eclipse.persistence.internal.sessions.AbstractSession.priviledgedExecuteNonSelectingCall(AbstractSession.java:5212)
	at org.eclipse.persistence.tools.schemaframework.TableDefinition.createForeignConstraintsOnDatabase(TableDefinition.java:896)
	at org.eclipse.persistence.tools.schemaframework.SchemaManager.createForeignConstraints(SchemaManager.java:194)
	at org.eclipse.persistence.tools.schemaframework.TableCreator.createConstraints(TableCreator.java:119)
	at org.eclipse.persistence.tools.schemaframework.TableCreator.createConstraints(TableCreator.java:96)
	at org.eclipse.persistence.tools.schemaframework.TableCreator.extendTables(TableCreator.java:550)
	at org.eclipse.persistence.tools.schemaframework.TableCreator.extendTablesAndConstraints(TableCreator.java:419)
	at org.eclipse.persistence.tools.schemaframework.TableCreator.extendTables(TableCreator.java:410)
	at org.eclipse.persistence.tools.schemaframework.SchemaManager.extendDefaultTables(SchemaManager.java:1187)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryProvider.generateDefaultTables(EntityManagerFactoryProvider.java:116)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.writeDDLToDatabase(EntityManagerSetupImpl.java:4398)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.writeDDL(EntityManagerSetupImpl.java:4326)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.writeDDL(EntityManagerSetupImpl.java:4226)
	at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:816)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:219)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:327)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:350)
	at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:313)
	at org.glassfish.persistence.jpa.JPADeployer$2.visitPUD(JPADeployer.java:461)
	at org.glassfish.persistence.jpa.JPADeployer$PersistenceUnitDescriptorIterator.iteratePUDs(JPADeployer.java:525)
	at org.glassfish.persistence.jpa.JPADeployer.iterateInitializedPUsAtApplicationPrepare(JPADeployer.java:507)
	at org.glassfish.persistence.jpa.JPADeployer.event(JPADeployer.java:408)
	at org.glassfish.kernel.event.EventsImpl.send(EventsImpl.java:131)
	at com.sun.enterprise.v3.server.ApplicationLifecycle.prepare(ApplicationLifecycle.java:562)
	at org.glassfish.deployment.admin.DeployCommand.execute(DeployCommand.java:561)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$2$1.run(CommandRunnerImpl.java:558)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$2$1.run(CommandRunnerImpl.java:554)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAs(Subject.java:361)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$2.execute(CommandRunnerImpl.java:553)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$3.run(CommandRunnerImpl.java:584)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$3.run(CommandRunnerImpl.java:576)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/javax.security.auth.Subject.doAs(Subject.java:361)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:575)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl.doCommand(CommandRunnerImpl.java:1496)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl.access$1300(CommandRunnerImpl.java:120)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1878)
	at com.sun.enterprise.v3.admin.CommandRunnerImpl$ExecutionContext.execute(CommandRunnerImpl.java:1754)
	at com.sun.enterprise.v3.admin.AdminAdapter.doCommand(AdminAdapter.java:564)
	at com.sun.enterprise.v3.admin.AdminAdapter.onMissingResource(AdminAdapter.java:251)
	at org.glassfish.grizzly.http.server.StaticHttpHandlerBase.service(StaticHttpHandlerBase.java:166)
	at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:520)
	at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:217)
	at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:182)
	at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:156)
	at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:218)
	at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:95)
	at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:260)
	at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:177)
	at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:109)
	at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:88)
	at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:53)
	at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:524)
	at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:89)
	at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:94)
	at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:33)
	at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:114)
	at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:569)
	at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:549)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-f94_7b'
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1915)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2136)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2070)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5187)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2055)
	at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:127)
	at jdk.internal.reflect.GeneratedMethodAccessor904.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40$1.invoke(ProfiledConnectionWrapper40.java:437)
	at com.sun.proxy.$Proxy344.executeUpdate(Unknown Source)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
	... 76 more



Note #1: the variable newColumn is, both at the first run and at the second, still in the comments.

Note #2: If remove this part ...
@ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)	
    public User getUser() { return user; }
    public void setUser(User user) { this.user = user; }

... I don't get errors if I remove newColumn from the comments (and the column will be replicated correctly on the table).

However, as you advised me, "drop-and-create-tables" remains the best method.

Report message to a moderator

Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835317 is a reply to message #1835077] Mon, 30 November 2020 15:52 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
As mentioned, this isn't an error that is thrown - your application deploy process should be returning successfully.
The line at org.eclipse.persistence.tools.schemaframework.SchemaManager.extendDefaultTables(SchemaManager.java:1187)
is wrapped in a try/catch block that just ignores the exceptions, so this can only be showing up in your logging as a warning.

I am surprised to see the exception stack trace in there as that same method tries to call:
this.session.getSessionLog().setShouldLogExceptionStackTrace(false);

so I suspect this has to do with the server you are using injecting its own logging mechanism into your persistence unit. You'll need to configure logging through your server console/settings, or try adding the <property name="eclipselink.logging.logger" value="DefaultLogger"/> persistence property to see if that lets you control it though persistence properties and direct to standard out.

Report message to a moderator

Previous Topic:EclipseLink Create-Or-Extend-Tables Foreign Key already exists
Next Topic:Quoted table names in @Column(..., table = "\"Addresses\"") produces DescriptorE
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jun 07 06:55:26 GMT 2023

Powered by FUDForum. Page generated in 0.02094 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly