When searching I get "OperationalError: Could not decode to UTF-8 column"

[daniel.beckham@…]

When searching in Trac, I see the following error:

OperationalError: Could not decode to UTF-8 column

I've isolated the problem to searches that match a specific ticket in the system. Any search that would match the description in that ticket throws the error. Searches for keywords that do not exist in that ticket's description work properly.

Full Traceback:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.4/site-packages/trac/Search.py", line 181, in process_request
    results += list(source.get_search_results(req, terms, filters))
  File "/usr/lib/python2.4/site-packages/trac/ticket/api.py", line 269, in get_search_results
    if status == 'closed':
OperationalError: Could not decode to UTF-8 column 'description' with text 'We need to full explore the possibilities for the RSS feeds.

[daniel.beckham@…]

I also see the following tracback when trying to open the ticket in my browser:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.4/site-packages/trac/ticket/web_ui.py", line 275, in process_request
    ticket = Ticket(self.env, id, db=db)
  File "/usr/lib/python2.4/site-packages/trac/ticket/model.py", line 40, in __init__
    self._fetch_ticket(tkt_id, db)
  File "/usr/lib/python2.4/site-packages/trac/ticket/model.py", line 83, in _fetch_ticket
    % ','.join(std_fields), (tkt_id,))
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.4/site-packages/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/usr/lib/python2.4/site-packages/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: Could not decode to UTF-8 column 'description' with text 'We need to full explore the possibilities for the RSS feeds.

[daniel.beckham@…]

Ok, final piece of the puzzle here is that I've confirmed that this particular ticket was created by a script that we ran some time ago to convert items from an old todo system to track tickets.

At this point, I think it's less of a defect and more of a needed enhancement for Trac to more gracefully handle database errors. In this particular instance, there would have been several good results that could have been displayed instead of an error page, or at least in addition to some sort of error output.

[Emmanuel Blot]

Trac now handles internally all text as unicode and accepts UTF-8 text as well. If the proper APIs are used to access Trac data, this kind of issue should not happen anymore. Previous releases of Trac were more sensitive to this kind of errors.

However, managing errors due to direct Trac DB changes - bypassing the APIs - is really out-of-scope.

You need to manually edit your Trac DB file to fix up this issue, using SQL commands.

[Christian Boos]

#5628, #5942 were closed as duplicates.