More MySQL and disk full problems
The MySQL//var partition full problems continue. Though I repaired the watchdog table, I get the following error whenever I try to view the log in drupal:
user warning: Incorrect key file for table '/var/tmp/#sql_31f_0.MYI'; try to repair it query: SELECT w.*, u.name, u.uid FROM watchdog w INNER JOIN users u ON w.uid = u.uid ORDER BY w.wid DESC LIMIT 0, 50 in /usr/local/www/drupal/includes/database.mysql.inc on line 120.
Note that this query joins two tables: users and watchdog. There is no #sql_31f_0 table in the query. However, MySQL is creating a temp table with which to perform the join, and /var doesn’t have enough space to hold a copy of the intermediate results of the join, since watchdog has grown too large.
I could truncate watchdog, but the real problem is that /var is too small (250MB). So, I added another virtual disk, /dev/ad3, which is a 2GB disk that I’ll dedicate to /var/db/mysql.
The Adding Disks section of the FreeBSD handbook covers adding a new disk pretty thoroughly.
Then I moved the contents of /var/db/mysql over to the new volume (which I screwed up a bit; hint: preserve the privs!), and put an entry in /etc/fstab to mount /dev/ad3s1d to /var/db/mysql.
I then created the my.cnf file in /var/db/mysql, and added the following:
[mysqld]
tmpdir=/var/db/mysql/tmp
To put temp tables in /var/db/mysql/tmp instead of /var/tmp, so they won’t exhaust the /var filesystem anymore. And voila!
Tags: Migrated from Drupal