Android Event:APP_SCOUT_HANG Warning SQLite

In Adroid I am using retrofit2 call to my server to obtain data.

In onResponse after checking the version in DB I am dropping and recreating tables in DB and then inserting data. Everything works fine, the data is filled correctly, but everytime I have a warning in console.

This is part of my code:

  @Override
        public void onResponse(@NonNull Call<List<Movie>> call, @NonNull Response<List<Movie>> response) {
            movieList = response.body();
            myDb = new DatabaseHelper(getApplicationContext());

            if (movieList != null) {
                if (response.isSuccessful() && movieList.size() > 0) {int liveVersion = 0;

                    liveVersion = Integer.parseInt(movieList.get(0).getVersiondb());

                    if (liveVersion>version || version==0) { myDb.clearTable();

                        for (int i = 0; i < movieList.size(); i++) {
                            myDb.insertData(movieList.get(i).getKateg(),movieList.get(i).getFullname(), movieList.get(i).getVersiondb());
                        }
                    }
                } } 
        }

   void clearTable() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, KATEG TEXT, FULLNAME TEXT, VERSION TEXT)");
        db.close();
    }

void insertData(String kateg, String fullname, String version) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2,kateg);
    contentValues.put(COL_3,fullname);
    contentValues.put(COL_4,version);

    db.insert(TABLE_NAME,null,contentValues);db.close();
}

The Warning says:

(Current message: duration=5005ms seq=39 h=android.os.Handler c=retrofit2.DefaultCallAdapterFactory$ExecutorCallbackCall$1$$ExternalSyntheticLambda0) MIUIScout App myapp.com W Event:APP_SCOUT_HANG Thread:main backtrace: at java.lang.String.charAt(Native Method)
at java.lang.String.equals(String.java:1271)
at android.database.DatabaseUtils.getSqlStatementType(DatabaseUtils.java:1574) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1050) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:654)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:62)
at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:34)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1700)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1571)
at my.app.DatabaseHelper.insertData(DatabaseHelper.java:99)
at my.app.StartActivity$1.onResponse(StartActivity.java:84)
at retrofit2.DefaultCallAdapterFactory$ExecutorCallbackCall$1.lambda$onResponse$0$retrofit2-DefaultCallAdapterFactory$ExecutorCallbackCall$1(DefaultCallAdapterFactory.java:89)
at retrofit2.DefaultCallAdapterFactory$ExecutorCallbackCall$1$$ExternalSyntheticLambda0.run(Unknown Source:6)
at android.os.Handler.handleCallback(Handler.java:938)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loopOnce(Looper.java:210)
at android.os.Looper.loop(Looper.java:299)
at android.app.ActivityThread.main(ActivityThread.java:8319)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:556) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1038)

In one line that I made bold it points to insertData method, however as it inserts everything without issue I am not sure what is wrong.

Solution

The warning appears to be due to too much of a delay on the main thread. Closing the database and then re-opening the database is relatively resource hungry.

Additionally doing many single transactions, as each insert is doing, will itself be inefficient.

Not only should you not close and re-open the database but you should perhaps consider doing all the inserts in the loop within a single transaction.

perhaps also consider that AUTOINCREMENT is probably not required as the id column will still be generated. AUTOINCREMENT adds yet another factor that increases the resources used. See https://www.sqlite.org/autoinc.html (first paragraph warns about this aspect).

Perhaps consider the following (that is loosely based upon your code)

To facilitate not getting instances of the database two version of the insertData method:-

void insertData(SQLiteDatabase db, String kateg, String fullname, String version) {
    if (db == null) db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL_2, kateg);
    contentValues.put(COL_3, fullname);
    contentValues.put(COL_4, version);
    db.insert(TABLE_NAME, null, contentValues);
}

void insertData(String kateg, String fullname, String version) {
    insertData(null, kateg, fullname, version);
}

note that the latter, doesn't need the current/in use SQliteDatabase to be passed as it then invokes the core/first insertData method with the SQLiteDatabase as null (so then gets an SQLiteDatabase).

The clearTable method is not used as it is likely more efficient to delete the rows (which dropping the table has to do anyway). Rather the delete is embedded within the suggested insertAfterClearIfApplicable method (obviously the method name can be changed to suit)

Note to suit brevity of the answer and at least syntax checking a list of Movies rather than responses is passed. Obviously you may wish to amend accordingly. The real intent of the answer is to demonstrate what are likely more efficient means i.e.

deleting all rows rather than dropping the table
embodying all the database actions within a single transaction

The code:-

void insertAfterClearIfApplicable(List<Movie> movieList, int liveVersion) {
    if (movieList == null || movieList.size() < 1) return; /* nothing to do so return */
    SQLiteDatabase db = this.getWritableDatabase(); /* get the SQLiteDatabase */
    db.beginTransaction(); /* Start a transaction */
    boolean allDoneOk = true; /*<<<<<<<<<< set to false if the transaction should be rolled back */
    if (movieList.size() > 0) {
        /* Handle the first element i.e. whether or not to delete all rows from the table */
        int version = Integer.parseInt(movieList.get(0).getVersion());
        if (liveVersion > version || version == 0) {
            /* might as well use delete instead of drop as drop has to delete all rows anyway
             *  and saves a little by not having to then create the table again */
            db.delete(TABLE_NAME, null, null);
        }
        /* Insert all of the rows to be added */
        for (Movie m : movieList) {
            insertData(db, m.getKateg(), m.getFullName(), m.getVersion());
        }
        /* unless otherwise flagged set the transaction to NOT rollback */
        /* note if not set as successful then ALL database actions will be rolled back */
        if (allDoneOk) db.setTransactionSuccessful();
     /* end the transaction will commit/write all the actions to the database (if set as susccessful) in a single go
        thus reducing the overheads */
        db.endTransaction();
    }
}

again please note the above is an approximation and utilises a List<Movie> rather than the response body and as such should be adapted/altered accordingly.
- i.e. then answer is intended to demonstrate the technique rather than the actual use.

Demonstration

Again not an exact attempt at replicating your code but an approximation MAINLY to highlight the efficiencies of the suggested single transaction v many transactions.

First the DatabaseHelper class (above but with logging added for timings):-

class DatabaseHelper extends SQLiteOpenHelper {
    public static final String TABLE_NAME = "thetable";
    public static final String COL_2 = "KATEG";
    public static final String COL_3 = "FULLNAME";
    public static final String COL_4 = "VERSION";

    public DatabaseHelper(Context context) {
        super(context, "the_database.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, KATEG TEXT, FULLNAME TEXT, VERSION TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    void clearTable() {
        Log.d(MainActivity.TAG,"Starting clearTable Method (DROP THEN CREATE)");
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        db.execSQL("CREATE TABLE " + TABLE_NAME + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, KATEG TEXT, FULLNAME TEXT, VERSION TEXT)");
        /* db.close(); */
        Log.d(MainActivity.TAG,"Ending clearTable Method");
    }

    void insertData(SQLiteDatabase db, String kateg, String fullname, String version) {
        if (db == null) db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL_2, kateg);
        contentValues.put(COL_3, fullname);
        contentValues.put(COL_4, version);
        db.insert(TABLE_NAME, null, contentValues);
    }

    void insertData(String kateg, String fullname, String version) {
        insertData(null, kateg, fullname, version);
    }

    void insertAfterClearIfApplicable(List<Movie> movieList, int liveVersion) {
        Log.d(MainActivity.TAG,"Starting CORE INSERTAFTERCLEARIFAPPLICABLE Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
        if (movieList == null || movieList.size() < 1) return; /* nothing to do so return */
        SQLiteDatabase db = this.getWritableDatabase(); /* get the SQLiteDatabase */
        db.beginTransaction(); /* Start a transaction */
        boolean allDoneOk = true; /*<<<<<<<<<< set to false if the transaction should be rolled back */
        if (movieList.size() > 0) {
            /* Handle the first element i.e. whether or not to delete all rows from the table */
            int version = Integer.parseInt(movieList.get(0).getVersion());
            if (liveVersion > version || version == 0) {
                Log.d(MainActivity.TAG,"Start deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
                /* might as well use delete instead of drop as drop has to delete all rows anyway
                 *  and saves a little by not having to then create the table again */
                db.delete(TABLE_NAME, null, null);
                Log.d(MainActivity.TAG,"End deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            }
            /* Insert all of the rows to be added */
            Log.d(MainActivity.TAG,"Start INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            for (Movie m : movieList) {
                insertData(db, m.getKateg(), m.getFullName(), m.getVersion());
            }
            Log.d(MainActivity.TAG,"End INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
            /* unless otherwise flagged set the transaction to NOT rollback */
            /* note if not set as successful then ALL database actions will be rolled back */
            if (allDoneOk) db.setTransactionSuccessful();
         /* end the transaction will commit/write all the actions to the database (if set as susccessful) in a single go
            thus reducing the overheads */
            db.endTransaction();
            Log.d(MainActivity.TAG,"Ending CORE INSERTAFTERCLEARIFAPPLICABLE DATA Method (transaction ended)"); /*<<<<<<<<<<!!!!!!!!!>>>>>>>>>>*/
        }
    }
}

Second testing code within MainActivity:-

public class MainActivity extends AppCompatActivity {
    public static final String TAG = "DBINFO";
    DatabaseHelper myDb;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myDb = new DatabaseHelper(this);
        myDb.getReadableDatabase(); /* Force open and thus onCreate so negate associated overheads */
        Response t1 = buildTestResponse(1,1000,0);
        tryItOut(1,t1,false);
        tryItOut(2,t1,true);
    }
    /*
        if (movieList != null) {
                if (response.isSuccessful() && movieList.size() > 0) {int liveVersion = 0;

                    liveVersion = Integer.parseInt(movieList.get(0).getVersiondb());

                    if (liveVersion>version || version==0) { myDb.clearTable();

                        for (int i = 0; i < movieList.size(); i++) {
                            myDb.insertData(movieList.get(i).getKateg(),movieList.get(i).getFullname(), movieList.get(i).getVersiondb());
                        }
                    }
                } }
     */

    /* Build some testing data */
     Response buildTestResponse(int liveVersion, int moviesToGenerate, int passedMovieVersion) {
         ArrayList<Movie> m = new ArrayList<>();
         int movieVersion=0;
         for (int i=0; i < moviesToGenerate;i++) {
             if (passedMovieVersion < 0) {
                 movieVersion = new Random().nextInt( liveVersion * movieVersion);
             }
             m.add(new Movie(String.valueOf(movieVersion),"KATEG" + i,"FNAME" + i));
         }
         return new Response(liveVersion,m);
     }
     /* TESTING approximation with logging for timings */
     void tryItOut(int testNumber, Response response, boolean newWay) {
         Log.d(TAG,"Starting TEST " + String.valueOf(testNumber));
         if (newWay) {
             myDb.insertAfterClearIfApplicable(response.getBody(),response.getLiveVersion());
         } else {
             if (response.getLiveVersion() > Integer.parseInt(response.getBody().get(0).getVersion())) {
                 myDb.clearTable();
                 Log.d(TAG,"Starting TEST INSERT LOOP (OLDWAY) " + String.valueOf(testNumber));
                 for (Movie m: response.getBody()) {
                     myDb.insertData(m.getKateg(),m.getFullName(),m.getVersion());
                 }
                 Log.d(TAG,"Ending TEST INSERT LOOP (OLDWAY) " + String.valueOf(testNumber));
             }
         }
         Log.d(TAG,"END of TEST " + String.valueOf(testNumber));
     }
}

Note Response and Movie classes are approximations created to demonstrate (for brevity and ease).

Results

The intention of the demonstration is to provide and example of the resource usage difference which affects the time difference between the suggested single transaction way and the way in the original question. The Log from the run (which uses both the old and the new ways, in that order) is:-

2024-01-22 11:30:59.323 D/DBINFO: Starting TEST 1
2024-01-22 11:30:59.323 D/DBINFO: Starting clearTable Method (DROP THEN CREATE)
2024-01-22 11:30:59.325 D/DBINFO: Ending clearTable Method
2024-01-22 11:30:59.326 D/DBINFO: Starting TEST INSERT LOOP (OLDWAY) 1
2024-01-22 11:30:59.748 D/DBINFO: Ending TEST INSERT LOOP (OLDWAY) 1
2024-01-22 11:30:59.748 D/DBINFO: END of TEST 1
2024-01-22 11:30:59.748 D/DBINFO: Starting TEST 2
2024-01-22 11:30:59.748 D/DBINFO: Starting CORE INSERTAFTERCLEARIFAPPLICABLE Method
2024-01-22 11:30:59.748 D/DBINFO: Start deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method
2024-01-22 11:30:59.748 D/DBINFO: End deleting ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE DATA Method
2024-01-22 11:30:59.748 D/DBINFO: Start INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)
2024-01-22 11:30:59.897 D/DBINFO: End INSERT ALL ROWS WITHIN INSERTAFTERCLEARIFAPPLICABLE Method (in Transaction)
2024-01-22 11:30:59.897 D/DBINFO: Ending CORE INSERTAFTERCLEARIFAPPLICABLE DATA Method (transaction ended)
2024-01-22 11:30:59.897 D/DBINFO: END of TEST 2

Clearing the table via DROP then CREATE (old way) takes 2 milliseconds. the new way (DELETE all rows) is done within 1 millisecond (2nd v 3rd line).
Inserting all 1000 rows the old way (each insert a single transaction) takes 422 milliseconds. The new/suggested way (all inserts in a single transaction) takes 149 milliseconds.

EVEN STILL using the main thread for database access is frowned upon, You may wish to consider undertaking the database access via another thread.