Bug #2488 Selected result not mutch for where condition

• Submitted: 23 Jan 2004 3:59
• Modified: 28 Jun 2004 19:12
• Reporter: Alexander Zaika
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 3.23.58
• OS: Linux (Linux)
• CPU Architecture: Any

[23 Jan 2004 3:59] Alexander Zaika

Description:
We have perl script: 
 
$sql = "select idnotification, datecreate, sendername, senderemail, 
receivername, receiveremail, status from notifications where status=0"; 
$status_st=$from->prepare($sql); 
$status_st->execute() || sendmailadmin($fromtoadmin, $adminemail, "Cannot 
start sendpostcard.pl: SQL Error", "$!"); 
while(@row=$status_st->fetchrow()) 
{ 
        if($row[6] != "0") 
        { 
                $body = "SQL: $sql\n MailId: ".$row[0]."; DateCreate: ".
$row[1]."; SenderName: ".$row[2]. 
                                "; SenderEmail: ".$row[3]."; ReceiverName: ".
$row[4]."; status: ".$row[6]."\n"; 
 
                sendmailadmin($fromtoadmin, $adminemail, "Cannot send 
notifications: Posible error in MySQL !!!", $body); 
        } 
..... 
 
And we received emails: 
SQL: select idnotification, datecreate, sendername, senderemail, receivername, 
receiveremail, status from notifications where status=0 
 MailId: 1; DateCreate: 2003-10-23; SenderName: drago; SenderEmail: 
[e-mail]; ReceiverName: drago; status: 2 
 
Table notifications have about 1 500 000 records. 
 

How to repeat:
I dont know :(, this script runs every 5 minutes and about 1 peer week we have  
this error.  
We debugging this problem about 1 mounth and think "Possible error in MySQL 
server ?" 
 
For more information (dump etc.) contact me: 
Email: [e-mail] 
Icq:   97246740

[25 Jan 2004 11:57] Alexander Zaika

Also we wrote shell script: 
 
#!/bin/bash 
 
sql="select idnotification, datecreate, sendername, senderemail, receivername, 
receiveremail, status from notifications where status=0" 
 
count=5000000 
 
i=0 
while [ ! $i = $count ]; do 
 
i=$(( $i + 1 )) 
echo $sql > "res/$i" 
echo $sql|mysql -utarj_user -ptarj_user tarjetas_postcards >> "res/$i" 
lc=(`wc -l "res/$i"`) 
if [ $lc -lt 1000 ]; then 
    rm -f "res/$i" 
fi 
echo $i 
 
done 
 
 
And we received next 341940th file: 
 
select idnotification, datecreate, sendername, senderemail, receivername, 
receiveremail, status from notifications where status=0 
idnotification  datecreate      sendername      senderemail     receivername    
receiveremail   status 
60      2003-10-16      Juli    [e-mail]       Claudia 
[e-mail]    2 
58      2003-10-16      alex    [e-mail]    HUGO    
[e-mail]    2 
57      2003-10-16      Arlette [e-mail]      Eduardo Varela  
[e-mail]        2 
56      2003-10-16      Patricia        [e-mail]  Carlos  
[e-mail]       2 
55      2003-10-16      daniel  [e-mail]        emmanuel        
[e-mail]     2 
54      2003-10-16      Arlette [e-mail]      Eduardo Varela  
[e-mail]        2 
53      2003-10-16      alex    [e-mail]    hugo    
[e-mail]    2 
52      2003-10-16      Eliut Rossetti  [e-mail] Sra. Felicidad 
de Rossetti      [e-mail]     2 
              ... ... ...

[26 Jan 2004 12:57] Dean Ellis

To narrow the possible points of failure, please add the complete CREATE TABLE statement for this table, and let me know what version of perl/DBD/DBI you are using.

[26 Jan 2004 13:39] Alexander Zaika

CREATE TABLE notifications 
( 
  idnotification int(11) DEFAULT '0' NOT NULL auto_increment, 
  datecreate date DEFAULT '0000-00-00' NOT NULL, 
  sendername char(50) DEFAULT '' NOT NULL, 
  receivername char(50) DEFAULT '' NOT NULL, 
  senderemail char(50) DEFAULT '' NOT NULL, 
  receiveremail char(50) DEFAULT '' NOT NULL, 
  status int(1) DEFAULT '0' NOT NULL, 
  PRIMARY KEY (idnotification) 
); 
create index notifications_datecreate on notifications(datecreate); 
create index notifications_status on notifications(status); 
 
We installed MySQL from sources (gcc - 3.3.2), our configuring script: 
./configure --prefix=/opt/mysql \ 
--enable-shared=yes \ 
--enable-static=no \ 
--enable-thread-safe-client \ 
--enable-assembler \ 
--with-gnu-ld \ 
--with-pthread \ 
--with-mysqld-user=mysql \ 
--without-debug 
 
System based on RedHat 7.3 (perl/DBD/DBI - not updated), 
Hardware: Intel dual Xeon (4 CPU). 
 
Now we updated mysql server from binaries, and make tests - for the present 
all work.

[29 Jan 2004 10:30] Alexander Zaika

We got similar error on binary distribution of MySQL 3.23.58 too, 
 
We have 5 didecated (3 xeon - SMP, 2 - P4) servers with equals DB 
on each server we have this problem. 
 
If You need SQL DUMP, remote access please conntact to as.