#986 closed enhancement (fixed)
MySQL database support
Reported by: | anonymous | Owned by: | Christopher Lenz |
---|---|---|---|
Priority: | high | Milestone: | 0.10 |
Component: | general | Version: | devel |
Severity: | major | Keywords: | database mysql |
Cc: | trac@…, manuzhai@…, dilinger@…, jjb@…, soloturn@… | Branch: | |
Release Notes: | |||
API Changes: | |||
Internal Changes: |
Description
I would like to use trac with the mysql/postgres. There are any plan to support other databases? If not, i would try it. Can somebody give me some hints about db layer?
Attachments (19)
Change History (81)
comment:1 by , 20 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
comment:3 by , 19 years ago
Resolution: | duplicate |
---|---|
Status: | closed → reopened |
Version: | 0.8 → devel |
Reopened per comments on #126. Created patch against r2625 to add experimental MySQL support.
At the moment it only supports connection strings in the format of:
mysql://user:password@host:port/database
mysql://user:password@host/database
I did have to make one database schema change because MySQL doesn't like to AUTO_INCREMENT a TEXT column, so I changed it to INT.
Beware: I still haven't tested to see if it breaks SQLite and PostgreSQL support!
by , 19 years ago
Attachment: | trac_mysql_r2625.patch added |
---|
patch against r2625 to enable experimental MySQL support
comment:4 by , 19 years ago
Summary: | MySQL/Postgresql database support → MySQL database support |
---|
comment:5 by , 19 years ago
Instead of changing the type to "int" in db_default
, change the to_sql
method for MySQL to set ctype
to "INT AUTO_INCREMENT"
instead of just appending AUTO_INCREMENT
to it.
comment:6 by , 19 years ago
I've tested the patch with SQLite: nothing seems to be broken. In particular search, initenv are working as usual.
by , 19 years ago
Attachment: | trac_mysql_r2628.patch added |
---|
patch now reflects suggestion by mgood. Reverted database modification for SQLite and Postgres, only affects MySQL for AUTO_INCREMENT fields. Patch is against r2628.
comment:7 by , 19 years ago
Cc: | added |
---|
comment:8 by , 19 years ago
Keywords: | mysql added |
---|---|
Milestone: | → 1.0 |
Priority: | normal → high |
Severity: | normal → major |
That last patch broke the session unit tests for me (using pysqlite). Can you have a look?
ERROR: test_add_anonymous_session_var (trac.web.tests.session.SessionTestCase) ---------------------------------------------------------------------- Traceback (most recent call last): File "D:\Workspace\src\trac\trunk\trac\web\tests\session.py", line 97, in test_add_anonymous_session_var session.save() File "D:\Workspace\src\trac\trunk\trac\web\session.py", line 191, in save if len(sids) >= 1: TypeError: len() of unsized object
(and 8 other similar errors)
by , 19 years ago
Attachment: | trac_mysql_r2636.patch added |
---|
patch now reflects fix problem indicated by cboos
comment:9 by , 19 years ago
After I figured out how to run the unit tests (TracDev/UnitTests), I was able to replicate and solve the problem in session.py. However, versioncontrol/cache.py:88 is still broken for SQLite (at least on my machine). The problem stems from the column node_change.change. 'Change' is a reserved word in MySQL, so I had previously been escaping it with `, but decided that specifying the table name was more appropriate. Although it doesn't seems to matter, because the unit tests for SQLite fail on both.
From what I've seen so far there's no common ground between MySQL and SQLite on escaping column names, and I'm reluctant to change a column name.
Recommended path forward??
comment:10 by , 19 years ago
For me, `change`
was not a problem with PySqlite. Are you using 1.1.6?
I'll have a closer look at that.
comment:11 by , 19 years ago
I'm running PySqlite 2.0.3 against SQLite 3.2.1, both stock versions from Ubuntu 5.10. I'll grab some newer versions this evening and see if that makes a difference.
comment:12 by , 19 years ago
Upgrading SQLite to the latest version 3.2.7 is what did the trick. After that, it appears to make no difference whether PySQL 1.1.6 or 2.0.5 is used. I reverted the patch from node_change.change
to `change`
. All the unit tests pass for me.
I still haven't heard from any postgres people on whether or not this breaks anything for them.
I'd be really stoked if this actually makes it into version 1.0.
comment:13 by , 19 years ago
Some initial tests I've run against PostgreSQL 8.0.3 indicate that this patch breaks current postgres support. initenv
dies because of the `change`
in versioncontrol/cache.py:88. We're back to an escaping problem because change
is a reserved word in MySQL, and I can't come up with a way to escape node_change.change
that will work for SQLite, MySQL and Postgres.
comment:14 by , 19 years ago
It's always an option to simply rename the columns… the node_change
column names aren't particulary clear anyway.
So how about:
- rename
change
tochange_type
, and - rename
kind
tonode_type
and then provide an upgrade script, of course.
by , 19 years ago
Attachment: | trac_mysql_r2644.patch added |
---|
patch contains suggestions of cmlenz, against r2644.
by , 19 years ago
Attachment: | trac_mysql_r2659.patch added |
---|
comment:15 by , 19 years ago
Milestone: | 1.0 → 0.10 |
---|
comment:16 by , 19 years ago
Cc: | added |
---|
comment:17 by , 19 years ago
It might be a viable strategy to first get the database schema change into trunk, and handle the MySQL support separately. The current patch is a little convoluted because it has both thrown together.
by , 19 years ago
Attachment: | db-changes-986-r2865.diff added |
---|
Changes the DB definition, upgrades format.
comment:18 by , 19 years ago
The patch I just attached does the schema changes as suggested by cmlenz, as applying to r2865. It seems to work for my trac, but I get three test failures:
====================================================================== ERROR: Testing env.get_known_users ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/manuzhai/dev/trac/trac/tests/env.py", line 14, in setUp self.env = Environment(env_path, create=True) File "/home/manuzhai/dev/trac/trac/env.py", line 80, in __init__ self.setup_log() File "/home/manuzhai/dev/trac/trac/env.py", line 231, in setup_log self.log = logger_factory(logtype, logfile, loglevel, logid) File "/home/manuzhai/dev/trac/trac/log.py", line 26, in logger_factory hdlr = logging.FileHandler(logfile) File "/usr/lib/python2.4/logging/__init__.py", line 753, in __init__ stream = open(filename, mode) IOError: [Errno 2] No such file or directory: '/tmp/trac-tempenv/log/trac.log' ====================================================================== ERROR: Testing env.get_version ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/manuzhai/dev/trac/trac/tests/env.py", line 14, in setUp self.env = Environment(env_path, create=True) File "/home/manuzhai/dev/trac/trac/env.py", line 80, in __init__ self.setup_log() File "/home/manuzhai/dev/trac/trac/env.py", line 231, in setup_log self.log = logger_factory(logtype, logfile, loglevel, logid) File "/home/manuzhai/dev/trac/trac/log.py", line 26, in logger_factory hdlr = logging.FileHandler(logfile) File "/usr/lib/python2.4/logging/__init__.py", line 753, in __init__ stream = open(filename, mode) IOError: [Errno 2] No such file or directory: '/tmp/trac-tempenv/log/trac.log' ====================================================================== FAIL: test_diff_dir_different_revs (trac.versioncontrol.tests.svn_fs.SubversionRepositoryTestCase) ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/manuzhai/dev/trac/trac/versioncontrol/tests/svn_fs.py", line 269, in test_diff_dir_different_revs (Node.DIRECTORY, Changeset.DELETE)), diffs.next()) File "/home/manuzhai/dev/trac/trac/versioncontrol/tests/svn_fs.py", line 238, in _cmp_diff self.assertEqual(expected[2], (got[2], got[3])) AssertionError: ('dir', 'delete') != (None, 'delete') ----------------------------------------------------------------------
I think the first two are unrelated, but the last one probably isn't. I can't seem to find the problem, though. Can someone else take a look?
comment:19 by , 19 years ago
You mean, you don't have the above errors using the unpatched r2865, but only after applying the patch, right?
comment:20 by , 19 years ago
Good point. The 2 log-errors are there without the patch, the last one is not.
by , 19 years ago
Attachment: | db-changes-986-r2865.2.diff added |
---|
Replace existing patch by improved version.
comment:21 by , 19 years ago
New version of the patch. This now passes all tests except two irrelevant ones.
Found and fixed the omittance that caused the third test, and added the dbupgrade file I forgot to add to the last patch. Please review and commit.
comment:22 by , 19 years ago
I think we should limit the renaming to just the DB column names, and not have them bubble through all the way up to the templates.
comment:23 by , 19 years ago
Okay, so also not the Python variables in the loops and such?
I will correct the patch tomorrow.
comment:24 by , 19 years ago
This patch is a followup to Manuzhai's last db-changes patch. It is against revision 2881, and strips out everything except for database-specific changes (that is, the whitespace changes and various get_changes() things have been removed). It also slightly tweaks the db17.py upgrade stuff to use the schema from db_default.py instead of hardcoding it in the upgrade script. Some minor formatting changes related to the larger columns name sizes were also done.
by , 19 years ago
Attachment: | trac-db-changes-r2881.patch added |
---|
updated db-changes patch against 2881
comment:25 by , 19 years ago
Patch applied in [2882]. Thanks guys!
Now on to the actual MySQL connector…
comment:26 by , 19 years ago
I have a few concerns/questions about Jeff's mysql patch, while looking throught it:
a) 'import mysqldb' unconditionally, instead of inside MySQLConnection's ctor means that it (python-mysqldb, that is) will be required for any install instead of just for installs that actually call the ctor, doesn't it?
b) like() comments say that the function is there for case-insensitive string matching, but LIKE in mysql is case-sensitive.
I'm updating the patch, so I may have some more questions..
comment:27 by , 19 years ago
Regarding the like() function, it looks like SQLite's LIKE supports case-insensitive matching by default (with a caveat), postgresql has ILIKE for that sort of thing, and mysql's LIKE is case-sensitive depending on the column type. If it's a plain VARCHAR, LIKE matches in a case-insensitive manner. If it's a VARCHAR BINARY type, LIKE matches in a case-sensitive manner. Ugh.
I think it would make more sense (and be less complex) to simply use LOWER() on arguments here, to ensure we're doing a case-insensitive match. If you folks agree, I'll follow up with a patch.
Oh, and the SQLite caveat?
"(A bug: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)."
comment:28 by , 19 years ago
Django's MySQL adapter uses LIKE BINARY %s
for case-sensitive searches.
comment:29 by , 19 years ago
Dunno if it's worth mentioning but mysql is case-insensitive on = as well as like…
select 'a' = 'A'; +-----------+ | 'a' = 'A' | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
comment:30 by , 19 years ago
It would appear that 'sql' is a reserved keyword in mysql, so having it be a column name breaks things. I've created a patch that updates this as well, renaming sql
in the report table to query
.
Here's the list of reserved words in mysql, fyi: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
by , 19 years ago
Attachment: | trac-sqlcolfix-r2944.patch added |
---|
rename 'sql' column to 'query' in report table; against r2944
by , 19 years ago
Attachment: | trac-sqlcolfix-r2944.2.patch added |
---|
rename 'sql' column in report table, w/ add. bugfix; against r2944
comment:31 by , 19 years ago
trac-mysql-r2944.patch includes trac-sqlcolfix-r2944.2.patch. Aside from including sqlcolfix, the following is changed from Jeff's original patches:
- drop stuff already merge in trunk
- only import MySQLdb module if calling the MySQLConnector ctor
- use consistent variable names between MySQLConnector and MySQLConnection
- use MySQLdb's insert_id() instead of doing another select (which is racy; if two inserts happen at around the same time, one of the SELECTs would've returned the wrong id)
- more concise session purging code
- default to port 3306
- strip / from path as the other db backends do
I still intend to see if I can clean up the to_sql() index creation stuff a bit..
by , 19 years ago
Attachment: | trac-mysql-r2944.2.patch added |
---|
mysql patch w/ index creation cleaned up; against r2944.
comment:32 by , 19 years ago
I just tested this with mysql 4.0; it didn't work due to it having different size limits on indices (original patches were tested w/ mysql 5.0). With 4.0, the total index prefix limit is 500 bytes, with a max per-column limit of 255 bytes.
by , 19 years ago
Attachment: | trac-mysql-r2946.patch added |
---|
updated patch that works w/ both mysql 4.0.x and 5.0.x; against r2946
comment:33 by , 19 years ago
You tested with 4.1 before? We should just get it in anyway; 4.1 is pretty old/stable, and we can always go back and add more backward compatibility later.
comment:34 by , 19 years ago
I tested w/ 5.0 previously; I have not tried w/ 4.1 yet. Feel free to try it (I'd also be curious about 3.x, but I don't think it's worth targeting 3.x; it's hard to find those installs anymore).
comment:35 by , 19 years ago
People applying this patch are also probably interested in #2822, if they'd like to edit wiki pages.
by , 19 years ago
Attachment: | trac-mysql-r2946.2.patch added |
---|
mysql patch w/ bugfix for deleting sessions; only run delete query when there's actually something to delete.
by , 19 years ago
Attachment: | trac-mysql-r2975.patch added |
---|
updated mysql patch, w/ additional mysql4.0 fixes; against r2975.
comment:36 by , 19 years ago
I've updated the patch to include mysql4.0 fixes. These include:
- update db17.py to use "CREATE TEMPORARY TABLE" instead of "CREATE TEMP TABLE", as the TEMP syntax is unsupported.
- the RecentChanges page was broken because mysql4.0 apparently does not support ordering by a function. So, instead of doing that, order by the result (which is what was desired anyways).
- the search used a subselect when searching wiki pages; instead of a subselect, I changed it to use a temp table.
comment:37 by , 19 years ago
Hrm, I'd rather not have to use temp tables instead of subselects. What version of MySQL is required for subselects and is there a major reason not to require that version? Or, could you try using a JOIN instead of a subselect?
comment:38 by , 19 years ago
Using a JOIN would've been complex, and I'm not sure which databases support what form of join (I was discussing it on IRC yesterday w/ cmlenz; someone mentioned that sqlite didn't support some forms of JOINs, and that temp tables would be ok).
4.1 is required for subselects. The major reason to not require it is because I'm running debian stable on servers, and debian is using mysql 4.0. ;)
It would be nice to have mysql4.0 support.
comment:39 by , 19 years ago
After running into some more issues, I'm really starting to question whether mysql4.0 support is really worth all this trouble.
Does anyone following this bug actually care about mysql4.0 support for Trac? What versions are people running? If no one else seems to care, I may just go w/ mysql5.0 (which is considered stable by the mysql people).
comment:40 by , 19 years ago
Cc: | added |
---|
comment:41 by , 19 years ago
What about MySQL 4.1 then? If I understood you correctly, it does supports subselects etc, so are there any outstanding problems with that version? I don't care about 4.0, but 4.1 support would be nice if it's not too much trouble.
comment:42 by , 19 years ago
I do actually care about mySQL on version 4.0 as much because I'd like to use it on my Cpanel based server and they won't be upgrading for a while yet…
I'd be happy to help with testing if time allowed but I'm swamped as it is at presetn but just to answer you question… I'd be one who'd be gratful for your continued perserverence.
Cheers…
comment:43 by , 19 years ago
As stated before, I run 4.1. As this is the before-last generally available version, has been production quality for quite a while, I think requiring it would be fine (especially given that MySQL support at all is a new feature for Trac, anyone could still work with the SQLite support!).
Or maybe we can have a separate patch that adds 4.0 support, or something, for those that desparately need it.
comment:44 by , 19 years ago
I've updated the patch; I dropped some mysql4.0 workarounds, and added additional 4.1 and 5.0 fixes. Most notably, the search now works.
comment:45 by , 19 years ago
I am getting an exception when I try to add a ticket or view one. The Exception is as followos:
OperationalError:(1267,"Illegal mix of collations (utf8_general_ci,IMPLICIT) and(latin1_swedish_ci,COERCIBLE) for operation 'UNION'")
The version I am using are:
Mysql | 5.0.19 |
Trac | 0.10 dev - patch# 3029 |
Subversion | 1.3.0 |
Os | WinXP Prof 2002 |
Python | 2.4 |
- You can refer to ticket:2973
by , 19 years ago
Attachment: | trac-mysql-r3030.patch added |
---|
solved the problem of adding or viewing a ticket (the Illegal mix of collations )
follow-up: 62 comment:46 by , 19 years ago
To solve the problem of (the Illegal mix of collations) which is related to the ticket addition or view modifiy the model file (trac/ticket/model.py) as follows:
replace the current code(starting from line number 251):
if when: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s AND time=%s " "UNION " "SELECT time,author,'attachment',null,filename " "FROM attachment WHERE id=%s AND time=%s " "UNION " "SELECT time,author,'comment',null,description " "FROM attachment WHERE id=%s AND time=%s " "ORDER BY time", (self.id, when, str(self.id), when, self.id, when)) else: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s " "UNION " "SELECT time,author,'attachment',null,filename " "FROM attachment WHERE id=%s " "UNION " "SELECT time,author,'comment',null,description " "FROM attachment WHERE id=%s " "ORDER BY time", (self.id, str(self.id), self.id))
with the following code:
if when: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s AND time=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'filename' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s AND time=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'description' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s AND time=%s " "ORDER BY time", (self.id, when, str(self.id), when, self.id, when)) else: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'filename' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'description' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s " "ORDER BY time", (self.id, str(self.id), self.id))
hope this will help (see patch # 3030)
comment:47 by , 18 years ago
Cc: | added |
---|
comment:48 by , 18 years ago
Cc: | added |
---|
comment:49 by , 18 years ago
Owner: | changed from | to
---|---|
Status: | reopened → new |
comment:51 by , 18 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
I applied and slightly modified Andres' patch in [3300].
Not sure about that collation problem mentioned above… why would a column use latin1_swedish_ci
collation?
Other problems with the MySQL support should be filed as separate tickets.
comment:52 by , 18 years ago
latin1_swedish_ci collation seems to give the most natural sort order for many languages.
comment:53 by , 18 years ago
None of the solutions for the collation problem above worked on my MySQL install. I eventually figured out a fix for it. All that is needed is to change the default charset for each table to utf8.
You can do this by hand, "ALTER TABLE…" or you can do as I did and do mysqldump -a tracdb > tracdb.sql
edit this file to change the charset in each table creation to DEFAULT CHARSET=utf8;
Also you will need to change the indexes to smaller sizes as utf8 is ?? (4 I think) bytes wide and you cannot have indexes larger than 1000 (on mine) In the mysql_backend.py the comments say 500 bytes.
Once you have made these changes do a
mysql tracdb < tracdb.sql
That is for existing installs.
Here are the changes that make this work for new installs.
change this
sql.append(',\n'.join(coldefs) + '\n)')
to
sql.append(',\n'.join(coldefs) + '\n) DEFAULT CHARSET=utf8')
and this
limit = 500 / len(columns) if limit > 255: limit = 255
to this
limit = 250 / len(columns) if limit > 250: limit = 250
and just in case I changed this
if vers < (4, 1): raise TracError, 'MySQL servers older than 4.1 are not supported!' cnx.query('SET NAMES %s' % charset) cnx.store_result() cnx.charset = charset
to this
if vers < (4, 1): raise TracError, 'MySQL servers older than 4.1 are not supported!' cnx.query('SET CHARACTER SET UTF8'); cnx.store_result() cnx.query('SET NAMES %s' % charset) cnx.store_result() cnx.charset = charset
comment:56 by , 18 years ago
Replying to anonymous:
I would like to use trac with the mysql/postgres. There are any plan to support other databases? If not, i would try it. Can somebody give me some hints about db layer?
ertertwdft
comment:57 by , 18 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
comment:59 by , 18 years ago
Just to clarify the status of this ticket: comment:51 was the authoritative "closing" of this ticket, saying support for MySQL has been integrated in trunk (0.10dev), is flagged "experimental" for now, mainly due to the lack of user feedback about how robust it is.
Further issues with this backend should be reported as new tickets; also, success stories could be reported there: DatabaseBackend#MySQL
follow-up: 61 comment:60 by , 18 years ago
I've tried the patches here, including altering my tables to be UTF8, but the error still occurs when submitting new tickets (bounced the server, etc). Has anyone else gotten this working?
comment:61 by , 18 years ago
Replying to james.a.stewart@gmail.com:
I've tried the patches here, including altering my tables to be UTF8, but the error still occurs when submitting new tickets (bounced the server, etc). Has anyone else gotten this working?
Which error do you refer to? Also, as noted above, this ticket was about integrated initial support for MySQL, not a catch-all ticket to discuss issues with this database (and there are many of them). Please be sure to check the MySqlDb page, which will help you to identify a ticket related to the problem you're seeing. If you don't find it, you're welcomed to create a new one (don't forget to mention the Trac, MySQL and mysqldb versions used, as well as the charsets used for the DB).
comment:62 by , 17 years ago
Replying to anonymous:
To solve the problem of (the Illegal mix of collations) which is related to the ticket addition or view modifiy the model file (trac/ticket/model.py) as follows:
replace the current code(starting from line number 251):
…
with the following code:
if when: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s AND time=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'filename' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s AND time=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'description' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s AND time=%s " "ORDER BY time", (self.id, when, str(self.id), when, self.id, when)) else: cursor.execute("SELECT time,author,field,oldvalue,newvalue " "FROM ticket_change WHERE ticket=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'filename' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s " "UNION " "SELECT _utf8 'time' COLLATE utf8_general_ci,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,_utf8 'null' COLLATE utf8_general_ci,_utf8 'description' COLLATE utf8_general_ci " "FROM attachment WHERE id=%s " "ORDER BY time", (self.id, str(self.id), self.id))
hope this will help (see patch # 3030)
I found that this worked great until I added an attachment to tickets and then I had problems. ValueError: invalid literal for int(): time
The following changes to the above code worked for me. I basically removed the conversions to utf8 for time, and null values.
cursor.execute("SELECT time,author,field,oldvalue,newvalue,1 " "FROM ticket_change WHERE ticket=%s AND time=%s " "UNION " "SELECT time,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,null,_utf8 'filename' COLLATE utf8_general_ci,0 " "FROM attachment WHERE id=%s AND time=%s " "UNION " "SELECT time,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,null,_utf8 'description' COLLATE utf8_general_ci,0 " "FROM attachment WHERE id=%s AND time=%s " "ORDER BY time", (self.id, when, str(self.id), when, self.id, when)) else: cursor.execute("SELECT time,author,field,oldvalue,newvalue,1 " "FROM ticket_change WHERE ticket=%s " "UNION " "SELECT time,_utf8 'author' COLLATE utf8_general_ci,_utf8 'attachment' COLLATE utf8_general_ci,null,_utf8 'filename' COLLATE utf8_general_ci,0 " "FROM attachment WHERE id=%s " "UNION " "SELECT time,_utf8 'author' COLLATE utf8_general_ci,_utf8 'comment' COLLATE utf8_general_ci,null,_utf8 'description' COLLATE utf8_general_ci,0 " "FROM attachment WHERE id=%s " "ORDER BY time", (self.id, str(self.id), self.id))
This is a duplicate of [126].
For questions, please post to the MailingList.