How to resolve Liquibase exception "Table 'TABLE_NAME_temporary" not found, when working with SQLite
As it's already second time I face this, here's a brief overview:
Occassionally, constraints in SQLite Database get "corrupted" (maybe it's just me, I dunno), and, when applying migrations, you might come across an exception that looks like this:
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/changelogs/008-add-lexemes.xml::8-3::62mkv:
Reason: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Table not found: 'ARTICLES_temporary'
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:659) ~[liquibase-core-3.10.2.jar:na]
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53) ~[liquibase-core-3.10.2.jar:na]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:97) ~[liquibase-core-3.10.2.jar:na]
at liquibase.Liquibase.update(Liquibase.java:201) ~[liquibase-core-3.10.2.jar:na]
at liquibase.Liquibase.update(Liquibase.java:178) ~[liquibase-core-3.10.2.jar:na]
at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:366) ~[liquibase-core-3.10.2.jar:na]
at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:314) ~[liquibase-core-3.10.2.jar:na]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
... 18 common frames omitted
Caused by: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Table not found: 'ARTICLES_temporary'
at liquibase.database.core.SQLiteDatabase.getAlterTableSqls(SQLiteDatabase.java:150) ~[liquibase-core-3.10.2.jar:na]
at liquibase.sqlgenerator.core.AddColumnGeneratorSQLite.generateSql(AddColumnGeneratorSQLite.java:83) ~[liquibase-core-3.10.2.jar:na]
at liquibase.sqlgenerator.core.AddColumnGeneratorSQLite.generateSql(AddColumnGeneratorSQLite.java:19) ~[liquibase-core-3.10.2.jar:na]
at liquibase.sqlgenerator.SqlGeneratorChain.generateSql(SqlGeneratorChain.java:30) ~[liquibase-core-3.10.2.jar:na]
at liquibase.sqlgenerator.SqlGeneratorFactory.generateSql(SqlGeneratorFactory.java:222) ~[liquibase-core-3.10.2.jar:na]
at liquibase.executor.AbstractExecutor.applyVisitors(AbstractExecutor.java:105) ~[liquibase-core-3.10.2.jar:na]
at liquibase.executor.jvm.JdbcExecutor.access$600(JdbcExecutor.java:43) ~[liquibase-core-3.10.2.jar:na]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:412) ~[liquibase-core-3.10.2.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:87) ~[liquibase-core-3.10.2.jar:na]
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:159) ~[liquibase-core-3.10.2.jar:na]
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276) ~[liquibase-core-3.10.2.jar:na]
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258) ~[liquibase-core-3.10.2.jar:na]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:622) ~[liquibase-core-3.10.2.jar:na]
... 26 common frames omitted
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: Table not found: 'ARTICLES_temporary'
at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:110) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:79) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:69) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:316) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:438) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:460) ~[liquibase-core-3.10.2.jar:na]
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/changelogs/008-add-lexemes.xml::8-3::62mkv:
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:365) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:339) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:417) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:365) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:339) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:107) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:60) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:39) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:217) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:246) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:230) ~[liquibase-core-3.10.2.jar:na]
at liquibase.database.core.SQLiteDatabase.getAlterTableStatements(SQLiteDatabase.java:49) ~[liquibase-core-3.10.2.jar:na]
at liquibase.database.core.SQLiteDatabase.getAlterTableSqls(SQLiteDatabase.java:143) ~[liquibase-core-3.10.2.jar:na]
... 38 common frames omitted
Caused by: liquibase.exception.DatabaseException: java.sql.SQLException: Table not found: 'ARTICLES_temporary'
at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:105) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData.getForeignKeys(JdbcDatabaseSnapshot.java:79) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.jvm.ForeignKeySnapshotGenerator.addTo(ForeignKeySnapshotGenerator.java:99) ~[liquibase-core-3.10.2.jar:na]
... 60 common frames omitted
Caused by: java.sql.SQLException: Table not found: 'ARTICLES_temporary'
at org.sqlite.jdbc3.JDBC3DatabaseMetaData$PrimaryKeyFinder.<init>(JDBC3DatabaseMetaData.java:2011) ~[sqlite-jdbc-3.31.1.jar:na]
at org.sqlite.jdbc3.JDBC3DatabaseMetaData.getImportedKeys(JDBC3DatabaseMetaData.java:1598) ~[sqlite-jdbc-3.31.1.jar:na]
at com.zaxxer.hikari.pool.ProxyDatabaseMetaData.getImportedKeys(ProxyDatabaseMetaData.java:169) ~[HikariCP-3.4.5.jar:na]
at com.zaxxer.hikari.pool.HikariProxyDatabaseMetaData.getImportedKeys(HikariProxyDatabaseMetaData.java) ~[HikariCP-3.4.5.jar:na]
at liquibase.snapshot.JdbcDatabaseSnapshot$CachingDatabaseMetaData$ForeignKeysResultSetCache.fastFetch(JdbcDatabaseSnapshot.java:653) ~[liquibase-core-3.10.2.jar:na]
at liquibase.snapshot.ResultSetCache.get(ResultSetCache.java:72) ~[liquibase-core-3.10.2.jar:na]
... 62 common frames omitted
Long story short, you can find conflicting objects in the database by running this query:
select sql from sqlite_master where sql like '%temporary%';
Then you'll see something like this:
CREATE TABLE ARTICLES_PARTS_OF_SPEECH (
article_id BIGINT,
part_of_speech_id BIGINT,
CONSTRAINT FK_ARTICLE FOREIGN KEY (article_id) REFERENCES "ARTICLES_temporary"(ID) ON DELETE CASCADE,
CONSTRAINT FK_PART_OF_SPEECH FOREIGN KEY (part_of_speech_id) REFERENCES PARTS_OF_SPEECH(ID))
I have no idea, why it got corrupted in the first place (I have: it's due to how Liquibase treats changes
like addColumn/dropColumn, so basically exactly as we'll be treating it here), but at least there's a way to fix it.
As long as SQLite, apparently, does not have DDL-level support for dropping or changing foreign keys (or any constraints, it seems), let's follow
this gem and implement it as follows:
CREATE TABLE ARTICLES_PARTS_OF_SPEECH_temp (article_id BIGINT,
part_of_speech_id BIGINT,
CONSTRAINT FK_ARTICLE FOREIGN KEY (article_id) REFERENCES ARTICLES(ID) ON DELETE CASCADE,
CONSTRAINT FK_PART_OF_SPEECH FOREIGN KEY (part_of_speech_id) REFERENCES PARTS_OF_SPEECH(ID));
insert into ARTICLES_PARTS_OF_SPEECH_temp (article_id, part_of_speech_id)
select article_id, part_of_speech_id from ARTICLES_PARTS_OF_SPEECH;
DROP TABLE ARTICLES_PARTS_OF_SPEECH;
ALTER table ARTICLES_PARTS_OF_SPEECH_temp RENAME TO ARTICLES_PARTS_OF_SPEECH;
Follow the same advice with all conflicting tables, and you should be fine...