I have gone through and made sure all my Cursors are closed. I am running with strict mode with detectLeakedSqlLiteObjects and detectLeakedClosableObjects and that never gets triggered. But when running on production a few users will crash with this:
android.database.CursorWindowAllocationException: Cursor window allocation of 2048 kb failed.
Could this be happening because the data is too large?
Could it be happening because some third party library is leaving cursors open, like an ad library for example? Are cursors shared between all DBs?
What else should I check?
Could this be happening because the data is too large?
In short No, you would get a different message:-
A CursorWindow must be able to hold at least one row, if a row is larger than the CursorWindow then you will get a message that indicates the size of the CursorWindow and the size of the data (larger than the former).
e.g. Window is full: requested allocation 3095146 bytes, free space 2096696 bytes, window size 2097152 bytes
So the row (which contains a column that requires 3095146 bytes) cannot fit into the window with is 2048k (2097152 / 1024) (prior rows/columns have used 456 bytes).
However, it may be that the 456 bytes has a full row or more, in which case that row or rows will be in the CursorWindow.
It may be that then the CursorWindow can accommodate the row that did not fit but then the following row may not fit. So that message may be repeated and even never appear again and all the data is extracted.
If the CursorWindow cannot handle the data then, at least for a BLOB, you would get,the actual failure message something like :-
android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=3, totalRows=4
Here's how this could all happen:-
2023-01-24 20:25:12.323 W/CursorWindow: Window is full: requested allocation 2000000 bytes, free space 1096590 bytes, window size 2097152 bytes
2023-01-24 20:25:12.394 W/CursorWindow: Window is full: requested allocation 3000000 bytes, free space 96638 bytes, window size 2097152 bytes
2023-01-24 20:25:12.398 W/CursorWindow: Window is full: requested allocation 3000000 bytes, free space 2096686 bytes, window size 2097152 bytes
2023-01-24 20:25:12.398 E/SQLiteQuery: exception: Row too big to fit into CursorWindow requiredPos=3, totalRows=4; query: SELECT * FROM example
2023-01-24 20:25:12.399 W/System.err: android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=3, totalRows=4
It should be noted that the CursorWindowSize varies, I believe dependant upon the Android version (used to 1Mb and I believe as large as 4Mb).
Could it be happening because some third party library is leaving cursors open, like an ad library for example? Are cursors shared between all DBs?
A Cursor is effectively a file, there is a limit on the number of files (file handles) that can be open/opened. So although Cursors are not shared. There is a limit of how many files can be open at the same time.
An issue that has arisen has been that cursors have apparently been closed BUT after a loop that was opening cursors for each iteration. So apparently closing BUT only closing the last (along the lines of the "how to simulate cursorwindow allocation" linked below).
Another cause could be that there is simply not enough memory from which to get the 2048k.
Furthermore, perhaps not relevant, is that a CursorWindow is only obtained/allocated if a Cursor is accessed. The following (with the getCount() commented out) works fine for 4000 cursors.:-
int numberOfCursors = 4000;
Cursor[] manyCursors = new Cursor[numberOfCursors];
int cursorsRetrieved = 0;
for (int i=0; i < numberOfCursors; i++) {
try {
manyCursors[i] = dbHelper.getWritableDatabase().rawQuery(
"SELECT " + DatabaseHelper.EXAMPLE_NAME_COL_NAME + " FROM " + DatabaseHelper.EXAMPLE_TABLE_NAME + ";",
null
);
cursorsRetrieved++;
//manyCursors[i].getCount();
} catch (Exception e) {
Log.d("CURSORMAX","Cursor count maxed out cursor " + (i+1));
e.printStackTrace();
break;
}
}
for (int i=0; i < cursorsRetrieved; i++) {
manyCursors[i].close();
}
With the getCount() in:-
E/CursorWindow: CursorWindow: mmap() failed: errno=12.
D/CURSORMAX: Cursor count maxed out cursor 1417
W/System.err: android.database.CursorWindowAllocationException: Could not allocate CursorWindow '/data/user/0/a.a.so75213985javsqlitecursorissues/databases/example.db' of size 2097152 due to error -12.
You may find the following of interest:-
Row too big to fit into CursorWindow requiredPos=0, totalRows=1;
Android: How to simulate CursorWindowAllocationException crash
Ok so I could try to catch the exception and then log the size of the data so I at least get an idea of that is what is happening.
Yes and if possible perhaps a copy of the database file(possibly files if using WAL).
The log should point you to exactly where the failure is occurring, you could then determine the query/table(s) where the issue is occurring and thus be able to reduce the code that needs to be investigated. You could also run tests/extracts on the data e.g. using the SQLite length function to determine the length columns.
e.g. something based upon SELECT rowid,length(the_column) AS rowsize FROM the_table GROUP BY rowid ORDER BY rowsize DESC;
Perhaps consider the following that will check every column in every row and the accumulated length of all the columns to see if there may be a row or rows that exceed the specified CursorWindow size (again the CursorWindowSize appears to be API dependant).
The DatabaseHelper class with the potentially pertinent isCursorRetrievable method that will check most tables (will not work on WITHOUT ROWID tables). The getAllTrapped method is what was used to provide the Blob Too Big log extracts.
class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "example.db";
public static final int DATABASE_VERSION = 1;
public static final String EXAMPLE_TABLE_NAME = "example";
public static final String EXAMPLE_ID_COL_NAME = BaseColumns._ID;
public static final String EXAMPLE_NAME_COL_NAME = "_name";
public static final String EXAMPLE_DATA_COL_NAME = "_data";
private static final String EXAMPLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + EXAMPLE_TABLE_NAME +
"(" +
EXAMPLE_ID_COL_NAME + " INTEGER PRIMARY KEY " +
"," + EXAMPLE_NAME_COL_NAME + " TEXT " +
"," + EXAMPLE_DATA_COL_NAME + " BLOB " +
");";
private static final String EXAMPLE_DROP_SQL = "DROP TABLE IF EXISTS " + EXAMPLE_CREATE_SQL + ";";
private SQLiteDatabase db;
private DatabaseHelper(Context context) {
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}
private static volatile DatabaseHelper instance;
public static DatabaseHelper getInstance(Context context) {
if (instance==null) {
instance = new DatabaseHelper(context);
instance.db = instance.getWritableDatabase();
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(EXAMPLE_CREATE_SQL);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public long insert(Long id, String name, byte[] data) {
ContentValues cv = new ContentValues();
if (id!=null) {
cv.put(EXAMPLE_ID_COL_NAME,id);
}
cv.put(EXAMPLE_NAME_COL_NAME,name);
cv.put(EXAMPLE_DATA_COL_NAME,data);
return db.insert(EXAMPLE_TABLE_NAME,null,cv);
}
public long insert(String name, byte[] data) {
return insert(null,name,data);
}
public Cursor getRowsFromExampleTable() {
return db.query(EXAMPLE_TABLE_NAME,null,null,null,null,null,null);
}
public Cursor getNoRowsFromAnyTable(String tableName) {
return db.query(tableName, null, "?", new String[]{"0"}, null, null, null);
}
@SuppressLint("Range") /* suppress the range check for getColumnIndex potentially being -1 (column not found in cursor) */
public boolean isCursorRetrievable(Long cursorWindowSize, String table) {
final String checkSuffix = "_check";
final String lengthSuffix = "_length";
final String totalName = "total_length";
final String rowidName = "rowid";
final String TAG = "CURSORWARN";
long rowcount;
long warncount=0;
long leeway = 100; /* leeway for extras (just a guess aka not aware of internals of CursorWindow) */
Cursor getColumns = getNoRowsFromAnyTable(table); /* Get no data (WHERE 0 (false) so never get any rows) just to get columns */
/* get the rowid as the first column to extract
NOTE limits this to tables that DO NOT have WITHOUT ROWID
NOTE for some reason need to override the alias (_id column in example) by aliasing (AS) to rowid
hence the AS clause naming rowid to rowid
*/
StringBuilder columnsClause = new StringBuilder().append(rowidName).append(" AS " + rowidName);
/* as the totalCluase is going to be added after the columnsClause and the column separating comma*/
StringBuilder totalClause = new StringBuilder().append(",");
/* get ready to store the generated column names
2 columns per column in the table
a column with true/false (true if length is not going to be an issue, false if so)
a column with the actual length of the stored data
*/
String[] checkColumns = new String[getColumns.getColumnCount()];
String[] lengthColumns = new String[getColumns.getColumnCount()];
/* For each column in the table build the two parts of the SQL */
for (int i=0; i < getColumns.getColumnCount();i++) {
/* if after the first actual column (ignoring rowid column) then
add the + to add the columns lengths (the for totals part of the SQL)
*/
if (i > 0) {
totalClause = totalClause.append(" + ");
}
checkColumns[i] = getColumns.getColumnName(i) + checkSuffix; /* generate and store the current check column name */
lengthColumns[i] = getColumns.getColumnName(i) + lengthSuffix; /* generate and store the current length column name */
columnsClause.append(",length(").append(getColumns.getColumnName(i)).append(") < ").append(cursorWindowSize); /* the check */
columnsClause.append(" AS ").append(getColumns.getColumnName(i)).append(checkSuffix); /* alias it */
columnsClause.append(",length(").append(getColumns.getColumnName(i)).append(")"); /* the length */
columnsClause.append(" AS ").append(getColumns.getColumnName(i)).append(lengthSuffix); /* alias it */
totalClause.append("length(").append(getColumns.getColumnName(i)).append(")"); /* the length of the data for the total */
}
totalClause.append(" AS " + totalName); /* alias the total column */
getColumns.close(); /* Done with the original columns so close the Cursor */
/* build the sql and output it to the log (help to debug if necessary) */
String generatedSQL = "SELECT " + columnsClause + totalClause + " FROM " + table;
Log.d(TAG, "Generated SQL is:-\n\t" + generatedSQL);
/* get the data (results of the check) */
Cursor result = db.rawQuery(generatedSQL,null);
rowcount = result.getCount(); /* get the number of rows extracted (ALL rows) */
/* traverse the cursor */
while (result.moveToNext()) {
/* only interested is rows that exceed the specified cursorWindowSize */
if (result.getLong(result.getColumnIndex(totalName))>= (cursorWindowSize - leeway)) {
warncount++; /* increment the number of warnings */
StringBuilder columnInfo = new StringBuilder();
/* prepare the individual column length and check */
for (int i=0; i < checkColumns.length; i++) {
columnInfo.append("\n\t\t").append(lengthColumns[i].replace(lengthSuffix,""))
.append(" Length=").append(result.getString(result.getColumnIndex(lengthColumns[i])))
.append(" OK=").append(result.getInt(result.getColumnIndex(checkColumns[i]))> 0)
.append(" OK Value= ").append(result.getInt(result.getColumnIndex(checkColumns[i])))
;
}
/* Write the warning to the log */
Log.d(TAG,"In table " + table +" there may be an issue with row " +
result.getLong(result.getColumnIndex(rowidName)) + " (<<<<< rowid)" +
"\n\t Total Length of Columns is " + result.getLong(result.getColumnIndex(totalName)) +
"\n\t Output consists of Columns and Lengths as per :-" +
columnInfo
);
}
}
/* all done so close the cursor*/
result.close();
/* write the summary if any warnings returning false i.e. not OK*/
if (warncount > 0) {
Log.d(TAG,"In table " + table + " " + warncount + " rows had warnings out of a total of " + rowcount + " rows (as above)");
return false;
}
/* if no warnings then return true i.e. OK/no issues */
return true;
}
public void getAllTrapped(String table) {
Cursor csr = db.query(table,null,null,null,null,null,null);
try {
while(csr.moveToNext()) {
for (int i = 0; i < csr.getColumnCount(); i++) {
switch (csr.getType(i)) {
case Cursor.FIELD_TYPE_BLOB:
csr.getBlob(i);
break;
case Cursor.FIELD_TYPE_STRING:
csr.getString(i);
break;
case Cursor.FIELD_TYPE_FLOAT:
case Cursor.FIELD_TYPE_INTEGER:
case Cursor.FIELD_TYPE_NULL:
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
csr.close();
}
}
}
Here's using the the above in some activity code:-
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = DatabaseHelper.getInstance(this);
/* populate the table with 100 rows with a blob that grows by 1000000 per row */
for (int i=0; i < (100); i++) {
dbHelper.insert("TEST_ROW" + i,populateByteArray(i * 1000000));
}
/* run the check */
dbHelper.isCursorRetrievable(2048L * 1024L,DatabaseHelper.EXAMPLE_TABLE_NAME);
/* get the CursorWindowSize (blob will not fit) error (trapped) */
dbHelper.getAllTrapped(DatabaseHelper.EXAMPLE_TABLE_NAME);
}
byte[] populateByteArray(int size) {
byte[] ba = new byte[size];
for (int i=0; i < size; i++) {
ba[i] = (byte) i;
}
return ba;
}
}
The log includes (just the first warning at the 4th row ):-
2023-01-24 21:01:20.360 D/CURSORWARN: Generated SQL is:-
SELECT rowid AS rowid,length(_id) < 2097152 AS _id_check,length(_id) AS _id_length,length(_name) < 2097152 AS _name_check,length(_name) AS _name_length,length(_data) < 2097152 AS _data_check,length(_data) AS _data_length,length(_id) + length(_name) + length(_data) AS total_length FROM example
2023-01-24 21:01:20.361 D/CURSORWARN: In table example there may be an issue with row 4 (<<<<< rowid)
Total Length of Columns is 3000010
Output consists of Columns and Lengths as per :-
_id Length=1 OK=true OK Value= 1
_name Length=9 OK=true OK Value= 1
_data Length=3000000 OK=false OK Value= 0