UCanAccess / Discussion / Help: drop table followed immediately by a create of the same table worked in 3.0.2 but fails in 3.0.3+

RavenAtSafe - 2016-01-13

As the subject says, UCanAccess 3.0.2 was able to drop a table and then immediately create it again. In 3.0.3+ you get the following stack. (Note that the version of Jackcess doesn't affect whether the error occurs.)

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.3.1 null

at net.ucanaccess.commands.CreateTableCommand.persist(CreateTableCommand.java:103)
at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:315)

at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
at net.ucanaccess.commands.DDLCommandEnlist.enlistCreateTable(DDLCommandEnlist.java:93)
at net.ucanaccess.commands.DDLCommandEnlist.enlistDDLCommand(DDLCommandEnlist.java:102)

at net.ucanaccess.jdbc.AbstractExecute.addDDLCommand(AbstractExecute.java:124)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:149)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessStatement.execute(UcanaccessStatement.java:152)
at COM.safe.fme.JdbcWriter.prepareForWriting(JdbcWriter.java:461)

at COM.safe.fme.JdbcWriter.write(JdbcWriter.java:309)
Caused by: java.nio.channels.ClosedChannelException

at sun.nio.ch.FileChannelImpl.ensureOpen(FileChannelImpl.java:99)
at sun.nio.ch.FileChannelImpl.size(FileChannelImpl.java:297)
at com.healthmarketscience.jackcess.impl.PageChannel.allocateNewPage(PageChannel.java:314)

at com.healthmarketscience.jackcess.impl.TableCreator.reservePageNumber(TableCreator.java:118)
at com.healthmarketscience.jackcess.impl.TableCreator.createTable(TableCreator.java:163)
at com.healthmarketscience.jackcess.impl.DatabaseImpl.createTable(DatabaseImpl.java:1024)
at com.healthmarketscience.jackcess.TableBuilder.toTable(TableBuilder.java:212)

at net.ucanaccess.converters.Persist2Jet.createTable(Persist2Jet.java:425)
at net.ucanaccess.commands.CreateTableCommand.persist(CreateTableCommand.java:101)
... 10 more

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

Marco Amadei - 2016-01-13

Nothing about changed between the 3.0.3 and the 3.0.3+ and I'm prety sure it isn't a regression.
Here's a junit test case I run before each release: https://sourceforge.net/p/ucanaccess/code/HEAD/tree/ucanaccess/trunk/src/test/java/net/ucanaccess/test/DropTableTest.java#l53
It works fine with both the 3.0.2 and the 3.0.3.
NEVERTHELESS
I can reproduce the java.nio.channels.ClosedChannelException for both the versions by changing the junit test case above.
The issue happens if the create table statement and the subsequent drop table statement are executed in the same transaction(between a conn.setAutoCommit(false) and a conn.commit()).
I'm thinking of an oracle-like implicit commit...

Last edit: Marco Amadei 2016-01-13

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

RavenAtSafe - 2016-01-13

My code definitely matches the pattern you describe

The issue happens if the create table statement and the subsequent drop table statement are executed in the same transaction(between a conn.setAutoCommit(false) and a conn.commit()).

For my part, I can consistently fix the issue by going back to UCanAccess 3.0.2, but perhaps there is something unique about the way I'm opening the file that makes my repro slightly different than yours.

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

Marco Amadei - 2016-01-13

It sounds sarcastic.
If it were a regression it would be simpler for me to fix.
My tests don't highlight a different behaviour between the two version.
It would be great if you provided a test case to reproduce the issue (obviously showing your special way to open the file :-ppp) .
The risk is to fix something else... going back to UCanAccess 3.0.2 may be a bad solution, if it implies being tied to one specific version for a single (easy-to- solve) bug.

Last edit: Marco Amadei 2016-01-13

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

RavenAtSafe - 2016-01-13

After a quick code review, I am doing at least one extra interesting thing in this scenario:

open file

connection.commit() // on nothing

drop table

create table

I wonder if the commit on nothing (which is done because it might have been required in other more complex scenarios) is the key to reproducing in one version but not the other.

If that doesn't get you any closer I'll have a complete repro program made up and we'll ship it over.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

RavenAtSafe - 2016-01-15

Hi Marco,

Are you waiting on a full repro? If so just let me know and we'll try to create one.

Thanks!

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

Marco Amadei - 2016-01-15

Yes, It would be great because I can't reproduce the issue in the way you suggested. You can either send it to my private email amadei.mar@gmail.com or here in this forum. May you be available to test the solution (once found) in your environment?

Last edit: Marco Amadei 2016-01-15

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

RavenAtSafe - 2016-01-15

We'll get working on the repro. Can definitely test the fix locally.

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

RavenAtSafe - 2016-01-26

Repro emailed as suggested, as it includes a sample database that may or may not be important.

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

RavenAtSafe - 2016-02-22

Hi Marco, any progress on this one? I'm about a week away from needing a resolution for this, and I'm wondering if there is anything we could do help get it addressed. Thanks!

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

Marco Amadei - 2016-02-22

2 days again with wake up call at 4.30 am, then I should be able to put fuĺl effort in the 3.0.4.

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

Marco Amadei - 2016-02-25

Hi Raven, I get my office now, I'll give you notice about this issue ASAP

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

Marco Amadei - 2016-02-26

fixed on svn, I'm going to release next week

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

RavenAtSafe - 2016-02-26

Great - thanks!

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

drop table followed immediately by a create of the same table worked in...

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

drop table followed immediately by a create of the same table worked in 3.0.2 but fails in 3.0.3+

drop table followed immediately by a create of the same table worked in...

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

drop table followed immediately by a create of the same table worked in 3.0.2 but fails in 3.0.3+ document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

drop table followed immediately by a create of the same table worked in 3.0.2 but fails in 3.0.3+