SQLiteCantOpenDatabaseException: unknown error (code 14 SQLITE_CANTOPEN): Could not open database

Question

I am using below code to open or create a sqlite db. The code is working fine for some phones but it is giving below error on Android P devices.

SQLiteDatabase myDB = null;
myDB = backup.this.openOrCreateDatabase(url[0], MODE_PRIVATE, null);

Logcat

2019-11-14 07:49:56.204 10213-10363/com.gadha.garden E/SQLiteLog: (14) cannot open file at line 36667 of [c255889bd9]
2019-11-14 07:49:56.204 10213-10363/com.gadha.garden E/SQLiteLog: (14) os_unix.c:36667: (2) open(/sdcard/Download/2019-11-14 07-49-56 AM.db) - 
2019-11-14 07:49:56.241 10213-10363/com.gadha.garden E/SQLiteDatabase: Failed to open database '/sdcard/Download/2019-11-14 07-49-56 AM.db'.
    android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14 SQLITE_CANTOPEN): Could not open database
        at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:211)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:195)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:503)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:204)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:196)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:880)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:865)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:766)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:772)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:757)
        at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:306)
        at com.gadha.garden.backup$backupRestore.doInBackground(backup.java:120)
        at com.gadha.garden.backup$backupRestore.doInBackground(backup.java:108)
        at android.os.AsyncTask$2.call(AsyncTask.java:333)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:764)

Answer 1

In case this doesn't work for someone reading this, make sure your database rules allow access. Check the "Usage" tab to see if there were unsuccessful attempts to access the database.

Answer 2

Considering the comment

I am trying to create a database. How can use disableWriteAheadLogging before creating a database? –

Then I believe that your issue is that you haven't granted the required permissions even though you say you have as per :-

Added both read and write permissions.

You need to grant WRITE_EXTERNAL_STORAGE (this implicitly grants READ_EXTERNAL_STORAGE).

For devices API 23+ Android 6+ rather than 9+ this requires permissions to be granted via the MANIFEST e.g. :-

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

But also that the user grant access as per Permission approval. For devices less then API 23 only the manifest permission is required.

Example

The following is a simple example that boh replicates the issue and shows that granting the permissions corrects the issue.

• The manifest contains the above uses-permission entry.

The user permission is reuested via the following (ExternalStoragePermissions.java) :-

abstract class ExternalStoragePermissions {

    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };

    public ExternalStoragePermissions(Activity callingActivity) {}
    // Note call this method
    public static void verifyStoragePermissions(Activity activity) {
        int permission = ActivityCompat.checkSelfPermission(
                activity,
                Manifest.permission.WRITE_EXTERNAL_STORAGE);

        if(permission != PackageManager.PERMISSION_GRANTED) {
            ActivityCompat.requestPermissions(
                    activity,
                    PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE
            );
        }
    }
}

The activity (MainActivity.java) used is :-

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        if(Build.VERSION.SDK_INT >= 23) {
            ExternalStoragePermissions.verifyStoragePermissions(this);
        }

        SQLiteDatabase myDB = null;
        myDB = this.openOrCreateDatabase("/sdcard/Download/2019-11-14 07-49-56 AM.db", MODE_PRIVATE, null);
        DatabaseUtils.dumpCursor(
                myDB.query("sqlite_master",null,null,null,null,null,null)
        );
        myDB.close();
    }
}

Results

When run for the first time the App displays :-

However as the main thread continues and access hasn't been granted then the log includes :-

2019-11-15 08:35:33.439 11874-11874/a.so58855993copyfromassets D/AndroidRuntime: Shutting down VM
2019-11-15 08:35:33.440 11874-11874/a.so58855993copyfromassets E/AndroidRuntime: FATAL EXCEPTION: main
    Process: a.so58855993copyfromassets, PID: 11874
    java.lang.RuntimeException: Unable to start activity ComponentInfo{a.so58855993copyfromassets/a.so58855993copyfromassets.MainActivity}: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14 SQLITE_CANTOPEN): Could not open database
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3270)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
        at android.os.Handler.dispatchMessage(Handler.java:107)
        at android.os.Looper.loop(Looper.java:214)
        at android.app.ActivityThread.main(ActivityThread.java:7356)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
     Caused by: android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 14 SQLITE_CANTOPEN): Could not open database
        at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:215)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:197)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:505)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:206)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:198)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:915)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:895)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:786)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:812)
        at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:797)
        at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:310)
        at a.so58855993copyfromassets.MainActivity.onCreate(MainActivity.java:27)
        at android.app.Activity.performCreate(Activity.java:7802)
        at android.app.Activity.performCreate(Activity.java:7791)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409) 
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83) 
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135) 
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016) 
        at android.os.Handler.dispatchMessage(Handler.java:107) 
        at android.os.Looper.loop(Looper.java:214) 
        at android.app.ActivityThread.main(ActivityThread.java:7356) 
        at java.lang.reflect.Method.invoke(Native Method) 
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930) 

However at this stage the App remains active as the dialog requesting permission is still active. Clicking Allow result in the App crashing but then restarting and successfully creating the database as per the log containig :-

2019-11-15 08:41:13.615 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5df21a1
2019-11-15 08:41:13.616 I/System.out: 0 {
2019-11-15 08:41:13.616 I/System.out:    type=table
2019-11-15 08:41:13.616 I/System.out:    name=android_metadata
2019-11-15 08:41:13.616 I/System.out:    tbl_name=android_metadata
2019-11-15 08:41:13.616 I/System.out:    rootpage=3
2019-11-15 08:41:13.616 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-11-15 08:41:13.616 I/System.out: }
2019-11-15 08:41:13.616 I/System.out: <<<<<

• Note the above is only a demonstration. In an App to be used you would obviously set the permissions before attempting to access the database.
• Ideally you should not hard code paths but use the system values e.g. Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS) for the path.
• Typically you would place Databases in the App's data via the Context's getDatabasePath method rather than place the dataset in the downloads folder.
  • Permission for the standard location in the App's data is granted and thus does not need to be requested.