Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Duplicate column name on SQLite upgrade for a new column

I've been getting an inconsistent, non-reproducible crash upon users upgrading to a new version of the local SQLite DB when updating the app.

Fatal Exception: android.database.sqlite.SQLiteException: duplicate column name: upload_pending (code 1): , while compiling: ALTER TABLE purchases ADD COLUMN upload_pending TINYINT DEFAULT 0
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (duplicate column name: upload_pending (code 1): , while compiling: ALTER TABLE purchases ADD COLUMN upload_pending TINYINT DEFAULT 0)
#################################################################

The column is new to this version of the app, which tells me the most likely bug is that SQLiteOpenHelper's onUpgrade method is being called twice. Here is the logic for how upgrade is handled:

@Override   
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    for(int currentUpgrade = oldVersion + 1; currentUpgrade <= newVersion; currentUpgrade++) {
        switch(currentUpgrade) {
            case 2: 
                //upgrade to db v2
                break;
            case 3:
                //upgrade to db v3
                break;
            //etc
            case 7:
                methodWhichUpdatesAnotherTable(db);
                db.execSQL("ALTER TABLE " + Purchases.TABLE_NAME
                            + " ADD COLUMN " + Purchases.UPLOAD_PENDING + " TINYINT DEFAULT 0");
                break;
        }
    }
}

EDIT: I've updated the code to include something important. The failing line is NOT the first ALTER statement in the upgrade. First, a method is called which makes two different alter statements (on a different table), and this portion works fine. This seems to eliminate the possibility that it's a concurrency issue, because if so these would be the first to fail.

Looking at this, the only way I can see this happening is if Android is calling onUpgrade twice, and not adjusting the oldVersion and newVersion params, resulting in case 7 being called twice. Conversely, it could be the case where onCreate is called, and onUpgrade is called afterwards, again with the database versions provided to the method not being correct.

As I mentioned at the start, I'm unable to reproduce this error, and it's only happening to <1% of users, but it is noticeable enough for me to want to solve it. If anyone has a guess I'd appreciate it, and if you need more info feel free to ask. Thanks!

like image 640
JMRboosties Avatar asked Apr 11 '17 21:04

JMRboosties


People also ask

What does duplicate column name mean in SQL?

September 30, 2019. This typically happens when you retrieve data from multiple tables with the same column name using a JOIN statement. You might receive an error like this: ERROR: Column 'col_name' in field list is ambiguous.

What is the command to add or delete columns in SQLite?

The syntax to ADD A COLUMN in a table in SQLite (using the ALTER TABLE statement) is: ALTER TABLE table_name ADD new_column_name column_definition; table_name.

What is duplicate column?

Excel for Microsoft 365 Excel for the web. Use the Duplicate command to copy a column. If you want to make significant changes to a column, it's a good idea to duplicate or copy the original column, and make your changes to the new column, so you don't inadvertently cause refresh errors down the road.

How to rename a column in SQLite?

If you’re using the SQLite with the version lower than 3.25.0 and could not upgrade, then you should follow these steps to rename a column: First, start a transaction. Second, create a new table whose structure is the same as the original one except for the column that you want to rename. Third, copy data from the original table to the new table.

How to insert on duplicate key update in SQLite?

With SQLite you cannot do the simple MySQL INSERT on duplicate key UPDATE: Instead, you have to do what is called an upsert. The concept is very similar to the MySQL example above.

How do I rename a table in SQL Server?

1 First, start a transaction. 2 Second, create a new table whose structure is the same as the original one except for the column that you want to rename. 3 Third, copy data from the original table to the new table. 4 Fourth, drop the original table. 5 Fifth, rename the new table to the original table. 6 Finally, commit the transaction.

What is the use of outline in SQLite?

SQLite is distinctive in that it stores the outline in the sqlite_schema table as the first content of the CREATE statement that characterizes the mapping. Subsequently, ALTER TABLE requirements to overhaul the content of the CREATE statement.


1 Answers

Had the same issue. Turned out, that I was creating the table for oldVersions before 4 and adding column for versions before 10. Because of that, both statements, CREATE TABLE and ALTER TABLE ADD COLUMN were executed for oldVersions pre 4:

if ( oldVersion < 4 && newVersion >= 4 ) { 
    TestTableDao.createTable( db, true );
}

if ( oldVersion < 10 && newVersion >= 10 ) { 
   db.execSQL( "ALTER TABLE TEXT_TABLE ADD COLUMN NEW_COLUMN TEXT" );
}

The fix was to check oldVersion for being after creation of table:

 if ( oldVersion < 10 && newVersion >= 10 ) { 
   if( oldVersion >=4 )
       db.execSQL( "ALTER TABLE TEXT_TABLE ADD COLUMN NEW_COLUMN TEXT" );
}

Hope it helps to someone.

like image 147
Michail Lukow Avatar answered Oct 17 '22 18:10

Michail Lukow