Tag: mysql

phpMyAdmin with mod_fcgid

I am currently migrating my server configuration away from mod_php towards mod_fcgid (the successor of mod_fastcgi), as this allows me to use different users for executing scripts in different directories. I use this to have every hosted virtual domain using its own system user. This should (in theory) prevent one buggy application to take over all other hosted domains as well.

I though faced one problem: I could not get phpMyAdmin working and this was a requirement of one of my clients. phpMyAdmin kept popping up the authentication dialog over and over again when using HTTP Basic Authentication.

After searching some time, I noticed that, when using PHP in CGI mode, the authentication data is not passed over to the script by default. A FAQ entry of phpMyAdmin brought the solution to this issue: a ReWrite Rule was needed for the directory containing phpMyAdmin:

RewriteEngine On
RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization},L]

Suddenly phpMyAdmin worked 😉

Bugzilla: Upgrading MySQL Database from Latin1 to UTF8

IMPORTANT Update 2009-07-09: This information is already several years old! You should not use this information for modern versions of Bugzilla (3.2 and above), which will allow you to convert to UTF-8 using checksetup.pl.

In the present case, we have a Bugzilla database created with a charset of latin1. Unfortunately, now after updating MySQL to 4.1, error occured when trying to assign a new developer to a bug, indicating mismatch of collations (UTF-8 vs. Latin1). This is caused by the fact that there is now latin1-data stored in an UTF-8 table.

The following procedure can be used to upgrade the database to UTF-8, eliminating the problem:

  1. mysqldump -p --default_character-set=latin1 --skip-set-charset bugs > dump.sql
  2. mysql -p --execute="DROP DATABASE bugs; CREATE DATABASE bugs CHARACTER SET utf8 COLLATE utf8_general_ci;"
  3. mysql -p --default-character-set=utf8 bugs < dump.sql
  4. perl -pe 's/latin1_bin/utf8_general_ci/g; s/latin1/utf8/g' dump.sql > dump-utf8.sql
  5. mysql -p --default-character-set=utf8 bugs < dump-utf8.sql

You should of course always check if the Pearl-RegEx only replaced charset declarations and not some matches within the data.

Thanks to TextSnippets for the script.