Month: January 2009

Don’t Forget to run mysql_upgrade when Upgrading to MySQL 5

Upgrading from MySQL 4 to MySQL 5 requires some changes to the mysql database structure. I’ve seen users bitten by this numerous times, and it is often difficult to track down because the database may work partially, but display strange errors.

On this occasion, the user saw that some tables were corrupted and couldn’t fix repair them with either REPAIR TABLE or the command line myisamchk. I saw the following errors in /var/log/mysqld.log which made it obvious:

/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
090122 16:07:03 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
090122 16:07:03 [ERROR] Column count of mysql.db is wrong. Expected 22, found 15. Created with MySQL 0, now running 50130. Please use mysql_upgrade to fix this error.
090122 16:07:03 [ERROR] mysql.user has no `Event_priv` column at position 29
090122 16:07:03 [ERROR] Cannot open mysql.event
090122 16:07:03 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

Simply run mysql_upgrade and it does a number of things required to get the MySQL server functioning again. It runs mysqlcheck, and doesn’t seem to pass the username/password arguments through, so it may be easier to create at ~/.my.cnf file with your username and password before running it:

[client]
user=root
pass=mypassword

Save Internet Audio Streams to MP3s

I’ve got a couple of radio programs that I like to listen to. The only problem is that I rarely am able to listen to them live. I was wishing that somebody made a good DVR-like device for the radio, but after some thought figured out a way to do it on my own using internet audio streams that most radio stations now have available.

Googling for instructions on how to save Internet audio streams will return a lot of semi-workable but mostly garbage instructions. The best set of instructions I found was at Instructables.com where the basic concept is to use the command-line mplayer to save the stream as a wave file, then use lame to convert it to an MP3.

The instructables tutorial had several downfalls though. First, it is not able to stop mplayer on its own, so it uses a second cron job to kill the original mplayer command – a little to crude for my taste. Secondly, and more importantly, you have to know the exact stream URL which is not easy to identify from most internet radio websites. They tend to hide the actual stream source behind layers of javascript so that their web-based players can synchronize ads and such while listening to the streams.

I created some PHP code that automates this process and makes it pretty simple. The basic streamsave class provides functions for downloading the wave file and converting it to an MP3. I then extend that class for specific radio stations that I want to save. The extended class provides functions that run through all of the javascript garbage to get to the actual stream source.

Using those classes, this simple script now saves my stream to an MP3 file and emails me the location when it is done:

<?php
require_once dirname(__FILE__).'/ss_640wgst.class.php';

$streamsave = new ss_640wgst();

$streamsave->stream_url = $streamsave ->getStreamURL();
$streamsave->seconds = 60 * 60; // One hour

// This saves the stream to a temporary wav file
$streamsave->save_stream();

// Now encode it to an mp3
$output_file = "/tmp/some_directory/some_program_".date('Y-m-d-His').'.mp3';
$streamsave->encode_to_mp3($output_file);

// Delete the large wav file
unlink($streamsave->wavfile);

// And tell me that the file was saved
echo "File saved (if all went okay) to {$streamsave->mp3file}\n\n";
mail('you@yourdomain.com', 'Audio File Saved', "File saved to {$streamsave->mp3file}");

// It would be cool to create a podcast XML file here that contains your new file

?>

Downloads

The abstract class file: streamsave.class.php
The extended class specifically for 640 WGST in Atlanta: ss_640wgst.class.php

I’ve created the extended classes for stations that are useful for me. If there seems to be any interest, I can work on developing that a bit more to make it more generalized and work for more radio stations.

MySQLDump To a Remote Server

I was running out of disk space on a server today. The server had a large database table that was no longer used, so I wanted to archive it and then drop the table. But the server didn’t have enough disk space to dump it out to disk before copying it off to a remote server for archiving.

The first thought was to run mysqldump dump on the destination machine, and to access the database over the network. That however, doesn’t compress or encrypt the data. Plus I would have had to create a mysql user with permission to access the database remotely.

The solution I came up with worked out well: mysqldump directly to the remote host with this command:

mysqldump <DATABASE_NAME> [mysqldump options] | gzip -c | ssh user@remotehost "cat > /path/to/some-file.sql.gz"

That pipes the mysqldump command through gzip, then to through and SSH connection. SSH on the remote side runs the ‘cat’ command to read the stdin, then redirects that to the actual file where I want it saved.