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/<package_path>/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.