UPDATE: During my vacation I was hammered with Trac spam, so I wrote a little tool to DeSpamTrac.
UPDATE: I have now upgraded to TracElevenOnDreamhost
This is a small howto on setting up Trac on your DreamHost account, using MySQL instead of SQLite as the database backend.
I was having database locking issues with SQLite -- moving to MySQL resolved that, and as a bonus the MySQL backend feels faster. The bulk of the setup is taken from the natmaster.com guide (fellow UT alum -- hook 'em horns); I've shown the modifications that I needed to get MySQL working as well.
Create a mysql database named trac through the DreamHost mysql panel, and in the First user section create a username (referred to as dbuser below) and password dbpass that will be used for database access. I created a mysql hostname like mysql.your-hostname.com, so just note the hostname that you created.
Initially, I tried installing trac 0.10.3 using python2.4, and ran into trouble with the easy install step. It wouldn't find any Trac plugins, and it was hard to debug.
Python 2.4 also seemed to be slower to initialize than python 2.3, so I went back to the default python, Python 2.3. I followed the natmaster.com guide except that I installed everything with --prefix=$HOME instead of --prefix=$HOME/packages, so all the steps below will reflect that install prefix.
as in the guide, except --prefix=$HOME
as in the guide except --prefix=$HOME
don't need this
don't need this
as in the guide except --prefix=$HOME
DreamHost recently upgraded the default subversion to 1.4.2, but you still need to compile it yourself because it looks like the python libraries haven't been updated.
I also had to add --enable-shared to the configure command, i.e.:
because during my first attempt it failed with:
Traceback (most recent call last):
File "<string>", line 1, in ?
File "/home/robm351/lib/svn-python/svn/client.py", line 19, in ?
from libsvn.client import *
File "/home/robm351/lib/svn-python/libsvn/client.py", line 5, in ?
import _client
ImportError: /home/robm351/lib/libsvn_ra_dav-1.so.0: undefined symbol: SSL_load_error_strings
Googling turned up that neon had a dependency ordering problem with libssl. For some reason, neon was loaded before libssl, causing that error. A workaround was to add LD_PRELOAD=libssl.so to the environment, which forces libssl to be loaded early in the process (before neon, at least) which allows it to work. I found another pointer which says to compile your own version of libssl and neon, where you point neon to the new version of libssl and then point subversion to use the already compiled version of neon. But, I found that simply forcing the shared library worked.
You don't need to install MySQL yourself; DreamHost provides a sufficiently current version in /usr.
The MySQL include files are in /usr/include and will get picked up by the install process
Since I was converting from an SQLite database to the MySQL database, I wanted to make sure that I didn't overwrite my SQLite data. To be safe, I created a new environment in trac_sites called ~/trac_sites/{projname}-mysql/ and left the sqlite environment intact in ~/trac_sites/{projname}/. Create the environment with:
and if you're starting from scratch, answer the questions as follows. If you're converting an existing SQLite environment, the important thing is the database connection string as that needs to point to the MySQL database in order to configure the tables. In the next step we'll be copying and modifying the old trac.ini file.
The trac-admin script will then initialize the table structure in the database.
Copy the trac.ini from your old environment to the new one with:
and in the [trac] section, change the database connection string and add the database_charset parameter:
database = mysql://{dbuser}:{dbpass}@{mysql.your.hostname}/trac
database_charset = latin1
The database_charset parameter is a workaround to a problem that we'll need to patch. See below.
To convert sqlite data, instructions on the trac wiki say to get a copy of the stuff in the sqlite database and repopulate the mysql database. That same trac wiki entry has a perl script to modify the sqlite databases. I found that the script wouldn't quite work and needed some additional help. Here's the full script I called cleansqlite.pl:
#!/usr/bin/env perl
while (<>) {
$i = $i . $_;
}
*{{{i =~ s/^CREATE TABLE.*?\);$//smgi;}}}
*{{{i =~ s/^BEGIN TRANSACTION;$//smgi;}}}
*{{{i =~ s/^COMMIT;$//smgi;}}}
*{{{i =~ s/INSERT INTO "(.*)" VALUES/INSERT INTO \1 VALUES/g;}}}
print $i;
Once that script is ready, here are the steps to convert the sqlite database into your mysql database:
On my installation, I got this error:
OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION'")
which seems to show that there's a mismatch in the character set. Apparently the mysql tables are created in latin1 by default, but trac uses utf8 to search. Changing the mysql table creation to utf8 results in
ERROR 1071 (42000) at line 23: Specified key was too long; max key length is 1000 bytes
which according to this mysql bug is because the combined primary key `type`(166),`id`(166),`filename`(166) takes more than 1000 characters. Somewhere I thought I saw that mysql allocates 4 bytes per utf8 character automatically in order to account for the possibility of all 4-byte characters, but I can't find that reference now.
The trac ticket #2873 points to ticket #3182 that has a patch for ~/lib/python2.3/site-packages/trac/db/mysql_backend.py. This patch adds a keyword to the trac.ini that forces trac to use the latin1 encoding:
Index: mysql_backend.py
===================================================================
--- mysql_backend.py (revision 3883)
+++ mysql_backend.py (working copy)
@@ -19,6 +19,7 @@
from trac.core import *
from trac.db.api import IDatabaseConnector
from trac.db.util import ConnectionWrapper
+from trac.config import Option
_like_escape_re = re.compile(r'([/_%])')
@@ -100,6 +101,9 @@
"""Connection wrapper for MySQL."""
poolable = True
+
+ charset = Option('trac', 'database_charset', 'utf8',
+ """Database charset for the connection""")
def _mysqldb_gt_or_eq(self, v):
"""This function checks whether the version of python-mysqldb
@@ -139,11 +143,11 @@
# on 1.2.1 made. -dilinger
if (self._mysqldb_gt_or_eq((1, 2, 1))):
cnx = MySQLdb.connect(db=path, user=user, passwd=password,
- host=host, port=port, charset='utf8')
+ host=host, port=port, charset=self.charset)
else:
cnx = MySQLdb.connect(db=path, user=user, passwd=password,
host=host, port=port, use_unicode=True)
- self._set_character_set(cnx, 'utf8')
+ self._set_character_set(cnx, self.charset)
ConnectionWrapper.__init__(self, cnx)
def cast(self, column, type):
Change the TRAC_ENV line in your fcgi (or cgi) script to your new trac environment directory:
export TRAC_ENV="$HOME/trac_sites/{projname}-mysql"
After killing all your fastcgi processes, you should have a trac environment working with your new mysql database.
I found a roadmap ticket lister on trac-hacks that I modified slightly to allow no keywords. Amazingly, just placing the python source code in ~/trac_sites/{project}/wiki-macros worked, and apache even picked it up without killing the fcgi process.
Using svn hooks, trac tickets can be updated to automatically add a comment to (and even close) the ticket based on the text of the svn commit. Trac has a post-commit-hook in its contrib directory that is a starting point.
To use it, place the script in the ~/svn/{project}/hooks directory as trac-post-commit-hook.py, and you'll have to create a file named post-commit that has some environmental settings:
#!/bin/bash
REPOS="$1"
REV="$2"
LOG=`/usr/bin/svnlook log -r $REV $REPOS`
AUTHOR=`/usr/bin/svnlook author -r $REV $REPOS`
TRAC_ENV='/home/{user}/trac_sites/{projname}-mysql/'
TRAC_URL='http://trac.flipturn.org/'
export PYTHONPATH='/home/{user}/lib:/home/{user}/lib/python2.3/site-packages'
/usr/bin/python /home/{user}/svn/{project}/hooks/trac-post-commit-hook.py \
-p "$TRAC_ENV" \
-r "$REV" \
-u "$AUTHOR" \
-m "$LOG" \
-s "$TRAC_URL"
I ran into problems where it would work from my user name, but not from the apache user. Turns out that the log file didn't have the correct permissions, so I had to do a chmod 666 ~/trac_sites/{projname}-mysql/log/trac.log before it would work. The other option might just be to turn off logging.
Ticket reports 6, 7, and 8 fail when using MySQL with an error similar to:
Report execution failed: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'rob', ')' THEN 'My Tickets' \r\sn ELSE 'Active Tickets' \r\sn END) AS __group' at line 3")
because apparently the python code puts an additional set of quote characters around the parameter $USER when generating the SQL. To fix this problem, simply click on the "Edit Report" button and edit the SQL call by replace all occurrances of '$USER' with $USER.