Description:
If you have a table on a server (let's call it sql-1) that uses a federated table to access a remote view (on sql-2), left joining against that view results in all columns in the view yielding NULL.
Example:
On sql-2:
CREATE TABLE TestView (
value INT
);
On sql-1:
CREATE TABLE FedView (
value INT
) ENGINE=Federated CONNECTION='sql-2/TestView';
SELECT fv.value FROM SomeTable AS st LEFT JOIN FedView AS fv ON fv.value = st.id;
-- fv.value will be NULL, even if the join matched a row
How to repeat:
Create a temporary directory, and run this script within it:
#!/usr/bin/perl
use strict;
use warnings;
use Cwd qw(getcwd);
use DBI;
use Fatal qw(mkdir open);
use File::Spec::Functions qw(catfile devnull);
########## Perl black magic for portable process killing and colors ############
###################### Not relevant to the bug in MySQL! #######################
BEGIN {
eval {
require POSIX;
*send_term = sub {
kill &POSIX::SIGTERM, $_[0];
};
if(-t STDOUT) {
# This function assumes that if you're on a POSIX system, you're running an
# ANSI-compatiable terminal
*yellow = sub {
return "\033[33m$_[0]\033[0m";
};
} else {
*yellow = sub {
return $_[0];
};
}
};
if($@) {
eval {
require Win32::Process;
Win32::Process->import;
*send_term = sub {
Win32::Process::KillProcess($_[0], 1);
};
# I don't know enough about Win32 to implement colors here...
*yellow = sub {
return $_[0];
};
};
if($@) {
die "Neither the POSIX nor the Win32::Process module could be found; exiting\n";
}
}
}
my $cwd = getcwd;
my $null = devnull;
my $data_location_primary = catfile($cwd, 'data_primary');
my $socket_location_primary = catfile($cwd, 'mysqld_primary.sock');
my $pid_location_primary = catfile($cwd, 'primary.pid');
my $data_location_secondary = catfile($cwd, 'data_secondary');
my $socket_location_secondary = catfile($cwd, 'mysqld_secondary.sock');
my $pid_location_secondary = catfile($cwd, 'secondary.pid');
sub unslurp {
my ( $filename, $contents ) = @_;
my $fh;
open $fh, '>', $filename;
print $fh $contents;
close $fh;
}
sub slurp {
my ( $filename ) = @_;
local $/;
my $fh;
open $fh, '<', $filename;
my $content = <$fh>;
close $fh;
return $content;
}
sub wipe_old_files {
system 'rm', '-rf', $data_location_primary, $data_location_secondary, 'my_primary.cnf', 'my_secondary.cnf';
}
sub install_mysql_files {
mkdir 'data_primary';
mkdir 'data_secondary';
system "mysql_install_db --datadir=$data_location_primary >/dev/null 2>&1";
system "mysql_install_db --datadir=$data_location_secondary >/dev/null 2>&1";
}
sub write_configuration {
unslurp 'my_primary.cnf', <<MY_CNF;
[client]
socket = $socket_location_primary
[mysqld]
socket = $socket_location_primary
datadir = $data_location_primary
pid-file = $pid_location_primary
skip-networking
federated
MY_CNF
unslurp 'my_secondary.cnf', <<MY_CNF;
[client]
socket = $socket_location_secondary
[mysqld]
socket = $socket_location_secondary
datadir = $data_location_secondary
pid-file = $pid_location_secondary
skip-networking
federated
MY_CNF
}
sub start_servers {
system "mysqld_safe --defaults-file=my_primary.cnf >$null 2>&1&";
system "mysqld_safe --defaults-file=my_secondary.cnf >$null 2>&1&";
print STDERR "servers started; waiting 10 seconds for them to get settled\n";
sleep 10;
}
sub create_tables {
my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock', 'root', '', {
PrintError => 0,
RaiseError => 1,
});
my $sql = <<SQL;
CREATE DATABASE testdb;
CREATE SERVER secondary FOREIGN DATA WRAPPER mysql
OPTIONS (DATABASE 'testdb', USER 'root', SOCKET '$socket_location_secondary');
CREATE TABLE testdb.TestTable (
value INT
);
CREATE TABLE testdb.FedTable (
value INT
) ENGINE=FEDERATED CONNECTION='secondary/TestTable';
CREATE TABLE testdb.FedView (
value INT
) ENGINE=FEDERATED CONNECTION='secondary/TestView';
SQL
print yellow("Setting up tables on primary:\n");
print $sql, "\n";
my @sql = split ';', $sql;
foreach $sql (@sql) {
next unless $sql =~ /\S/;
$dbh->do($sql);
}
$dbh->disconnect;
$dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock', 'root', '', {
PrintError => 0,
RaiseError => 1,
});
$sql = <<SQL;
CREATE DATABASE testdb;
CREATE TABLE testdb.TestTable (
value INT
);
CREATE VIEW testdb.TestView AS SELECT * FROM testdb.TestTable;
SQL
print yellow("Setting up tables on secondary:\n");
print $sql, "\n";
@sql = split /;/, $sql;
foreach $sql (@sql) {
next unless $sql =~ /\S/;
$dbh->do($sql);
}
$dbh->disconnect;
}
sub insert_fake_data {
my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock:database=testdb', 'root', '', {
PrintError => 0,
RaiseError => 1,
});
my $sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)');
for(my $i = 1; $i <= 9; $i += 2) {
$sth->execute($i);
}
for(my $i = 15; $i <= 20; $i++) {
$sth->execute($i);
}
undef $sth;
$dbh->disconnect;
$dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock:database=testdb', 'root', '', {
PrintError => 0,
RaiseError => 1,
});
$sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)');
for(my $i = 2; $i <= 10; $i += 2) {
$sth->execute($i);
}
for(my $i = 15; $i <= 20; $i++) {
$sth->execute($i);
}
undef $sth;
$dbh->disconnect;
}
sub kill_servers {
my $pid = slurp $pid_location_primary;
chomp $pid;
send_term $pid;
$pid = slurp $pid_location_secondary;
chomp $pid;
send_term $pid;
}
sub dump_query {
my ( $instance, $sql ) = @_;
my $dbh = DBI->connect("dbi:mysql:mysql_socket=mysqld_$instance.sock:database=testdb", 'root', '', {
PrintError => 0,
RaiseError => 1,
});
my $text = sprintf("Running SQL on %s: %s", $instance, yellow($sql));
print $text, "\n";
print '-' x length($text), "\n";
print '', "\n";
my $sth = $dbh->prepare($sql);
$sth->execute;
my @columns = @{$sth->{NAME}};
my @max_lengths = map { length } @columns;
my $rows = $sth->fetchall_arrayref;
foreach my $row (@$rows) {
@$row = map { defined() ? $_ : 'NULL' } @$row;
for(my $i = 0; $i < @$row; $i++) {
my $max_len = $max_lengths[$i];
my $len = length($row->[$i]);
$max_lengths[$i] = $len if $len > $max_len;
}
}
my $format = join(' | ', map { "%${_}s" } @max_lengths) . "\n";
printf $format, @columns;
printf $format, map { '-' x $_ } @max_lengths;
printf $format, @$_ foreach @$rows;
$sth->finish;
undef $sth;
$dbh->disconnect;
}
wipe_old_files;
install_mysql_files;
write_configuration;
start_servers;
eval {
create_tables;
insert_fake_data;
dump_query 'primary', 'SELECT * FROM TestTable';
dump_query 'secondary', 'SELECT * FROM TestTable';
dump_query 'secondary', 'SELECT * FROM TestView';
dump_query 'primary', 'SELECT tt.value AS local, ft.value AS remote FROM TestTable AS tt LEFT JOIN FedTable as ft ON tt.value = ft.value';
dump_query 'primary', 'SELECT tt.value AS local, fv.value AS remote FROM TestTable AS tt LEFT JOIN FedView as fv ON tt.value = fv.value';
};
if($@) {
warn $@;
}
kill_servers;
Description: If you have a table on a server (let's call it sql-1) that uses a federated table to access a remote view (on sql-2), left joining against that view results in all columns in the view yielding NULL. Example: On sql-2: CREATE TABLE TestView ( value INT ); On sql-1: CREATE TABLE FedView ( value INT ) ENGINE=Federated CONNECTION='sql-2/TestView'; SELECT fv.value FROM SomeTable AS st LEFT JOIN FedView AS fv ON fv.value = st.id; -- fv.value will be NULL, even if the join matched a row How to repeat: Create a temporary directory, and run this script within it: #!/usr/bin/perl use strict; use warnings; use Cwd qw(getcwd); use DBI; use Fatal qw(mkdir open); use File::Spec::Functions qw(catfile devnull); ########## Perl black magic for portable process killing and colors ############ ###################### Not relevant to the bug in MySQL! ####################### BEGIN { eval { require POSIX; *send_term = sub { kill &POSIX::SIGTERM, $_[0]; }; if(-t STDOUT) { # This function assumes that if you're on a POSIX system, you're running an # ANSI-compatiable terminal *yellow = sub { return "\033[33m$_[0]\033[0m"; }; } else { *yellow = sub { return $_[0]; }; } }; if($@) { eval { require Win32::Process; Win32::Process->import; *send_term = sub { Win32::Process::KillProcess($_[0], 1); }; # I don't know enough about Win32 to implement colors here... *yellow = sub { return $_[0]; }; }; if($@) { die "Neither the POSIX nor the Win32::Process module could be found; exiting\n"; } } } my $cwd = getcwd; my $null = devnull; my $data_location_primary = catfile($cwd, 'data_primary'); my $socket_location_primary = catfile($cwd, 'mysqld_primary.sock'); my $pid_location_primary = catfile($cwd, 'primary.pid'); my $data_location_secondary = catfile($cwd, 'data_secondary'); my $socket_location_secondary = catfile($cwd, 'mysqld_secondary.sock'); my $pid_location_secondary = catfile($cwd, 'secondary.pid'); sub unslurp { my ( $filename, $contents ) = @_; my $fh; open $fh, '>', $filename; print $fh $contents; close $fh; } sub slurp { my ( $filename ) = @_; local $/; my $fh; open $fh, '<', $filename; my $content = <$fh>; close $fh; return $content; } sub wipe_old_files { system 'rm', '-rf', $data_location_primary, $data_location_secondary, 'my_primary.cnf', 'my_secondary.cnf'; } sub install_mysql_files { mkdir 'data_primary'; mkdir 'data_secondary'; system "mysql_install_db --datadir=$data_location_primary >/dev/null 2>&1"; system "mysql_install_db --datadir=$data_location_secondary >/dev/null 2>&1"; } sub write_configuration { unslurp 'my_primary.cnf', <<MY_CNF; [client] socket = $socket_location_primary [mysqld] socket = $socket_location_primary datadir = $data_location_primary pid-file = $pid_location_primary skip-networking federated MY_CNF unslurp 'my_secondary.cnf', <<MY_CNF; [client] socket = $socket_location_secondary [mysqld] socket = $socket_location_secondary datadir = $data_location_secondary pid-file = $pid_location_secondary skip-networking federated MY_CNF } sub start_servers { system "mysqld_safe --defaults-file=my_primary.cnf >$null 2>&1&"; system "mysqld_safe --defaults-file=my_secondary.cnf >$null 2>&1&"; print STDERR "servers started; waiting 10 seconds for them to get settled\n"; sleep 10; } sub create_tables { my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock', 'root', '', { PrintError => 0, RaiseError => 1, }); my $sql = <<SQL; CREATE DATABASE testdb; CREATE SERVER secondary FOREIGN DATA WRAPPER mysql OPTIONS (DATABASE 'testdb', USER 'root', SOCKET '$socket_location_secondary'); CREATE TABLE testdb.TestTable ( value INT ); CREATE TABLE testdb.FedTable ( value INT ) ENGINE=FEDERATED CONNECTION='secondary/TestTable'; CREATE TABLE testdb.FedView ( value INT ) ENGINE=FEDERATED CONNECTION='secondary/TestView'; SQL print yellow("Setting up tables on primary:\n"); print $sql, "\n"; my @sql = split ';', $sql; foreach $sql (@sql) { next unless $sql =~ /\S/; $dbh->do($sql); } $dbh->disconnect; $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock', 'root', '', { PrintError => 0, RaiseError => 1, }); $sql = <<SQL; CREATE DATABASE testdb; CREATE TABLE testdb.TestTable ( value INT ); CREATE VIEW testdb.TestView AS SELECT * FROM testdb.TestTable; SQL print yellow("Setting up tables on secondary:\n"); print $sql, "\n"; @sql = split /;/, $sql; foreach $sql (@sql) { next unless $sql =~ /\S/; $dbh->do($sql); } $dbh->disconnect; } sub insert_fake_data { my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock:database=testdb', 'root', '', { PrintError => 0, RaiseError => 1, }); my $sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)'); for(my $i = 1; $i <= 9; $i += 2) { $sth->execute($i); } for(my $i = 15; $i <= 20; $i++) { $sth->execute($i); } undef $sth; $dbh->disconnect; $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock:database=testdb', 'root', '', { PrintError => 0, RaiseError => 1, }); $sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)'); for(my $i = 2; $i <= 10; $i += 2) { $sth->execute($i); } for(my $i = 15; $i <= 20; $i++) { $sth->execute($i); } undef $sth; $dbh->disconnect; } sub kill_servers { my $pid = slurp $pid_location_primary; chomp $pid; send_term $pid; $pid = slurp $pid_location_secondary; chomp $pid; send_term $pid; } sub dump_query { my ( $instance, $sql ) = @_; my $dbh = DBI->connect("dbi:mysql:mysql_socket=mysqld_$instance.sock:database=testdb", 'root', '', { PrintError => 0, RaiseError => 1, }); my $text = sprintf("Running SQL on %s: %s", $instance, yellow($sql)); print $text, "\n"; print '-' x length($text), "\n"; print '', "\n"; my $sth = $dbh->prepare($sql); $sth->execute; my @columns = @{$sth->{NAME}}; my @max_lengths = map { length } @columns; my $rows = $sth->fetchall_arrayref; foreach my $row (@$rows) { @$row = map { defined() ? $_ : 'NULL' } @$row; for(my $i = 0; $i < @$row; $i++) { my $max_len = $max_lengths[$i]; my $len = length($row->[$i]); $max_lengths[$i] = $len if $len > $max_len; } } my $format = join(' | ', map { "%${_}s" } @max_lengths) . "\n"; printf $format, @columns; printf $format, map { '-' x $_ } @max_lengths; printf $format, @$_ foreach @$rows; $sth->finish; undef $sth; $dbh->disconnect; } wipe_old_files; install_mysql_files; write_configuration; start_servers; eval { create_tables; insert_fake_data; dump_query 'primary', 'SELECT * FROM TestTable'; dump_query 'secondary', 'SELECT * FROM TestTable'; dump_query 'secondary', 'SELECT * FROM TestView'; dump_query 'primary', 'SELECT tt.value AS local, ft.value AS remote FROM TestTable AS tt LEFT JOIN FedTable as ft ON tt.value = ft.value'; dump_query 'primary', 'SELECT tt.value AS local, fv.value AS remote FROM TestTable AS tt LEFT JOIN FedView as fv ON tt.value = fv.value'; }; if($@) { warn $@; } kill_servers;