MySQL crashes during request

[max_mont]

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.

[Michael Austin]

Post by max_mont
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.
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.

[max_mont]

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=b.Ve_Groupe
where a.Ve_Pa_Id = 148 and a.Ve_Val ' at line 4"

Have you got an idea ?

Many thanks in advance.

Post by Michael Austin

Post by max_mont
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.
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.
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'));
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

[Michael Austin]

Post by max_mont
Thanks for your help but I'm not an expert in SQL language.

[snip unecessary]

Post by max_mont

Post by Michael Austin
Looks like a poor design with multiple types of data in one table.

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.

Post by max_mont

Post by Michael Austin
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