Why my order of execute is wrong?


#1

Please, help me, i already week cannot find cause.
If hibernate.hbm2ddl.auto set-up “create-drop” i will be have a trouble:

dependences
<dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.1-api</artifactId>
        <version>1.0.2.Final</version>
</dependency>
<dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.3.7.Final</version>
</dependency>
<dependency>
        <groupId>org.hibernate.common</groupId>
        <artifactId>hibernate-commons-annotations</artifactId>
        <version>5.0.5.Final</version>
</dependency>
cfg.xml
<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>

        <property name = "hibernate.dialect">
            org.hibernate.dialect.MySQL57Dialect
        </property>

        <property name = "hibernate.connection.driver_class">
            com.mysql.cj.jdbc.Driver
        </property>

        <property name = "hibernate.connection.url">
            jdbc:mysql://192.168.1.29:3306/tdb?autoReconnect=true&amp;useSSL=false
        </property>

        <property name = "hibernate.connection.username">
            rt
        </property>

        <property name = "hibernate.connection.password">
            y4OjNKOI9xnMXuV20uG
        </property>

        <property name = "hibernate.temp.use_jdbc_metadata_defaults">
            false
        </property>
        <property name = "hibernate.hbm2ddl.auto">
            create-drop
        </property>
        <property name = "show_sql">
            true
        </property>
        <property name = "hibernate.id.new_generator_mappings">
            false
        </property>
        <property name = "hibernate.globally_quoted_identifiers">
            true
        </property>
        <property name="format_sql">
            false
        </property>
        <property name="use_sql_comments">
            false
        </property>
        <mapping class="db.TestDB01" />
        <mapping class="db.TestDB02" />
        <mapping class="db.TestDB03" />

    </session-factory>
</hibernate-configuration>
Users - TestDB01
import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.Cascade;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "users", schema = "tdb")
public class TestDB01 {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Getter	@Setter	private int id;
	@Column(name = "name")
	@Getter	@Setter	private String name;

	@Column(name = "age")
	@Getter	@Setter	private int age;

	@OnDelete(action = OnDeleteAction.CASCADE)
	@OneToMany(mappedBy = "user")
	@Getter	@Setter	private List<TestDB02> autos;

	public TestDB01() {
	}

	public TestDB01(String name, int age) {
		this.name = name;
		this.age = age;
		autos = new ArrayList<>();
	}

}
Autos - TestDB02
import lombok.Getter;
import lombok.Setter;
import javax.persistence.*;

@Entity
@Table(name = "autos")
public class TestDB02 {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Getter	@Setter	private int id;

	@Column (name = "model")
	@Getter	@Setter	private String model;

	@Column (name = "color")
	@Getter	@Setter	private String color;

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "user_id")
	@Getter	@Setter	private TestDB01 user;

	public TestDB02() {
	}

	public TestDB02(String model, String color) {
		this.model = model;
		this.color = color;
	}
}

Hibernate attempt do work, but order of sql is wrong:

  1. Hibernate: drop table if exists tdb.users
  2. Hibernate: alter table autos drop foreign key FKmx3qhsa37wyfkttnpc9niyb7t
    So, it gove me this
Summary
Nov 09, 2018 1:57:47 PM org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "drop table if exists `tdb`.`users`" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "drop table if exists `tdb`.`users`" via JDBC Statement
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlString(SchemaDropperImpl.java:375)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.applySqlStrings(SchemaDropperImpl.java:359)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.dropFromMetadata(SchemaDropperImpl.java:241)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.performDrop(SchemaDropperImpl.java:154)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:126)
	at org.hibernate.tool.schema.internal.SchemaDropperImpl.doDrop(SchemaDropperImpl.java:112)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:144)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
	at db.HibernateSessionFactoryUtil.getSessionFactory(HibernateSessionFactoryUtil.java:22)
	at db.UserDao.saveTest(UserDao.java:64)
	at db.UserService.saveTest(UserService.java:41)
	at Main.attempt5(Main.java:111)
	at Main.main(Main.java:15)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)

FK is correct. If i run manualy this two sql, only in reverce order, it will be work fine.
So, what i done wrong?

P.S. If i set-up hibernate.hbm2ddl.auto to “update”, then hibernate will be give me Exceptions, that “table already exist” (why it does? In this mode hibernate must attempt create table if it exist?)


#2

That could be because the tables in the DB don’t match the ones in the Hibernate mapping.

As for this issue:

Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

Your DB contains some other table which has a FK to users so dropping the users table fails.


#3

Sorry, name in DB and Classes is matches (i it see in my IDE)
I mean it : @Table(name = “users”, schema = “tdb”) - it match with name of table…
If cause was be so easy, then i no wrote this topic :frowning:

Sorry, i no see this in my DB or model


#4

Facing same issue here. Help is appreciated.


#5

Try to replicate the problem using this test case template.


#6

Wow! I checked repo and code from your link and attempt use…
So, test worked fine, and, after, i used persistence.xml in my project (for my test-db, user, driver, pass).
And it now working without trouble in “update”-mode!

persistance.xml
<persistence-unit name="test_ast_db_2" transaction-type="RESOURCE_LOCAL">

        <description>Hibernate test case template Persistence Unit</description>
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <exclude-unlisted-classes>false</exclude-unlisted-classes>

        <properties>
            <property name="hibernate.archive.autodetection" value="class, hbm"/>

            <!--<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>-->
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL57Dialect"/>
            <!--<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>-->
            <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver"/>
            <!--<property name="hibernate.connection.url" value="jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1"/>-->
            <property name="hibernate.connection.url" value="jdbc:mysql://192.168.1.29:3306/tdb?autoReconnect=true&amp;useSSL=false"/>
            <!--<property name="hibernate.connection.username" value="sa"/>-->
            <property name="hibernate.connection.username" value="rt"/>
            <property name="hibernate.connection.password" value="y4OjNKOI9xnMXuV20uGm" />

            <property name="hibernate.connection.pool_size" value="5"/>

            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>

            <property name="hibernate.max_fetch_depth" value="5"/>

            <property name="hibernate.cache.region_prefix" value="hibernate.test"/>
            <property name="hibernate.cache.region.factory_class"
                      value="org.hibernate.testing.cache.CachingRegionFactory"/>

            <!--NOTE: hibernate.jdbc.batch_versioned_data should be set to false when testing with Oracle-->
            <property name="hibernate.jdbc.batch_versioned_data" value="true"/>

            <property name="javax.persistence.validation.mode" value="NONE"/>
            <property name="hibernate.service.allow_crawling" value="false"/>
            <property name="hibernate.session.events.log" value="true"/>
        </properties>

    </persistence-unit>

So, great thanks to you!
Conclusions - before, i wrote wrong configuration…