Encode strings into database encoding before cursor.execute()

[xiled@…]

In trac/db/utils.py in def execute(self, sql, args=None):

it should be something like:

if args:
  encoded_args = []
  for a in args:
    try:
        encoded_args.append( a.encode("db_coding") ) # utf8 in my case
    except:
         encoded_args.append( a )
     return cursor.execute(..., encoded_args)

Note: I know that try/except is ugly here but type checking type(a) == types.UnicdeType did not worked for me. Somehow u"" passed through.

Otherwise if strings is not encoded.. i get error from psycopg2 .. when ticket resolution is in russian.

2009-10-14 17:36:17,215 pid: 31501, ERROR sql_escape_percent(sql) : INSERT INTO ticket_change (ticket,time,author,field,oldvalue,newvalue) VALUES (%s, %s, %s, %s, %s, %s) => [3, 1255520177, u'admin', 'resolution', u'', u'\u043d\u0435\u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e \u0440\u0435\u0448\u0438\u0442\u044c']
2009-10-14 17:36:17,218 pid: 31501, ERROR Internal Server Error:
Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/web/main.py", line 467, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/web/main.py", line 212, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/ticket/web_ui.py", line 188, in process_request
    return self._process_ticket_request(req)
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/ticket/web_ui.py", line 541, in _process_ticket_request
    self._do_save(req, ticket, action)
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/ticket/web_ui.py", line 1196, in _do_save
    cnum=internal_cnum):
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/ticket/model.py", line 301, in save_changes
    self[name]))
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/db/util.py", line 75, in execute
    return self.cursor.execute(sql_escape_percent(sql), new_args)
  File "/usr/lib/python2.5/site-packages/Trac-0.12dev_r8661-py2.5.egg/trac/db/util.py", line 75, in execute
    return self.cursor.execute(sql_escape_percent(sql), new_args)
ProgrammingError: can't adapt

I dont know if it is psycopg2 bug.. but i think encoding strings before execute is good.

[Remy Blank]

Isn't that done automatically by psycopg2 when a unicode string is passed?

[Christian Boos]

You were trying to change the resolution to "невозможно решить", this should just work.

It works for me when using SQLite database, and I also just tested with a PostgreSQL backend and I could indeed reproduce the issue.

System used for the test:

Trac:	0.12dev-r8656
Python:	2.6.1 (r261:67517, Dec 4 2008, 16:51:00) [MSC v.1500 32 bit (Intel)]
setuptools:	0.7a1
psycopg2:	2.0.8
Genshi:	0.6dev-r1071
Babel:	0.9.4
Pygments:	1.0dev-20090211
Subversion:	1.6.2 (r37639)
jQuery:	1.3.2

[Christian Boos]

Weird. Same system but different version (latest 0.11-stable), it worked. Switched back to 0.12dev again: now it works!

Need to find more details about this "can't adapt" error.

[Christian Boos]

Interesting, there's a duplicate here with this comment: ticket:3501#comment:4

[Christian Boos]

When it works, I'm able to see such output in the log with [trac] debug_sql = true:

3:23:16 PM Trac[util] DEBUG: SQL: 'UPDATE ticket SET resolution=%s WHERE id=%s'
3:23:16 PM Trac[util] DEBUG: args: (u'\u043d\u0435\u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e \u0440\u0435\u0448\u0438\u0442\u044c', 1)

But if I force unicode instances to actually be Markup instances, I systematically get the "can't adapt" error. Note that I get the same result if I force the empty sentinel value in.

So I guess we need extra adapters for those types, as the comment linked above suggested.

I'm not yet sure about the specific circumstances that make us get a Markup instance there in some cases and not in other, and in particular if this sequence is 0.12 specific, need further investigations for that.

[Christian Boos]

Well, of course it's obviously 0.12 specific, as we're running 0.11-stable on psycopg2 here on t.e.o ;-)

And we actually don't try to store a Markup string but the shiny new empty value (r8661). This happens after the first "close", for the oldvalue field of the resolution value in the ticket_change table.

Patch follows.

[Christian Boos]

In postgres_backend, register_adapter for Markup and Empty unicode subclasses (follows-up and applies on r8661)

[Christian Boos]

The MySQL binding, like the SQLite one, is also immune to the problem.

[Christian Boos]

Patch applied, and unit tests added, in r8669.

Interestingly the unit tests helped to discover a similar issue with the MySQL bindings. There, unicode subclasses were treated as str, so it worked for the empty value (as str(u'') is fine) and also worked for Markup instances which contained only 'ascii' characters (that's why I thought it was OK in comment:8), but not on the more general case with non-ASCII characters.