Modify

Opened 6 years ago

Closed 6 years ago

#2764 closed defect (fixed)

Sporadic error on Postgres

Reported by: goldman Owned by: doki_pen
Priority: normal Component: AnnouncerPlugin
Severity: minor Keywords:
Cc: Trac Release: 0.11

Description

It does not seem to impact normal operation, but I'm gettin tons of error messages in Postgres logs:

HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
STATEMENT:
                    SELECT value
                      FROM session_attribute
                     WHERE sid='jbrink'
                       AND authenticated=true
                       AND name='announcer_email_format_ticket'

ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT DISTINCT s.sid, n.value, e.value FROM session AS s  LEFT JOIN session_attribute AS n ON (n.sid=s.sid   and n.authenticated=1 AND n.name = 'name')  LEFT JOIN session_attribute AS e ON (e.sid=s.sid   AND e.authenticated=1 AND e.name = 'email') WHERE s.authenticated=1 ORDER BY s.sid

I cant pinpoint where this happens - tried browsing around Trac but couldnt trigger it. Obviously the cause is 'authenticated=true' clause, should have been 'authenticated=1'

Attachments (3)

postgres-announcer.patch (550 bytes) - added by doki_pen 6 years ago.
authenticated2int.patch (2.3 KB) - added by doki_pen 6 years ago.
convert authenticated to int before any query.
ann_rule2str.patch (2.4 KB) - added by doki_pen 6 years ago.
cast rule in select to string before running query

Download all attachments as: .zip

Change History (12)

comment:1 Changed 6 years ago by goldman

Here is excerpt from Trac log when it happens:

2008-03-25 05:12:11,204 Trac[api] ERROR: AnnouncementSystem failed.
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/announcerplugin/api.py", line 376, in _real_send
    distributor.distribute(transport, packages[transport], evt)
  File "build/bdist.linux-i686/egg/announcerplugin/distributors/email_distributor.py", line 157, in distribute
    format = self._get_preferred_format(event.realm, name, authenticated)
  File "build/bdist.linux-i686/egg/announcerplugin/distributors/email_distributor.py", line 205, in _get_preferred_format
    """, (sid, authenticated, 'announcer_email_format_%s' % realm))
  File "/usr/lib/python2.5/site-packages/Trac-0.11b1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11b1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: integer = boolean
LINE 5:                AND authenticated=true
                                        ^
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

comment:2 Changed 6 years ago by doki_pen

Try the postgres-announcer.patch. I think the problem is that sqlite doesn't really have types, so true == 1. Maybe if we make it a text column trac can figure it out. I'm not using postgres, so I can't tell. The idea is to change the schema itself. You will have to install it on a fresh environment so the schema can be generated.

Changed 6 years ago by doki_pen

comment:3 Changed 6 years ago by doki_pen

  • Owner changed from ixokai to doki_pen
  • Status changed from new to assigned

Changed 6 years ago by doki_pen

convert authenticated to int before any query.

comment:4 Changed 6 years ago by doki_pen

This newer patch will work better. Please test and reply.

comment:5 Changed 6 years ago by goldman

Now I'm getting the following error:

ERROR:  operator does not exist: text = integer at character 272
HINT:  No operator matches the given name and argument type(s). You might need t
o add explicit type casts.
STATEMENT:
                            SELECT transport, sid, authenticated
                              FROM subscriptions
                             WHERE enabled=1 AND managed='watcher'
                               AND realm='ticket'
                               AND category='*'
                               AND rule=1037

Column 'rule' is of type text yet is being compared to integer. This no longer works in Postgres 8.3 (used to work in 8.2)

comment:6 Changed 6 years ago by doki_pen

thanks for working on this with me. Looks like we are getting closer to a solution. sqlite does a lot of automatic casting and I think this is what we are seeing. Postgres doesn't. Try this patch.

Changed 6 years ago by doki_pen

cast rule in select to string before running query

comment:7 Changed 6 years ago by goldman

The patch fails, all hunks get rejected. I'm on revision 4069. Excerpt from .rej file:

***************
*** 76,82 ****
                 AND realm=%s
                 AND category=%s
                 AND rule=%s
-         """, (sid, authenticated and 1 or 0, 'watcher', realm, '*', resource))

          result = cursor.fetchone()
          if result:
--- 76,82 ----
                 AND realm=%s
                 AND category=%s
                 AND rule=%s
+         """, (sid, int(authenticated), 'watcher', realm, '*', str(resource)))

          result = cursor.fetchone()
          if result:
***************

comment:8 Changed 6 years ago by anonymous

can you try

cd announcerplugin/0.11
svn revert . -R 
svn up
cat ann_rule2str.patch | patch -p0 

comment:9 Changed 6 years ago by goldman

  • Resolution set to fixed
  • Status changed from assigned to closed

OK this seems to have done the trick. Thanks!

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.