Error when adding a ticket (using Trac-Mysql DB ) - Illegal mix of collations

[amgad.mosleh@…]

Hi every one

I am using Trac with MySQL data base and I am getting an exception when I try to add a ticket. The Trace of the Exception is as followos:

Traceback (most recent call last):
  File "C:\engines\Python24\lib\site-packages\trac\web\main.py", line 283, in dispatch_request
    dispatcher.dispatch(req)
  File "C:\engines\Python24\lib\site-packages\trac\web\main.py", line 170, in dispatch
    resp = chosen_handler.process_request(req)
  File "C:\engines\Python24\lib\site-packages\trac\ticket\web_ui.py", line 219, in process_request
    self._insert_ticket_data(req, db, ticket, reporter_id)
  File "C:\engines\Python24\lib\site-packages\trac\ticket\web_ui.py", line 424, in _insert_ticket_data
    changelog = ticket.get_changelog(db=db)
  File "C:\engines\Python24\lib\site-packages\trac\ticket\model.py", line 271, in get_changelog
    "ORDER BY time", (self.id,  str(self.id), self.id))
  File "C:\engines\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\engines\Python24\lib\site-packages\trac\db\util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "C:\engines\Python24\lib\site-packages\MySQLdb\cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
  File "C:\engines\Python24\lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1267, "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION'")

Thanks in advance

[anonymous]

Sorry I forget to mention that Iam using the following versions:

Mysql	5.0.19
Trac	0.10 dev - patch# 3029
Subversion	1.3.0
Os	WinXP Prof 2002
Python	2.4

[anonymous]

This was solved in ticket #968

[mgood]

Don't close a ticket as fixed unless some change has been committed to SVN. This should probably be a duplicate, since the problem has been address in the patches for #968.

[mgood]

Issue already raised in #968.

[anonymous]

Actually, I believe the proper ticket is #986 and not 968.

[troy]

I'm using a 11dev version checked out on 10-28-06. Not sure which version.

When I created my database in MySQL it defaulted to latin1_sweedish_ci charset instead of utf8 (which is normal for my setup).
Heres what I did to make it work:
I had to alter /usr/local/lib/python2.5/site-packages/trac/db/mysql_backend.py
to the following in order to no longer throw the error mentioned above.

136 # python-mysqldb 1.2.1 added a 'charset' arg that is required for
137 # unicode stuff. We hack around that here for older versions; at
138 # some point, this hack should be removed, and a strict requirement
139 # on 1.2.1 made. -dilinger
140 if (self._mysqldb_gt_or_eq((1, 2, 1))):
141 #cnx = MySQLdb.connect(db=path, user=user, passwd=password,
142 # host=host, port=port, charset='utf8')
143 cnx = MySQLdb.connect(db=path, user=user, passwd=password,
144 host=host, port=port, charset='latin1')
145 else:
146 cnx = MySQLdb.connect(db=path, user=user, passwd=password,
147 host=host, port=port, use_unicode=True)
148 #self._set_character_set(cnx, 'utf8')
149 self._set_character_set(cnx, 'latin1')

[troy]

Sorry about the formating above.

I'm using a 11dev version checked out on 10-28-06. Not sure which version.

When I created my database in MySQL it defaulted to latin1_sweedish_ci charset
instead of utf8 (which is normal for my setup).
Heres what I did to make it work:
I had to alter /usr/local/lib/python2.5/site-packages/trac/db/mysql_backend.py
to the following in order to no longer throw the error mentioned above.

136 # python-mysqldb 1.2.1 added a 'charset' arg that is required for
137 # unicode stuff. We hack around that here for older versions; at
138 # some point, this hack should be removed, and a strict requirement
139 # on 1.2.1 made. -dilinger
140 if (self._mysqldb_gt_or_eq((1, 2, 1))):
141 #cnx = MySQLdb.connect(db=path, user=user, passwd=password,
142 # host=host, port=port, charset='utf8')
143 cnx = MySQLdb.connect(db=path, user=user, passwd=password,
144 host=host, port=port, charset='latin1')
145 else:
146 cnx = MySQLdb.connect(db=path, user=user, passwd=password,
147 host=host, port=port, use_unicode=True)
148 #self._set_character_set(cnx, 'utf8')
149 self._set_character_set(cnx, 'latin1')

[cboos]

Hm, that's barely more readable ;) See TracDev/SubmittingPatches for some advices about how to produces patches.

IIUC, you said that setting the charset parameter to the same value is your server charset (latin1) in your case solved all the problems?

Please follow-up on #3182, I'd be interested to know if that solved all issues.
In particular, what happens when using UTF-8 characters not part of the latin1 encoding, in that case.

E.g. try to insert some excerpts of TracUnicode in your tickets, and check how this behaves...