I'm stuck in unique situation. In my app, I have local database. Somehow one of the tables got filled with junk data and database grew massively large. By the time I came to know, size was over 1 gb. So I kept getting database locked exception as reading that junk rows on that table was very very slow. After debugging, I figured out which table it was and now I want to delete it (either drop whole table or delete all rows) but whenever I perform any operation on that table, database gets locked and ANR happens. So I'm not sure how to get rid of that table. I can't uninstall app.
Note that I'm using GreenDao in my android app.
I've tried following things
1) DROP TABLE mytable;
2) DELETE FROM mytable;
3) DaoSession.getMyDao().deleteAll(); // this is a greedao method but it internally performs 2nd query I mentioned I think
In all the cases. It is just producing ANR.and app database gets locked.
Edit: I tried it in separate thread also, It just avoided ANR but did not drop table and database was still locked.
After lock I get this whenever other thread tried to write to db which is obvious.
E/SQLiteDatabase: Failed to open database '/data/user/0/myapp/databases/mydb.db'.
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
#################################################################
Error Code : 5 (SQLITE_BUSY)
Caused By : The database file is locked.
(database is locked (code 5): , while compiling: PRAGMA journal_mode)
#################################################################
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1000)
at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:704)
at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:385)
at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:359)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:248)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:199)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:514)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:206)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:178)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:934)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:895)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:708)
at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:646)
at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:283)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
at com.intouchapp.database.IntouchDb.getNewReadableDaoSesssion(IntouchDb.java:80)
at com.intouchapp.models.ActivityLogsDb.getCursorOfAllResults(ActivityLogsDb.java:298)
at com.intouchapp.services.ActivityLogsDbInsertionService.onHandleIntent(ActivityLogsDbInsertionService.java:73)
at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:66)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.os.HandlerThread.run(HandlerThread.java:61)
Also I get this too,
The connection pool for database '+data+user+0+myapp+databases+mydb' has been unable to grant a connection to thread 2262 (SyncAdapterThread-1) with flags 0x2 for 12.0060005 seconds.
Connections: 1 active, 0 idle, 0 available.
Requests in progress:
executeForChangedRowCount started 12645ms ago - running, sql="DELETE FROM 'mytable'"
This clearly states that It is not able to execute "DELETE FROM 'mytable'"
this particular query hence the lock.
SQLite locks database when it is used to write operation. You should take care about some points regarding this :-