Taming the Distributed Database Problem: A Case Study Using MySQL
By Giuseppe Maxia, July 08, 2004
There are situations where data belonging to a given context are nonetheless scattered among different servers, possibly in separate locations. In such situations, gathering data together is more difficult than dealing with a unified set of tables, where all of them were under the same engine. The challenge is purely technological. It is easy to create a virtual file system, making the database engine believe that all the files are in the same machine, even though they are just a link over the network, perhaps hundreds of miles apart. However, in such a case the performance would be so unfavorable that the whole purpose of having a DBMS would be mystified. Hence, the distributed database studies, which try to find clever ways of reducing the technological gaps and make normal operations look, if not ordinary business, at least affordable.
Listing 2 make_shadow_table.pl: Creates the auxiliary structure
for remote comparison
#!/usr/bin/perl -w
# make_shadow_table
# (C) Giuseppe Maxia 2003
#
# Creates a shadow table with a CRC signature and
# the corresponding primary key.
#
use strict;
use DBI;
my $skip_timestamp = 1;
my ($host, $db, $table) = @ARGV ;
$table or die
"syntax: make_signatures host database table\n";
my @signature_queries = (
['drop signatures', qq{
DROP TABLE IF exists shadow_table
}],
['create signatures', qq{
CREATE TABLE shadow_table (
signature CHAR(32) NOT NULL PRIMARY KEY,
span BIGINT UNSIGNED NOT NULL,
PK !PK_TYPE! NOT NULL,
UNIQUE KEY PK(PK),
KEY span (span)
)}],
['insert signatures', qq{
INSERT INTO
shadow_table (signature, PK, span)
SELECT
MD5(CONCAT_WS('/#/',!COLUMN_NAMES!)),
!PK_NAME!,
!PK_NAME! / 512
FROM
!TABLE_NAME!
}]
);
sub make_signatures {
my $dbh = shift;
my $table = shift;
my $key_pos = 3;
my $description = $dbh->selectall_arrayref(qq{describe $table});
my ($version) = @{$dbh->selectcol_arrayref(qq{select version()})};
if ($version =~ /4\.1/) {
$key_pos = 4;
}
my @pkeys = grep {$_->[$key_pos] eq "PRI"} @$description;
die "can't make signatures. Table $table has no primary key.\n"
unless scalar @pkeys ;
my ($primary, $pk_type) =();
if (scalar @pkeys > 1) { # composite PK
$primary = join "," , map {"`$_->[0]`"} @pkeys;
$primary = "MD5(CONCAT_WS('/#/'," . $primary . "))";
$pk_type = "CHAR(40)";
}
else {
($primary, $pk_type) = map {'`' . $_->[0] . '`', $_->[1]} @pkeys;
}
my $columns ='';
for (@$description) {
next if $skip_timestamp && ($_->[1] =~ /timestamp/i);
my $column = "`$_->[0]`";
$columns .= ',' if $columns;
if (($_->[2] eq 'YES') &&
((not $_->[$key_pos]) || (($_->[$key_pos]) && \<BR>
($_->[$key_pos]) ne 'PRI'))) {
$column = "COALESCE($column, '#NULL#')"
}
$columns .= $column;
}
for (@signature_queries)
{
my $query = $_->[1];
$query =~ s/!PK_TYPE!/$pk_type/;
$query =~ s/!PK_NAME!/$primary/g;
$query =~ s/!TABLE_NAME!/$table/;
$query =~ s/!COLUMN_NAMES!/$columns/;
print "#$_->[0] $query\n";
$dbh->do ($query);
}
}
my $dbh = DBI->connect("DBI:mysql:$db;host=$host", "myuser","secret",{RaiseError=>1});
make_signatures ( $dbh, $table);
