0

I have a Spring Boot project with ~500 flyway migration files that are ran against oracle sql database (19c). When inside Intellij Idea, I'm able to run migration scripts successfully with mvn flyway:migrate. However when doing the same at application startup, it runs through about 70% of them and then crashes with this error:

Migration V6_2021.06__XXXXXXXXXXXXXXXXX.sql failed
--------------------------------------------------------------------------------
SQL State  : 42000
Error Code : 4098
Message    : ORA-04098: trigger 'XXXXXXXXXXX' is invalid and failed re-validation
Location   : db/migration/V6_2021.06__XXXXXXXXXXXXXXXXX.sql 
Line       : 7
Statement  : INSERT INTO....

I don't want to paste rest of the error message as I think it's not relevant, as exacltly the same script is executed successfully when migrating from IDE. As for my application.properties file it looks like that:

spring.datasource.url=jdbc:oracle:thin:@//XXXX:1521/XXXX
spring.datasource.username=XXXX
spring.datasource.password=XXXX
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.flyway.driver-class-name=oracle.jdbc.OracleDriver
spring.flyway.url=jdbc:oracle:thin:@//XXXX:1521/XXXX
spring.flyway.user=XXXX
spring.flyway.password=XXXX
spring.flyway.table=schema_version

... But again, i guess that's ok since it executes ~300 files and then crashes. The problem is - why the same script works when migrating inside IDE, and fails when running automatically at app startup? What to look for? What to chekck? I am sooo out of ideas.

EDIT: also adding my flyway-maven-plugin config:

<plugin>
   <groupId>org.flywaydb</groupId>
   <artifactId>flyway-maven-plugin</artifactId>
   <version>4.2.0</version>
   <configuration>
      <driver>oracle.jdbc.OracleDriver</driver>
      <url>jdbc:oracle:thin:@//XXXX:1521/XXXX</url>
      <user>XXXX</user>
      <password>XXXX</password>
      <baselineOnMigrate>true</baselineOnMigrate>
      <baselineVersion>1</baselineVersion>
   </configuration>
</plugin>
brtk
  • 65
  • 1
  • 6
  • 1
    can you try matching maven plugin config and your spring boot properties config? I see you might be missing atleast 2 properties `spring.flyway.baseline-on-migrate=true` `spring.flyway.baseline-version=1` ref: https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html#appendix.application-properties.data-migration – swapyonubuntu Oct 13 '22 at 14:32
  • Yeah sure, will try it right away, but tbh i'm so hopeless after all my earlier attempts, that i don't expect it to solve it:) Will let you know in a while! – brtk Oct 13 '22 at 15:19

1 Answers1

1

Fixed. The problem was that some of migration scripts lacked '/' symbol separating multiple trigger definitions contained in single file. Adding missing slashes solved issue of applying migrations at application startup. Why did they worked correctly when running directly from IDE will remain mystery for me. It's worth mentioning that one script had '/' between trigger definitions, but it was preceded with two whitespaces, apparently that is also not correct because when i removed spaces it started to work like a charm.

brtk
  • 65
  • 1
  • 6