3

I have a project using EclipseLink-2.6.0 and UCanAccess-3.0.4 (as DB driver) to modify an MS Access database. (NB legacy requirement - not my choice).

There is a table with a column of single precision (Again, legacy requirement - not my choice).

If the field is updated then any updates after that point 'appear' to be committed, but after closing and reopening the database the last update to the field is always the initial update and all other updates have been lost.

I have put together a project that has tests this scenario using both JPA and using JDBC directly. The JDBC connection correctly flushes the changes to the database, while the JPA test does not.

The URL for the test project is: https://github.com/william-ferguson-au/eclipselink-flush-test

NB the UCanAccess connection is configured with SingleConnection=true because I need to ensure that once the EntityManagerFactory is closed, the lock on the MS Access DB is released. I think this is now the crux of the issue.

The failing test output looks something like:

[EL Info]: server: 2016-05-01 17:59:35.858--ServerSession(1690826692)--Detected server platform: org.eclipse.persistence.platform.server.NoServerPlatform.
lastValue=0.0 entry#value=0.0 newValue=1.4
lastValue=1.4 entry#value=1.4 newValue=2.8
lastValue=2.8 entry#value=2.8 newValue=4.2
lastValue=4.2 entry#value=4.2 newValue=5.6
lastValue=5.6 entry#value=5.6 newValue=7.0
lastValue=7.0 entry#value=7.0 newValue=8.4
lastValue=8.4 entry#value=8.4 newValue=9.8
lastValue=9.8 entry#value=9.8 newValue=11.2
lastValue=11.2 entry#value=11.2 newValue=12.6
lastValue=12.6 entry#value=12.6 newValue=14.0
lastValue=14.0 entry#value=14.0 newValue=15.4
lastValue=15.4 entry#value=15.4 newValue=16.8
lastValue=16.8 entry#value=16.8 newValue=18.2

Database - closed
Database - reopened

[EL Warning]: 2016-05-01 17:59:39.906--session_manager_no_partition
 lastValue=18.2
[EL Info]: server: 2016-05-01 17:59:39.922--ServerSession(1276502808)--Detected server platform: org.eclipse.persistence.platform.server.NoServerPlatform.
lastValue=18.2 entry#value=1.4 newValue=28.2
[EL Warning]: 2016-05-01 17:59:40.568--session_manager_no_partition

java.lang.AssertionError: Expected=18.2 found=1.4

    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.assertTrue(Assert.java:41)
    at au.com.xandar.eclipselink.flush.DoubleFlushTest.verifyEntityValue(DoubleFlushTest.java:133)
    at au.com.xandar.eclipselink.flush.DoubleFlushTest.testExplicitUpdatesJPA(DoubleFlushTest.java:120)

If I set SingleConnection=false when configuring the EntityManagerFactory then the updates are flushed to the database and are visible when the database is reopened. But then I have no way of releasing the file lock on the MS Access DB until, the VM is destroyed.

So I thought it was a bug in EclipseLink (https://bugs.eclipse.org/bugs/show_bug.cgi?id=492773). But now I am thinking it is more likely a bug in the way that UCanAccess handles SingleConnection=true in a JPA environment. Or perhaps I am missing some config that is required in order to make EclipseLink JPA flush in the same manner as JDBC.

Dies this should like expected behaviour using UCanAccess and SingleConnection=true?

FOLLOW UP #1

@GordThompson pointed out that the original DB field was single rather than double. And that changing the DB field to double (to match the original Entity field) made everything work OK. But I can't change the DB. So I changed the Entity field to Float (ie single) to match the DB field and the problem remains and is even more pervasive.

Then I realised that the behaviour changes depending upon the value of the UCanAccess SingleConnection property in a JPA environment.

FOLLOW UP #2

It is replicable using UCanAccess only when using a PreparedStatement and SingleConnection=true. See updated test cases.

FOLLOW UP #3 It appears that the problem only becomes apparent when making updates to a Float/Double field, closing the DB, opening the DB, checking the field, closing the DB, repeat same for a different DB at which point none of the values for the 2nd DB get persisted. Still occurring with UCanAccess-3.0.5

Discusion moved to https://sourceforge.net/p/ucanaccess/discussion/general/thread/a6a192d0

William
  • 20,150
  • 8
  • 49
  • 91
  • 1
    Can you try tweaking your test suite to use a native HSQLDB database instead of UCanAccess/Access to see if it behaves differently? – Gord Thompson May 01 '16 at 14:08
  • It may be an ucanaccess issue, I'll let you know my findings ASAP – jamadei May 01 '16 at 15:04
  • It doesn't seem to be a ucanaccess issue, I've just tried with the same values. @William In order to confirm that the issue isn't at the ucanaccess/driver layer, may you try to execute the same updates through sql statements and directly with the ucanaccess console on your database? – jamadei May 01 '16 at 19:53
  • 1
    @jamadei if you look at the test project, I do exactly that. JDBC statements using UCanAccess as the driver works just fine. – William May 01 '16 at 23:13
  • (cc: @jamadei ) - My findings: I downloaded the test project from GitHub and imported it into Eclipse, and I was able to reproduce the issue. I tweaked the project to use a SQLite database and the issue did not recur. Then I tweaked it again to use a "plain" HSQLDB database and the issue also did not recur. – Gord Thompson May 01 '16 at 23:31
  • Thanks @GordThompson. Would you be able to submit your 2 tweaks as pull requests? It suggests to me that it is the way that EclipseLink is configured to use UCanAccess that is improper. – William May 02 '16 at 00:02
  • (cc: @jamadei ) - Follow-up re: my answer: When I created the SQLite and HSQLDB databases I defined the DOUBLE_RESULT column as `DOUBLE`. I didn't check the field definition in the Access database until after I posted my earlier comment. – Gord Thompson May 02 '16 at 00:27
  • @jamadei looks like this **IS** a UCanAccess issue. Specifically around usage of SingleConnection=true from JPA. See my updated FloatFlushTest – William May 02 '16 at 01:34
  • @jamadei, narrowed it further to use of PreparedStatement + SingleConnection=true. Replicable using pure JDBC with UCanAccess. See test cases. – William May 02 '16 at 18:51
  • @William yes, yes, already done, root cause found, looking for a solution – jamadei May 02 '16 at 19:42

2 Answers2

2

This is a critical bug due to a misalignment, under specific conditions, between the mirror hsqldb database and the data persisted in the mdb. It indirectly depends on the way the FLOAT data are managed in hsqldb. I've already found the solution, so the fix will be in the 3.0.5. I'm going to release it ASAP, hopefully this week end, at later the next week. Thank you guys!

jamadei
  • 1,700
  • 9
  • 8
  • I'm going to release it this week – jamadei May 09 '16 at 03:32
  • I still see the error with UCanAccess 3.0.5. I have updated the test project to show that. – William May 14 '16 at 08:00
  • Just tried and the error disappear. I'll check for it again later, please be sure you're actually using the 3.0.5. – jamadei May 14 '16 at 11:00
  • @William Yes, all your tests work fine for me. Please check again your environment and give me a feedback. Thank you in advance. – jamadei May 14 '16 at 19:32
  • I am **definitely** using UcanAccess-3.0.5. Java-1.8.0_74. Maven-3.3.9. Tests fail at lines 145:132:68 261:245:181 of FloatFlushTest with `AssertionError: Expected=18.2 found=21.0`. Essentially once a value is in the DB float column it isn't able to change it. Is there a better forum to discuss this? I couldn't see any way to discuss issues on SourceForge. – William May 14 '16 at 23:37
  • NB The FloatTest that you added to the UCanAccess source **doesn't** close and reopen the database between setting the value and checking the value. It also **doesn't** set the UCanAccess `SingleConnection` property to `true`. – William May 14 '16 at 23:43
  • Please fetch the latest version of my test project and execute 'mvn test' – William May 14 '16 at 23:45
  • I can't see anything in the UCanAccess commits from 527 onwards that would have any impact on persisting a float value. The only thing that seems vaguely relevant is the changes to IndexSelector in 527, but that would only be relevant if the column being changed were an index which it is not. What am I missing? – William May 15 '16 at 00:29
  • Note also that my tests are against an `mdb` rather than an `accdb` as you have used in the UCanAccess FloatTest. – William May 15 '16 at 01:06
  • the name IndexSelector is misleading: that is the fix – jamadei May 15 '16 at 04:45
  • it's irrelevant if it is an Mdb or an accdb – jamadei May 15 '16 at 04:53
  • I've run 10 time your test before and after the release: it works fine. yes we have to go on sourceforge – jamadei May 15 '16 at 04:56
  • note that the mentioned method of IndexSelector is for all columns, not just indexes – jamadei May 15 '16 at 04:59
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/111964/discussion-between-jamadei-and-william). – jamadei May 15 '16 at 05:03
  • nevertheless once I get home I'll try again your updated test (even if I've seen it works, yes I've tried the updated one). My test did reproduce the issue but you can't know what it does. And singleconnection true setting just gives us evidence of the issue, it's not part of. See you on sourceforge – jamadei May 15 '16 at 05:34
0

If you open your test database in Access

eclipselink-flush-test-master\src\test\db\PairedDown-2000.mdb

and then open TABLE1 in Design View you will see that the DOUBLE_RESULT field is actually defined as Single

TABLE1.png

When I changed that field to Double and re-ran your test project the issue went away.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for pointing out that the field was actually a single in the DB (DBeaver is reporting it as a Double). But I can't change the DB. If I change the entity to be a Float (ie single) then this just pushes the problem to the first update of the value. See the new FloatFlushTest I have added to the project.It seems to be tied to using UCanAccess SingleConnection=True and JPA. – William May 02 '16 at 01:33
  • Sorry, my previous reply wasn't as clear as it could be. I can't change the DB, but I can change the Entity field. However, changing it to Float (ie single) to match the DB just makes the problem more pervasive in that no updates are accepted after the first update. See the updated test cases. – William May 02 '16 at 07:04
  • If so, this is a major bug to be fixed asap. I'll let you know soon about it. – jamadei May 02 '16 at 11:34