Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLite clean fails due to violated foreign key constraints #2417

Closed
mpe85 opened this issue Jun 27, 2019 · 9 comments

Comments

@mpe85
Copy link

commented Jun 27, 2019

Which version and edition of Flyway are you using?

5.2.4 Community Edition

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

I'm on the latest version already

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java API

Which database are you using (type & version)?

SQLite 3.28.0
(same behavior with 3.27.2, 3.25.2 and 3.23.1)

Which operating system are you using?

Win 10

What did you do?

I use SQLite with activated foreign keys (when they're deactivated it works of course, but I need foreign key constraints in my project).
If there are two simple tables and one table has a FK contraint to the other table, the clean doesn't work if there are is at least one record in both of the tables.
What's also strange: The clean works on an in-memory database, but fails on a file-based database.

I created a small project that shows the problem perfectly:
https://github.com/mpe85/flyway-sqlite-clean

This is the migration that is used:

CREATE TABLE "SomeTable"(
  "Id" INTEGER PRIMARY KEY
);

CREATE TABLE "SomeOtherTable"(
  "Id" INTEGER PRIMARY KEY,
  "SomeId" INT,
  CONSTRAINT "FK_SomeOtherTable_SomeId" FOREIGN KEY("SomeId") REFERENCES "SomeTable"("Id")
);

INSERT INTO "SomeTable" VALUES (1);
INSERT INTO "SomeOtherTable" VALUES (1, 1);

It seems as if flyway tries to drop the tables in the wrong order.

What did you expect to see?

Database should be cleaned (all tables dropped)

What did you see instead?

This exception:

Unable to drop "main"."SomeTable"
---------------------------------
SQL State  : null
Error Code : 19
Message    : [SQLITE_CONSTRAINT]  Abort due to constraint violation (FOREIGN KEY constraint failed)

org.flywaydb.core.internal.exception.FlywaySqlException: 
Unable to drop "main"."SomeTable"
---------------------------------
SQL State  : null
Error Code : 19
Message    : [SQLITE_CONSTRAINT]  Abort due to constraint violation (FOREIGN KEY constraint failed)

	at org.flywaydb.core.internal.database.base.SchemaObject.drop(SchemaObject.java:83)
	at org.flywaydb.core.internal.database.sqlite.SQLiteSchema.doClean(SQLiteSchema.java:90)
	at org.flywaydb.core.internal.database.base.Schema.clean(Schema.java:149)
	at org.flywaydb.core.internal.command.DbClean$3.call(DbClean.java:172)
	at org.flywaydb.core.internal.command.DbClean$3.call(DbClean.java:169)
	at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:169)
	at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:113)
	at org.flywaydb.core.Flyway.doClean(Flyway.java:1488)
	at org.flywaydb.core.Flyway.access$300(Flyway.java:85)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1506)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1499)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1711)
	at org.flywaydb.core.Flyway.clean(Flyway.java:1499)
	at com.mpe85.FlywayTest.testSqliteCleanFileBased(FlywayTest.kt:35)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:628)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:117)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:184)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:180)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:127)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.processAllTestClasses(JUnitPlatformTestClassProcessor.java:102)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.access$000(JUnitPlatformTestClassProcessor.java:82)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor.stop(JUnitPlatformTestClassProcessor.java:78)
	at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.stop(SuiteTestClassProcessor.java:61)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
	at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
	at com.sun.proxy.$Proxy2.stop(Unknown Source)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.stop(TestWorker.java:132)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:175)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:157)
	at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:404)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:63)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:46)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:55)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.sqlite.SQLiteException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (FOREIGN KEY constraint failed)
	at org.sqlite.core.DB.newSQLException(DB.java:941)
	at org.sqlite.core.DB.newSQLException(DB.java:953)
	at org.sqlite.core.DB.execute(DB.java:854)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:56)
	at org.flywaydb.core.internal.jdbc.JdbcTemplate.execute(JdbcTemplate.java:215)
	at org.flywaydb.core.internal.database.sqlite.SQLiteTable.doDrop(SQLiteTable.java:55)
	at org.flywaydb.core.internal.database.base.SchemaObject.drop(SchemaObject.java:81)
	... 90 more
@axelfontaine

This comment has been minimized.

Copy link
Member

commented Jun 27, 2019

I can't get this to fail with the latest code. Can you try again with 6.0.0-beta2 ?

@mpe85

This comment has been minimized.

Copy link
Author

commented Jun 27, 2019

I updated my little test project to 6.0.0-beta2 and it still fails.
Have you cloned it and ran 'gradlew test', or do you have any other test case?

@mpe85

This comment has been minimized.

Copy link
Author

commented Jun 27, 2019

I also did a quick test with a snapshot build of the latest sources from the master branch.
It shows the some behavior too.

@mpe85

This comment has been minimized.

Copy link
Author

commented Jun 28, 2019

I created two PRs for 5.2.x and 6.0.0 that fixes the problem.
My test case works now with this fix applied.

The cause is that Flyway drops tables in a random order (I guess).
If foreign keys are enabled and there is a record in another table (that is not dropped yet) that references the table to be dropped by a foreign key constraint, the error described above occurs.
It's not possible to drop foreign keys in SQLite (you have to drop the whole table to get rid of them), so one solution would be to determine a 'correct' order in which tables should be dropped. But this is not quite easy because SQLite does not provide a function or view to retrieve the foreign key constraints which are currently present in the database.
Another solution is to simply disable foreign keys before dropping the table and re-enabling them afterwards (of course only if they were enabled before). This is how it did it in my PR and it works perfectly fine.

@axelfontaine axelfontaine added this to the Flyway 6.0.0 milestone Jun 28, 2019

axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Jun 28, 2019
@axelfontaine

This comment has been minimized.

Copy link
Member

commented Jun 28, 2019

Thank you very much for investigating this and submitting pull requests. We decided to implement this slightly differently, but the end result should be identical.

@mpe85

This comment has been minimized.

Copy link
Author

commented Jun 28, 2019

@axelfontaine

No, it does not work the way you implemented it.
The PRAGMA command only changes the setting for the current query.
So if you execute jdbcTemplate.execute("PRAGMA foreign_keys = OFF"); it does not have any effects on the queries you execute afterwards.
In the first place I also tried to implement it like you did, and I fell into the same pit.
This is why I turn it off and on again in the same query in which the actual drop statment is executed.

Could you please re-open this issue?

@axelfontaine axelfontaine reopened this Jun 28, 2019

@mpe85

This comment has been minimized.

Copy link
Author

commented Jul 2, 2019

@axelfontaine Will you revise this or shall I provide another PR?

@mpe85

This comment has been minimized.

Copy link
Author

commented Jul 10, 2019

@axelfontaine Do you know a workaround for this until it is fixed?

@juliahayward

This comment has been minimized.

Copy link
Member

commented Aug 9, 2019

@mpe85 Now fixed along the lines of your initial PR (except the check for foreign keys being enabled is done once per schema and cached, rather than once per table). Many thanks for your contribution.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.