Android Sqlite TRANSACTION issue

Solar2D Android
#1

Hi,
 

I’m having a pretty significant issue ever since coming up to the iOS 9 compatible Corona builds (currently seeing it on 2015.2731).

It first surfaced when some devices (starting with Sony Xperia Z3 Compact) started failing to perform database updates when they are wrapped in BEGIN TRANSACTION and COMMIT statements. This only happens when there is more that one statement in the transaction (I guess it ignores them anyway when there’s only one statement).

It appears that the transaction is successful (no error when you run it), but when you go to start another transaction (with another BEGIN TRANSACTION) you get a “Cannot start a transaction within a transaction” error thrown by Sqlite.

Using AWS Device Farm I tested on a few more devices and found it was much more widespread than I first realised (no wonder I was getting so many negative reviews on Google Play with my latest release!!). It fails on most of the Sony Xperia family, Samsung Galaxy family, etc. The devices were on a variety of operating system versions (4.4.2, 5.0.2, 5.1.1). I’m sure there’s more, but I stopped testing and started fixing to resolve the immediate issue.

I found that removing the transactions and just firing the queries individually overcame the issue, so I modified my code temporarily to work this way. It’s obviously a pretty dangerous way to operate and I want to resolve the issue asap to get back to using transactions again ASAP.

I tried to replicate with the HelloWorld example, but found it doesn’t happen with simple examples - it’s like it’s something that only happens when the queries are getting bigger or the database is getting bigger. When I copied my database creation code from my main app into a HelloWorld example it started failing with exactly the same error on the specific devices (so I have a worked example of the issue).

I’m inclined to think this is related to a recent Corona update, as it’s very old code that’s been running for ever.

Has any one else come across this?

Would appreciate your input @robmiracle

Nathan.

#2

Can you produce a demo app that shows the problem?

#3

Yes I have it.

#4

Best for me to submit it as a bug?

#5

Yes please. And also post the ID of the bug in this thread after you submit it (it will be in the email that confirmed your submission).

#6

Logged

Case 43735

#7

When I got into the weeds I found the actual issue is an error coming back from the SQLite - “unable to open the database file” and it was only occurring in transactions where there were multiple updates. 

The dev guys have acknowledged the issue but say they aren’t going to do anything about it as they believe it’s an issue in the SQLite library they use rather than Corona itself :frowning:

Not a good outcome. Makes me frustrated that I’m paying for this product and the bug isn’t going to be addressed. Guess it’s time to look for alternatives?

#8

We looked at this more and determined this issue affects all Android devices and is NOT Corona-specific. The problem looks to be an issue with where SQLite tries to create the temporary journal file on Android. See the following link with other Android users complaining about the issue. 

http://sqlite.1065341.n5.nabble.com/Android-SQLite-3-8-2-issue-with-triggers-amp-constraints-td73097.html

We tried the work around suggested with your HelloWorld test project and it’s now working without throwing an error on an Android device.

[lua]    if flDB then

        --we have a file to work with  

        flDB:exec(“PRAGMA temp_store = memory;”)        – **Added code**[/lua]

#9

Thanks Tom - this is great and seems to be working. Thanks for taking another look and getting to the bottom of it.

Nathan.

#10

Can you produce a demo app that shows the problem?

#11

Yes I have it.

#12

Best for me to submit it as a bug?

#13

Yes please. And also post the ID of the bug in this thread after you submit it (it will be in the email that confirmed your submission).

#14

Logged

Case 43735

#15

When I got into the weeds I found the actual issue is an error coming back from the SQLite - “unable to open the database file” and it was only occurring in transactions where there were multiple updates. 

The dev guys have acknowledged the issue but say they aren’t going to do anything about it as they believe it’s an issue in the SQLite library they use rather than Corona itself :frowning:

Not a good outcome. Makes me frustrated that I’m paying for this product and the bug isn’t going to be addressed. Guess it’s time to look for alternatives?

#16

We looked at this more and determined this issue affects all Android devices and is NOT Corona-specific. The problem looks to be an issue with where SQLite tries to create the temporary journal file on Android. See the following link with other Android users complaining about the issue. 

http://sqlite.1065341.n5.nabble.com/Android-SQLite-3-8-2-issue-with-triggers-amp-constraints-td73097.html

We tried the work around suggested with your HelloWorld test project and it’s now working without throwing an error on an Android device.

[lua]    if flDB then

        --we have a file to work with  

        flDB:exec(“PRAGMA temp_store = memory;”)        – **Added code**[/lua]

#17

Thanks Tom - this is great and seems to be working. Thanks for taking another look and getting to the bottom of it.

Nathan.