DB problems again

[Andreas Heinz]

Hi Peter,

i could imagine what's this query for, but unfortunatly this doenst lead 
me to a conclusion to where this mysql hang comes from.
i just pasted this query into phpmyadmin and directly into mysql 
console. both times it generated the same behavior: mysql server uses 
100% cpu and doesnt react anymore.

i'm using mysql5, could that be an issue?

thanks for your help,
Andreas

Peter C. Ndikuwera wrote:
> That's just the query to select the highest rated albums in your 
> collection (for the "Favourite Albums" section of the startup tab).
> 
> Have you tried running this query on your database from the mysql prompt?
> 
> On 11/24/06, *Andreas Heinz* <andi at a80.net <mailto:andi at a80.net>> wrote:
> 
>     As nobody responded, i just enabled logging of my mysql server. the
>     following sql query seems to end in end endless loop:
> 
>       SELECT BINARY
>     album.name <http://album.name>,album.id
>     <http://album.id>,Avg(COALESCE(NULLIF(statistics.percentage, 0), 50))
>     AS Avgstatisticspercentage,artist.id <http://artist.id> FROM tags
>     LEFT JOIN album ON
>     album.id=tags.album LEFT JOIN artist ON artist.id=tags.artist LEFT JOIN
>     statistics ON statistics.url=tags.url AND statistics.deviceid =
>     tags.deviceid  WHERE 1 AND ( 1 AND album.name <http://album.name> <>
>     'Unknown' AND
>     album.name <http://album.name> <> ''  ) AND tags.sampler = 0  AND
>     tags.deviceid IN
>     (1,2,3,4,-1) GROUP BY album.id <http://album.id>,artist.id
>     <http://artist.id>,BINARY album.name <http://album.name> HAVING
>     Count(album.id <http://album.id>)>3 AND Avg(statistics.percentage)>0
>     ORDER BY
>     Avgstatisticspercentage DESC  LIMIT 5 OFFSET 0
> 
>     can someoney imagine why?
> 
>     i did an example query to check if " tags.url = statistics.url and
>     tags.deviceid = statistics.deviceid" gives any result and it does. so
>     i'm clueless where this database hang comes from :(
> 
>     thanks
>     Andreas
> 
>     Andreas Heinz wrote:
>      > Hi there,
>      >
>      > after i posted recently my problems moving a nfs share. i'm now
>     at that
>      > point that i want to insert a backup of the stats, but if i do
>     and start
>      > amarok, amarok hangs at the start and tells me that it needs long to
>      > start, if maybe something ist wrong.
>      >
>      > all the stats rows look like that one which amarok creates when i
>     have
>      > an empty stats table and let amarok create a few new records, to
>     test if
>      > amarok itself works. so the problem seems to be in the statsbackup.
>      >
>      > all device id point to device id 2 with is connected to /. path
>     is also
>      > correct.
>      >
>      > btw the mysql server is fully loaded when amarok hangs, so it
>     seems to
>      > be a db problem right? maybe somekind of query that's not correct?
>      > unfortunatly i dont know how to tell, at which query amarok hangs.
>      >
>      > maybe someone of you has a clue what the problem could be? :)
>      >
>      > thanks again
>      > Andreas
>      > _______________________________________________
>      > Amarok mailing list
>      > Amarok at kde.org <mailto:Amarok at kde.org>
>      > https://mail.kde.org/mailman/listinfo/amarok
>      >
>     _______________________________________________
>     Amarok mailing list
>     Amarok at kde.org <mailto:Amarok at kde.org>
>     https://mail.kde.org/mailman/listinfo/amarok
> 
> 
> 
> 
> -- 
> "Gentlemen, you can't fight in here! This is the War Room" - The 
> President (Dr. Strangelove)
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok