Search code examples
javams-accessjdbcucanaccess

UCanAccess - Unable to reconnect to Access database after using keepMirror

I am trying to read an Access database using UCanAccess.

The code is working but is tremendously slow due to the database being large. As the database rarely changes, I am trying to use the keepMirror suggestion in

Slow initial connection to MS access database; .

connection = DriverManager.getConnection(UcanaccessDriver.URL_PREFIX + databaseFile + ";keepMirror=/some/dir/test/resources/db-mirror/mirror");

On the first run, the application works perfectly and creates the mirror files in the directory above. On subsequent runs, however, I get the following exception:

Exception in thread "main" java.lang.RuntimeException: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 error in script file line: 289 /some/dir/test/resources/db-mirror/mirror-783471167 a UNIQUE constraint already exists on the set of columns in statement [CREATE CACHED TABLE <TABLE DEF> ON DELETE CASCADE ON UPDATE CASCADE)] at de.gdfsuezenergie.stromnev.enet.ENETConnector.init(ENETConnector.java:69) at de.gdfsuezenergie.stromnev.Main.setUp(Main.java:374) at de.gdfsuezenergie.stromnev.Main.main(Main.java:165) at de.gdfsuezenergie.stromnev.MainTest.main(MainTest.java:9) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144) Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.6 error in script file line: 289 /some/dir/test/resources/db-mirror/mirror-783471167 a UNIQUE constraint already exists on the set of columns in statement [CREATE CACHED TABLE <TABLE DEF> ON DELETE CASCADE ON UPDATE CASCADE)] at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:264) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:270) at de.gdfsuezenergie.stromnev.enet.ENETConnector.createConnection(ENETConnector.java:86) at de.gdfsuezenergie.stromnev.enet.ENETConnector.init(ENETConnector.java:57) ... 8 more Caused by: java.sql.SQLException: error in script file line: 289 /some/dir/test/resources/db-mirror/mirror-783471167 a UNIQUE constraint already exists on the set of columns in statement [CREATE CACHED TABLE <TABLE DEF> ON DELETE CASCADE ON UPDATE CASCADE)] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.(Unknown Source) at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source) at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at net.ucanaccess.jdbc.DBReference.getHSQLDBConnection(DBReference.java:440) at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231) ... 12 more Caused by: org.hsqldb.HsqlException: error in script file line: 289 /some/dir/test/resources/db-mirror/mirror-783471167 a UNIQUE constraint already exists on the set of columns in statement [CREATE CACHED TABLE <TABLE DEF> ON DELETE CASCADE ON UPDATE CASCADE)] at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.scriptio.ScriptReaderText.readDDL(Unknown Source) at org.hsqldb.scriptio.ScriptReaderBase.readAll(Unknown Source) at org.hsqldb.persist.Log.processScript(Unknown Source) at org.hsqldb.persist.Log.open(Unknown Source) at org.hsqldb.persist.Logger.open(Unknown Source) at org.hsqldb.Database.reopen(Unknown Source) at org.hsqldb.Database.open(Unknown Source) at org.hsqldb.DatabaseManager.getDatabase(Unknown Source) at org.hsqldb.DatabaseManager.newSession(Unknown Source) ... 19 more Caused by: org.hsqldb.HsqlException: a UNIQUE constraint already exists on the set of columns at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.ParserTable.addTableConstraintDefinitions(Unknown Source) at org.hsqldb.StatementSchema.getResult(Unknown Source) at org.hsqldb.StatementSchema.execute(Unknown Source) at org.hsqldb.Session.executeCompiledStatement(Unknown Source) ... 28 more

I understand that this is therefore violating some constraint on this cached table, what I don't understand is why it is running this script. I thought the whole point of creating the mirror was that I wouldn't need to reapply any scripts?

Does someone know how to read the mirror properly?

Note: <TABLE DEF> => I haves skipped the table definition for brevity.

Solution

  • This issue is known to occur when a table in the Access database has more than one unique index defined for a given column, e.g., a Primary Key index (Primary=Yes, Unique=Yes) and another separate Unique index (Primary=No, Unique=Yes).

    DupUnique.png

    Access itself does not have any problem with this, but HSQLDB (which UCanAccess uses for its backing database) does not permit more than one unique constraint on a set of columns.

    The best solution is to open the .accdb or .mdb database in Access and remove the redundant non-Primary unique index.