PFunc

I've had a project over on google code for a while now called PFunc.  It was inspired by FUNC a project I used to use, but due to it's linear processing I figured I could improve by threading.  Func is written in python, and I'm far more comfortable in perl.  So I wrote Pfunc.  It's purpose, like Func's is to execute commands on arbitrary groups of servers immediately.  I figured Pfunc would be a threaded tied-me-over until the Func project progressed and added asynchronous processing of commands.  Instead the Func project has gone in a different direction, trying to be pluggable.  My tied-me-over has lasted much longer than I ever imagined.  I have yet to find something to replace it with.

Pfunc's focus is narrow.  It's about running a command on arbitrary groups of servers instantly all at the same time from the command line and providing easily readable output.  Pfunc also logs the output and exit status of all commands run through it to a mysql database.  Pfunc will copy files, but it is not a configuration manager.  Pfunc should only be used on a closed private network or over an stunnel, because while it does verify commands  to execute and files via DSA signatures, it passes traffic in plain-text. 

Setup is simple, download the code on the central machine you want to control other machines from, and put a copy on all the machines you want to control.  Create a database and import the provided .sql file.  One daemon called dsacertd runs on your central server to provide certs to clients for signing commands and small files.  Configure it to point to your database server and start this up, then create some clients and groups using the phostadd.pl and pgroupadd.pl commands.  Now you can configure the client machines to connect to the same database server and dsacertd server, then start up the pfuncd daemon.  That's it, your done.  Now you can execute arbitrary commands on any group of servers you defined with the pfunc command on your central server.  Add the -o option and get nicely formatted output, or you only receive exit status for each machine in the group.  While pfunc does not suffer from the interpolation problems of Func, command-line interpolation is still an issue.  You've gotta escape what your shell requires escaping.

Pfunc has become an integral part of my workplace and the application I support.  We use it for many things, including our RPM based release process, using it to execute synchronized yum update commands across groups of web servers.

Add AUTO_INCREMENT to MyISAM without Recreating the Table

Here's a Filthy hack.

Using ALTER table to add AUTO_INCREMENT to a column in MySQL takes significantly longer than you might think.  It's because behind the scenes it has to recreate the whole table.  If you've been using sequence tables and now you'd like to do away with them, this may take much longer than you'd think.  I recently ran into this problem.  I had around 450 databases with tens of thousands of tables in each that I needed to convert.  I found this article on mysql performance blog from a while back.  This only works for MyISAM.  It suggests that at least at that time one could simply create a new table defined the way you wanted it ( in their example case removing AUTO_INCREMENT) and switch out the .frm files from your new table to the one you want to change.  They go on to specifically say it doesn't work to add AUTO_INCREMENT.  While that might have been the case at the time, it's not true now.  You can add AUTO_INCREMENT to a column this way, with the addition of switching out the .MYI file as well.  Here's the process:

Do a 'SHOW CREATE TABLE `tablename`' on the table you'd like to add AUTO_INCREMENT.  Take the output and create a new table.  Alter the new empty table adding AUTO_INCREMENT with  MAX+1 from the PRIMARY KEY of the original table as the AUTO_INCREMENT value.  Make sure your column type matches the existing type of the table you're converting.  (I.E. int(10) unsigned)

ALTER TABLE `convert_table` MODIFY COLUMN `id` INT(10) UNSIGNED AUTO_INCREMENT, AUTO_INCREMENT = 1000;

Now switch out the .frm and .MYI files in /var/lib/mysql/databasename/ from the new table to the old one.  Then it's likely a good idea to repair the table.  Flushing and Locking the tables is also likely a good idea.

Here's the Perl code I wrote to do it to all of the tables I needed to do this for.  Backup first and use at your own extreme risk, it really is a filthy hack.

#!/usr/bin/perl
use strict;

use warnings;
use DBI;
use Carp;
use File::Copy;
use Getopt::Long;

my $dbuser = q{root}; # Database user
my $dbpass = q{}; # Database password
my $db;
my $safe = undef; # Lock and Flush Tables
my $remove = undef; # Drop the sequence tables
my $type = undef; # Don't convert the table if the Primary key isn't an int(10) unsigned
my $ext = '_seq'; # Pattern appened to your sequence tables
 
GetOptions(
'database|d=s' => \$db,
'safe|s' => \$safe,
'remove|r' => \$remove,
'type|t' => \$type,
'extension|x=s' => \$ext,
);
unless ( $db ) {
croak qq{ You must provide a database (-d/--database)\n };
}
my $dbh = DBI->connect(
qq{dbi:mysql:host=localhost;database=$db},
$dbuser,
$dbpass,
{ RaiseError => 1 }
) or croak qq{Can't connect to the db, I haven't done anything yet, no cleanup needed.\n}.DBI->errstr;

# Get a list of all the sequence tables
my $sthm = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sthm->execute($db,qq{\%_$ext});

print "Commencing filthy hack\n";

$| = 1; # So we get output during the while
MAIN:
while ( my $row = $sthm->fetchrow_hashref ) {
# Dump the $ext to get the table name
$row->{'TABLE_NAME'} =~ s/$ext$//;
my $table = $row->{'TABLE_NAME'};
# Make sure the table exists
my $sth = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sth->execute($db,$table);
unless( $sth->fetchall_arrayref->[0]->[0]) {
print qq{\nTable $table missing, mabye you should drop $table$ext\n};
next MAIN;
}
# Get the table definition
$sth = $dbh->prepare(qq{SHOW CREATE TABLE $table});
$sth->execute;
my ($primary,$create_statement,$type);
while ( my $create = $sth->fetchrow_arrayref ) {
if ( $create->[1] =~ /AUTO_INCREMENT/ ) {
print "\nTable $table already has auto_increment, you might want to check this table out.\n";
next MAIN;
}

my $desc = $dbh->selectall_hashref(qq{DESC $table}, q{Key});
unless ( $desc->{'PRI'} ) {

print qq{\nWTF? No primary col found for table $table, this is going to need manual intervention.\n};
next MAIN;
}
# If type option set, don't convert if the type for the primary col isn't an int(10) unsigned
if ( $type ) {
unless ( $desc->{'PRI'}->{'Type'} eq 'int(10) unsigned' ) {
print qq{\nWTF? Your primary key isn't an 'int(10) unsigned' on table $table, it's a }. $desc->{'PRI'}->{'Type'} .qq{, I'm not touching it.\n};
next MAIN;
} 
}
$type = $desc->{'PRI'}->{'Type'};
# change the table name in the create statement to convert_table
$create->[1] =~ s/CREATE TABLE `[^`]+`(.+)/CREATE TABLE `convert_table`$1/;
# find the primary key
$primary = $desc->{'PRI'}->{'Field'};
$create_statement = $create->[1];
}

# Get the max val from the table to use for AUTO_INCREMENT
$sth = $dbh->prepare(qq{SELECT MAX(`$primary`) FROM `$table`});
$sth->execute;
my ($id) = @{ $sth->fetchall_arrayref} ;
$id = $id->[0] ? $id->[0]+1 : 1;

# Make sure we have an int cause the orginal DB might really be honked up
unless ( int($id) ) {
print "$id for table $table is jacked up. Manual intervention needed.\n";
next MAIN;
}

# Create the convert table
$sth = $dbh->prepare($create_statement);
$sth->execute or croak qq{Can't create the convert_table, you might need to do some cleanup\n}.DBI->errstr;

# For safty sake
if ( $safe ) {
$dbh->do(qq{FLUSH TABLES `$table`, `$table$ext`});
$dbh->do(qq{LOCK TABLES `$table` WRITE, `$table$ext` WRITE, `convert_table` WRITE});
}
# Alter the empty convert_table
my $alterquery = qq{
ALTER TABLE `convert_table`
MODIFY COLUMN `$primary` $type AUTO_INCREMENT,
AUTO_INCREMENT = $id
};
$sth = $dbh->prepare($alterquery);
$sth->execute or croak qq{WTF? Can't alter the convert table, cleanup needed for sure, you've got locked tables\n}.DBI->errstr;

# Switch out the .frm and .MYI files from the convert table the table we're fixing
copy(qq{/var/lib/mysql/$db/convert_table.frm},qq{/var/lib/mysql/$db/$table.frm}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";
copy(qq{/var/lib/mysql/$db/convert_table.MYI},qq{/var/lib/mysql/$db/$table.MYI}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";

# Just in case, Cause this is a flithy hack
$dbh->do(qq{REPAIR TABLE `$table`}) if $safe;

# Shouldn't need to do with but it's a good non-invasive test of the table
$alterquery = qq{ALTER TABLE `$table` AUTO_INCREMENT = $id };
my $sth7 = $dbh->prepare($alterquery);
$sth7->execute or croak qq{Hope you backed up, filthy hack failed. Cleanup needed for sure, you have locked tables and possible corruption, try repair with USE_FRM\n}.DBI->errstr;

# Clean up
$dbh->do(qq{DROP TABLE `convert_table`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
if ( $remove ) {
$dbh->do(qq{DROP TABLE `$table$ext`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
if ( $safe ) {
$dbh->do(q{UNLOCK TABLES}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
print ".";
}

print "\nFilthy hack complete! Hope I didn't just corrupt everything\n"