JDBC3DatabaseMetaData > getColumns may cause [SQLITE_ERROR] (too many terms in compound SELECT)

[ChristianFischerJena]

I have a database with approx. 100 tables and an application using Spring+Hibernate. After upgrading from Hibernate 4.3.11 to 5.4.12 I get this error: "[SQLITE_ERROR] SQL error or missing database (too many terms in compound SELECT)". It looks like Hibernate5 uses the getColumns method of JDBC3DatabaseMetaData for schema validation. When this method is invoked the SQLITE_ERROR is thrown. (see stacktrace at the end).

I digged through the code and found that the getColumns method generates a sql statement with a select cause for each table and each column within each table. In my case this statment has a size of about 500kb containing several thousand selects. However sqlite limits the number of selects in a single query to 512.

For this issue looks really bad, because I expect most people using Hibernate5 + sqlite to run into this issue. I see no way to prevent hibernate from calling getColumns.

I found a ugly, workaroung by proxying SqliteConnection, overriding getMetaData, subclassing JDBC4DatabaseMetaData, overriding getColumns and splitting the offending statement into one statement for each table and combining the results in a CombinedResultSet.

I using

• sqlite driver version: 3.23.1
• hibernate version: 5.4.12.Final

Stacktrace:

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (too many terms in compound SELECT)
	at org.sqlite.core.DB.newSQLException(DB.java:909)
	at org.sqlite.core.DB.newSQLException(DB.java:921)
	at org.sqlite.core.DB.throwex(DB.java:886)
	at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
	at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
	at org.sqlite.core.DB.prepare(DB.java:227)
	at org.sqlite.jdbc3.JDBC3Statement.executeQuery(JDBC3Statement.java:81)
	at org.sqlite.jdbc3.JDBC3Statement.executeQuery(JDBC3Statement.java:71)
	at org.sqlite.jdbc3.JDBC3DatabaseMetaData.getColumns(JDBC3DatabaseMetaData.java:1260)
	at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.populateTablesWithColumns(InformationExtractorJdbcDatabaseMetaDataImpl.java:354)
	... 74 common frames omitted
Wrapped by: org.hibernate.exception.GenericJDBCException: Error accessing tables metadata
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.convertSQLException(InformationExtractorJdbcDatabaseMetaDataImpl.java:102)
	at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.populateTablesWithColumns(InformationExtractorJdbcDatabaseMetaDataImpl.java:387)
	at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTables(InformationExtractorJdbcDatabaseMetaDataImpl.java:341)
	at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTablesInformation(DatabaseInformationImpl.java:120)
	at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:39)
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:89)
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:68)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:192)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:314)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468)
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1237)
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
	... 61 common frames omitted
Wrapped by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Error accessing tables metadata
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378)
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
	... 57 common frames omitted

[pviotti]

This is really a showstopper for people using medium-large databases on Sqlite with Hibernate.
I see that limit handling has just been implemented in https://github.com/xerial/sqlite-jdbc/pull/527 , so in theory it would be possible to use a connection parameter such as jdbc.url=jdbc:sqlite:mysqlite3.db?limit_compound_select=5000 to change that limit.
@xerial , has limit handling been included in the last release? (it doesn't look like so from the changelog..) If not, could you please cut a new release that provides that?

PS: from the description in the Sqlite documentation, it seems that parameter can only be set to a lower value than its default (500)...

[xerial]

@pviotti It seems even after #527 is merged, it's still not effective for increasing the limit size https://github.com/xerial/sqlite-jdbc/issues/523#issuecomment-673834827

[pviotti]

Yes, it looks like some limits have an upper bound, which I think it's defined here. For SQLITE_MAX_COMPOUND_SELECT they don't enforce any upper bound, and, as written here, if one sets it to 0, the limit is not enforced.
I just tried with v3.32.3.3-SNAPSHOT and the following config:

jdbc.url=jdbc:sqlite:mysqlite3.db?limit_compound_select=0
jdbc.class=org.sqlite.JDBC
hibernate.dialect=org.hibernate.dialect.SQLiteDialect
hibernate.hbm2ddl.auto=update

and the error is not there anymore :tada: so I believe this issue can be closed.

[gotson]

@ChristianFischerJena @pviotti can i close this ?