New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLiteCantOpenDatabaseException unable to open db-journal #380

Closed
battlmonstr opened this Issue Aug 28, 2015 · 13 comments

Comments

Projects
None yet
8 participants
@battlmonstr
Copy link

battlmonstr commented Aug 28, 2015

I'm getting an exception that comes from SQLite not being able to open db-journal.
I'm running a lot of queries successfully, but at some point it crashes.
This happens rarely, and it is hard to reproduce.

If you google "db-journal" and SQLiteCantOpenDatabaseException , it gives a lot of similar crashes that people have (without using DBFlow), and the solution they suggest is to close the DB handle, wait a second and try to open it again.

The first question, should it be done inside DBFlow, or can it be done in my code ?

Another question is: is it possible to catch this kind of exception centrally somehow, because I don't want to put every DBFlow call in a try-catch. (or should I?)

Device: Google Nexus 7
OS : Android 5

log:

[ 08-27 15:43:07.297  3493: 3493 E/SQLiteLog ]
(14) cannot open file at line 30046 of [9491ba7d73]
(14) os_unix.c:30046: (24) open(/data/data/com.my.app/databases/data.db-journal) - 
[ 08-27 15:43:07.297  3493: 3493 E/SQLiteLog ]
(14) statement aborts at 26: [SELECT * FROM ...] unable to open database file
FATAL EXCEPTION: main
android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file (code 14)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845)
    at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836)
    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144)
    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
    at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197)
    at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237)
    at com.raizlabs.android.dbflow.sql.SqlUtils.convertToList(SqlUtils.java:134)
    at com.raizlabs.android.dbflow.sql.SqlUtils.queryList(SqlUtils.java:56)
    at com.raizlabs.android.dbflow.sql.language.BaseModelQueriable.queryList(BaseModelQueriable.java:35)
    at com.raizlabs.android.dbflow.sql.language.Where.queryList(Where.java:347)

@battlmonstr battlmonstr changed the title SQLiteCantOpenDatabaseException unable to open database file SQLiteCantOpenDatabaseException unable to open db-journal file Aug 28, 2015

@battlmonstr battlmonstr changed the title SQLiteCantOpenDatabaseException unable to open db-journal file SQLiteCantOpenDatabaseException unable to open db-journal Aug 28, 2015

@kaeawc

This comment has been minimized.

Copy link
Contributor

kaeawc commented Sep 5, 2015

@battlmonstr You'll run into this crash whenever you're rapidly querying a Sqlite database, especially on multiple threads. I've always found when dealing with network or disk that try catches are for the best. You can create a singleton class that is your query handler to centralize your error handling.

@glureau

This comment has been minimized.

Copy link
Contributor

glureau commented Nov 6, 2015

Same problem here.
@kaeawc I have 2 questions for you try/catch method:

1/ When you're using this, you're putting try/catch around every methods that can call a SQL request? How does work your singleton? Looks a lot of boilerplate without speaking about error scenarios you have to write everywhere, no?

2/ How do you handle when the issue comes from the lib? (see below)
W/Binder: Caught a RuntimeException from the binder stub implementation. W/Binder: android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file (code 14) W/Binder: at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method) W/Binder: at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:845) W/Binder: at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:836) W/Binder: at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62) W/Binder: at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:144) W/Binder: at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133) W/Binder: at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:197) W/Binder: at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:237) W/Binder: at com.raizlabs.android.dbflow.sql.SqlUtils.convertToModel(SqlUtils.java:162) W/Binder: at com.raizlabs.android.dbflow.sql.SqlUtils.querySingle(SqlUtils.java:225) W/Binder: at com.raizlabs.android.dbflow.sql.language.BaseModelQueriable.querySingle(BaseModelQueriable.java:40) W/Binder: at com.raizlabs.android.dbflow.sql.language.Where.querySingle(Where.java:366) W/Binder: at com.playtmn.tablet.common.db.dbflow.DataChangeServiceImpl$1.onModelStateChanged(DataChangeServiceImpl.java:62) W/Binder: at com.raizlabs.android.dbflow.runtime.FlowContentObserver.onChange(FlowContentObserver.java:244) W/Binder: at android.database.ContentObserver.dispatchChange(ContentObserver.java:163) W/Binder: at android.database.ContentObserver$Transport.onChange(ContentObserver.java:195) W/Binder: at android.database.IContentObserver$Stub.onTransact(IContentObserver.java:60) W/Binder: at android.os.Binder.execTransact(Binder.java:404) W/Binder: at dalvik.system.NativeStart.run(Native Method)

@battlmonstr

This comment has been minimized.

Copy link
Author

battlmonstr commented Nov 6, 2015

Following an advice from @kaeawc I've made my own wrapper of the query methods that I use from DBFlow (like queryList, querySingle etc), and I have to use it instead of DBFlow methods all over the place. I would really prefer if this functionality (common exception handler) would be implemented by the DBFlow itself, because it's easy to miss out wrapping some call, and also it's not trivial to do for all DBFlow methods in general.

@glureau

This comment has been minimized.

Copy link
Contributor

glureau commented Nov 7, 2015

Yes, but what could be the good implementation to manage these exceptions?
When using ContentObserver we could wait and re-try (should probably be on the CO thread).
But when selecting some data, do we want a retry? How much? What if it's still in error after a while?

@Ydvisual

This comment has been minimized.

Copy link

Ydvisual commented Nov 21, 2015

battlmonstr,

Make sure for every query that has a cursor, you close() the cursor when done.
If you don't, you can get that kind of error you are getting after many queries.

Let me know if that helps!

@ahangchen

This comment has been minimized.

Copy link

ahangchen commented Dec 2, 2015

I got this exception after I query the database many times. In my sql, I do a lot of subquery, and when the exception looked like this:
E/SQLiteLog: (14) cannot open file at line 31775 of [cf538e2783]
E/SQLiteLog: (14) os_unix.c:31775: (30) open(./etilqs_TcZfcNFRZAgwTln) -
E/SQLiteLog: (14) statement aborts at 51: [SELECT ....
W/System.err: ...database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file (code 14)
W/System.err: at ....database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
W/System.err: at .... sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:913)
W/System.err: at com.tencent.moai.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:819)
W/System.err: at ....database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
W/System.err: at com.tencent.moai.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:159)
W/System.err: at ... database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:147)

I think it was caused by the error of the temp file etilqs_TcZfcNFRZAgwTln. Could somebody tell me any reason that could cause the failure of opening this file?

@battlmonstr

This comment has been minimized.

Copy link
Author

battlmonstr commented Dec 7, 2015

Ydvisual,

It's not a problem of opened cursors. I'm only using queryList() and querySingle(), which close the cursor for me. I also use some Delete-s, for which I call queryClose().

@Ydvisual

This comment has been minimized.

Copy link

Ydvisual commented Dec 7, 2015

OK battlmonstr; I see.
Any luck with the problem recently?

On Mon, Dec 7, 2015 at 6:30 PM, battlmonstr notifications@github.com
wrote:

Ydvisual,

It's not a problem of opened cursors. I'm only using queryList() and
querySingle(), which close the cursor for me. I also use some Delete-s, for
which I call queryClose().


Reply to this email directly or view it on GitHub
#380 (comment).

@franzejr

This comment has been minimized.

Copy link

franzejr commented Dec 13, 2015

I think I'm having the same issue on a app I'm working on.

@ahangchen

This comment has been minimized.

Copy link

ahangchen commented Dec 20, 2015

I got my answer. This crash happened when I do a lot of query on my DB;
Then, sqlite will open a lot of page;
you can have a look at sqlite3.c or pager.c,
when it failed on sqlitepcacheFetchStress() because memory limit,
the pager will open a temporary file using the unixOpen(),
unixOpen() will invoke unixGetTempname() to get the temp file name,
unixGetTempname() will invoke unixTempFileDir() to get the directory for the temp file,
and what you can see in unixTempFileDir()?

/*
** Return the name of a directory in which to put temporary files.
** If no suitable temporary file directory can be found, return NULL.
*/
static const char *unixTempFileDir(void){
  static const char *azDirs[] = {
     0,
     0,
     0,
     "/var/tmp",
     "/usr/tmp",
     "/tmp",
     0        /* List terminator */
  };
  unsigned int i;
  struct stat buf;
  const char *zDir = 0;

  azDirs[0] = sqlite3_temp_directory;
  if( !azDirs[1] ) azDirs[1] = getenv("SQLITE_TMPDIR");
  if( !azDirs[2] ) azDirs[2] = getenv("TMPDIR");
  for(i=0; i<sizeof(azDirs)/sizeof(azDirs[0]); zDir=azDirs[i++]){
    if( zDir==0 ) continue;
    if( osStat(zDir, &buf) ) continue;
    if( !S_ISDIR(buf.st_mode) ) continue;
    if( osAccess(zDir, 07) ) continue;
    break;
  }
  return zDir;
}

in android system, none of the directory is writable,
so sqlite use "." for temp file, which means current working directory.
That's all right, but what is current working directory?
I use
system("ls . > /sdcarc/0.txt");
and cat the 0.txt in adb shell
and found that

 current working directory is "/", the root directory of system !!!!!!

so it's sure that sqlite can not create of open this temp file named "./etilqs_3P2SKRP0Ge6cj3T".
It means that, every app using sqlite on android system may crash for this reason.

To trigger this crash, you can insert a lot of records into you databases and repeatly do something like select * from a where a.x in (select * from b) or other things that will rapidly exhaust the memory for page, and you will trigger sqlite to open this kind of temp file and get the crash.

so how to fix the problem?
Our team found a solution:
execute this sql : "PRAGMA temp_store_directory='your_dir_name'" when you first open a database connection, so the temp dir will be set to somewhere writable.

I suggest to set the temp dir to your database directory's subdirectory like:
/data/data/packagename/my_temp_dir
don't set the directory to sdcard, which will cause a failure of fstat in sqltie,

Sqlite will automatically clear the temp files under the directory after the temp file is useless,
except for some terrible condition like that sqlite opens a temp file, but crash before it removes the temp file.

Good luck.

if you can understand Chinese, you can have a look at this link:
http://www.cnblogs.com/hellocwh/p/5061805.html
I make a summary of this BUG.

@battlmonstr

This comment has been minimized.

Copy link
Author

battlmonstr commented Dec 21, 2015

Hardcore stuff, @cwhgithub 梦里风林 !
I will certainly try it out.
Thank you.

@agrosner

This comment has been minimized.

Copy link
Owner

agrosner commented May 4, 2016

I assume this is fixed?

@agrosner agrosner closed this May 4, 2016

@developer1011

This comment has been minimized.

Copy link

developer1011 commented Dec 16, 2017

@ahangchen this pragma is deprecated

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory-name';

Query or change the value of the sqlite3_temp_directory global variable, which many operating-system interface backends use to determine where to store temporary tables and indices.

When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers in the database connection that issued the pragma are immediately deleted. In practice, temp_store_directory should be set immediately after the first database connection for a process is opened. If the temp_store_directory is changed for one database connection while other database connections are open in the same process, then the behavior is undefined and probably undesirable.

Changing the temp_store_directory setting is not threadsafe. Never change the temp_store_directory setting if another thread within the application is running any SQLite interface at the same time. Doing so results in undefined behavior. Changing the temp_store_directory setting writes to the sqlite3_temp_directory global variable and that global variable is not protected by a mutex.

The value directory-name should be enclosed in single quotes. To revert the directory to the default, set the directory-name to an empty string, e.g., PRAGMA temp_store_directory = ''. An error is raised if directory-name is not found or is not writable.

The default directory for temporary files depends on the OS. Some OS interfaces may choose to ignore this variable and place temporary files in some other directory different from the directory specified here. In that sense, this pragma is only advisory.

This pragma is deprecated and exists for backwards compatibility only. New applications should avoid using this pragma. Older applications should discontinue use of this pragma at the earliest opportunity. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED.

Source

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment