Android 11 Open database failed. Could not open database on some Android devices

[TheGameNewWorldOrder]

Hi,

Some of our users have reported an issue related to the SQLite lib. After updating their phone's OS( Color OS) to 11(Android 11), SQLite operation is broken. In debug mode I see below error:

OPEN database: StorageExtension.db failed, aborting any pending transactions and following if trying to call any transaction:

error: openDatabase {"dblocation": "nosync", "location": "default", "name": "StorageExtension.db"} [[Error: Could not open database]]

I have verified the app and plugin work fine on Color OS 7 and 10, but broken on 11.

Expected Behavior

DB connection is as usual and DB can be opened.

Current Behavior

Opening DB failed

Possible Solution

Related to Android 11?

Steps to Reproduce (for bugs)

Example code im using:

`

SQLite.enablePromise(true); SQLite.DEBUG(true);

const init = async () => { await SQLite.echoTest(); db = await SQLite.openDatabase({ name: DATABASE_NAME, location: 'default', }); await createTables(db); }; `

Note that this code works fine on older Color OS and other Android phone like Samsung and Huawei

Your Environment

• React Native SQLite Storage Version used: "5.0.0"
• React Native version used: "0.63.3"
• Operating System and version (simulator or device): Oppo Color OS 11, Android 11
• IDE used: Android Studio 3.5

Debug logs

As shown above, the db was created but at /data/data//databases/

I have also tried the solution to create pre-populated DB but with no luck: https://medium.com/infinitbility/react-native-sqlite-storage-422503634dd2

Any idea why this is happening? We also have users on OnePlus and Pixel reporting the same problem.

[TheGameNewWorldOrder]

after looking into the Android code of the plugin, i found that the operation failed at long var1 = SQLiteNative.sqlc_db_open(this.dbfilename, this.openflags); which returns negative value on Android 11 while returning 0 on lower versions. Possible related reports in other sqlite implementation: https://github.com/storesafe/cordova-sqlite-storage/issues/954#issuecomment-716970227

[kinor]

Hi. Has there been any movement in resolving this problem? Thanks

[andpor]

@kinor not able to allocate time to this but all PR (backward compatible and also covering Android native) are welcome.

[bekkii77]

I'm experiencing similar issues. batch execution error: database not open after opening the database and receiving confirmation it's open and database already open: <databasename> These are only appearing in Android 11, Android 10 is fine.

[LeeKyungJoon]

I run well on Android 11. Looking at the uploaded code, createFromLocation is missing. I think it should be necessary. Add it and try it out.

[TheGameNewWorldOrder]

@LeeKyungJoon , this is not a pre-populated database. This issue happens on specific OS/Devices only. What device/OS did you use? My testing on huawei and Samsung were all fine, but Oppo/ColorOS fails.

[TheGameNewWorldOrder]

If your target SDK is 29, it runs fine on Android 11, but since Google forces all app updates to target 30 or above, this is no longer an option. We have 4 month left to do the update.

[KarenMuller]

Hi, Did you find a solution for this? I know public external storage changed from Android 11. But I am not sure where to put my sqlite database and how to access this. Db must not be deleted if app is uninstalled. Other apps must also be able access this sqlite db.

[AmolFC]

Any update on this? App is not working on many devices with Android 11 now.

[himu243]

Facing the Similar issue. It is showing the following error on my Samsung Galaxy M51 device.

Android Version: 11 OneUI Version: 3.1

LOG new transaction is waiting for open operation LOG OPEN database: master.db failed, aborting any pending transactions LOG error: openDatabase {"dblocation": "nosync", "name": "master.db"} [[Error: Could not open database]]

P.S. This is only happening when i make the targetSDKVersion 30. It is working fine on targetSDKVersion 29.

Can someone please help on this one?

Thanks in Advance

[zzorba]

Seeing this issue as well now, once I started targeting SDK Version 30 (RN65+).

I am not an Android expert, but I believe it is something related to this article (https://developer.android.com/about/versions/11/privacy/storage#permissions-target-11), which details that once you target SDK 30, you now must use the modern permissions system for accessing disks (on all versions). I was using the android-native version, which might be part of the problem here.

[psantos9]

Facing a similar issue as well. Tested on Android 9.0 API 28, Android 10.0 API 29 and Android 11 API 30.

My use case is as follows: Download a zipped db file from remote server into a cache directory, unzip it and place it into a Documents folder. This workflow works well with "expo-sqlite" library, I can sucessfully download, unzip and load the db.

While trying to reproduce it with the "react-native-sqlite-storage" I'm getting the "Error: Could not open database" issue. I'm using the following command for opening the database:

SQLite.openDatabase({
  name: 'mydatabase.sqlite',
  createFromLocation: '/data/user/0/com.myapp/files/SQLite/mydatabase.sqlite',
  location: 'default'
})

Also, I've confirmed that the "mydatabase.sqlite" file does indeed exist on the "/data/user/0/com.myapp/files/SQLite/" folder.

However, if I manually place the "mydatabase.sqlite" file directly into my project android asset's folder ("android/app/src/main/assets") and change the opening command attribute "createFromLocation" to "~mydatabase.sqlite", the database loads!

I've noticed that I'm using an absolute path for the first example, while on the second I'm using a relative path... Could it be a reason for the issue I'm seeing?

If someone could shed some light on this, I would be very gratefull...

Many thanks in advance!

[rblalock]

I am running into an identical problem.

Related to this and PR #492 .... curiously enough ... I removed the custom SQLite build and used the device's SQLite and I got it to work with my existing code.

i.e. I removed this from the react-native.config.js and it worked.

    "react-native-sqlite-storage": {
      platforms: {
        android: {
          sourceDir:
            "../node_modules/react-native-sqlite-storage/platforms/android-native",
          packageImportPath: "import io.liteglue.SQLitePluginPackage;",
          packageInstance: "new SQLitePluginPackage()"
        }
      }
    }

[himu243]

@rblalock It worked for me as well but not sure how is it working. Can you please explain that as well?

[rblalock]

I assume it has to do with the drivers for SQLite but I'm not 100% sure

[zzorba]

@himu243 so removing those lines means it will interface with the system level SQLite system instead of the one packaged in this project, which I assume resolves the disk permission issue by sidestepping it.

One problem with this approach is if your app supports older versions of android, you might be missing some key SQLite features that your system depends on. For example in my own testing, earlier versions didn’t have “on conflict” support.

[himu243]

@zzorba Though it fixed the issue of OpenDatabase but we I'm facing several other issues like in my SQL Query, I'm not able to use features like JSON_OBJECT , datetime etc.

Do we have any solution for that ?

[zzorba]

In the short term I downgraded back to RN64 and placed android:requestLegacyExternalStorage="true" on my AndroidManifest.xml > application node to workaround this. By all accounts this continues to work, even for people with Android 11 (at least, reports of crashes among my ~600 beta testers have stopped). I believe as long as you target SDK Version 29 (instead of 30) you can still opt out of the new file permission structure.

It's not a long term solution, but I haven't had time to fully explore either: 1) where the android-native library stores its database file. 2) how to either change that location or request permission from the Android 11 permissions system to allow read/write access to it. 3) How to migrate an existing database, or if a rebuild is needed.

[andpor]

I am seeking volunteers to test the new android native libraries. I am a bit skeptical that they contain all required features such as JSON support. There is a JSON test file in the examples directory that should work without issues on android native. If there are error, the new binaries need additional work.

[zzorba]

So, as of today (November 2021) targeting API 30 is now a requirement of distributing apps to the Google Play Store. So there's no workaround for this anymore if you need to use the 'android-native' library.

[kevinranks]

@andpor How can we help?

[himu243]

@andpor I have some findings on this one. Let me know how can I help.

[rubenmaas]

We have just created a PR that solves this issue (PR here). We've patched and updated the previously added native libraries.

[andpor]

We need some volunteers to test these new binaries. @rubenmaas thanks for this contribution. Could you confirm JSON support is baked in and that all tests including JSON test pass. Thanks

[himu243]

@andpor @rubenmaas I will check that and confirm if this issue still persists or is it resolved in the mentioned PR. It is not occurring on all Android 11 Devices. Luckily, I have the device on which this issue is reproducing !

[himu243]

@andpor @rubenmaas For me, this issue was reproducing on Samsung Galaxy M51 and I confirm that now its working fine on this particular device. Thanks a lot guys :-)

[xotahal]

Hi everyone, any progress on this? Do we still need to test anything? Can we help?

[andpor]

Hi All - I need confirmation that the new binaries have been tested by folks who actually use android-native...especially the JSON interface...

[shenyingyin]

@andpor @rubenmaas Hi. We had this issue reproducing on Galaxy S20 5G and Galaxy S10 and has been resolved with the new binaries. Hope the information can help.

[andpor]

@shenyingyin - thanks for the update. While I appreciate that new binaries seem to fix this particular issue, I need to get some confirmations that it is backward compatible especially from JSON interface perspective before I do any merges.

[FelixMo42]

@andpor I tested it on several devices (android, 9, 10, 11 and 12), the JSON interface worked perfectly for me.

Edit: I've also tested it on several ios devices and it doesn't seem to break anything. The qa team for the company gave are app using this a go, and didn't find any issues.

[FelixMo42]

@andpor Is there any update on this being merged in? We've used this fork for our beta testers, and no problems have poped up. I've tested all the JSON features were not using in our app on my phone and they seem to be working.

[prtkkmrsngh]

@andpor we at MobileOffize are using new android native libraries in production for a month now and didn't face any issue even with JSON support, hope you consider this to merge the PR.

[FatemeMirzaeii]

any updates on this?

[xotahal]

We're already using the PR that fixing this and works as expected. So we can also confirm it 👍

[jawand]

I am running into an identical problem.

Related to this and PR #492 .... curiously enough ... I removed the custom SQLite build and used the device's SQLite and I got it to work with my existing code.

i.e. I removed this from the react-native.config.js and it worked.

    "react-native-sqlite-storage": {
      platforms: {
        android: {
          sourceDir:
            "../node_modules/react-native-sqlite-storage/platforms/android-native",
          packageImportPath: "import io.liteglue.SQLitePluginPackage;",
          packageInstance: "new SQLitePluginPackage()"
        }
      }
    }

@rblalock this works. But have no idea if I am breaking other Android APIs?

@rubenmaas not sure why, but I tried your PR code and the issue for API 30 is still there. double checked if I have the newest binaries in node_modules. I am testing it on Samsung S10 and S20 these are the devices causing problem for our production users.

[FelixMo42]

@jawand Removing the "react-native-sqlite-storage" config breaks the json sql api (or at least it did last time I tested it).

[hammondreg]

@karenmuller Karen - I have the same problem as you ... did you ever find the correct Android directory to store your sqlite db? such that you could persist it i.e. delete the app and then still be able to use it?

[KarenMuller]

@KarenMuller Karen - I have the same problem as you ... did you ever find the correct Android directory to store your sqlite db? such that you could persist it i.e. delete the app and then still be able to use it?

@hammondreg Nope, sorry .....

Please let me know if you ever find a solution please.

[hammondreg]

@KarenMuller ... well I got almost there ... I can create a sqlite-net-pcl db with my Xamarin app in the Downloads directory, and when I uninstall the app the sqlite db and its journal file are still in Downloads. However (!!) when I re-install EXACTLY the same Xamarin app it refuses to open the db saying (un)helpfully "Cannot open database". I'm using an Android 11 Samsung tablet as a test device and my Xamarin app is built against Android 11 (API 30) and all Android libraries are up-to-date. Surprisingly (although I don't see why I should be surprised) my Xamarin app manifest has READ_EXTERNAL_STORAGE and WRITE_INTERNAL_STORAGE unchecked (yes, unchecked) but even if I check these two, I call can't re-install and open the db =:-{

[hammondreg]

@KarenMuller

Why A Persistent Android Database Is Never Going To Happen

Its because the ‘spotty-faced kids who can’t get a date’ at Google ‘developed’ a system and are petrified of allowing users to do anything useful on it. As Ken Olsen (and I paraphrase here) once opined ‘here on the East Coast we develop computers, out on the West Cost they just build toys’ and that’s the nub of it.

Android 11 closed all the loopholes that Android 10 allowed so ALL of us grown-up developers at the mercy of Google who now ship every new tablet, phone and device with Android 11 or Android 12 or whatever; one thing for certain is – they are never going to go backwards. So if one develops an application - and targets a particular Android version – one is going to come unstuck one day. Google will ‘pull the rug out from under your feet; because the ‘spotties’ have never written – or can ever know how to write – a proper commercial or scientific operating system. They don’t know how, and they can’t learn how, to do it and they don’t WANT to know how to do it.

I have spent the last week-end reading all the docs and trying hundreds of different test and it boils down to this: when one builds an Android app, one is casting it in concrete, because even if one finds an external directory AND one manages to create a database inside it .. that database is inextricably linked – glued if you will – to the application that created it i.e. it only allows access to itself by its creator application and EVEN if one deletes the application (leaving the db behind) and downloads EXACTLY the same application (without any changes whatsoever) then that app cannot open the db - created by a previous incarnation of itself - in any shape or form whatsoever. Even if the db resides in an ‘external’ or so-called ‘public’ directory; in fact even if it resides in ANY directory ANYWHERE within one’s particular version of Android. Yes, one could do this on Android 10 if one marked the app with READ_EXTERNAL_STORAGE and/or WRITE_EXTERNAL_STORAGE, but now on Android 11 even if one marks one’s app with MANAGE_EXTERNAL_STORAGE you still can’t access a db created by another app OR a previous version of one’s own app.

And even if ANY of the above were possible, one would fall foul of the policy at Google instigated by the ‘spotties’ which says ‘anything we don’t like or approve of, you won’t be able to upload to the Play Store’ so go take a hike, Mr Old Fogey Developer.

So there you have it; Google simply don’t want proper, grown-up, commercial apps running on Android.

The fault lies with me, then. I foolishly thought that an Android device was a computer, when in fact it is a toy, and that is purely down to my own incorrect perception of what I was dealing with.

So how does one achieve database persistence? The answer is to use one’s back end servers which hold data persistently e.g. in either a MySQL, SQL Server or Oracle or other such database; lets call this our remote SERVER db. Here goes:

1. Download your app to Android

2. Has your app already created its own LOCAL db? No => create a LOCAL db Yes => do nothing i.e. carry on

3. Does this LOCAL db have any tables? No => create all the LOCAL db tables you need Yes => do nothing i.e. carry on

4. Do the tables in this LOCAL db have data? No => download these data records from your remote SERVER db, insert them into your LOCAL db, and then into your app Yes => load this LOCAL db data into your app.

Away you go … your app has all the data (yippee!) it needs and it runs

5. Do you have to perform Inserts, Updates and Deletes? No => do nothing i.e. carry on Yes => send them all to the remote SERVER first Was this send successful? No => try again a limited number of times Yes => update the LOCAL db with whatever you sent to your remote SERVER

…and thus you keep your LOCAL db in step with your remote SERVER and vice versa.

Downsides? Of course. You are doing more networking and updating than necessary because you are keeping TWO databases in synch and that involves more programming, more heartache and more pain. Upsides? Plenty. You are no longer dependant on Google or any Android version or the spotty-faced kids because even at Android 999 (my God, I hope I’m well dead by then) … it should still allow you to create a LOCAL db which is all you need. Even if you update of delete your app (!!) then you can start from Step 1 above and you are back in business. Steps 2 and 3 are trivial and take nanoseconds. Step 4 means for 99% of the time you don’t have to re-populate your LOCAL db because it will always be in synch with your remote SERVER db - provided of course, that you have ensured Step 5 has worked properly for any Inserts, Updates and Deletes.

So, its not perfect by any means, but you are going to get the best of both worlds, i.e. your app can target Android devices (and there are 4 billion of them(!) so they can’t be ignored – much as I would like to), reasonably fast LOCAL db access, some networking overhead and persistent - albeit remote - SERVER db storage. But to my simple mind the most important facet of all … non-reliance on ANY Android version and therefore non-reliance on Google’s spotty-faced kids BUT with the ability to stuff your app into their PlayStore (says it all, doesn’t it?) without the aforementioned ‘spotties’ bitching about it.

I have spent the last 12 years fighting Windows/Visual Studio/Winforms/WPF/UWP and Xamarin AND now MAUI … I’m certainly not going to spend the next 15 years fighting Google!

[castalonirenz]

[castalonirenz]

I'm experiencing similar issues. batch execution error: database not open after opening the database and receiving confirmation it's open and database already open: <databasename> These are only appearing in Android 11, Android 10 is fine.

do you have fixes on this one? im also experiencing this one

[Ehsan0Hejazi]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use:

https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30

npm i react-native-sqlite-storage-api30

[castalonirenz]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use:

https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30

npm i react-native-sqlite-storage-api30

Can you tell us what are the changes you have done here so we can have an idea? thanks in advance

[Ehsan0Hejazi]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use: https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30 npm i react-native-sqlite-storage-api30

Can you tell us what are the changes you have done here so we can have an idea? thanks in advance

I have added new android native libraries patches which is under /platforms/android-native/libs other parts are remained the same,

so now you can switch your android target API to 30 and there will be no bugs and errors

[castalonirenz]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use: https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30 npm i react-native-sqlite-storage-api30

Can you tell us what are the changes you have done here so we can have an idea? thanks in advance

I have added new android native libraries patches which is under /platforms/android-native/libs other parts are remained the same,

so now you can switch your android target API to 30 and there will be no bugs and errors

I tried your library there is an error when im trying to build an apk

[castalonirenz]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use: https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30 npm i react-native-sqlite-storage-api30

Can you tell us what are the changes you have done here so we can have an idea? thanks in advance

I have added new android native libraries patches which is under /platforms/android-native/libs other parts are remained the same,

so now you can switch your android target API to 30 and there will be no bugs and errors

The app is crashing on apk release

[Ehsan0Hejazi]

Due to maintainer inactivity to accept PRs I've created another package based on this package with new android native libraries for API 30 available to use: https://www.npmjs.com/package/react-native-sqlite-storage-api30 https://github.com/Ehsan0Hejazi/react-native-sqlite-storage-api30 npm i react-native-sqlite-storage-api30

Can you tell us what are the changes you have done here so we can have an idea? thanks in advance

I have added new android native libraries patches which is under /platforms/android-native/libs other parts are remained the same, so now you can switch your android target API to 30 and there will be no bugs and errors

The app is crashing on apk release

Please create an issue with more details under the package github so we can track it

[castalonirenz]

thanks