Bug #4655 | SELECT clause ignoring one WHERE condition | ||
---|---|---|---|
Submitted: | 20 Jul 2004 20:26 | Modified: | 17 Sep 2004 18:35 |
Reporter: | EDUARDO DIAZ COMELLAS | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.0.20 | OS: | Linux (Debian) |
Assigned to: | Dean Ellis | CPU Architecture: | Any |
[20 Jul 2004 20:26]
EDUARDO DIAZ COMELLAS
[26 Jul 2004 18:40]
Dean Ellis
Can you submit a test case (either the VB or the Perl case would be fine) that demonstrates this behavior?
[26 Jul 2004 18:46]
EDUARDO DIAZ COMELLAS
There are 2 apps accessing this table. The relevant code follows: APP #1: sql = "INSERT INTO pruebas_rx_tbl (fecha, ip, puerto, nserie, tt, datos)" sql = sql & " VALUES ('" & Format(fecha, "yyyy-mm-dd Hh:Nn:Ss") & "', '" & ip & "'" & ", " & puerto _ & ", " & nserie & ", " & ", " & tt & ", '" & datos & "')" dbGps.Execute sql, , adExecuteNoRecords APP #2: sql = "SELECT * FROM pruebas_rx_tbl" sql = sql & " WHERE procesado = 0 AND tt = 26 OR procesado = 0 AND tt = 27" sql = sql & " ORDER BY rx_id ASC LIMIT 10000" rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly If rsPruebasRx.RecordCount >= 10000 Then Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _ & rsPruebasRx.RecordCount & ") de tipo 26 o 27 sin procesar!" & vbCrLf, True) Else Do While Not rsPruebasRx.EOF 'ponemos a procesado seg = Second(Now) If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then 'dejamos algunos segundos sin procesar... dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords End If rsPruebasRx.MoveNext Loop End If rsPruebasRx.Close DoEvents sql = "SELECT * FROM `pruebas_rx_tbl`" sql = sql & " WHERE `procesado` = 0 AND `tt` != 26 AND `tt` != 27 AND `tt` != 9" sql = sql & " ORDER BY tt ASC, rx_id DESC LIMIT 10000" rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly If rsPruebasRx.RecordCount >= 10000 Then Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _ & rsPruebasRx.RecordCount & ") de tipo <>9, <>26 y <>27 sin procesar!" & vbCrLf, True) Else Do While Not rsPruebasRx.EOF 'ponemos a procesado seg = Second(Now) If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then 'dejamos algunos segundos sin procesar... dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords End If rsPruebasRx.MoveNext Loop End If rsPruebasRx.Close DoEvents sql = "SELECT * FROM `pruebas_rx_tbl`" sql = sql & " WHERE `procesado` = 0 AND `datos` IS NOT NULL AND `tt` = 9" sql = sql & " ORDER BY rx_id DESC LIMIT 10000" rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly If rsPruebasRx.RecordCount >= 10000 Then Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _ & rsPruebasRx.RecordCount & ") de tipo 9 sin procesar!" & vbCrLf, True) Else Do While Not rsPruebasRx.EOF 'ponemos a procesado seg = Second(Now) If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then 'dejamos algunos segundos sin procesar... dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords End If rsPruebasRx.MoveNext Loop End If rsPruebasRx.Close DoEvents
[26 Jul 2004 18:52]
EDUARDO DIAZ COMELLAS
This perl app is just a monitor that prints out the number of rows returned. I wrote it becouse a couldn't believe that this was a MySQL error. The typical output counts from 0 to 30 rows to each query. The bad one counts arround 500.000 for the first and 30.000 for the second. May you need any further information, don't hesitate to contact me. If you want the logs or even access to the machine, please contact me at ediaz at ultreia dot es #!/usr/bin/perl # use DBI; $|=1; ($retardo)=@ARGV; die "Sintaxis: $0 milisegundos\n" if($#ARGV != 0) ; $db = DBI->connect ("dbi:mysql:dbname=mydb:host=192.168.1.10","blabla","blablabla")|| die "Fallo al conectar a la base de datos\n"; while(1) { ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); $dat1=$db->selectall_arrayref("SELECT COUNT(*) FROM `rx_tbl` WHERE 1 AND `procesado` = 0 AND `tt` <> 26 AND `tt` <> 27 AND `tt` <> 9"); $err1=$db->err.":".$db->errstr; $dat2=$db->selectall_arrayref("SELECT COUNT(*) FROM `rx_tbl` WHERE `procesado` = 0 AND `tt` = 26 OR `procesado` = 0 AND `tt` = 27" ); $err2=$db->err.":".$db->errstr; print $year."/".$mon."/".$mday." ".$hour.":".$min.":".$sec.",".${$$dat1[0]}[0].",".${$$dat2[0]}[0].",".$err1.",".$err2."\n"; select(undef, undef, undef, $retardo/1000); } $db->disconnect;
[17 Sep 2004 18:35]
Dean Ellis
After extensive testing, I am unable to repeat this. This may be a problem with the build you are using; try our official, current 4.0 release binary and our current MyODBC driver instead.