Edgewall Software
Modify

Opened 13 years ago

Closed 12 years ago

Last modified 12 years ago

#3182 closed defect (worksforme)

Error when adding or viewing a ticket using MySQL

Reported by: spr@… Owned by: Jonas Borgström
Priority: high Milestone:
Component: ticket system Version: devel
Severity: normal Keywords: mysql
Cc:
Release Notes:
API 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)

comment:1 Changed 13 years ago by 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.

comment:2 Changed 12 years ago by Christian Boos

Keywords: mysql added
Milestone: 0.10.1

comment:3 Changed 12 years ago by 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.

comment:4 Changed 12 years ago by Christian Boos

#3949 was closed as duplicate.

comment:5 Changed 12 years ago by Christian Boos

Priority: normalhigh

comment:6 Changed 12 years ago by anonymous

Resolution: fixed
Status: newclosed

comment:7 Changed 12 years ago by Matthew Good

Resolution: fixed
Status: closedreopened

Don't close tickets without a comment.

comment:8 Changed 12 years ago by anonymous

Resolution: worksforme
Status: reopenedclosed

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)

comment:9 in reply to:  1 Changed 12 years ago by 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.

comment:10 Changed 12 years ago by 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]
--------------

comment:11 Changed 12 years ago by Christian Boos

Milestone: 0.10.4

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jonas Borgström.
The resolution will be deleted.
to The owner will be changed from Jonas Borgström to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.