SQL errors after update

strb commented 8 years ago

Hi, just updated from 0.5.3 (IIRC) to 0.7.0 via plugin manager. I restarted gajim afterwards, and I've also tried disabling+reenabling the plugin.

I can't open the plugin configuration. I also can't receive messages anymore. There may be other stuff that doesn't work properly, too. I haven't tested exhaustively. The root cause appears to be a bug with the database migration, as I repeatedly get the following stack trace in the console:

Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/gajim/plugins/helpers.py", line 114, in wrapper
    result = f(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/gajim/plugins/gui.py", line 235, in on_configure_plugin_button_clicked
    result = plugin.config_dialog.run(self.window)
  File "/usr/lib64/python2.7/site-packages/gajim/plugins/helpers.py", line 114, in wrapper
    result = f(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/gajim/plugins/gui.py", line 335, in run
    self.on_run()
  File "/home/andy/.local/share/gajim/plugins/omemo/ui.py", line 198, in on_run
    self.update_context_list()
  File "/home/andy/.local/share/gajim/plugins/omemo/ui.py", line 301, in update_context_list
    fprDB = state.store.identityKeyStore.getAllFingerprints()
  File "/home/andy/.local/share/gajim/plugins/omemo/omemo/liteidentitykeystore.py", line 103, in getAllFingerprints
    for row in c.execute(q):
sqlite3.OperationalError: no such column: trust

I get similar traces from various different callbacks, all terminating at the same error.

If you need anything else, let me know.

lovetox commented 8 years ago

hm in the updates since 0.5.3 i added no column trust, so it looks like the DB was corrupted even before, but we started to use the column now

maybe we can fix this without setting you back to start.

could you open the sqlite db, and look if in the identities Table the column trust is missing? if it is, just add it. should solve the problem.

you could look if there is anything else missing, this is the creation code:

"CREATE TABLE IF NOT EXISTS identities (" +
            "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "recipient_id TEXT," +
            "registration_id INTEGER, public_key BLOB, private_key BLOB," +
            "next_prekey_id NUMBER, timestamp NUMBER, trust NUMBER);")

lovetox commented 8 years ago

i think i have an idea why the column never got created, because NUMBER is not a valid fieldtyp. older sqlite version would create the column anyway, newer maybe not.

could you find out what sqlite version you have? i guess you are on linux

strb commented 8 years ago

Thanks for responding so quickly!

Okay, so the schema of ~/.local/share/gajim/omemo_ACCOUNT.db has:

CREATE TABLE identities (_id INTEGER PRIMARY KEY AUTOINCREMENT,recipient_id TEXT,registration_id INTEGER, public_key BLOB, private_key BLOB,next_prekey_id INTEGER, timestamp INTEGER, UNIQUE(recipient_id, registration_id));

So the column is definitely missing. I have been using this plugin for quite a while, so maybe it was added at some point further back? What's odd to me is that I have the timestamp column as an INTEGER, where it's created as NUMBER in the code.

Either way, I'm on a gentoo system, so sqlite is "self-compiled". The version is

3.12.0 2016-03-29 10:14:15 e9bb4cf40f4971974a74468ef922bdee481c988b

What field type would be correct? Not having looked at the code, I'd assume INTEGER should work, right? So I could just ALTER the table to fix this on my system?

lovetox commented 8 years ago

@strb indeed this is very strange, i scanned the history of github, to my knowledge there was never added the trust column in a commit, it was always there from the inital plugin commit.

SQLite doesnt really have Datatypes like known from other DBs, you could name the Datatyp whatever you want, normally sqlite should add the column no matter what. sqlite determines a datatype from the content itself. so we give only a hint, but if this doesnt match sqlite does not care.

just add a trust column with datatype INTEGER. this should solve everything.

we started using that column after 5.3, so if it was missing before it didnt matter, thats why this error happens now

strb commented 8 years ago

Yup, ALTER TABLE identities ADD COLUMN trust INTEGER; did the trick. Since we can't really find out how I got to this place, I guess I'll close here.

omemo / gajim-omemo

SQL errors after update #85