hsqldb reports "user lacks privilege or object not found" only in memory-based database
hsqldb, mybatis, spring-mvc
Solution
You shouldn't use shutdown=true in this context. It seems the connection is closed and the database is shutdown, which means the memory database no longer exists.
You should also avoid using VALUE as a column name. This is a keyword.
Problem
Environment: Spring(3.2.3.RELEASE) + MyBatis(3.2.2) + HSQL(2.3.0) ``` <resultMap id="hashMapResult" type="java.util.HashMap"> <result property="key" column="key" /> <result property="value" column="value" /> </resultMap> <select id="getSettings" resultMap="hashMapResult"> SELECT "KEY","VALUE" from "PUBLIC"."SETTINGS"; </select> create table "SETTINGS" ( "KEY" varchar(255) not null, "VALUE" varchar(512) not null, CONSTRAINT SETTINGS_KEY_UNIQUE UNIQUE("KEY") ); ``` URL: jdbc:hsqldb:mem:mydb;sql.syntax_mys=true;shutdown=true; When using memory database, the following errors appeared: ``` Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SETTINGS at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] at org.hsqldb.error.Error.error(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] at org.hsqldb.SchemaManager.getTable(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] at org.hsqldb.ParserDQL.readTableName(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) ~[hsqldb-2.3.0.jar:2.3.0] ``` But when using file-based database jdbc:hsqldb:file:mydb;sql.syntax_mys=true;shutdown=true; no errors at all. I am using Spring's EmbeddedDatabaseFactory to init the database: ``` try { EmbeddedDatabaseFactory dbFactory = new EmbeddedDatabaseFactory(); DatabaseConfig dbConfig = appConfig.getDbConfig(); ResourceDatabasePopulator populator = new ResourceDatabasePopulator(); populator.setContinueOnError(false); populator.setIgnoreFailedDrops(false); DefaultResourceLoader resourceLoader = new DefaultResourceLoader(); populator.addScript(resourceLoader.getResource(dbConfig .getSqlSchemeFile())); populator.addScript(resourceLoader.getResource(dbConfig .getSqlDataFile())); dbFactory.setDatabasePopulator(populator); dbFactory.setDatabaseName(dbConfig.getName()); dbFactory.setDatabaseConfigurer(new HSQLConfigurer(dbConfig)); return dbFactory.getDatabase(); } catch (Exception e) { logger.error("dataSource error", e); //$NON-NLS-1$ return null; } ``` Anyone knows why?