1

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.

Community
  • 1
  • 1
Causteau
  • 117
  • 1
  • 10
  • I doubt the size of the database is the issue in connection. It would be an issue actual querying of large tables not initial connection. If you read OP's answer to own question from the link you posted, he/she points out a networking/shared server was the issue. – Parfait Aug 27 '16 at 12:32
  • Be sure to use a hard-wired LAN network. And try testing speeds with database on a CPU hard desk drive. As a reminder never use database in internet folders (Dropbox, Google Drive, OneDrive, etc.), flash/thumb drives, or drives with low storage space. – Parfait Aug 27 '16 at 12:35
  • 1
    Check the definition of the table in question to see if it 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). I have see that cause problems with `keepMirror` in the past. – Gord Thompson Aug 27 '16 at 12:36
  • The database is on my own hard drive. It's aproximately 1.5GB and the application takes around 5 mins to run, in contrast to the 40 secs when ran with Java6 and sun.jdbc.odbc.JdbcOdbcDriver. – Causteau Aug 28 '16 at 03:01
  • You are on the right way, just follow the Gord's suggestion. – jamadei Aug 28 '16 at 13:36

2 Answers2

0

I managed to get it to work but it's a bit hacky. Basically by removing the Unique Constraints from the mirror script it finally managed to get through. Sadly I don't have knowledge of the data, so need to work out if its still correct, but I believe it is.

Thanks for the pointers everyone! Very much appreciated.

Causteau
  • 117
  • 1
  • 10
0

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418