>Description:
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)
>How-To-Repeat:
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.
>Fix:
Unknown
>Submitter-Id: <[EMAIL PROTECTED]>
>Originator: Fred van Engen
>Organization: XO Communications B.V.
>MySQL support: none
>Synopsis: NULL and 0 values when reading from MERGE table while INSERTing
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.32 (Source distribution)
>Environment:
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.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
