Edgewall Software
Modify

Opened 13 years ago

Closed 11 years ago

Last modified 11 years ago

#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@…
Release Notes:
API 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)

trac_mysql_r2625.patch (10.4 KB ) - added by trac@… 12 years ago.
patch against r2625 to enable experimental MySQL support
trac_mysql_r2628.patch (10.4 KB ) - added by trac@… 12 years ago.
patch now reflects suggestion by mgood. Reverted database modification for SQLite and Postgres, only affects MySQL for AUTO_INCREMENT fields. Patch is against r2628.
trac_mysql_r2636.patch (10.4 KB ) - added by trac@… 12 years ago.
patch now reflects fix problem indicated by cboos
trac_mysql_r2636.2.patch (10.4 KB ) - added by trac@… 12 years ago.
fixes previous breaks on unit tests
trac_mysql_r2644.patch (27.4 KB ) - added by trac@… 12 years ago.
patch contains suggestions of cmlenz, against r2644.
trac_mysql_r2659.patch (27.2 KB ) - added by trac@… 12 years ago.
new patch against [2659] because the updates in [2653] caused some conflicts
db-changes-986-r2865.diff (30.9 KB ) - added by Manuzhai 12 years ago.
Changes the DB definition, upgrades format.
db-changes-986-r2865.2.diff (28.9 KB ) - added by Manuzhai 12 years ago.
Replace existing patch by improved version.
trac-db-changes-r2881.patch (5.7 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
updated db-changes patch against 2881
trac-sqlcolfix-r2944.patch (6.0 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
rename 'sql' column to 'query' in report table; against r2944
trac-sqlcolfix-r2944.2.patch (5.1 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
rename 'sql' column in report table, w/ add. bugfix; against r2944
trac-mysql-r2944.patch (13.4 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
updated mysql patch, against r2944
trac-mysql-r2944.2.patch (11.7 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
mysql patch w/ index creation cleaned up; against r2944.
trac-mysql-r2946.patch (11.8 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
updated patch that works w/ both mysql 4.0.x and 5.0.x; against r2946
trac-mysql-r2946.2.patch (11.9 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
mysql patch w/ bugfix for deleting sessions; only run delete query when there's actually something to delete.
trac-mysql-r2975.patch (15.7 KB ) - added by anonymous 12 years ago.
updated mysql patch, w/ additional mysql4.0 fixes; against r2975.
trac-mysql-r3029.patch (27.9 KB ) - added by Andres Salomon <dilinger@…> 12 years ago.
updated mysql patch
trac-mysql-r3030.patch (30.4 KB ) - added by amgad Mosleh <amgad.mosleh@…> 12 years ago.
solved the problem of adding or viewing a ticket (the Illegal mix of collations )
mysql_charset_patch.diff (1.5 KB ) - added by Jim Nanney <jnanney@…> 11 years ago.
diff for patching my changes above

Download all attachments as: .zip

Change History (81)

comment:1 Changed 13 years ago by Christopher Lenz

Resolution: duplicate
Status: newclosed

This is a duplicate of [126].

For questions, please post to the MailingList.

comment:2 Changed 13 years ago by Christopher Lenz

Errm, a duplicate of #126, of course.

comment:3 Changed 12 years ago by trac@…

Resolution: duplicate
Status: closedreopened
Version: 0.8devel

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!

Changed 12 years ago by trac@…

Attachment: trac_mysql_r2625.patch added

patch against r2625 to enable experimental MySQL support

comment:4 Changed 12 years ago by Christopher Lenz

Summary: MySQL/Postgresql database supportMySQL database support

comment:5 Changed 12 years ago by Matthew Good

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 Changed 12 years ago by Christian Boos

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

Changed 12 years ago by trac@…

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 Changed 12 years ago by trac@…

Cc: trac@… added

comment:8 Changed 12 years ago by Christian Boos

Keywords: mysql added
Milestone: 1.0
Priority: normalhigh
Severity: normalmajor

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)

Changed 12 years ago by trac@…

Attachment: trac_mysql_r2636.patch added

patch now reflects fix problem indicated by cboos

comment:9 Changed 12 years ago by 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??

comment:10 Changed 12 years ago by Christian Boos

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

Changed 12 years ago by trac@…

Attachment: trac_mysql_r2636.2.patch added

fixes previous breaks on unit tests

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

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

comment:14 Changed 12 years ago by Christopher Lenz

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.

Changed 12 years ago by trac@…

Attachment: trac_mysql_r2644.patch added

patch contains suggestions of cmlenz, against r2644.

Changed 12 years ago by trac@…

Attachment: trac_mysql_r2659.patch added

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

comment:15 Changed 12 years ago by Christopher Lenz

Milestone: 1.00.10

comment:16 Changed 12 years ago by Manuzhai

Cc: manuzhai@… added

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

Changed 12 years ago by Manuzhai

Attachment: db-changes-986-r2865.diff added

Changes the DB definition, upgrades format.

comment:18 Changed 12 years ago by 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?

comment:19 Changed 12 years ago by Christian Boos

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

comment:20 Changed 12 years ago by Manuzhai

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

Changed 12 years ago by Manuzhai

Attachment: db-changes-986-r2865.2.diff added

Replace existing patch by improved version.

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

comment:22 Changed 12 years ago by Christopher Lenz

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 Changed 12 years ago by Manuzhai

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

I will correct the patch tomorrow.

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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-db-changes-r2881.patch added

updated db-changes patch against 2881

comment:25 Changed 12 years ago by Christopher Lenz

Patch applied in [2882]. Thanks guys!

Now on to the actual MySQL connector…

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

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

comment:28 Changed 12 years ago by Christopher Lenz

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

comment:29 Changed 12 years ago by (none)

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 Changed 12 years ago by 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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-sqlcolfix-r2944.patch added

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

Changed 12 years ago by Andres Salomon <dilinger@…>

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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-mysql-r2944.patch added

updated mysql patch, against r2944

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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-mysql-r2944.2.patch added

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

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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-mysql-r2946.patch added

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

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

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

comment:35 Changed 12 years ago by Andres Salomon <dilinger@…>

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

Changed 12 years ago by Andres Salomon <dilinger@…>

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.

Changed 12 years ago by anonymous

Attachment: trac-mysql-r2975.patch added

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

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

comment:37 Changed 12 years ago by Matthew Good

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

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

comment:40 Changed 12 years ago by Andres Salomon <dilinger@…>

Cc: dilinger@… added

comment:41 Changed 12 years ago by Christopher Lenz

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 Changed 12 years ago by 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…

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

Changed 12 years ago by Andres Salomon <dilinger@…>

Attachment: trac-mysql-r3029.patch added

updated mysql patch

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

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

Mysql5.0.19
Trac0.10 dev - patch# 3029
Subversion1.3.0
OsWinXP Prof 2002
Python2.4

Changed 12 years ago by amgad Mosleh <amgad.mosleh@…>

Attachment: trac-mysql-r3030.patch added

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

comment:46 Changed 12 years ago by 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)

comment:47 Changed 12 years ago by anonymous

Cc: jjb@… added

comment:48 Changed 12 years ago by anonymous

Cc: soloturn@… added

comment:49 Changed 12 years ago by Christopher Lenz

Owner: changed from Jonas Borgström to Christopher Lenz
Status: reopenednew

comment:50 Changed 12 years ago by Christopher Lenz

Status: newassigned

I'm working on integrating the patches now.

comment:51 Changed 12 years ago by Christopher Lenz

Resolution: fixed
Status: assignedclosed

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 Changed 12 years ago by anonymous

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

comment:53 Changed 11 years ago by 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

comment:54 Changed 11 years ago by Jim Nanney <jnanney@…>

Oh and all those changes were in mysql_backend.py

Changed 11 years ago by Jim Nanney <jnanney@…>

Attachment: mysql_charset_patch.diff added

diff for patching my changes above

comment:55 Changed 11 years ago by anonymous

Resolution: fixed
Status: closedreopened

eqrtetwert

comment:56 in reply to:  description Changed 11 years ago by 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

comment:57 Changed 11 years ago by anonymous

Resolution: fixed
Status: reopenedclosed

comment:58 Changed 11 years ago by Christian Boos

Funny how the database related tickets attract TicketButchers …

comment:59 Changed 11 years ago by Christian Boos

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

comment:60 in reply to:  54 ; Changed 11 years ago by 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?

comment:61 in reply to:  60 Changed 11 years ago by Christian Boos

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 in reply to:  46 Changed 11 years ago by 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))

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christopher Lenz.
The resolution will be deleted.
to The owner will be changed from Christopher Lenz 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.