UCanAccess / Discussion / Help: Exception AbstractExecute.addDDLCommand

Jonathan Montwell - 2017-02-15

Exception creating index due to:UCAExc:::4.0.1 null
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 null
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:154)
at com.cocc.datamover.helpers.DataProcessor.createTableIndex(DataProcessor.java:612)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:326)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)
Caused by: java.sql.SQLException: UCAExc:::4.0.1 null
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:182)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:205)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)

This is an odd issue on a table that seems to work fine on one VM and not another. Everything appears to be the same across both servers. The table creation that follows this error throws

Table creation failed for the table [ACCTUSERFIELD] Cause:UCAExc:::4.0.1 connection exception: closed
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 connection exception: closed
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:154)
at com.cocc.datamover.helpers.DataProcessor.createAccessTableQuery(DataProcessor.java:522)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:310)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)
Caused by: java.sql.SQLException: connection exception: closed
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:182)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:205)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)

I'm assuming this is just because the connection automatically closes after the unknown error above.

Below is the code that was previously used the java jdbc-odbc driver.

private void createTableIndex(Set<TableConstraintsDTO> columnConstraints, String tableName, Connection accessConn) throws Exception { StringBuffer sqlCreate = null; Iterator<TableConstraintsDTO> itr = columnConstraints.iterator(); while (itr.hasNext()) { sqlCreate = new StringBuffer(); TableConstraintsDTO colConstraints = itr.next(); Character constraintflag = colConstraints.getConstraintFlag(); if (CHAR_I == constraintflag.charValue()) { sqlCreate.append(CREATE_INDEX).append(UNDERSCRORE).append(tableName); String columnName = colConstraints.getColumnName(); sqlCreate.append(UNDERSCRORE).append(columnName).append(INDEX_ON).append(tableName).append(SPACE); sqlCreate.append(OPEN_PARENTHESIS).append(columnName).append(CLOSE_PARENTHESIS); logger.info("Creating Index for table: " + tableName + " and column name:" + columnName); try { Statement stmtAccess = accessConn.createStatement(); stmtAccess.execute(sqlCreate.toString()); accessConn.commit(); stmtAccess.close(); logger.info("Index created Successfully"); } catch (SQLException e) { logger.error("Exception creating index due to:" + e.getMessage(), e); } } } }
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Gord Thompson - 2017-02-15

Does the table in question have any columns with names that include accented characters, non-latin characters, or punctuation?

Have you verified that the two VMs are using the same default character set, locale, etc.?

Would it be possible to have your exception handler dump out an actual CREATE INDEX statement that fails consistently on the troublesome VM?

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-15

So this was the statement
CREATE INDEX IDX_ACCTTITLE_ACCTNBR ON ACCTTITLE (ACCTNBR)

I ran it locally against and it worked fine.

It seems to be an environmental issue with the server that executing the our code.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-15

We're starting to lean towards a memory or concurrency issue. The job ran fine when it was run alone, but failed with another process running at the same time.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Gord Thompson - 2017-02-15
  
  Are you talking about two separate processes using UCanAccess (or Jackcess) to hit the same database simultaneously?
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Jonathan Montwell - 2017-02-15
    
    They hit different databases with different connection instances, but I need take a closer look at what is happening to the connection as it exits create table, then calls to create the index right after that.
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-15

Table creation failed for the table [AGREEPERSACCESSTYPACCT] Cause:UCAExc:::4.0.1 user lacks privilege or object not found: DOCREQ
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 user lacks privilege or object not found: DOCREQ
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:154)
at com.cocc.datamover.helpers.DataProcessor.createAccessTableQuery(DataProcessor.java:522)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:310)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)
Caused by: java.sql.SQLException: user lacks privilege or object not found: DOCREQ
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:182)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:205)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)
... 5 more
2017-02-15 12:58:38,414 Thread [39936] ERROR DataProcessor - Table creation failed for the table [AGREEPERSACCESSTYPACCT] Cause:UCAExc:::4.0.1 user lacks privilege or object not found: DOCREQ
com.cocc.datamover.exception.ApplicationException: Table creation failed for the table [AGREEPERSACCESSTYPACCT] Cause:UCAExc:::4.0.1 user lacks privilege or object not found: DOCREQ
at com.cocc.datamover.helpers.DataProcessor.createAccessTableQuery(DataProcessor.java:528)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:310)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)

Same place, different error.

This worked fine running one job at a time. Failed on two jobs concurrently.

These errors never occurred on 3.0.6, but is probably related to how our connection is being handled in our code after uncommenting the createTableIndex call after adding 4.0.1.

Last edit: Jonathan Montwell 2017-02-15

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-15

Table creation failed for the table [ORGADDRUSE] Cause:UCAExc:::4.0.1 object name already exists: GENERICINSERT_ACCTWRN
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.1 object name already exists: GENERICINSERT_ACCTWRN
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:154)
at com.cocc.datamover.helpers.DataProcessor.createAccessTableQuery(DataProcessor.java:522)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:310)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)
Caused by: java.sql.SQLException: object name already exists: GENERICINSERT_ACCTWRN
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:182)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:205)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)
... 5 more
2017-02-15 13:59:04,479 Thread [39938] ERROR DataProcessor - Table creation failed for the table [ORGADDRUSE] Cause:UCAExc:::4.0.1 object name already exists: GENERICINSERT_ACCTWRN
com.cocc.datamover.exception.ApplicationException: Table creation failed for the table [ORGADDRUSE] Cause:UCAExc:::4.0.1 object name already exists: GENERICINSERT_ACCTWRN
at com.cocc.datamover.helpers.DataProcessor.createAccessTableQuery(DataProcessor.java:528)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:310)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)

Unpredictable errors for some reason, always ending up at AbstractExecute.AddDDLCoommand though. I commented out the createTableIndex code again, so now the only change was updating UCanAccess from 3.0.6 to 4.0.1. No issues with concurrency on 3.0.6.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-15

FIXED by adding ;openExclusive=true to the connection string.

Not sure why it wasn't an issue on 3.0.6, but this worked.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Gord Thompson - 2017-02-15

Good to hear. Thanks for letting us know.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-02-28

This doesn't appear to fix the issue afterall. I'm going to spend some more time on this in the coming weeks. The error seems to be originating in the jackess library though.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jonathan Montwell - 2017-03-06

"CREATE INDEX IDX_AGREEPERSACCESSTYPACCT_AGREENBR ON AGREEPERSACCESSTYPACCT (AGREENBR)"

java.lang.NullPointerException
at com.healthmarketscience.jackcess.impl.TableUpdater.<init>(TableUpdater.java:51)
at com.healthmarketscience.jackcess.IndexBuilder.addToTable(IndexBuilder.java:212)
at net.ucanaccess.converters.Persist2Jet.createIndex(Persist2Jet.java:723)
at net.ucanaccess.commands.CreateIndexCommand.persist(CreateIndexCommand.java:54)
at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:315)
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
at net.ucanaccess.commands.DDLCommandEnlist.enlistCreateIndex(DDLCommandEnlist.java:154)
at net.ucanaccess.commands.DDLCommandEnlist.enlistDDLCommand(DDLCommandEnlist.java:111)
at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:180)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:205)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)
at com.cocc.datamover.helpers.DataProcessor.createTableIndex(DataProcessor.java:613)
at com.cocc.datamover.helpers.DataProcessor.copyFromOracleToAccess(DataProcessor.java:326)
at com.cocc.datamover.helpers.DataProcessor.runProcessor(DataProcessor.java:218)
at com.cocc.datamover.helpers.DataProcessor.callProcess(DataProcessor.java:109)
at com.cocc.datamover.helpers.DataProcessor.run(DataProcessor.java:95)

Only occurs when running multiple jobs on multiple threads.

Only have concurrency issues after upgrading from 3.0.6 to 4.0.1

I have to imagine that multiple threads are hitting the same instance of jackcess and wiping out each others variables.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Exception AbstractExecute.addDDLCommand

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

Exception AbstractExecute.addDDLCommand

Exception AbstractExecute.addDDLCommand

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

Exception AbstractExecute.addDDLCommand document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

Exception AbstractExecute.addDDLCommand