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!
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.
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.
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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With