A while back I had some problems with MySQL running out of space on /var. Today I found out my Gallery2 was down, responding to all requests with “An error has occurred while interacting with the database.”

In the config.php file in the gallery2 install folder, there’s a line that sets the debug mode. By default it’s:

$gallery->setDebug(false);

For debugging I changed it to:

$gallery->setDebug('buffered'); 

and reloaded the Gallery main page. That displays a shitload of debug messages at the end of the page. In my case, I saw the familiar MySQL message about a table being corrupted. This time, it was g2_CacheMap. I did a quick repair table g2_CacheMap in mysql, and voila!, Gallery is back up.

Last weekend my problem with disk space exhaustion resurfaced, and seemed to upset MySQL, as I get the following warning when I log into Drupal:

user warning: Table ‘./drupal/watchdog’ is marked as crashed and should be repaired query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, ‘user’, ‘Session opened for root.’, 0, ”, ‘http://apocryph.org/?q=user/login’, ‘http://apocryph.org/?q=user/login’, ‘65.89.8.127’, 1153755477) in /usr/local/www/drupal/includes/database.mysql.inc on line 120.

This morning I noticed the /var filesystem was at 100% capacity on bonzo, leaving MySQL somewhat hosed. Upon poking around in /var (which, sadly, I only made about 300MB) I removed a CVSUp catalog for the FreeBSD src tree that was taking up alot of space, and with the time that bought, sought a permanent fix.

First, I see three big files in /var/db/mysql: ib_logfile0, ib_logfile1, and ibdata1. Upon a bit of googling, I found these were the tablespace and log files for the InnoDB storage engine in MySQL, somewhat akin to the data and log device files SQL Server uses.

The problem, though, is that none of my databases use InnoDB. The MyISAM storage engine is fine for my needs, and considerably less complicated. So, I poked around for the my.cnf config file that’s supposed to be in /var/db/mysql, but it’s not there, suggesting the InnoDB stuff was compiled in. At any rate, I moved the files elsewhere and made sure my databases were still working, which they are if you’re reading this.

Recently I’ve tried to teach the Iraqi developers about the ASP.NET GridView, and how easy it makes displaying and editing multiple records. Unfortunately, we’ve had a helluva time making it work with MySQL.

First, we were using the ODBC.Net ADO.NET provider, and the 3.52 MySQL ODBC driver. This works fine, except the MySQL ODBC driver doesn’t support Unicode, a minor detail which means Arabic script is turned into garbage en route to the database! Supposedly, Unicode support is coming in the next version of the driver, discontinuously numbered ‘5.0’, which has been in development for over 1.5 years and is still in alpha; I was unable to get it to even install on my laptop without cryptic errors about missing files.

I’m going to use MySQL 5 on aenea, so I thought this would be a good time to try out phpMyAdmin, to see what all the fuss is about.

First, I’ve installed and got working both Apache 2 and PHP 5, both via their respective ports.

I next installed MySQL 5 server and phpMyAdmin via their ports. I enabled MySQL by adding mysql_enable="YES" to /etc/rc.conf. I started it with /usr/local/etc/rc.d/mysql-server.sh start.

To get phpMyAdmin started, I added an alias to the /usr/local/www/phpMyAdmin folder created by the port, so requests for /phpmyadmin would resolve there. To do that was a trivial task for Alias in /usr/local/etc/apache2/httpd.conf:

I’ve been diligently backing up bonzo using qdb.pl, my homegrown backup script. bonzo is a very important machine, as he stores my entire digital image archive, my Subversion repository, MySql databases containing Drupal and Gallery2 data, and 24-hour evaluation copies of large software packages. However, I realized somewhat belatedly that my MySql databases were not being backed up as part of my backup regime.

I’ve decided on a two-layer backup approach to backing up MySql:

• First, I’ll backup the MySql ISAM files in /var/db/mysql. Since I only use MyISAM and not the more advanced MySql storage options, this will be sufficient most of the time. However, in the off chance that a backup runs in the middle of a MySql transaction, the ISAM files may be in an inconsistent state. Which leads me to the second layer…