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.

Train lines

With the exception of city centre termini, train lines don’t end somewhere interesting and exciting, but where demand and excitement has dropped off so far they can’t be bothered going any further.

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.