Author: Brandon (Page 11 of 29)

Using MySQL Bitwise operators to get the network portion of an IP Address

March 11, 2009 / Brandon / 0 Comments

I like to store IP addresses in a database as unsigned 32-bit integers. It is efficient and elegant and is just fun to work with.

I recently had somebody scraping a site using a range of IP addresses from the same subnet to get around our bot-detection which worked at an individual IP level. It was a pretty simple change to summarize that at the network level. Just do a bitwise AND with 4294967040 to mask out the last octet

The number ‘4294967040’ came by calculating 2^32 – 2^8 so the binary number has all ones in the first 24 bits and all zeros in the last 8 bits.

  SELECT  ip, INET_NTOA(ip&4294967040) AS ipaddr, 
  FROM some_table
  GROUP BY ipaddr

Now those poor fools at 208.86.255.0/24 will be getting cached data from who-knows-when and they won’t even know it.

Apt ‘MMap ran out of room’ errors

February 28, 2009 / Brandon / 0 Comments

I’m seeing this problem more and more frequently when running apt-get or apt-cache commands. It seems to get most of the way through updating itself and then dies with this error:

E: Dynamic MMap ran out of room
E: Error occured while processing rapidsvn (NewVersion1)
E: Problem with MergeList /var/state/apt/lists/something_repodata_primary.xml
E: The package lists or status file could not be parsed or opened.

My understanding is that apt is running of some cache space and quits. I think this is referring to some memory limit, but am not sure (if anybody has a better explanation of the actual problem, please comment).

The fix is easy enough. Just create a file named /etc/apt/apt.conf.d/local.conf with this line:

APT::Cache-Limit 50000000;

That raises the limit from the default (I believe 8Mb) to 50Mb and has worked well for me.

CSV from the MySQL Command Line

February 18, 2009 / Brandon / 0 Comments

I frequently need to generate a quick report generated from a quick database query. I have been doing this by creating a simple PHP script that queries the database and then displays the results as a CSV. However, I just found a way to do it directly from the MySQL command line, which makes it possible to skip the steps of creating the PHP script.

Simply run the command like this:

mysql>SELECT * FROM sometable INTO OUTFILE '/tmp/output.csv'
   FIELDS TERMINATED BY ',';

That will save the results to the file specified in a simple CSV format. There are also options for enclosing fields in quotes, the line terminators, and several other options.

It is now 1234567890

February 13, 2009 / Brandon / 0 Comments

It went by without much fanfare:

[root@dev ~]# date +%s
1234567890

Good Email Obfuscator

February 9, 2009 / Brandon / 1 Comment

I usually avoid putting mailto links on websites because they are easily scraped by spammers and fed into spam lists. But sometimes it is just necessary to have one. There are ways of encoding email addresses using javascript or special HTML characters that make it more difficult for spammers to see the email addresses.

I’ve done the encoding on my own for a while, but just found this Email Obfuscator that does a good job of it, so I’ll be using that form now on.

SSH Error: trying to get more bytes 4 than in buffer 0

February 6, 2009 / Brandon / 7 Comments

I ran across this cryptic error message in an SSH log file today:

Feb  7 03:33:21 hostname sshd[19439]: error: buffer_get_ret: trying to get more bytes 4 than in buffer 0
Feb  7 03:33:21 hostname sshd[19439]: fatal: buffer_get_int: buffer error

The problem is actually a corrupt line in a users ~/.ssh/authorized_keys file. This user had copy/pasted a new key into his authorized_keys file and it had a newline after the ssh-rsa. Strangely enough, people were still able to authenticate if their key was above the corrupted line. Users whose key was listed below the corrupt line were not able to log in.

PHP 5.1 Doesn’t have timezone_identifiers_list() by default

February 5, 2009 / Brandon / 0 Comments

According to the PHP documentation for timezone_identifiers_list(), that function should be included in PHP 5.1.x. The note on DateTime installation mentions, however, that it was only experimental support, and had to be compiled specifically to support it.

The fix, then, is to recompile PHP 5.1.x with

CFLAGS=-DEXPERIMENTAL_DATE_SUPPORT=1

or to upgrade to PHP 5.2 where it is enabled by default.

My particular problem surfaced with some Drupal code that required the function.

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

January 22, 2009 / Brandon / 0 Comments

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

January 21, 2009 / Brandon / 1 Comment

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('[email protected]', '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

January 2, 2009 / Brandon / 2 Comments

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.