I have some tables (authentication0106 through authentication0108) for which I create a MERGE table (authentication). Some Perl script is adding records to authentication0107 as fast as it can. While it is doing this, I run the mysql client and issue the following query

select count(*), max(id), max(auth_time) from authentication;

Most of the time, this results in one row with sane values, e.g.:

+----------+---------+----------------+ | count(*) | max(id) | max(auth_time) | +----------+---------+----------------+ | 59776 | 59776 | 982157198 | +----------+---------+----------------+ 1 row in set (0.00 sec)

But sometimes I get results like these (which is wrong because all id's and auth_time's have a value):

+----------+---------+----------------+ | count(*) | max(id) | max(auth_time) | +----------+---------+----------------+ | 650614 | NULL | NULL | +----------+---------+----------------+ 1 row in set (0.00 sec)

Or even this (max(id) seems okay, but max(auth_time) is wrong):

+----------+---------+----------------+ | count(*) | max(id) | max(auth_time) | +----------+---------+----------------+ | 680402 | 680402 | NULL | +----------+---------+----------------+ 1 row in set (0.00 sec)

1. Create tables in mysql client:

create table authentication0106 ( id int not null, auth_time int not null, primary key(id), key auth_time(auth_time)) ; create table authentication0107 ( id int not null, auth_time int not null, primary key(id), key auth_time(auth_time)) ; create table authentication0108 ( id int not null, auth_time int not null, primary key(id), key auth_time(auth_time)) ; create table authentication ( id int not null, auth_time int not null, primary key(id), key auth_time(auth_time)) type=merge union=(authentication0106,authentication0107,authentication0108);

2. Start this Perl script:

#!/usr/local/bin/perl5 -w

use DBI; use DBD::mysql;

use POSIX;

sub main {

my ($dbname) = @_;

#DBI->trace(2);

my $prev_yearweek = ''; my $sth_yearweek = '';

my $dbh =
DBI->connect("dbi:mysql:database=$dbname:mysql_socket=/tmp/mysql-3.23.32.sock",
"test", "test", {RaiseError => 1});

# # find last stored auth_time and id # my $lasttime; my $sth = $dbh->prepare("select max(id), max(auth_time) from
authentication");

$sth->execute(); ($id, $lasttime) = ($sth->fetchrow_array())[0..1]; $id = $id ? $id + 1 : 1; $sth = undef;

for ($i=0; $i<1000000; $i++) {

my $time = time();

# # see for which week this is an authentication #

my $yearweek = strftime("%g%U", localtime($time)); $yearweek = '0107';

if ($yearweek ne $prev_yearweek) { $prev_yearweek = $yearweek; $sth_yearweek = $dbh->prepare( "insert into authentication".$yearweek."
set id = ?, auth_time = ?"); }

$sth_yearweek->execute( $id++, $time, ); }

#$dbh->commit(); $dbh->disconnect(); }

my $dbname = 'test';

&main($dbname);

3. Repeatedly execute this query in the mysql client while the Perl script above is running:

select count(*), max(id), max(auth_time) from authentication;

It seems to be some race-condition so there's no guaranteed reproduction but it gives incorrect results once every 5 or 10 times in my case.

Unknown

System: SunOS lei 5.7 Generic_106541-07 sun4u sparc SUNW,Ultra-250 Architecture: sun4

Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /opt/gcc/lib/gcc-lib/sparc-sun-solaris2.7/2.8.1/specs gcc version 2.8.1 Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1693556 Sep 22 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Oct 7 1999 /lib/libc.so ->
./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Sep 22 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1693556 Sep 22 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Oct 7 1999 /usr/lib/libc.so ->
./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Sep 22 1999 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/opt/mysql-3.23.32 Perl: This is perl, version 5.005_03 built for sun4-solaris

!!! The above is generated by mysqlbug on the system that MySQL runs on. The !!! MySQL source was compiled on an identical system with gcc 2.95.2.

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-fred=nl.x...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php