Edgewall Software
Modify

Opened 14 years ago

Closed 13 years ago

Last modified 12 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@… Branch:
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@… 13 years ago.
patch against r2625 to enable experimental MySQL support
trac_mysql_r2628.patch (10.4 KB ) - added by trac@… 13 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@… 13 years ago.
patch now reflects fix problem indicated by cboos
trac_mysql_r2636.2.patch (10.4 KB ) - added by trac@… 13 years ago.
fixes previous breaks on unit tests
trac_mysql_r2644.patch (27.4 KB ) - added by trac@… 13 years ago.
patch contains suggestions of cmlenz, against r2644.
trac_mysql_r2659.patch (27.2 KB ) - added by trac@… 13 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 13 years ago.
Changes the DB definition, upgrades format.
db-changes-986-r2865.2.diff (28.9 KB ) - added by Manuzhai 13 years ago.
Replace existing patch by improved version.
trac-db-changes-r2881.patch (5.7 KB ) - added by Andres Salomon <dilinger@…> 13 years ago.
updated db-changes patch against 2881
trac-sqlcolfix-r2944.patch (6.0 KB ) - added by Andres Salomon <dilinger@…> 13 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@…> 13 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@…> 13 years ago.
updated mysql patch, against r2944
trac-mysql-r2944.2.patch (11.7 KB ) - added by Andres Salomon <dilinger@…> 13 years ago.
mysql patch w/ index creation cleaned up; against r2944.
trac-mysql-r2946.patch (11.8 KB ) - added by Andres Salomon <dilinger@…> 13 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@…> 13 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 13 years ago.
updated mysql patch, w/ additional mysql4.0 fixes; against r2975.
trac-mysql-r3029.patch (27.9 KB ) - added by Andres Salomon <dilinger@…> 13 years ago.
updated mysql patch
trac-mysql-r3030.patch (30.4 KB ) - added by amgad Mosleh <amgad.mosleh@…> 13 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@…> 13 years ago.
diff for patching my changes above

Download all attachments as: .zip

Change History (81)

comment:1 by Christopher Lenz, 14 years ago

Resolution: duplicate
Status: newclosed

This is a duplicate of [126].

For questions, please post to the MailingList.

comment:2 by Christopher Lenz, 14 years ago

Errm, a duplicate of #126, of course.

comment:3 by trac@…, 13 years ago

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!

by trac@…, 13 years ago

Attachment: trac_mysql_r2625.patch added

patch against r2625 to enable experimental MySQL support

comment:4 by Christopher Lenz, 13 years ago

Summary: MySQL/Postgresql database supportMySQL database support

comment:5 by Matthew Good, 13 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 Christian Boos, 13 years ago

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

by trac@…, 13 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 trac@…, 13 years ago

Cc: trac@… added

comment:8 by Christian Boos, 13 years ago

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)

by trac@…, 13 years ago

Attachment: trac_mysql_r2636.patch added

patch now reflects fix problem indicated by cboos

comment:9 by trac@…, 13 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 Christian Boos, 13 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 trac@…, 13 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.

by trac@…, 13 years ago

Attachment: trac_mysql_r2636.2.patch added

fixes previous breaks on unit tests

comment:12 by trac@…, 13 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 trac@…, 13 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 Christopher Lenz, 13 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 to change_type, and
  • rename kind to node_type

and then provide an upgrade script, of course.

by trac@…, 13 years ago

Attachment: trac_mysql_r2644.patch added

patch contains suggestions of cmlenz, against r2644.

by trac@…, 13 years ago

Attachment: trac_mysql_r2659.patch added

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

comment:15 by Christopher Lenz, 13 years ago

Milestone: 1.00.10

comment:16 by Manuzhai, 13 years ago

Cc: manuzhai@… added

comment:17 by Manuzhai, 13 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 Manuzhai, 13 years ago

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

Changes the DB definition, upgrades format.

comment:18 by Manuzhai, 13 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 Christian Boos, 13 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 Manuzhai, 13 years ago

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

by Manuzhai, 13 years ago

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

Replace existing patch by improved version.

comment:21 by Manuzhai, 13 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 Christopher Lenz, 13 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 Manuzhai, 13 years ago

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

I will correct the patch tomorrow.

comment:24 by Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 years ago

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

updated db-changes patch against 2881

comment:25 by Christopher Lenz, 13 years ago

Patch applied in [2882]. Thanks guys!

Now on to the actual MySQL connector…

comment:26 by Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 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 Christopher Lenz, 13 years ago

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

comment:29 by (none), 13 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 Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 years ago

Attachment: trac-sqlcolfix-r2944.patch added

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

by Andres Salomon <dilinger@…>, 13 years ago

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

by Andres Salomon <dilinger@…>, 13 years ago

Attachment: trac-mysql-r2944.patch added

updated mysql patch, against r2944

comment:31 by Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 years ago

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

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

comment:32 by Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 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 Manuzhai, 13 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 Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 years ago

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

by Andres Salomon <dilinger@…>, 13 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 anonymous, 13 years ago

Attachment: trac-mysql-r2975.patch added

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

comment:36 by Andres Salomon <dilinger@…>, 13 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 Matthew Good, 13 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 Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 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 Andres Salomon <dilinger@…>, 13 years ago

Cc: dilinger@… added

comment:41 by Christopher Lenz, 13 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 test@…, 13 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 Manuzhai, 13 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.

by Andres Salomon <dilinger@…>, 13 years ago

Attachment: trac-mysql-r3029.patch added

updated mysql patch

comment:44 by Andres Salomon <dilinger@…>, 13 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 amgad.mosleh@…, 13 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:

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

by amgad Mosleh <amgad.mosleh@…>, 13 years ago

Attachment: trac-mysql-r3030.patch added

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

comment:46 by anonymous, 13 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 anonymous, 13 years ago

Cc: jjb@… added

comment:48 by anonymous, 13 years ago

Cc: soloturn@… added

comment:49 by Christopher Lenz, 13 years ago

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

comment:50 by Christopher Lenz, 13 years ago

Status: newassigned

I'm working on integrating the patches now.

comment:51 by Christopher Lenz, 13 years ago

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 by anonymous, 13 years ago

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

comment:53 by jnanney@…, 13 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:54 by Jim Nanney <jnanney@…>, 13 years ago

Oh and all those changes were in mysql_backend.py

by Jim Nanney <jnanney@…>, 13 years ago

Attachment: mysql_charset_patch.diff added

diff for patching my changes above

comment:55 by anonymous, 13 years ago

Resolution: fixed
Status: closedreopened

eqrtetwert

in reply to:  description comment:56 by anonymous, 13 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 anonymous, 13 years ago

Resolution: fixed
Status: reopenedclosed

comment:58 by Christian Boos, 13 years ago

Funny how the database related tickets attract TicketButchers …

comment:59 by Christian Boos, 13 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

in reply to:  54 ; comment:60 by james.a.stewart@…, 12 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?

in reply to:  60 comment:61 by Christian Boos, 12 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).

in reply to:  46 comment:62 by anonymous, 12 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))

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.