Values are getting stored in .db-shm and .db-wal instead of actual SQLite file while INSERT in Android Studio

Question

I am inserting values to an Existing database stored in data>data>package_name>databasefile. but while Inserting the values , Instead of getting stored in data.db file, the values are auto-creating data.db-shm and data.db-wal files , and getting saved in it. I cant access the .db-shm and .db-wal file , but I can see from the modification time , that whenever I insert values these two files are modified not the actual one.

Below are my code -

MainActivity.java

package com.data.wifi;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import com.getcapacitor.BridgeActivity;
import com.getcapacitor.Plugin;

import java.util.ArrayList;

public class MainActivity extends BridgeActivity {

  DBHelper dbHelper = new DBHelper(this);

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    db.execSQL("INSERT INTO applogs VALUES ('7760','Tue Oct 27 2020','Values inserted Successfully' )");

    this.init(savedInstanceState, new ArrayList<Class<? extends Plugin>>() {{
      // Additional plugins you've installed go here
      
    }});
  }

  @Override
  public void onSaveInstanceState(Bundle outState) {
    super.onSaveInstanceState(outState);
    outState.clear();
  }
}

DBHelper.java

package com.data.wifi;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

  public static final int DATABASE_VERSION = 1;
  public static final String DATABASE_NAME = "data.db";
  public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  }
}

Whenever I am trying to run this code, two files are being created with same db name with different extensions (.db-shm and .db-shm) and it from the modification time , It looks like these two file are the ones getting modified all the time.In this pic wifi.db is the actual database file . but two other files are created automatically and looks like it is modifying only

PS- This code is working absolutely perfect in emulator, only when I try to run it on actual mobile device I am facing this issue. Edit- When I run the code in an emulator , the values are inserted in the original dbfile properly, and no such shm and wal files are created there . I am seeing this is an issue , because when I try to extract my db file , i dont see my inserted values there. neithr the db file looks modified

Read this: https://sqlite.org/tempfiles.html and this: https://stackoverflow.com/questions/7778723/what-are-the-db-shm-and-db-wal-extensions-in-sqlite-databases — forpas, Oct 27 '20 at 14:16
Hi @forpas , thanx for reply, actually the issue I am facing is that these two additional files are getting updated instead of the original one. — Sam Verma, Oct 27 '20 at 14:21
This is not an issue. These files are temporary and will be deleted by SQLite. Check also this: https://sqlite.org/wal.html — forpas, Oct 27 '20 at 14:53
That's normal behavior for a database in wal journal mode, yeah. Nothing to worry about. — Shawn, Oct 27 '20 at 15:21
The values are inserted. You can then select/update/delete them. Why do you think this is an "issue"? I mean, why do you care about exact file names sqlite use to store its data? This is how write-ahead logging works, that's all. — Grisha, Oct 27 '20 at 15:48
this is an issue , because I dont see the inserted values, when I open the db file, I mean the new inserted values are not seen in my db file. — Sam Verma, Oct 28 '20 at 04:01
Hi @Grisha ,I am seeing this is an issue , because when I try to extract my db file , i dont see my inserted values there. neithr the db file looks modified — Sam Verma, Oct 28 '20 at 04:06

score 2 · Accepted Answer · answered Oct 28 '20 at 06:10

This behavior is normal. This is the way write-ahead logging works. If you want to backup or transfer the database to some other place, you can execute

 pragma wal_checkpoint

to ensure that all your inserted values are stored in the main (.db3) file. Or you can use

pragma journal_mode=DELETE

See this question: How to manually perform checkpoint in SQLite android?

and this: https://www.sqlite.org/pragma.html#pragma_journal_mode

score 1 · Answer 2 · answered Jun 14 '22 at 21:01

To move your data from .db-wal to .db use PRAGMA wal_checkpoint, following code worked for me -:

SQLiteDatabase db = this.getWritableDatabase();
String query = "PRAGMA wal_checkpoint(full)";

Cursor cursor = db.rawQuery(query, null);
if (cursor != null && cursor.moveToFirst()) {
    int a = cursor.getInt(0);
    int b = cursor.getInt(1);
    int c = cursor.getInt(2);

}
if (cursor != null) {
    cursor.close();
}

Values are getting stored in .db-shm and .db-wal instead of actual SQLite file while INSERT in Android Studio

2 Answers2