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  |
Andreas B 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
|
|
| |
Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835043 is a reply to message #1835013] |
Mon, 23 November 2020 21:04   |
Chris Delahunt 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.
|
|
|
Re: Failure create-and-extend-tables, foreign key creation, postgreSQL [message #1835077 is a reply to message #1835043] |
Tue, 24 November 2020 11:44   |
Luigi Annuzzi 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.
|
|
| |
Goto Forum:
Current Time: Sat Dec 02 22:59:23 GMT 2023
Powered by FUDForum. Page generated in 0.02066 seconds
|