ActiveState Perl 5.10, Windows XP, and DBD-mysql

A few months ago I installed ActivePerl 5.10 on a Windows XP Pro workstation. Next I tried to install DBD::mysql using CPAN, it failed. When I browsed modules via ppm, a GUI in this version of ActiveState Perl on Windows, DBD::mysql was not listed as an available module.

I then downloaded the source code, manually modified the MAKE file, fiddled with Visual Studio NMAKE, compiled it a few times, without success.

Google revealed the existence of Strawberry Perl. So I removed ActiveState Perl, installed Strawberry, ran ppm install DBI, ppm install DBD-mysql. And it worked.

Fast-forward a few weeks, I started playing with EPIC, a plugin for Eclipse that supposedly provides a nice IDE for Perl development and debugging. As a result, I started fiddling with PadWalker, a prerequisites for EPIC. I couldn’t remember all the details now due to frustration, but suffice it to say that I wrestled with EPIC, Eclipse, and Strawberry so much that I thought maybe I should gave ActiveState another look. Plus, I always wanted to verify for myself what the deal is with ActiveState Perl and DBD-mysql.

So I un-installed Strawberry Perl 5.10, reinstalled ActiveState Perl 5.10. And just like last time, DBD-mysql wouldn’t install. After un-installing ActiveState, reinstalling Strawberry, running ppm install DBD-mysql, I am back in business.

So, if you want to do Perl scripting with MySQL on Windows using DBD-mysql, pick Strawberry Perl, not ActiveState.

Quick way of finding version number of a Perl module

Run this on command line

perl -MMyModule -le 'print "$MyModule::VERSION\n"'

For instance, to find out the version number of DBI installed on the machine, run

perl -MDBI -le 'print "$DBI::VERSION\n"'

To find out the version of DBD::mysql, run

perl -MDBD::mysql -le 'print "$DBD::mysql::VERSION\n"'

One caveat: this process does not seem to work on Windows, at least for my Strawberry Perl 5.10 installed on my Windows XP. I had to use the script below on Windows to get the version information. Here is the error message when I tried:

C:\junk>perl -MDBI -le ‘print “$DBI::VERSION\n”‘
Can’t find string terminator “‘” anywhere before EOF at -e line 1.

#!/usr/bin/perl
use DBI;
use DBD::mysql;

print "The version for DBI is $DBI::VERSION\n";
print "The version for DBD::mysql is $DBD::mysql::VERSION\n";

Perl script to convert MySQL character set to utf8

“Groovy, baby!”, as international man of mystery likes to say.

It seems converting character and text data to utf8 character set is a common task for MySQL, especially during an upgrade. In fact, I had trouble with it during server and WordPress database upgrade for this blog site. I wrote about it in this post, where I explained how to do it step-by-step using a command line tool such as mysql, taking advantage of some nifty code generation trick with information_schema.

One drawback of that method is that it’s a manual process, therefore time-consuming and error-prone.

I spent some time today to cook up a simple Perl script to automate this task. See below. Remember to change MyDatabase to your database name (there are 3 places that need to be changed), and adjust the user name and password accordingly. This script also prints out sql statements used during the process so you will know what has been done to the database.

I have tested this successfully on my own WordPress blog. Hopefully it will help somebody out there. Enjoy!

#!/usr/bin/perl
# MyUtf8Converter.pl - convert all character data to utf8 character set
use strict;
use warnings;
use DBI;

# data source name, username, password, connection attributes
my $dsn = "DBI:mysql:MyDatabase:localhost";
my $user_name = "MySQLUserName";
my $password = "MySQLPassword";
my %conn_attrs = (RaiseError => 1, PrintError => 0, AutoCommit => 1);

# connect to database
my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs);

# Convert char types first. Use information_schema to generate alter table code to convert data to binary first.
my $sth = $dbh->prepare ("SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 'char', 'binary')) FROM information_schema.columns WHERE table_schema = 'MyDatabase' and data_type LIKE '%char%'");

$sth->execute ();

# Run alter table stamement. First convert char types to binary types, then convert those to char types utf8 character set
while (my ($val) = $sth->fetchrow_array ())
    {
        print $val, "\n";
        $dbh->do ($val);
        $val =~ s/binary/char/;
        $val = $val . " CHARACTER SET utf8";
        print $val, "\n";
        $dbh->do ($val);
    }
$sth->finish ();
# Now we convert text data. Use information_schema to generate alter table code to convert data to blob first.
$sth = $dbh->prepare ("SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 'text', 'blob')) FROM information_schema.columns WHERE table_schema = 'MyDatabase' and data_type LIKE '%text%'");

$sth->execute ();

# Run alter table stamement. First convert text types to blob types, then convert those to text types utf8 character set
while (my ($val) = $sth->fetchrow_array ())
    {
        print $val, "\n";
        $dbh->do ($val);
        $val =~ s/blob/text/;
        $val = $val . " CHARACTER SET utf8";
        print $val, "\n";
        $dbh->do ($val);
    }
$sth->finish ();

$dbh->disconnect ();

Identify a proxy when automatic configuration script is used

Many, if not all, corporations control and monitor employees web surfing and email activities. Sometimes a company may choose to use automatic configuration script to enable employees’ web connection. This can be verified by:

  • Internet Explorer: Tools -> Internet Options-> LAN Settings -> “Use automatic configuration script”;
  • Firefox 3: Tools -> Options -> Advanced -> Network -> Settings… -> Automatic proxy configuration URL:

Sometimes you need to know a proxy server name or IP address. For instance, if you use Perl and would like to do an automatic binary install of a module, then you will need to define a proxy for cpan to use. This is the command to do that:

set HTTP_proxy=http://my.proxy.server:8000/

So obviously you need to know a proxy server name or IP address. To find that out, you need to copy the location where the automatic configuration script is located, put that address into your browser and open it. It will prompt you to save it as a file. Do that, then open the file up to search for proxy server names or IP addresses.

As a side, I was not able to install DBD::mysql on a Windows XP machine with cpan. I also tried to do manual installation from source code, with no success. I use ActiveState Perl 5.10 and 5.1.30-community MySQL Community Server (GPL). I stumbled into Strawberry Perl while doing research for that issue. Maybe I will check it out in the future.

Move SQL Server backup file and restore it with Perl

#There are two key parts for this script. The first part copies the latest backup and paste it in the local g drive. The second part do the restore from this backup file. It’s a LightSpeed backup and restore. Linchi Shea’s SQLDBA helper Perl files are needed. This is mainly for my own safekeeping.

#Part 1
use File::Copy;
use File::stat;
use SQLDBA::Utility qw( dbaRunOsql );  # import the function

$dirname = shift or die "Please provide a directory to search for";
$timediff=0;

opendir DIR, "$dirname";

while (defined ($file = readdir(DIR)))
{
	if($file ne "." && $file ne "..")
	{
		$diff = time()-stat("$dirname/$file")->mtime;
		if($timediff == 0)
                       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
                if($diff< $timediff)
		       {
                       	$timediff=$diff;
                       	$newest=$file;
                       }
        }
}

print $newest,"\n";

copy("$dirname/$newest", "g:/$newest");


#Part 2
# Construct the SQL script
my $sql =<<__SQL__;
SET NOCOUNT ON

USE Master

DECLARE \@strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid smallint,
ecid smallint,
status varchar(30),
loginname varchar(128),
hostname varchar(128),
blk char(5),
dbname nchar(50),
cmd nchar(16))

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'MyDatabase'

DECLARE \@spid varchar(10)
DECLARE \@dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO \@spid, \@dbname2
WHILE (\@\@fetch_status <> -1)
BEGIN
	IF (\@\@fetch_status <> -2)
	BEGIN
	PRINT 'Killing ' + \@spid
	SET \@strSQL = 'KILL ' + \@spid
	EXEC (\@strSQL)
	END
	FETCH NEXT FROM LoginCursor INTO  \@spid, \@dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers
PRINT 'Done'

-- The line below is the command to restrict access (behine EM) to dbo (schema owner) and sysadm (sys user):
--exec sp_dboption N\@dbname, N'dbo use', N'true'
--exec sp_dboption N\@dbname, N'dbo use', N'false'



EXEC master.dbo.xp_restore_database \@database='MyDatabase'
  , \@filename = 'G:\\$newest'
GO

__SQL__

# Prepare the osql command line options
my $optRef = {
      '-E' => undef,
      '-n' => undef,
      '-w' => '1024',
      '-d' => 'master',
      '-l' => '5'
   };

# Execute the SQL script on the APOLLO named instance on the local server
my $result = dbaRunOsql('MyServerName', $sql, $optRef);
if (!defined $result) {
   die "***Err: failed to run osql.exe.";
}
else {
   print $result;  # Print the results
}