Caused by: android.database.sqlite.SQLiteException: no such table: (code 1) Android

Question

We have a sqlite database in our Application. Its working fine for all the users but few of them experiencing the Caused by: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings error.

Below is my sqlite helper class to create the db and the error log. In assert/Master.db we have the table generalSettings. But after copying it to the device the table is missing. This is happening only for few users. I searched for the solution but I cant find the exact one. Team please help me to fix this.

Code:

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.StringWriter;

import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class InstallDB extends SQLiteOpenHelper {
    Context ctx;

    String DBNAME;
    String DBPATH;
    Modules modObj = new Modules();

    public InstallDB(Context context, String name) {
        super(context, name, null, 1);
        this.ctx = context;
        this.DBNAME = name;

        this.DBPATH = this.ctx.getDatabasePath(DBNAME).getAbsolutePath();
        Log.e("Path 1", DBPATH);

    }

    public void createDataBase() {

        boolean dbExist = checkDataBase();

        SQLiteDatabase db_Read = null;

        if (!dbExist) {
            synchronized (this) {

                db_Read = this.getReadableDatabase();
                Log.e("Path 2", this.getReadableDatabase().getPath());
                db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

            // try {

            // } catch (IOException e) {
            // throw new Error("Error copying database");
            // }
        }
    }

    private boolean checkDataBase() {

        SQLiteDatabase checkDB = null;

        try {
            String myPath = DBPATH;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (Exception e) {
            Log.i("SQLite Error", "database does't exist yet.");
        }

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

        return checkDB != null ? true : false;
    }

    private void copyDataBase() {

        try {
            InputStream myInput = ctx.getAssets().open(DBNAME);
            String outFileName = DBPATH;

            OutputStream myOutput = new FileOutputStream(outFileName);

            byte[] buffer = new byte[1024 * 3];

            int length = 0;

            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }

            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch (Exception e) {
            Modules.stacTaceElement = e.getStackTrace();

            StringWriter stackTrace1 = new StringWriter();
            e.printStackTrace(new PrintWriter(stackTrace1));
            System.err.println(stackTrace1);

            Intent send = new Intent(Intent.ACTION_SENDTO);
            String uriText;

            uriText = "mailto:test@test.com"
                    + "&subject=Error Report"
                    + "&body="
                    + stackTrace1.toString();

            uriText = uriText.replace(" ", "%20");
            Uri uri = Uri.parse(uriText);

            send.setData(uri);
            ctx.startActivity(Intent.createChooser(send, "Send mail..."));
            // TODO: handle exception
        }

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

Error Log:

java.lang.RuntimeException: Unable to start activity ComponentInfo{palmagent.FidelityAgent.Two/palmagent.FidelityAgent.Two.PassNew}: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2209)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2269)
at android.app.ActivityThread.access$800(ActivityThread.java:139)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1210)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5102)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
at palmagent.FidelityAgent.Two.masterDatabase.selectquery(masterDatabase.java:59)
at palmagent.FidelityAgent.Two.Modules.checkDatabase(Modules.java:28825)
at palmagent.FidelityAgent.Two.PassNew$LoaduserDetails.onPreExecute(PassNew.java:140)
at android.os.AsyncTask.executeOnExecutor(AsyncTask.java:587)
at android.os.AsyncTask.execute(AsyncTask.java:535)
at palmagent.FidelityAgent.Two.PassNew.onCreate(PassNew.java:120)
at android.app.Activity.performCreate(Activity.java:5248)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1110)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2173)
... 11 more

Are you writing a deployed copy of your database onto the one that Android would create? I think that's a bad idea as Android holds some private data in the database itself. — brummfondel, Jul 08 '14 at 14:17
@brummfondel - We have 10 tables and some static datas in the Db. So creating the db will takes long time. So we copied the database in Android. Please let me know the best way to do this. — Sniper, Jul 08 '14 at 15:23
I am facing the same problem after testing my app on some devices & emulators http://stackoverflow.com/questions/24406326/no-such-table-in-api-2-2 — Vivek Warde, Jul 18 '14 at 09:07

score 36 · Answer 1 · edited Oct 04 '16 at 05:28

The problem is because some of device is upgrading your app, so the checkDataBase() returning true, so you are not calling copyDataBase(). So you are using previous database which doesn't have generalSettings table. To solve this try:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(newVersion>oldVersion)
  copyDatabase();
}

and also update your constructor:

public InstallDB(Context context, String name) {
    super(context, name, null, DB_VERSION); 
    // DB_VERSION is an int,update it every new build

    this.ctx = context;
    this.DBNAME = name;
    this.DBPATH = this.ctx.getDatabasePath(DBNAME).getAbsolutePath();
    Log.e("Path 1", DBPATH);

}

Can OnUpgrabde be called with a newVersion actually lower than the oldVersion ? — Stephane Mathis, Jul 21 '14 at 20:54

Babul Mirdha · Answer 2 · 2016-11-06T09:33:57.053

20

After spending couple of hours I got this solution:

1) Settings > Application Manager

2) Select App

3) Clear Data

4) Uninstall App

Now Run app from Android Studio

Hope this works properly

edited Nov 06 '16 at 09:33

answered Oct 04 '16 at 04:36

Babul Mirdha

3,816
1
22
25

9

this only works temporary for testing, but won't work for users with older version – A. N Nov 02 '18 at 07:06
1

This will definitely crash the app for your users, and you would have to tell all of them to uninstall/clear data. This is a recipe to lose users. The other answers about performing a DB upgrade are the much better way to resolve this while retaining loyal users. – yiati Oct 05 '22 at 19:18
I just renamed databaseName for tests and it helps. In Gradle uninstallAll for my database module not working. Also not working Rebuild Project. I think it is a bug in AS, something with caches. – Elron Jun 23 '23 at 20:07

score 8 · Answer 3 · answered Jul 21 '14 at 13:12

This error Occurs Because you are not using DATABASE_VERSION

public class DatabaseHelper extends SQLiteOpenHelper {
    private static SQLiteDatabase sqliteDb;

    private static DatabaseHelper instance;

    private static final int DATABASE_VERSION = 1;

Increase Your version every time you make changes in your database just increase DATABASE_VERSION with +1..

NoWar · Answer 4 · 2015-11-23T00:26:17.273

5

Another possible solution is just uninstall an App from the Android emulator and after this run it again.

If you just want to remove a application:

1.Start the emulator.
2.Open the Android settings app.
3.Select "Applications" (Called "Apps" on Android 4.0 or higher)
4.Select "Manage Applications" (Only on Android 3.2 or lower)
5.Select the application you want to uninstall.
6.Click "Uninstall"

edited Nov 23 '15 at 00:26

answered Nov 23 '15 at 00:20

NoWar

36,338
80
323
498

2

This makes sense when you changed the database structure or naming during development. If you changed it between releases you need to handle the upgrade for users and can't tell them to reinstall the app manually ;-) – hb0 Jan 24 '19 at 13:40

score 4 · Answer 5 · answered Mar 06 '17 at 11:28

Suppose if you run your app with Database Version 1, then if you alter the table structure or add a new table, you must increase your Database Version to 2 and further if you make more changes to it.

public class AppDatabase extends SQLiteOpenHelper {

    // Database Name
    private static final String DATABASE_NAME = "myDatabase";

    // Database Version
    private static final int DATABASE_VERSION = 1;    

    public AppDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
}

Increase this DATABASE_VERSION value if any alterations are done.

Yes, this is a good practice. – Isaac Darlong Apr 11 '23 at 16:40 — Isaac Darlong, Apr 11 '23 at 16:40

Blasanka · Answer 6 · 2018-10-01T21:22:40.187

2

Even if you have coded for few tables and upgraded database, your app will crash. What you have to do is for each and every table create database changes upgrade the DATABASE_VERSION

If you have few DatabaseHelper classes(read this), you have to add this method to your db helper:

@Override
public void onOpen(SQLiteDatabase db) {
    onCreate(db);
}

edited Oct 01 '18 at 21:22

answered Sep 28 '18 at 01:19

Blasanka

21,001
12
102
104

score 2 · Answer 7 · answered Sep 04 '19 at 06:15

2

Update the DATABASE_VERSION and uninstall the app. Now run the app.

answered Sep 04 '19 at 06:15

paul polash

1,108
10
10

yes @paul this way we can manage, but if we don't want to uninstall app then in that case what should we do. – Vishwa Pratap May 14 '21 at 09:22

score 1 · Answer 8 · answered Sep 29 '16 at 07:25

1

If you are using GreenDao and get this error, be sure you are unistalling the app and try again. This solved my problem

answered Sep 29 '16 at 07:25

Catluc

1,775
17
25

score 0 · Answer 9 · answered Jul 21 '14 at 05:18

it's an upgrading exception. Make sure you have the table in your previous database. If not, create it. PS: if you're newly dev this app, uninstall it from your emulator or your device and re-install. But its not recommended for the data will ne lost.

score 0 · Answer 10 · answered Mar 20 '16 at 02:28

0

1.Change Your DataBase Version Or First Uninstall Your Apps In the Emulator or Phone And Re-install. I In This way Think Your Problem Will be solved.

answered Mar 20 '16 at 02:28

Md.Tarikul Islam

1,241
1
14
16

Muhammad Qasim · Answer 11 · 2019-12-31T11:42:46.893

0

kindly initiallize copy database or database helper in main class like that Database helper = new Database(this); helper.execute(sqliteobj)

edited Dec 31 '19 at 11:42

answered Dec 31 '19 at 07:31

Muhammad Qasim

11
1
3

score 0 · Answer 12 · answered Jan 01 '20 at 04:19

0

it is another solution to this problem some time developer didnot knows that there is not space between column name and its end like KEY_ADDRESS, this is wrong approach use space between comma and name of attribute

answered Jan 01 '20 at 04:19

Muhammad Qasim

11
1
3

score 0 · Answer 13 · answered Jan 02 '20 at 11:09

try remove getReadableDatabase

 if (!dbExist) {
            synchronized (this) {

                db_Read = this.getReadableDatabase();
                Log.e("Path 2", this.getReadableDatabase().getPath());
                db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

to

 if (!dbExist) {
            synchronized (this) {

                //db_Read = this.getReadableDatabase();
              //  Log.e("Path 2", this.getReadableDatabase().getPath());
              //  db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

remember to delete the installed application before running it. — Muhammad Rafiqi Liputo, Jan 02 '20 at 11:11

score 0 · Answer 14 · edited Jun 01 '20 at 19:53

I have had the same issue, when I created an update for app. By google documentation onCreate() from SQLiteOpenHelper is called only when the app is installed first time. So if you want to add a table you need to increment the database version and add

onUpgrade() { your_table.create(database) }

@Override public void onUpgrade(final SQLiteDatabase database, final int oldVersion, final int newVersion) { YourTable.onCreate(database); }

score 0 · Answer 15 · answered Sep 02 '20 at 21:16

My scenario is different:

I am using the Room's createFromAsset() to switch between a couple of database files, achieving that requires to first delete the database and recreate it again to be loaded with the other database file..

During the deletion process, the LiveData that I observe from the database raises SQLiteException: no such table and of course it's logical, because the LiveData senses that the underlying data is changed, and tries to get the new data, and finds that the table is gone.

To solve this: either use normal data (no LiveData) or stop observing the LiveData and re-observe it again when the database is recreated successfully.

Note: without deleting the database file, Room continues to use a cached version of the old database file.

score -2 · Answer 16 · answered Aug 07 '19 at 07:08

-2

In my case I changed my db name and its work for me

answered Aug 07 '19 at 07:08

Rashed Zzaman

123
12

score -2 · Answer 17 · answered Sep 10 '21 at 14:49

-2

Try with this solution, this one works for me:

Select App
Clear Cache
Clear Data
Uninstall App

And then run the app from Android Studio

answered Sep 10 '21 at 14:49

Vishal_VE

1,852
1
6
9

Caused by: android.database.sqlite.SQLiteException: no such table: (code 1) Android

17 Answers17

Linked