MySQL database support

[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?

[cmlenz]

This is a duplicate of [126].

For questions, please post to the MailingList.

[cmlenz]

Errm, a duplicate of #126, of course.

[trac@…]

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!

[trac@…]

patch against r2625 to enable experimental MySQL support

[mgood]

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.

[cboos]

I've tested the patch with SQLite: nothing seems to be broken. In particular search, initenv are working as usual.

[trac@…]

patch now reflects suggestion by mgood. Reverted database modification for SQLite and Postgres, only affects MySQL for AUTO_INCREMENT fields. Patch is against r2628.

[cboos]

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)

[trac@…]

patch now reflects fix problem indicated by cboos

[trac@…]

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

[cboos]

For me, `change` was not a problem with PySqlite. Are you using 1.1.6? I'll have a closer look at that.

[trac@…]

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.

[trac@…]

fixes previous breaks on unit tests

[trac@…]

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.

[trac@…]

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.

[cmlenz]

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 to change_type, and
• rename kind to node_type

and then provide an upgrade script, of course.

[trac@…]

patch contains suggestions of cmlenz, against r2644.

[trac@…]

new patch against [2659] because the updates in [2653] caused some conflicts

[Manuzhai]

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.

[Manuzhai]

Changes the DB definition, upgrades format.

[Manuzhai]

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?

[cboos]

You mean, you don't have the above errors using the unpatched r2865, but only after applying the patch, right?

[Manuzhai]

Good point. The 2 log-errors are there without the patch, the last one is not.

[Manuzhai]

Replace existing patch by improved version.

[Manuzhai]

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.

[cmlenz]

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.

[Manuzhai]

Okay, so also not the Python variables in the loops and such?

I will correct the patch tomorrow.

[Andres Salomon <dilinger@…>]

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.

[Andres Salomon <dilinger@…>]

updated db-changes patch against 2881

[cmlenz]

Patch applied in [2882]. Thanks guys!

Now on to the actual MySQL connector...

[Andres Salomon <dilinger@…>]

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

[Andres Salomon <dilinger@…>]

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.)."

[cmlenz]

Django's MySQL adapter uses LIKE BINARY %s for case-sensitive searches.

[anonymous]

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)

[Andres Salomon <dilinger@…>]

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

[Andres Salomon <dilinger@…>]

rename 'sql' column to 'query' in report table; against r2944

[Andres Salomon <dilinger@…>]

rename 'sql' column in report table, w/ add. bugfix; against r2944

[Andres Salomon <dilinger@…>]

updated mysql patch, against r2944

[Andres Salomon <dilinger@…>]

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

[Andres Salomon <dilinger@…>]

mysql patch w/ index creation cleaned up; against r2944.

[Andres Salomon <dilinger@…>]

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.

[Andres Salomon <dilinger@…>]

updated patch that works w/ both mysql 4.0.x and 5.0.x; against r2946

[Manuzhai]

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.

[Andres Salomon <dilinger@…>]

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).

[Andres Salomon <dilinger@…>]

People applying this patch are also probably interested in #2822, if they'd like to edit wiki pages.

[Andres Salomon <dilinger@…>]

mysql patch w/ bugfix for deleting sessions; only run delete query when there's actually something to delete.

[anonymous]

updated mysql patch, w/ additional mysql4.0 fixes; against r2975.

[Andres Salomon <dilinger@…>]

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.

[mgood]

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?

[Andres Salomon <dilinger@…>]

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.

[Andres Salomon <dilinger@…>]

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).

[cmlenz]

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.

[test@…]

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

[Manuzhai]

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.

[Andres Salomon <dilinger@…>]

updated mysql patch

[Andres Salomon <dilinger@…>]

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.

[amgad.mosleh@…]

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

[amgad Mosleh <amgad.mosleh@…>]

solved the problem of adding or viewing a ticket (the Illegal mix of collations )

[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):

        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)

[cmlenz]

I'm working on integrating the patches now.

[cmlenz]

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.

[anonymous]

latin1_swedish_ci collation seems to give the most natural sort order for many languages.

[jnanney@…]

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

[Jim Nanney <jnanney@…>]

Oh and all those changes were in mysql_backend.py

[Jim Nanney <jnanney@…>]

diff for patching my changes above

[anonymous]

eqrtetwert

[anonymous]

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

[cboos]

Funny how the database related tickets attract TicketButchers ...

[cboos]

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

[james.a.stewart@…]

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?

[cboos]

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).

[anonymous]

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