#3182 closed defect (worksforme)
Error when adding or viewing a ticket using MySQL
Reported by: | Owned by: | Jonas Borgström | |
---|---|---|---|
Priority: | high | Milestone: | |
Component: | ticket system | Version: | devel |
Severity: | normal | Keywords: | mysql |
Cc: | Branch: | ||
Release Notes: | |||
API Changes: | |||
Internal Changes: |
Description
When adding or viewing a ticket, I'm receiving the same error as found in #2973 and #986. The traceback is as follows:
Traceback (most recent call last): File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 300, in dispatch_request dispatcher.dispatch(req) File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 190, in dispatch resp = chosen_handler.process_request(req) File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 280, in process_request self._insert_ticket_data(req, db, ticket, reporter_id) File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 561, in _insert_ticket_data changelog = ticket.get_changelog(db=db) File "/usr/lib/python2.3/site-packages/trac/ticket/model.py", line 287, in get_changelog "ORDER BY time", (self.id, str(self.id), self.id)) File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in execute return self.cursor.execute(sql_escape_percent(sql), args) File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in execute return self.cursor.execute(sql_escape_percent(sql), args) File "/usr/lib64/python2.3/site-packages/MySQLdb/cursors.py", line 163, in execute self.errorhandler(self, exc, value) File "/usr/lib64/python2.3/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION'")
Running trac from subversion, revision 3344.
MySQL version 4.1.12 from RHEL4
Python MySQLdb version 1.2.1_p2
Attachments (0)
Change History (11)
follow-up: 9 comment:1 by , 18 years ago
comment:2 by , 18 years ago
Keywords: | mysql added |
---|---|
Milestone: | → 0.10.1 |
comment:3 by , 18 years ago
Is this perhaps a setting that should be stored in trac.ini?
It appears to work when I put a
database_charset = latin1
under [trac]
and then this code change:
Index: mysql_backend.py =================================================================== --- mysql_backend.py (revision 3883) +++ mysql_backend.py (working copy) @@ -19,6 +19,7 @@ from trac.core import * from trac.db.api import IDatabaseConnector from trac.db.util import ConnectionWrapper +from trac.config import Option _like_escape_re = re.compile(r'([/_%])') @@ -100,6 +101,9 @@ """Connection wrapper for MySQL.""" poolable = True + + charset = Option('trac', 'database_charset', 'utf8', + """Database charset for the connection""") def _mysqldb_gt_or_eq(self, v): """This function checks whether the version of python-mysqldb @@ -139,11 +143,11 @@ # on 1.2.1 made. -dilinger if (self._mysqldb_gt_or_eq((1, 2, 1))): cnx = MySQLdb.connect(db=path, user=user, passwd=password, - host=host, port=port, charset='utf8') + host=host, port=port, charset=self.charset) else: cnx = MySQLdb.connect(db=path, user=user, passwd=password, host=host, port=port, use_unicode=True) - self._set_character_set(cnx, 'utf8') + self._set_character_set(cnx, self.charset) ConnectionWrapper.__init__(self, cnx) def cast(self, column, type):
Let me know if this is helpful.
Thanks.
comment:5 by , 18 years ago
Priority: | normal → high |
---|
comment:6 by , 18 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
comment:7 by , 18 years ago
Resolution: | fixed |
---|---|
Status: | closed → reopened |
Don't close tickets without a comment.
comment:8 by , 18 years ago
Resolution: | → worksforme |
---|---|
Status: | reopened → closed |
This seems to be a bug with MySQL:
Noted as fixed in MySQL 4.1.19, 5.0.20, 5.1.8 changelogs.
(Had the same issue, but worked with when upgraded MySQL)
comment:9 by , 18 years ago
This answer pointed me in the right direction. I thought perhaps some more detail could help others.
I found the following section in the file mysql_backend.py
# python-mysqldb 1.2.1 added a 'charset' arg that is required for
# unicode stuff. We hack around that here for older versions; at # some point, this hack should be removed, and a strict requirement # on 1.2.1 made. -dilinger if (self._mysqldb_gt_or_eq((1, 2, 1))):
cnx = MySQLdb.connect(db=path, user=user, passwd=password,
host=host, port=port, charset='utf8')
else:
cnx = MySQLdb.connect(db=path, user=user, passwd=password,
host=host, port=port, use_unicode=True)
# This is the original line
self._set_character_set(cnx, 'utf8')
# This is the change I made
self._set_character_set(cnx, 'latin1')
ConnectionWrapper.init(self, cnx)
Hope this helps some others.
Replying to spr@cs.byu.edu:
After further investigation, I discovered that the error occurs due to the fact trac initiates the database connection with charset set to 'utf8', though the database has a charset of 'latin1'.
A quick fix is to simply change line 114 in db/mysql_backend.py to
port=port, use_unicode=True, charset='latin1')This isn't really a great fix. Instead one could change the charset after connecting after checking to see what charset the database is using, but I'm at a loss as to how I should do that.
comment:10 by , 18 years ago
> > checking to see what charset the database is using, but I'm at a loss as to how I should do that.
you can do that from inside the server (assumed you'r loged in) by typing a:
mysql> \s
that gives you the status-information of the actual running mysql-server, like this example:
-------------- mysql Ver 14.7 Distrib 4.1.13, for suse-linux (i686) using readline 5.0 Connection id: 89719 Current database: Current user: root@localhost SSL: Not in use Current pager: less Using outfile: '' Using delimiter: ; Server version: 4.1.13 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 8 days 17 hours 33 min 47 sec Threads: 9 Questions: 1627913 Slow queries: 69 Opens: ....[pruned] --------------
comment:11 by , 18 years ago
Milestone: | 0.10.4 |
---|
After further investigation, I discovered that the error occurs due to the fact trac initiates the database connection with charset set to 'utf8', though the database has a charset of 'latin1'.
A quick fix is to simply change line 114 in db/mysql_backend.py to
This isn't really a great fix. Instead one could change the charset after connecting after checking to see what charset the database is using, but I'm at a loss as to how I should do that.