Hi Sergiu, On 01/18/2010 02:06 PM, Sergiu Dumitriu wrote:
On 01/18/2010 10:15 AM, Anca Luca wrote:
Hi Flavius,
On 01/17/2010 05:23 PM, Flavius Olaru wrote:
Hi,
I've done a installation on my localhost (Windows XP machine) of XE 2.1.1, Tomcat 6 and MySQL 5.1. After this i wrote a bad hql query and executed it which brought MySQL to spyke the CPU usage to 100% and even more rendering both the localhost and database unusable.
{{velocity}} #set($badhql = ", BaseObject obj, StringProperty stringprop, LargeStringProperty largestringprop where (doc.name like '%$text%' or doc.content like '%$text%') or (obj.name=doc.fullName and stringprop.id.id=obj.id and stringprop.value like '%$text%') or (obj.name=doc.fullName and largestringprop.id.id=obj.id and largestringprop.value like '%$text%')")
as we already discussed (but logging here for posterity), I don't see why this would be called a "bad hql". It highly depends on the specific instance, the data in it, etc. Any query which is 'good' on one instance can be 'bad' on another one.
This one IS bad, no matter how you try to look at it. It joins all documents with all possible objects, and all possible string and largestring properties, since the link between them is OR-ed, not AND-ed. It should be a left(or right?)-join relationship, but frankly in my early, hasty tests with this kinds of joins in HQL, I never got it to work, since there are 4 tables to join. So, another approach would be to put the property test in an "or exists(...)", which is not as performant as a left join, but at least it selects properly matched objects and properties.
agreed for this particular case, but the discussion was about hqls which take too much time. To the limit, somebody might _actually_ need all these tables joined, and we wouldn't call that "bad", wouldn't we? If we put the problem like this, then we have a conversation about how to enforce people to join tables only by foreign keys (which we don't even have set in xwiki iirc), which is another discussion. Thanks, Anca
Also, the responsiveness / unresponsiveness of the server depends on the configuration of the machine, but I agree that there can be queries& instances that would render any machine (build with current technologies) unusable.
I personally prefer to be able to write whatever I want, even if it renders the machine unusable, I needed it in some situations and I prefer this to _not_ be enforced: if I want a query to take longer, I should be able to do it and setup my own safety nets (warn user, etc). After all, it's an application platform and the application developers are (anyway, should be, in an ideal world) responsible of what they're writing.
#set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
{{include document="XWiki.Results"/}} {{/velocity}}
The bad thing is that $xwiki.searchDocuments brought me to this state (no programming rights.).
If there would be a possibility to allow 'bad queries' only for programming APIs (considering that all other calls should be "safe"), then this could be a decent middle way, otherwise I'm for no limits.
Now my question is: Are there any remedies against this? MySQL configuration statements that prevent this kind of behaviour?
as Oana was remarking earlier in a conversation, the settings could be done on database server side, regardless of xwiki, and depending on what your specific needs on an instance are.
Thanks, Anca