We have been working on an android app which uses cocos2dx. We use sqlite3 for storing data locally on the device. We use c++ code to connect and use the database. Recently we used transactions, it runs fine on iOS platform (cocos2dx apps are cross platform) but it fails on android.
This is weird because only update queries in the transaction fail and that too if you run multiple updates in a loop. Every other query (insert, delete, select) runs fine in loop. We are not using multiple threads, so whatever runs, runs on the main thread neither we are opening multiple connections to the database. The same code works if you turn off the transaction. We don't want to give up transactions. One solution is to use "insert or replace" query instead of update which is insert or insert-delete but I am not sure what will happen if the table is having foreign key relationship. We can drop the foreign key constraint if we assume the data is proper.
For each query we prepare a statement, step through it and then finalize it so every statement is finalized even if the query fails. For pragma setting and begin and end transaction we are using sqlite_exec which wraps the above process.
Here is a test log:-
09-03 19:36:35.175: D/cocos2d-x debug info(30445): PRAGMA foreign_keys=ON 09-03 19:36:35.175: D/cocos2d-x debug info(30445): begin transaction 09-03 19:36:35.180: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.185: D/cocos2d-x debug info(30445): enter loop 09-03 19:36:35.185: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 2 where Test_ID = 133 09-03 19:36:35.185: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.185: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.185: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.185: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.185: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.190: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.190: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 2 where Test_ID = 135 09-03 19:36:35.195: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 2 where Test_ID = 135 09-03 19:36:35.195: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.195: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.195: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.195: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.195: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.195: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 1 where Test_ID = 140 09-03 19:36:35.195: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 1 where Test_ID = 140 09-03 19:36:35.200: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.200: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.200: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.200: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.200: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.200: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 1 where Test_ID = 141 09-03 19:36:35.200: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 1 where Test_ID = 141 09-03 19:36:35.205: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.205: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.205: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.205: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.205: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.205: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 1 where Test_ID = 142 09-03 19:36:35.205: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 1 where Test_ID = 142 09-03 19:36:35.215: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.215: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.215: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.215: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.215: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.215: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 0 where Test_ID = 144 09-03 19:36:35.215: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 0 where Test_ID = 144 09-03 19:36:35.220: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.220: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.220: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.220: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.220: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.220: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 0 where Test_ID = 146 09-03 19:36:35.220: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 0 where Test_ID = 146 09-03 19:36:35.225: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.225: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.225: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.225: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.225: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.225: D/cocos2d-x debug info(30445): update TestTable set ID_Type = 1 where Test_ID = 158 09-03 19:36:35.230: D/cocos2d-x debug info(30445): Error 14 update TestTable set ID_Type = 1 where Test_ID = 158 09-03 19:36:35.230: D/cocos2d-x debug info(30445): insert into TestTable(Test_ID) values(1) 09-03 19:36:35.230: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.230: D/cocos2d-x debug info(30445): delete from TestTable where Test_ID = 1 09-03 19:36:35.230: D/cocos2d-x debug info(30445): Executed 09-03 19:36:35.230: D/cocos2d-x debug info(30445): select Test_ID, ID_Type from TestTable 09-03 19:36:35.235: D/cocos2d-x debug info(30445): exit loop 09-03 19:36:35.235: D/cocos2d-x debug info(30445): rollback
The above log indicates where it enters the loop and where it exits the loop. Inside loop we perform update, insert, delete and select in the same order. You can create any table and check this. I have no idea what is going wrong. We have checked following things:-
- No multiple thread accessing.
- No multiple connections at the same time.
- The database and database_journal (temporary database for transaction) has read write permission otherwise even insert and delete will fail.
- We tried begin immediate transaction
- We tried switching off the Synchronization pragma
- We tested on different android devices (samsung and micromax)
If you drop the transaction the same code works fine.
Edit
You can try this on android, update query fails. If you just remove the transaction, it will work fine. You can try insert, delete or select all these work fine. Typically update execute maximum once and then sqlite error 14 "unable to open database" comes.
Code Here:
sqlite3 *pSqliteConn = NULL;
if (sqlite3_open(PATH_OF_DB.c_str(), &pSqliteConn) == SQLITE_OK)
{
bool success = true;
char *pErrorMsg = NULL;
int code = sqlite3_exec(pSqliteConn, "begin transaction", NULL, NULL, &pErrorMsg);
if (pErrorMsg)
{
success = false;
CCLog("Error %d %s", code, pErrorMsg);
sqlite3_free(pErrorMsg);
}
else
{
CCLog("begin transaction");
}
for (int i = 0; i < 5; i++)
{
pErrorMsg = NULL;
code = sqlite3_exec(pSqliteConn, "update Test_Table set ID_Type = 2 where Test_ID = 144", NULL, NULL, &pErrorMsg);
if (pErrorMsg)
{
success = false;
CCLog("Error %d %s", code, pErrorMsg);
sqlite3_free(pErrorMsg);
}
else
{
CCLog("Executed");
}
}
if (success)
{
pErrorMsg = NULL;
code = sqlite3_exec(pSqliteConn, "commit", NULL, NULL, &pErrorMsg);
if (pErrorMsg)
{
CCLog("Error %d %s", code, pErrorMsg);
sqlite3_free(pErrorMsg);
}
else
{
CCLog("commit");
}
}
else
{
pErrorMsg = NULL;
code = sqlite3_exec(pSqliteConn, "rollback", NULL, NULL, &pErrorMsg);
if (pErrorMsg)
{
CCLog("Error %d %s", code, pErrorMsg);
sqlite3_free(pErrorMsg);
}
else
{
CCLog("rollback");
}
}
sqlite3_close(pSqliteConn);
}
else
{
CCLog("connection failed");
}
Log of above code in Android:
09-09 17:25:58.384: D/cocos2d-x debug info(12087): begin transaction
09-09 17:25:58.387: D/cocos2d-x debug info(12087): Executed
09-09 17:25:58.387: D/cocos2d-x debug info(12087): Error 14 unable to open database file
09-09 17:25:58.387: D/cocos2d-x debug info(12087): Error 14 unable to open database file
09-09 17:25:58.388: D/cocos2d-x debug info(12087): Error 14 unable to open database file
09-09 17:25:58.388: D/cocos2d-x debug info(12087): Error 14 unable to open database file
09-09 17:25:58.389: D/cocos2d-x debug info(12087): rollback
