Skip to content

Instantly share code, notes, and snippets.

@62mkv

62mkv/README.md

Created December 27, 2020 12:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save 62mkv/d5ac5b7c839ab58d9b174cf2a0b241b6 to your computer and use it in GitHub Desktop.
Save 62mkv/d5ac5b7c839ab58d9b174cf2a0b241b6 to your computer and use it in GitHub Desktop.
Download ZIP
Liquibase + SQLite problem with _temporary tables
Raw
README.md

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...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment