Trac Collation Error with the Full Blog Plugin's Categories

This probably won't come up for many people since I suspect this is related to the [blog:deploying-trac-on-shared-hosting oddities involved with setting up trac] on my hosting provider. But in case it does... here's the details.

When going to a Trac FullBlogPlugin category link I was getting the following error:

Trac detected an internal error:    
InternalError: (1253, u"COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8'")

This wasn't hard to fix, but did take a couple of steps.

First... dump the database... just in case you mess something up later.

mysqldump -u trac -p trac > trac.backup

Then dump again and convert from latin1 to utf8. The details on this were found here.

mysqldump --add-drop-table -u trac -p trac | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 > tmp.trac.dump

Now we restore the converted data:

mysql -u trac -p trac < tmp.trac.dump

And lastly... we need to change the default character set and collation on the database:

mysql -u trac -p trac  > ALTER DATABASE trac DEFAULT CHARACTER SET = 'utf8';  > ALTER DATABASE trac DEFAULT COLLATE = 'utf8_general_ci';

That seems to have fixed the problem.