469,658 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

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.

Jan 2 '07 #1
3 2322
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
Jan 5 '07 #2
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.

Michael Austin a écrit :
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
Jan 5 '07 #3
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

Jan 6 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Gary L. Burnore | last post: by
8 posts views Thread by William Drew | last post: by
reply views Thread by Matt Weakly | last post: by
6 posts views Thread by Mikael Syska | last post: by
10 posts views Thread by bill | last post: by
reply views Thread by Aahz | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.