MySQL Partitioning Automation

This is one cool post.

It’s a shame that MySQL doesnt have the ability to automatically add partitions based upon date. For example, if result of a function should generate a new partition:

PARTITION BY YEARWEEK(MyDateTimeCol)

And hey presto, a bunch of new partitions would appear every time you insert new data thats in a new range. YEARWEEK() may be too course for your massive amount of data – perhaps TO_DAYS(). Perhaps it’s not that massive a data set, so just YEAR() would suffice. But having the new partitions created upon INSERT of data that matches would be great. As would deletion of partitions when a partion is empty. Nice bit of housekeeping.

Extracting IPv4 addresses from IPv6 in MySQL

Here’s a stored procedure that will let you find an IPv4 address embedded inside an IPv6 address, as a stored procedure in MySQL:


DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `IPv4_from_IPv6`(IPv6 varchar(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
# Split into blocks
DECLARE Block1, Block2, Block3, Block4, Block5, Block6, Block7, Block8 varchar(4);
DECLARE IPv4 varchar(255);
SELECT SUBSTRING_INDEX(IPv6, ":", 1)                           INTO Block1;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 2), ":", -1) INTO Block2;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 3), ":", -1) INTO Block3;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 4), ":", -1) INTO Block4;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 5), ":", -1) INTO Block5;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 6), ":", -1) INTO Block6;
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(IPv6, ":", 7), ":", -1) INTO Block7;
SELECT SUBSTRING_INDEX(IPv6, ":", -1) INTO Block8;
SELECT INET_NTOA(CONV(concat(Block7, Block8), 16, 10)) INTO IPv4;
IF (Block1 <> 0)
THEN
RETURN NULL;
ELSEIF (Block2 <> 0)
THEN
RETURN NULL;
ELSEIF (Block3 <> 0)
THEN
RETURN NULL;
ELSEIF (Block4 <> 0)
THEN
RETURN NULL;
ELSEIF (Block5 <> 0)
THEN
RETURN NULL;
ELSEIF (Block6 <> "FFFF")
THEN
RETURN NULL;
END IF;
RETURN IPv4;
END $$

Very handy.

File::Pid to catch multiple execution

CPAN carries a module called File::Pid, that implements a PID file object that writes to a file. The documentation suggests it be used as:

  use File::Pid;
  my $pidfile = File::Pid->new({
    file => '/some/file.pid',
  });
  $pidfile->write;
  if ( my $num = $pidfile->running ) {
      die "Already running: $num\n";
  }
  $pidfile->remove;

However, if you write() before calling running(), then the PID in the PID file gets overwritten with the current script’s PID, and thus running() always returns the current scripts PID, and thus, if you run the example, it always bombs out.
Instead, you want to put the call to write() after the call to running():

  use File::Pid;
  my $pidfile = File::Pid->new({file => '/some/file.pid',  });
  die "Already running in PID $num" if ( my $num = $pidfile->running );
  $pidfile->write;
  do_seomthing_useful();
  $pidfile->remove;

Easy.

Perl Search and Replace, using variables

Perl is a reasonable scripting language (as are others, so shh!). It has always had strong regular expression support; those regular expressions can also be used to do substitutions, such as:


my $pet = "I have a dog";
$pet =~ s/dog/cat/;

Neat enough. But lets say I want to look up the parts of the “s///” that define my search text, and my replacement text. Easy enough:


my $pet = "I have a dog";
my $search = "dog";
my $replace = "cat";
$pet =~ s/$search/$replace/;

But lets make our substitution a little more complex – I want to match a URL, and have the host and port lower case, but leave the path as the case it comes in, and I don’t want to be entering expressions as the replacement text! Lets try:


my $url = 'http://www.FOo.COm/wibbLE';
my $search = '^([^:]+://[^/]+)/?(.*)?$';
my $replace = '\L$1\E/$2';
print $url if $url =~ s/$search/$replace/;

Sadly, while $search matches, the replace is the string “\L$1\E/$2“. It appears that we need to use a combination of “/e” mode (evaluate as expression) to evaluage this replacement string. Of course, when we’re doing eval, we want to ensure we don’t have malicious content in $replace, such as “unlink()” and friends who could do Bad Things. So my solution was to escape double quotes from my $replace string, wrap that all in double quotes, and pass in “/ee“:


my $search = '^([^:]+://[^/]+(:\d+)?)/?(.*)?$'; # From database
my $replace = '\L$1\E/$3'; # From database
$replace =~ s/"/\\"/g; # Protection from embedded code
$replace = '"' . $replace . '"'; # Put in a string for /ee
print $url if $url =~ s/$search/$replace/ee;

This will give us:

  • Port and host name lower case
  • Hostname (and port) will always have a slash after it
  • Bad code like unlink() won’t be run
  • The expression that we initally set/fetch/got in $replace is just a vanilla replacement term, not arbitary Perl code.

Acer Aspire Revo 3700 with Linux

Looks like Acer is releasing a Linux model of its new Revo, product code “PT.SEMEC.003“; listed as a 160 GB HDD, 2 GB Linux version. Insight UK have them for £230. See this list of sites for prices; all around £200 – £300.  Gigabit ethernet sees nice, as does 802.11n if you need to fall back to wireless. Amazon ar elisting it for £349, so a little steep there. Here’s the press release from Acer, dated September 9.