Report 6 All Tickets By Milestone (Including closed): execute(sql_escape_percent(sql), args)

[jtk@…]

Running Trac 0.11 r5187 svn, Report 6: All Tickets By Milestone (Including closed) aborts with exception:

ProgrammingError:
File "/software/svn/Trac/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)

​http://localhost:8000/acme/report/6

Trac detected an internal error:
ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

Traceback short form

File "/software/svn/Trac/trac/web/main.py", line 429, in dispatch_request
  dispatcher.dispatch(req)
File "/software/svn/Trac/trac/web/main.py", line 231, in dispatch
  content_type)
File "/software/svn/Trac/trac/web/chrome.py", line 557, in render_template
  if not req.session or not int(req.session.get('accesskeys', 0)):
File "/software/svn/Trac/trac/web/api.py", line 161, in __getattr__
  value = self.callbacks[name](self)
File "/software/svn/Trac/trac/web/main.py", line 271, in _get_session
  return Session(self.env, req)
File "/software/svn/Trac/trac/web/session.py", line 53, in __init__
  self.get_session(req.authname, authenticated=True)
File "/software/svn/Trac/trac/web/session.py", line 74, in get_session
  (sid, int(authenticated)))
File "/software/svn/Trac/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/software/svn/Trac/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)

Traceback long form

Python Traceback
Most recent call last:

    * File "/software/svn/Trac/trac/web/main.py", line 429, in dispatch_request
      Code fragment:
       424. if not env and env_error:
       425. raise HTTPInternalError(env_error.message)
       426. try:
       427. try:
       428. dispatcher = RequestDispatcher(env)
       429. dispatcher.dispatch(req)
       430. except RequestDone:
       431. pass
       432. return req._response or []
       433. finally:
       434. if not run_once:
      Local variables:
      Name	Value
      after 	[' except RequestDone:', ' pass', ' ...
      before 	[' if not env and env_error:', ' raise ...
      dispatcher 	<trac.web.main.RequestDispatcher object at 0xb662760c>
      e 	<psycopg2.ProgrammingError instance at 0xb764382c>
      env 	<trac.env.Environment object at 0xb76f814c>
      env_error 	None
      env_name 	'acme'
      env_parent_dir 	None
      env_path 	'/var/lib/trac/acme'
      env_paths 	['/var/lib/trac/acme']
      environ 	{'HTTP_AUTHORIZATION': 'Basic anRrOmE3YXd3d3Rm', 'HTTP_REFERER': ...
      exc_info 	(<class psycopg2.ProgrammingError at 0xb675126c>, ...
      filename 	'/software/svn/Trac/trac/web/main.py'
      frames 	[{'function': 'dispatch_request', 'lines_before': [' if not env and ...
      has_admin 	True
      line 	' dispatcher.dispatch(req)'
      lineno 	428
      message 	u'ProgrammingError: current transaction is aborted, commands ignored until ...
      path_info 	['report', '6']
      req 	<Request "GET /report/6">
      run_once 	False
      script_url 	None
      start_response 	<bound method WSGIServerGateway._start_response of ...
      tb 	<traceback object at 0x858c2d4>
      tb_hide 	None
      traceback 	'Traceback (most recent call last):\n File ...
    * File "/software/svn/Trac/trac/web/main.py", line 231, in dispatch
      Code fragment:
       226. req.display(template, content_type or 'text/html')
       227. else: # Genshi
       228. template, data, content_type = \
       229. self._post_process_request(req, *resp)
       230. output = chrome.render_template(req, template, data,
       231. content_type)
       232. # Give the session a chance to persist changes
       233. if req.session:
       234. req.session.save()
       235.  
       236. if 'hdfdump' in req.args:
      Local variables:
      Name	Value
      chosen_handler 	<trac.ticket.report.ReportModule object at 0xb6627a4c>
      chrome 	<trac.web.chrome.Chrome object at 0xb671cd4c>
      content_type 	None
      data 	{'dateinfo': <function dateinfo at 0xb5d38764>, 'context': <Context ...
      err 	(<class psycopg2.ProgrammingError at 0xb675126c>, ...
      handler 	<trac.ticket.report.ReportModule object at 0xb6627a4c>
      req 	<Request "GET /report/6">
      resp 	('report_view.html', {'dateinfo': <function dateinfo at 0xb5d38764>, ...
      self 	<trac.web.main.RequestDispatcher object at 0xb662760c>
      template 	'report_view.html'
    * File "/software/svn/Trac/trac/web/chrome.py", line 557, in render_template
      Code fragment:
       552.  
       553. doctype = {'text/html': DocType.XHTML_STRICT}.get(content_type)
       554. if doctype:
       555. if req.form_token:
       556. stream |= self._add_form_token(req.form_token)
       557. if not req.session or not int(req.session.get('accesskeys', 0)):
       558. stream |= self._strip_accesskeys
       559.  
       560. req.chrome['links'] = {}
       561. req.chrome['scripts'] = []
       562. data.setdefault('chrome', {}).update({
      Local variables:
      Name	Value
      content_type 	'text/html'
      data 	{'all': <function all at 0xb7a3c454>, 'req': <Request "GET /report/6">, ...
      doctype 	('html', '-//W3C//DTD XHTML 1.0 Strict//EN', ...
      filename 	'report_view.html'
      fragment 	False
      method 	'xhtml'
      req 	<Request "GET /report/6">
      self 	<trac.web.chrome.Chrome object at 0xb671cd4c>
      stream 	<genshi.core.Stream object at 0xb5d4665c>
      template 	<MarkupTemplate "report_view.html">
    * File "/software/svn/Trac/trac/web/api.py", line 161, in __getattr__
      Code fragment:
       156.  
       157. def __getattr__(self, name):
       158. """Performs lazy attribute lookup by delegating to the functions in the
       159. callbacks dictionary."""
       160. if name in self.callbacks:
       161. value = self.callbacks[name](self)
       162. setattr(self, name, value)
       163. return value
       164. raise AttributeError(name)
       165.  
       166. def __repr__(self):
      Local variables:
      Name	Value
      name 	'session'
      self 	<Request "GET /report/6">
    * File "/software/svn/Trac/trac/web/main.py", line 271, in _get_session
      Code fragment:
       266. def _get_perm(self, req):
       267. perms = PermissionSystem(self.env).get_user_permissions(req.authname)
       268. return PermissionCache(perms)
       269.  
       270. def _get_session(self, req):
       271. return Session(self.env, req)
       272.  
       273. def _get_timezone(self, req):
       274. try:
       275. return timezone(req.session.get('tz', self.default_timezone
       276. or 'missing'))
      Local variables:
      Name	Value
      req 	<Request "GET /report/6">
      self 	<trac.web.main.RequestDispatcher object at 0xb662760c>
    * File "/software/svn/Trac/trac/web/session.py", line 53, in __init__
      Code fragment:
        48. self.get_session(sid)
        49. else:
        50. if req.incookie.has_key(COOKIE_KEY):
        51. sid = req.incookie[COOKIE_KEY].value
        52. self.promote_session(sid)
        53. self.get_session(req.authname, authenticated=True)
        54.  
        55. def bake_cookie(self, expires=PURGE_AGE):
        56. assert self.sid, 'Session ID not set'
        57. self.req.outcookie[COOKIE_KEY] = self.sid
        58. self.req.outcookie[COOKIE_KEY]['path'] = self.req.base_path
      Local variables:
      Name	Value
      env 	<trac.env.Environment object at 0xb76f814c>
      req 	<Request "GET /report/6">
      self 	{}
    * File "/software/svn/Trac/trac/web/session.py", line 74, in get_session
      Code fragment:
        69. refresh_cookie = True
        70. self.sid = sid
        71.  
        72. cursor.execute("SELECT last_visit FROM session "
        73. "WHERE sid=%s AND authenticated=%s",
        74. (sid, int(authenticated)))
        75. row = cursor.fetchone()
        76. if not row:
        77. return
        78. self._new = False
        79. self.last_visit = int(row[0])
      Local variables:
      Name	Value
      authenticated 	True
      cursor 	<trac.db.util.IterableCursor object at 0xb5d46c14>
      db 	<trac.db.pool.PooledConnection object at 0xb7643aac>
      refresh_cookie 	False
      self 	{}
      sid 	u'myuser'
    * File "/software/svn/Trac/trac/db/util.py", line 50, in execute
      Code fragment:
        45. def execute(self, sql, args=None):
        46. # -- In case of SQL errors, uncomment the following 'print' statements
        47. # print 'execute', repr(sql)
        48. if args:
        49. # print repr(args)
        50. return self.cursor.execute(sql_escape_percent(sql), args)
        51. return self.cursor.execute(sql)
        52.  
        53. def executemany(self, sql, args=None):
        54. # print 'executemany', repr(sql)
        55. if args:
      Local variables:
      Name	Value
      args 	(u'myuser', 1)
      self 	<trac.db.util.IterableCursor object at 0xb5d46c14>
      sql 	'SELECT last_visit FROM session WHERE sid=%s AND authenticated=%s'
    * File "/software/svn/Trac/trac/db/util.py", line 50, in execute 

Config info

Trac:  	0.11dev-r5182
Python: 	2.4.4 (#1, Dec 22 2006, 19:27:50) [GCC 4.1.1 (Gentoo 4.1.1-r3)]
setuptools: 	0.6c5
psycopg2: 	2.0.5.1
Genshi: 	0.4dev-r527
Pygments: 	0.8dev-r3299
Subversion: 	1.4.3 (r23084)

Running with tracd:

# tracd --port 8000 --basic-auth acme,/var/lib/svn/acme/conf/svnusers,laptop /var/lib/trac/acme

SQL statement obtained by edit action on report 6:

​http://localhost:8000/acme/report/6?action=edit

All Tickets By Milestone (Including closed)

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC

[anonymous]

I got the same error with r5198

[lorne.wilson1@…]

I am seeing the same issue in 0.11dev-r5694

I played around with the SQL a little and found out that it is the CASE statement in the ORDER BY clause that it is complaining about.

I replaced the report with the following SQL and it seems to work fine:

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed') DESC

[olistudent]

Problem is still present in r7198. But the workaround from ​comment 3 works for me, too.

[Christian Boos]

This is actually a duplicate of #5543.

Replying to olistudent:

Problem is still present in r7198.

Sure, we only fix the default reports in the code, so already installed reports won't get updated, as most of the time they're already customized, copied, etc.

But the workaround from ​comment 3 works for me, too.

Right, you need to fix the existing reports manually.