Error when adding or viewing a ticket using MySQL

[spr@…]

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

[spr@…]

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.

[d_dorothy@…]

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.

[cboos]

#3949 was closed as duplicate.

[mgood]

Don't close tickets without a comment.

[anonymous]

This seems to be a bug with MySQL:

http://bugs.mysql.com/bug.php?id=15949

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)

[peter.caron@…]

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.

[anonymous]

> > 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]
--------------