MySQL crashes during request

Hi,
I'm working on MySQL 5.0 on Windows XP.
I've tested a request on MSAccess and it's working. But if I launch it
under MySQL, the PC crashes and I have to restart server.
this is the request :
SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 147 and
Ve_Val ='NO_OUTIL' and Ve_Groupe
IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
148 and Ve_Val ='FIB' and Ve_Groupe
IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
149 and Ve_Val ='1.4732'));

There are 3 requests. If I put 2 requests, it's running. But If I put
more than 2 requests, it crashes.

Have you an an idea about this problem ?

Many thanks in advance for your help.

Re: MySQL crashes during request

max_mont wrote:

> Hi,
> I'm working on MySQL 5.0 on Windows XP.
> I've tested a request on MSAccess and it's working. But if I launch it
> under MySQL, the PC crashes and I have to restart server.
> this is the request :
> SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 147 and
> Ve_Val ='NO_OUTIL' and Ve_Groupe
> IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
> 148 and Ve_Val ='FIB' and Ve_Groupe
> IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =
> 149 and Ve_Val ='1.4732'));
>
> There are 3 requests. If I put 2 requests, it's running. But If I put
> more than 2 requests, it crashes.
>
> Have you an an idea about this problem ?
>
> Many thanks in advance for your help.
>


Looks like a poor design with multiple types of data in one table.

Your query:
SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id = 147 and
Ve_Val ='NO_OUTIL' and Ve_Groupe IN
(SELECT Ve_Groupe From bd_resultat.Valeurs_Entree
Where Ve_Pa_Id = 148 and Ve_Val ='FIB'
and Ve_Groupe IN
(SELECT Ve_Groupe From bd_resultat.Valeurs_Entree
Where Ve_Pa_Id = 149 and Ve_Val ='1.4732'));

First cut:

SELECT * From bd_resultat.Valeurs_Entree
Where Ve_Pa_Id = 147 and Ve_Val ='NO_OUTIL' and Ve_Groupe IN
(
SELECT distinct a.Ve_Groupe From bd_resultat.Valeurs_Entree a,
bd_resultat.Valeurs_Entree b
on a.Ve_Groupe=b.Ve_Groupe
where A.Ve_Pa_Id = 148 and a.Ve_Val ='FIB'
and B.Ve_Pa_id = 149 and b.Ve_Val ='1.4732'
);

you can investigate other join syntax to get what you need rather than the
sub-selects to the same table which can be quite expensive in terms of performance.

--
Michael Austin.
Database Consultant

Re: MySQL crashes during request

Thanks for your help but I'm not an expert in SQL language.
I've tried your request but I've an error :

"You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'on a.Ve_Groupe=3Db.Ve_Groupe
where a.Ve_Pa_Id =3D 148 and a.Ve_Val ' at line 4"

Have you got an idea ?

Many thanks in advance.



Michael Austin a =E9crit :

> max_mont wrote:
>
> > Hi,
> > I'm working on MySQL 5.0 on Windows XP.
> > I've tested a request on MSAccess and it's working. But if I launch it
> > under MySQL, the PC crashes and I have to restart server.
> > this is the request :
> > SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =3D 147 and
> > Ve_Val =3D'NO_OUTIL' and Ve_Groupe
> > IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =3D
> > 148 and Ve_Val =3D'FIB' and Ve_Groupe
> > IN (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =3D
> > 149 and Ve_Val =3D'1.4732'));
> >
> > There are 3 requests. If I put 2 requests, it's running. But If I put
> > more than 2 requests, it crashes.
> >
> > Have you an an idea about this problem ?
> >
> > Many thanks in advance for your help.
> >
>
>
> Looks like a poor design with multiple types of data in one table.
>
> Your query:
> SELECT * From bd_resultat.Valeurs_Entree Where Ve_Pa_Id =3D 147 and
> Ve_Val =3D'NO_OUTIL' and Ve_Groupe IN
> (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree
> Where Ve_Pa_Id =3D 148 and Ve_Val =3D'FIB'
> and Ve_Groupe IN
> (SELECT Ve_Groupe From bd_resultat.Valeurs_Entree
> Where Ve_Pa_Id =3D 149 and Ve_Val =3D'1.4732'));
>
> First cut:
>
> SELECT * From bd_resultat.Valeurs_Entree
> Where Ve_Pa_Id =3D 147 and Ve_Val =3D'NO_OUTIL' and Ve_Groupe IN
> (
> SELECT distinct a.Ve_Groupe From bd_resultat.Valeurs_Entree a,
> bd_resultat.Valeurs_Entree b
> on a.Ve_Groupe=3Db.Ve_Groupe
> where A.Ve_Pa_Id =3D 148 and a.Ve_Val =3D'FIB'
> and B.Ve_Pa_id =3D 149 and b.Ve_Val =3D'1.4732'
> );
>
> you can investigate other join syntax to get what you need rather than the
> sub-selects to the same table which can be quite expensive in terms of pe=
rformance.
>=20
> --=20
> Michael Austin.
> Database Consultant

Re: MySQL crashes during request

max_mont wrote:
> Thanks for your help but I'm not an expert in SQL language.
[snip unecessary]

>>Looks like a poor design with multiple types of data in one table.
>>
>>First cut:
>>
Try this:
SELECT * From bd_resultat.Valeurs_Entree
Where Ve_Pa_Id = 147 and Ve_Val ='NO_OUTIL' and Ve_Groupe IN
(
SELECT distinct a.Ve_Groupe From bd_resultat.Valeurs_Entree a,
bd_resultat.Valeurs_Entree b
where a.Ve_Groupe=b.Ve_Groupe
and A.Ve_Pa_Id = 148 and a.Ve_Val ='FIB'
and B.Ve_Pa_id = 149 and b.Ve_Val ='1.4732'
);

or:

SELECT * From bd_resultat.Valeurs_Entree a,
bd_resultat.Valeurs_Entree b,
bd_resultat.Valeurs_Entree c
where a.Ve_Groupe=b.Ve_Groupe
and b.Ve_groupe=c.Ve_groupe
and a.Ve_Pa_Id = 147 and a.Ve_Val ='NO_OUTIL'
and b.Ve_Pa_Id = 148 and b.Ve_Val ='FIB'
and c.Ve_Pa_id = 149 and c.Ve_Val ='1.4732';

make sure you have an index (primary or otherwise) on ve_groupe, Ve_Pa_Id and
Ve_Val.


>>
>>you can investigate other join syntax to get what you need rather than the
>>sub-selects to the same table which can be quite expensive in terms of performance.
>>
>>--

Michael Austin.
Database Consultant