flipturn.org - stuff. things. other stuff.
dvorak | pantera | hacks

TracOnDreamhost

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.

DreamHost setup

SVN setup

  • Added a svn subdomain through the Dreamhost control panel.
  • Created new subversion repository for the project pointing to the subdomain

Trac subdomain

  • Added a trac subdomain using the Dreamhost control panel, and enabled FastCGI

MySQL setup

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.

Installing Trac following the natmaster.com guide

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.

Trac 0.10.3

as in the guide, except --prefix=$HOME

Clearsilver 0.10.4

as in the guide except --prefix=$HOME

SQLite

don't need this

PySQLite

don't need this

SWIG 1.3.31

as in the guide except --prefix=$HOME

Installing subversion 1.4.2

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

  • ./configure --prefix=$HOME --with-swig=$HOME/bin/swig --without-berkeley-db --with-ssl --with-zlib --enable-shared

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.

MySQL

You don't need to install MySQL yourself; DreamHost provides a sufficiently current version in /usr.

mysql-python 1.2.1_p2

The MySQL include files are in /usr/include and will get picked up by the install process

  • cd ~/install_files
  • wget http://downloads.sourceforge.net/mysql-python/MySQL-python-1.2.1_p2.tar.gz
  • tar zxf MySQL-python-1.2.1_p2.tar.gz
  • cd MySQL-python-1.2.1_p2
  • python setup.py install --prefix=$HOME

Setting up the Trac Environment

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:

  • trac-admin $HOME/trac_sites/{projname}-mysql initenv

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.

  • Project Name: {Whatever you want}
  • Database connection string: mysql://{dbuser}:{dbpass}@{mysql.your.hostname}/trac
  • Type of version control: {Accept the default (just hit return)}
  • Path to repository: /home/{yourusername}/svn/{your svn project id}/
  • Templates directory: {Accept the default (just hit return)}

The trac-admin script will then initialize the table structure in the database.

If you're installing a new Trac site and don't have an sqlite database to convert, skip to the Trac modifications section below.

Copying the old configuration

Copy the trac.ini from your old environment to the new one with:

  • cd ~/trac_sites
  • cp {projname}/conf/trac.ini {projname}-mysql/conf/trac.ini

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.

Convert the sqlite database to mysql

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:

  • cd ~/trac_sites
  • echo ".dump"|sqlite3 {projname}/db/trac.db>trac.sqlite
  • perl cleansqlite.pl trac.sqlite>trac.sqlite.dataonly
  • mysqldump -u {dbuser} -p -h {mysql.your-hostname.com} trac --no-data>trac.mysql
  • cat trac.mysql trac.sqlite.dataonly >trac.loadmysql
  • mysql -u {dbuser} -p -h {mysql.your-hostname.com} trac<trac.loadmysql

Trac modifications

Working around the database charset error

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.

Patching Trac

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

Webserver configuration

FCGI config

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.

Macros

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.

Subversion post-commit-hook

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.

MySQL Report Errors

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.

erection pills california erection pills california erection pills california erection pills new york
levitra over the counter sale cialis online cialis online sale cialis withoutperscription