OperationalError: table cache has 2 columns but 3 values were supplied

[r.ataman@…]

After upgrading 0.12 to 1.0.1 (which worked like a charm), we can not change permissions via administration without this error message. The same happens if we use "trac-admin <path> permission add <username>", "trac-admin <path> permission list <username>" works fine.

How to Reproduce

While doing a POST operation on /admin/general/perm, Trac issued an internal error.

(please provide additional details here)

Request parameters:

{'__FORM_TOKEN': u'aa9c096b6d039bee13a8719c',
 'cat_id': u'general',
 'panel_id': u'perm',
 'path_info': None,
 'remove': u'Ausgew\xe4hlte Eintr\xe4ge entfernen',
 'sel': u'cm5hdGFtYW4=:VFJBQ19BRE1JTg=='}

User agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22

System Information

Trac	1.0.1
Babel	0.9.5
Genshi	0.6 (without speedups)
mod_python	3.3.1
Pygments	1.3.1
pysqlite	2.4.1
Python	2.6.2 (r262:71600, Oct 24 2009, 03:15:21) [GCC 4.4.1 [gcc-4_4-branch revision 150839]]
pytz	2010h
RPC	1.1.0-r7971
setuptools	0.6c12
SQLite	3.6.16
jQuery	1.7.2

Enabled Plugins

AdvancedTicketWorkflowPlugin	0.10dev-r7701
IniAdmin	0.2
TracAccountManager	0.2.1dev-r7737
TracHTTPAuth	1.1
TracMasterTickets	3.0.1
TracXMLRPC	1.1.0-r7971

Python Traceback

Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-x86_64/egg/trac/admin/web_ui.py", line 125, in process_request
    path_info)
  File "build/bdist.linux-x86_64/egg/trac/admin/web_ui.py", line 421, in render_admin_panel
    perm.revoke_permission(subject, action)
  File "build/bdist.linux-x86_64/egg/trac/perm.py", line 349, in revoke_permission
    self.store.revoke_permission(username, action)
  File "build/bdist.linux-x86_64/egg/trac/perm.py", line 249, in revoke_permission
    del self._all_permissions
  File "build/bdist.linux-x86_64/egg/trac/cache.py", line 78, in __delete__
    CacheManager(instance.env).invalidate(id)
  File "build/bdist.linux-x86_64/egg/trac/cache.py", line 266, in invalidate
    (id, 0, _id_to_key.get(id, '<unknown>')))
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 121, in execute
    cursor.execute(query, params)
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: table cache has 2 columns but 3 values were supplied

[r.ataman@…]

Additional information about the table cache:

sqlite> .schema cache
CREATE TABLE cache (
    id text PRIMARY KEY,
    generation integer
);

sqlite> select * from cache;
trac.wiki.api.WikiSystem.pages|66
trac.ticket.api.TicketSystem.fields|297

[r.ataman@…]

May be related to #10803.

This statement fixed it for me:

sqlite> DROP TABLE cache; 
sqlite> CREATE TABLE cache ( 
id text PRIMARY KEY, 
generation integer 
); 

Seems that db27.py was not executed.

[r.ataman@…]

Pasted the wrong create statement, that's the right one:

CREATE TABLE cache (id integer primary key, generation integer, key text);

[Christian Boos]

Well, yes, it seems the upgrade did miss a step, as you've figured out the fix you had to do manually corresponds to db27.py​.

Unfortunately I have no idea how this could have happened.

If someone's able to reproduce this upgrade failure condition, please reopen and detail the reproduction steps (or at least the error message and the trac.log corresponding to the failed upgrade).