I published an SQLite3 storage back-end to NIST::NVD on the CPAN. It’s pretty quick. About as fast as the DB_File one, but without the down side of being tied to DB_File. It shouldn’t be too difficult to re-factor this code to any DBI-based database. MariaDB anyone?

I know it works on Debian. The nightly CPAN test results should come back shortly and I’ll find out how well it works on other platforms.

Pluggable M3 (Monitis Monitor Manager) Framework

Who needs an introduction about M3? – Perhaps no one!
After gaining some reputation with M3, providing extra-easy integration of any monitor into Monitis it was time to take it to the next level.

Generally speaking, the work flow of M3 was described in detail in this article.

After some thought and design, we’ve decided it’d be best if M3 was pluggable. Pluggable in terms of being able to easily add execution and parsing plugins.
The interface and behavior of M3 …

A customer opened a support issue to ask about some help determining why they were seeing a lot of Lock Wait Timeouts. I asked them to enable the InnoDB Lock Monitor so that I could get a look at what was going on in their transactions and whether there might be some locks held longer than necessary.

The customer sent in a 184MB MySQL error log with 4773836 lines. I started looking through it, but I could tell I was going to need a better way to get a better overview of the file than what I'd be able to piece together trying to poke through it and look for individual lines. I started piping the file through a variety of UNIX tools to narrow down what I was seeing.

I ended up with this mess:

 < mysqld.err grep ACTIVE | cut -d' ' -f 2,4 | sort -rn -k 2 | perl -F, -ane 'print "$F[0] $F[1]" if not $v{$F[0]}; $v{$F[0]} = $F[1];' | head

It's hideous, but it's pretty helpful. Here's the output:

1EC080F1 …

For a work project, I wrote a library in perl that can be used to query the NVD feed that NIST publishes here:

http://nvd.nist.gov/download.cfm

Here’s a snippit from the perldoc:

use NIST::NVD::Query;
 
# use convert_nvdcve to generate these files from the XML dumps at
# http://nvd.nist.gov/download.cfm
 
my( $path_to_db, $path_to_idx_cpe ) = @ARGV;
 
my $q = NIST::NVD::Query->new( database => $path_to_db,
                               idx_cpe  => $path_to_idx_cpe,
                              );
 
# Given a Common Platform Enumeration urn, returns a list of known
# CVE IDs
 
my $cve_id_list = $q->cve_for_cpe( cpe => 'cpe:/a:zaal:tgt:1.0.6' );
 
my @entry;
 
foreach my $cve_id ( @$cve_id_list ){
 
  # Given a CVE ID, returns a CVE entry
 
  my $entry = $q->cve( cve_id => …

A friend of mine was building a web site so his customers could view his current inventory of transportation containers, and he asked me for help on how to sort the rows of information that appeared on his site. So, in this post, I will give you a quick example on how to sort columns on a web page.

First, let’s start with an inventory database that we will build in MySQL:

Next, here are some SQL statements to populate the MySQL database with some sample data.

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any kind of data. In this post, I will show you how to create a MySQL database and then the web pages needed to create new addresses, edit the same …

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large DML statement was actually being executed. A customer was performing some modifications on …

Part One of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was making any progress.

Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given the fact that nothing else (major) was happening in the database (more on this reason later).

With InnoDB, you can …

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get it to work. So, I took the …