Drop/recreate index leads to Index already exists error

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Drop/recreate index leads to Index already exists error

Jan Slodicka
This post has NOT been accepted by the mailing list yet.
Hello

We have SQLite 3.8.8.1 and use it from .Net/c# environment on various platforms.

This is the logic that we use for bulk inserts:
1. BEGIN IMMEDIATE
2. Drop table indexes
3. Execute table inserts
4. Recreate indexes
5. COMMIT

Should any error happen, the sequence is interrupted and an explicit ROLLBACK is executed. Of course, application can be killed in the middle of any operation, but this was not the case.

In the case I am referring to everything succeeded up to the point 4, where SQLite failed with "index XXX already exists" error, whereby XXX referred to the first table index. (The table contact has several indexes.)

Here is the code used to drop all table indexes. (Table contact in this case.)

string cmd = "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name=='contact' AND sql NOTNULL";
string cmd1 = "";
using (var r = db.ExecuteReader(cmd)) {
        while (r.Read()) {
                string name = r.GetString(0);
                if (name.IndexOf("autoindex") == -1)
                        cmd1 += "DROP INDEX [" + name + "];";
        }
}
db.ExecuteNonQuery(cmd1);

Point 4 (recreate indexes) is never executed unless the above code was executed.

What else can I say:
- The code is run frequently by a large number of users on iOS/Android/WP8/Windows/WinRT.

- The observed error happens rarely. While I don't have any stats, it might be once in (at least) 100,000 trials.

- If the error happens, we usually get a user complaint. It looks like all the complaints concern only WinRT/iOS platforms.

- By chance I got a more detailed log from the last error. It looks like our application was first forcefully killed while executing an unrelated code. (I cannot exclude that it was a DB operation, but it certainly did not concern the contact table. Killing means that the DB connection was not properly closed.) Then the app was restarted, DB operations (reads and writes) worked flawlessly, but after some time the described index error happened.

I am going to "fix" the problem by replacing all CREATE INDEX by CREATE INDEX IF NOT EXISTS. While this might cure the symptoms, it might just mask a deeper error.

Do you have any idea what might be the problem?