在SQLite升级中，新列的列名重复 [英] Duplicate column name on SQLite upgrade for a new column

问题描述

在更新应用程序时，用户升级到本地SQLite DB的新版本时，出现了不一致，不可重现的崩溃.

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)
#################################################################

此列是此应用程序版本的新增内容，它告诉我最可能的错误是SQLiteOpenHelper的onUpgrade方法被调用了两次.这是处理升级的逻辑:

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;
        }
    }
}

我已经更新了代码以包含一些重要的内容.失败的行不是升级中的第一个ALTER语句.首先，调用一个使两个不同的alter语句(在不同的表上)的方法，该部分可以正常工作.这似乎消除了并发问题的可能性，因为如果是这样的话，这些将是第一个失败的问题.

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.

看这件事，我能看到这种情况的唯一方法是，如果Android两次调用onUpgrade，而不调整oldVersion和newVersion参数，导致案例7被两次调用.相反，可能是在调用onCreate并随后调用onUpgrade的情况下，再次提供给该方法的数据库版本不正确.

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.

正如我在开始时提到的那样，我无法重现此错误，并且仅在<1％的用户中发生，但是对于我来说，解决它已经足够明显了.如果有人有猜测，我将不胜感激，如果您需要更多信息，请随时提问.谢谢！

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!

推荐答案

出现了同样的问题.原来，我正在为4之前的oldVersions创建表，并为10之前的版本添加列.因此，对4之前的oldVersions执行了CREATE TABLE和ALTER TABLE ADD COLUMN这两个语句:

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" );
}

解决方法是在创建表之后检查oldVersion是否存在:

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" );
}

希望对某人有帮助.

这篇关于在SQLite升级中，新列的列名重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！