Re: Server hangs and table gets corrupted on simple subselect

[Gleb Paharenko]

Hello.

> As mentioned, none of these work and all of these hang the server and
> break the database.


Even after weird queries server shouldn't hang and break the database.
Switch to the official binaries of the latest release (4.1.14 now)
and check if the problem remains.




"Reitsma, Rene - COB" <reitsmar@stripped> wrote:
> Hi,
> 
> I wonder if someone can help me with the following simple(?) MySQL
> problem.
> 
> I have a table 'action' with about 180,000 web server requests records
> running under=20
> MySQL Ver 14.7 Distrib 4.1.12 (Fedora Core 4)
> 
> mysql>desc action
> +-------------+--------------+------+-----+---------+----------------+
> | Field       | Type         | Null | Key | Default | Extra          |
> +-------------+--------------+------+-----+---------+----------------+
> | id          | int(11)      |      | PRI | NULL    | auto_increment |
> | host_ip     | varchar(16)  |      |     |         |                |
> | file        | varchar(255) |      |     |         |                |
> | querystring | varchar(255) | YES  |     | NULL    |                |
> | timestamp   | datetime     | YES  |     | NULL    |                |
> +-------------+--------------+------+-----+---------+----------------+
> 5 rows in set (0.00 sec)
> 
> From this table, I must delete all records associated with host_ips that
> occur only once in the table (all unique host_ips).
> 
> I have tried the following approaches; all of which hang the server and
> corrupt the table:
> 
> Method 1: First create a 'totals' table that holds for each host_ip the
> number of occurrences in the 'action' table:
> 
> mysql> create table totals as select host_ip, count(*) as hits
>       from action
>       group by host_ip
>       order by hits;
> 
> Next, combine the tables in a query (a 'select' for now, but a 'delete'
> eventually):
> 
> mysql> select from action where host_ip in
>       (
>         select host_ip
>         from totals
>         where hits =3D 1
>       );
> 
> Method 2: use an explicit join:
> 
> mysql> select host_ip
>       from action, totals
>       where action.host_ip =3D totals.host_ip
>       and totals.hits =3D 1;
> 
> Method 3: don't use the 'totals' table at all:
> 
> mysql> select host_ip
>       from action
>       group by host_id having count(*) =3D 1;
> 
> As mentioned, none of these work and all of these hang the server and
> break the database.
> 
> How do I do this?
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com