List:General Discussion« Previous MessageNext Message »
From:Ken Williams Date:May 4 1999 3:02am
Subject:Re: SELECT not returning all matching rows
View as plain text  
I just thought of something - before the table started acting wacky, I did a
big delete on it.  I used something like this Perl code:


   my $sth = $dbh->prepare("select thread,message from messages");
   $sth->execute;
   
   while (($thread, $message) = $sth->fetch) {
      unless ($valid{$thread}) {
          $dbh->do("delete from messages where thread=$thread " .
                   "and message='$message'");
      }
   }
   
Is it bad luck to delete records like this when you've got an iterator working
on the same table in the same session?  For this app, I had about 70,000
records in the table, and I deleted about 30,000.


monty@stripped (Michael Widenius) wrote:
>>>>>> "Ken" == Ken Williams <ken@stripped> writes:
>
>>> Description:
>Ken> This is from a mysql session.  Note that the first SELECT statement
>Ken> should have returned the row that the second SELECT statement returned
>Ken> too.
>
>mysql> show columns from messages;
>Ken> +---------+-----------------------+------+-----+---------+-------+
>Ken> | Field   | Type                  | Null | Key | Default | Extra |
>Ken> +---------+-----------------------+------+-----+---------+-------+
>Ken> | thread  | mediumint(8) unsigned |      | PRI | 0       |       |
>Ken> | message | varchar(4)            |      | PRI |         |       |
>Ken> +---------+-----------------------+------+-----+---------+-------+
>Ken> 2 rows in set (0.00 sec)
>
>mysql> select * from messages where thread=116225;
>Ken> +--------+---------+
>Ken> | thread | message |
>Ken> +--------+---------+
>Ken> | 116225 | 0       |
>Ken> +--------+---------+
>Ken> 1 row in set (0.00 sec)
>
>mysql> select * from messages where message = 'abc';
>Ken> +--------+---------+
>Ken> | thread | message |
>Ken> +--------+---------+
>Ken> | 116225 | abc     |
>Ken> +--------+---------+
>Ken> 1 row in set (0.78 sec)
[...]
>Hi!
>
>This looks very confusing!
>
>Any change you could upload the table to
>ftp://www.mysql.com/pub/mysql/secret so that we at TCX can take a look 
>at this.
>
>If this is a bug, then one of the following suggestions may fix this
>problem:
>
>- Change message from VARCHAR to CHAR.
>- Remove message from the 'thread' index.
>
>Regards,
>Monty
>


Thread
SELECT not returning all matching rowsKen Williams24 Apr
  • SELECT not returning all matching rowsMichael Widenius25 Apr
    • Re: SELECT not returning all matching rowsKen Williams4 May