ProgrammingError: operator does not exist: text = integer

[Moritz Bunkus]

I've checked the other tickets here with similar subjects; none of them applies.

AccountManagerPlugin 0.5dev from trunk, revision 12516; Trac 1.0.1; PostgreSQL 9.1

Happens when you log in. In my special case I've registered a new user account, logged in for the first time. Error occurred.

Error message from log file:

2013-01-08 18:49:21,821 Trac[main] ERROR: Exception caught while post-processing request:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 243, in dispatch
    self._post_process_request(req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 340, in _post_process_request
    f.post_process_request(req, *(None,)*extra_arg_count)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 123, in post_process_request
    if req.authname and req.authname != 'anonymous':
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 135, in authenticate
    authname = authenticator.authenticate(req)
  File "build/bdist.linux-i686/egg/acct_mgr/util.py", line 83, in wrap
    return func(self, *args, **kwds)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 334, in authenticate
    user = self._remote_user(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 680, in _remote_user
    if acctmgr.check_password(user, password) == True:
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 273, in check_password
    valid = store.check_password(user, password)
  File "build/bdist.linux-i686/egg/acct_mgr/db.py", line 106, in check_password
    """, (self.key, user))
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
InternalError: current transaction is aborted, commands ignored until end of transaction block
InternalError: current transaction is aborted, commands ignored until end of transaction block

2013-01-08 18:49:21,876 Trac[main] ERROR: Internal Server Error:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 179, in dispatch
    chosen_handler)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 325, in _pre_process_request
    chosen_handler = filter_.pre_process_request(req, chosen_handler)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 420, in pre_process_request
    if not req.session.authenticated or \
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 268, in _get_session
    return Session(self.env, req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/session.py", line 200, in __init__
    if req.authname == 'anonymous':
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/api.py", line 304, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/web/main.py", line 135, in authenticate
    authname = authenticator.authenticate(req)
  File "build/bdist.linux-i686/egg/acct_mgr/util.py", line 83, in wrap
    return func(self, *args, **kwds)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 334, in authenticate
    user = self._remote_user(req)
  File "build/bdist.linux-i686/egg/acct_mgr/web_ui.py", line 680, in _remote_user
    if acctmgr.check_password(user, password) == True:
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 277, in check_password
    self._maybe_update_hash(user, password)
  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 381, in _maybe_update_hash
    if not get_user_attribute(self.env, 1, user, 'password_refreshed', 1):
  File "build/bdist.linux-i686/egg/acct_mgr/model.py", line 125, in get_user_attribute
    cursor.execute(sql, sql_args)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: text = integer
LINE 4:         WHERE sid=1 AND authenticated='testfufu' AND name='p...

Relevant configuration:

[account-manager]
db_htpasswd_hash_type = sha
force_passwd_change = true
hash_method = HtPasswdHashMethod
htpasswd_file = /opt/trac/kivitendo/htpasswd
htpasswd_hash_type = sha
login_attempt_max_count = 0
notify_actions =
password_store = SessionStore
persistent_sessions = False
refresh_passwd = true
register_check = BasicCheck, EmailCheck, UsernamePermCheck, RegExpCheck
verify_email = true

[components]
acct_mgr.admin.accountguardadminpage = enabled
acct_mgr.admin.accountmanageradminpage = enabled
acct_mgr.api.accountmanager = enabled
acct_mgr.db.sessionstore = enabled
acct_mgr.guard.accountguard = enabled
acct_mgr.htfile.abstractpasswordfilestore = disabled
acct_mgr.htfile.htdigeststore = disabled
acct_mgr.htfile.htpasswdstore = disabled
acct_mgr.http.httpauthstore = disabled
acct_mgr.notification.accountchangelistener = enabled
acct_mgr.notification.accountchangenotificationadminpanel = enabled
acct_mgr.pwhash.htdigesthashmethod = enabled
acct_mgr.pwhash.htpasswdhashmethod = enabled
acct_mgr.register.basiccheck = enabled
acct_mgr.register.bottrapcheck = enabled
acct_mgr.register.emailcheck = enabled
acct_mgr.register.emailverificationmodule = enabled
acct_mgr.register.regexpcheck = enabled
acct_mgr.register.registrationmodule = enabled
acct_mgr.register.usernamepermcheck = enabled
acct_mgr.web_ui.loginmodule = enabled

I have a fix for this. When I apply it I get the next error message (only showing the last lines) in a related place:

  File "build/bdist.linux-i686/egg/acct_mgr/api.py", line 381, in _maybe_update_hash
    if not get_user_attribute(self.env, 1, user, 'password_refreshed', 1):
  File "build/bdist.linux-i686/egg/acct_mgr/model.py", line 125, in get_user_attribute
    cursor.execute(sql, sql_args)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1dev_r11468-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: text = integer
LINE 4:         WHERE sid=1 AND authenticated='testfufu' AND name='p...

This error message shows two problems: 1. the data type mismatch and 2. the field order is wrong (have to swap the name and authenticated columns).

I'll attach a diff that fixes both places for me. PostgreSQL is simply way more picky about data types than other databases.

[Moritz Bunkus]

In the second error message testfufu is the user name I've chosen for the new user. Hence the column order problem.

[Steffen Hoffmann]

Easy even to guess, sure.

This is bad. But it means, that it'll just refresh the hash until the end of time - yet a bit funny, that I've never noticed that before. Thanks for reporting it. I'll go and prepare unit testing for these functions alongside a fix for all of us.

Btw, I do strongly agree, that PostgreSQL is a very reliable partner for uncovering db access code weaknesses.

[Steffen Hoffmann]

Hm interesting, my new unit tests fail to reproduce it at least for PostgreSQL 8.4.13 (on Debian Squeeze). Didn't try with 9.1 yet, but I will, because its available for me too, just not setup by now.

[Steffen Hoffmann]

(In [12528]) AccountManagerPlugin: Ensure proper type 'string' even for storing numeric values in Trac db table session_attribute, refs #10772.

In contrast to the proposed patch I preferred to prevent future issues as well by improving the function get_user_attribute itself. However, thanks to mbunkus for testing hash refresh better than me before.

[Steffen Hoffmann]

Changes including additional unit tests have been run against PostgreSQL server version 9.1.7 too, with similar results. But I've noticed a systematical speed gain of about 7 % for the full test suite compared to running same code with server version 8.4.13.

[Steffen Hoffmann]

(In [12529]) AccountManagerPlugin: Ensure proper type 'int' for 'authenticated' in Trac db table session_attribute, refs #10772.

Consequent continuation of [12528], and some compatibility code re-arranged.

[Ryan J Ollos]

I've hit this issue running AccountManagerPlugin 0.4.4. It seems significant enough to warrant a 0.4.5 release, assuming 0.5 won't go out for a while. Is that something I can help make happen?

[Ryan J Ollos]

#13438 closed as a duplicate.