Discussion :

[isa150183]

Bonjour à tous
Aprés des jours de tests, et plusieurs arrets de mon site (service mysqld), je suis sure que c'est cette requete $sql qui fait tout planter :

function list_recipes ($qdb, $qdborder, $limit) {
GLOBAL $db, $module_name, $pagenum, $query, $op;
if ($pagenum == "") { $pagenum = 1 ; }
$offset = ($pagenum-1) * $_SESSION["recipnum"] ;
$sql = "select Distinct R.id, recipename, contributer, photo, source, counter,S.score/S.number_votes as rate from ".$_SESSION["recipeprefix"]."_recipes R LEFT JOIN ".$_SESSION["recipeprefix"]."_score S ON(R.id=S.id) LEFT join ".$_SESSION["recipeprefix"]."_recipes_categories_rl C ON(R.id=C.id) LEFT join ".$_SESSION["recipeprefix"]."_categories N ON(N.id=C.cat_id) $qdb $qdborder limit $offset, $limit";
//echo "$sql";

list_recipes_dis ($qdb, $qdborder, $limit,$sql);

}

".$_SESSION["recipeprefix"]." = nuke

Elle provient du modules recipes pour phpnuke (je veux garder phpnuke, là n'est pas la question alors pas de remarque sur phpnuke svp )

Voici mon info.log où l'on voit l'erreur

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x806fcf4
0x81219e8
0x814a4b6
0x814a3a3
0x819a3f4
0x8092286
0x808e4a5
0x80769a0
0x807aacc
0x8075b55
0x8074ef7
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8315ad0 = select Distinct R.id, recipename, contributer, photo, source, counter,S.score/S.number_votes as rate from recipes_recipes R LEFT JOIN recipes_score S ON(R.id=S.id) LEFT join recipes_recipes_categories_rl C ON(R.id=C.id) LEFT join recipes_categories N ON(N.id=C.cat_id) limit 30, 30
thd->thread_id=47

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 47 did to cause the crash. In some cases of really
bad corruption, the values shown above may be invalid

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash

Que puis je faire ? Comment optimiser cette requete ?
Merci beaucoup

[Adjanakis]

Bonjour,

La requête a-t-elle été reproduite via un client MySQL ?

[isa150183]

Et bien cette requete est dans un fichier index.php de mon site, comment loptimiser ou autre?

[Adjanakis]

que donne un EXPLAIN de cette requête ?

[isa150183]

Bonjour
Maintenant je suis sure et certaine que c'est bien la requete qui fait planter le serveur.

Voici le explain :

EXPLAIN SELECT DISTINCT R.id, recipename, contributer, photo, source, counter, S.score / S.number_votes AS rate FROM recipes_recipes R LEFT JOIN recipes_score S ON ( R.id = S.id ) LEFT JOIN recipes_recipes_categories_rl C ON ( R.id = C.id ) LEFT JOIN recipes_categories N ON ( N.id = C.cat_id )

table type possible_keys key key_len ref rows Extra
R ALL NULL NULL NULL NULL 249 Using temporary
S eq_ref PRIMARY PRIMARY 4 R.id 1
C ref PRIMARY PRIMARY 4 R.id 1 Using index; Distinct
N eq_ref PRIMARY PRIMARY 4 C.cat_id 1 Using index; Distinct

Je ne sais pas ce que cela veut dire ?

Merci !!