6

I have pretty much the same problem like this user. Hibernate can't drop the tables of my in-memory test database after each SpringBootTest (e.g. when running mvn test). The desired behavior would be ddl-auto=create-drop, but this doesn't work.

I think the reason could be an invalid order of the DROP TABLE statements, so that Hibernate tries to delete tables on which other tables still depend on.

My data.sql script only contains INSERT statements and the schema is automatically created based on my entities. I tried adding DROP TABLE statements to the top of data.sql and they all pass (ddl-auto=create), because I can specify the order in which they have to be dropped. On the other side, I now have to specify the schema creation in data.sql too..

Is there a way to specify the order of drop statements while not having to specify the schema creation? Or does anyone know a solution for the initial problem?

EDIT:

I want to give an example. I have an User entity that has relationships to other entities (M:N, 1:N, 1:1). When the schema is created, hibernate drops all tables, creates them and adds constrains:

// first test file:
Hibernate: drop table user if exists
... // drop other tables
Hibernate: create table user (username varchar(255) not null, ... , primary key (username))
... // create other tables
Hibernate: alter table X add constraint FKgi38hy0tsrdm332gdjrc0uhm3 foreign key (username) references user
Hibernate: alter table Y add constraint FK5svpy1b71l4jxni0xylrbbdtv foreign key (username) references user
Hibernate: alter table Z add constraint FK5a8fxbb0ug3eo1lisdrrxbbj foreign key (username) references user

// next test file:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Cannot drop "USER" because "FKGI38HY0TSRDM332GDJRC0UHM3, FK5SVPY1B71L4JXNI0XYLRBBDTV, FK5A8FXBB0UG3EO1LISDRRXBBJ" depends on it; SQL statement:
drop table user if exists [90107-200]

This process doesn't work after the first test file, because it violates the constrains. This is why I wanted to specify the drop order.

I don't use CascadeType on my entities, could that cause the problem?

Melkor
  • 243
  • 2
  • 14
  • Can you show your mapping and the drop statements? – Simon Martinelli Jan 02 '20 at 10:21
  • Do you really want the code of all my entities? – Melkor Jan 02 '20 at 10:25
  • First post the drop statements and tell us on which drop it fails. Then we will see if there is an issue with the mapping – Simon Martinelli Jan 02 '20 at 10:27
  • The drop statements don't fail if I specify them manually. It fails if my data.sql only contains the insert statements. But Hibernate should drop the tables after each test, because I use create-drop. This doesn't work tho (error in linked question) – Melkor Jan 02 '20 at 10:32
  • Oracle address this problem with the [CASCADE CONSTRAINTS](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm#SQLRF54847) clause of the `DROP TABLE` statement. H2 has also `CASCADE `[option](https://www.tutorialspoint.com/h2_database/h2_database_drop.htm) but apparently relevant only for views. Having said this it will not help you, as you can manage the `DROP` manually. Anyway a *small reproducible example* (two entities must be sufficient) will be highly appreciated. – Marmite Bomber Jan 02 '20 at 10:38
  • Spring Boot does not execute create-drop after each test method. Only after ich Test class. – Simon Martinelli Jan 02 '20 at 10:39
  • 1
    @SimonMartinelli well it does not seems so. Not with replace=none option and springboot 2.2.2. It was like that with 2.0.4, but with this option and springboot 2.2.2 db is initilized just once, not dropped, and initialization for next test file fails. – Martin Mucha Jan 02 '20 at 10:42
  • @MartinMucha Oh yes you are right. – Simon Martinelli Jan 02 '20 at 10:43
  • @SimonMartinelli I want it to be dropped after each test file, sorry if I didn't state that. – Melkor Jan 02 '20 at 10:52
  • @MarmiteBomber thanks for your reply. It's difficult for me to extract an example from the project. But as you mentioned the cascade option: Could the reason be a lack of `CascadeType` specifications at my entity relationships? – Melkor Jan 02 '20 at 10:56
  • @MarmiteBomber I added a sample from the hibernate debug log, does that help? – Melkor Jan 02 '20 at 13:04

2 Answers2

7

I finally found a work around for my problem. As I said, the errors where caused by trying to drop tables on which other tables still depend on. I thought that this could be related to missing CascadeType specifcations, but I couldn't fix it.

Then I found this answer and it works for me. Additional to my data.sql file (where all my data is inserted to the auto-created schema) I now have a drop-tables.sql where I can specify the correct order of DROP statements. This file is executed before the automatic schema creation and therefore solves my problem.

application.properties:

spring.jpa.properties.javax.persistence.schema-generation.database.action=drop-and-create
spring.jpa.properties.javax.persistence.schema-generation.drop-source=script-then-metadata
spring.jpa.properties.javax.persistence.schema-generation.drop-script-source=drop-tables.sql
Melkor
  • 243
  • 2
  • 14
  • 2
    It didn't work for me. I found a solution https://stackoverflow.com/a/60012915/607637 that works for me. Let me know if this works for you. – gtiwari333 Feb 01 '20 at 00:33
-1

You must annotate your test classes with DirtiesContext:

@DirtiesContext(methodMode = MethodMode.BEFORE_CLASS)

This will rebuild the test context and therefore also create-drop your schema.

Read more about this: https://www.baeldung.com/spring-dirtiescontext

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • 1
    I think you mean `ClassMode.BEFORE_CLASS` but this doesn't solve my problem. Yes it triggers the dropping of the DB schema, but again this throws the described error of the linked question – Melkor Jan 02 '20 at 11:54
  • in spring-boot 2.2.2 with replace=none this won't help either. I don't know what was changed between 2.0.4 and 2.2.2, I'd like to control that, but I have no idea how to do that, and cannot find answer anywhere. If you know how to force wpring to reinit db after every test file (in my case @DataJpaTest) even if replace=none is in place, please help. I'm not grown onto replace=none, but another thing I'm unable to do is to force autoconfigured datasource to create schema :( – Martin Mucha Jan 02 '20 at 13:41