[xwiki-users] Bad HQL queries - xwiki-users@xwiki.org

Caleb James DeLisle

17 Jan 17 Jan

4:49 p.m.

I think at the database level you can tell it to cease if there is a query which goes on for more than x milliseconds. http://mtop.sourceforge.net/ mkill can kill long running mysql queries. In postgres it's a configuration parameter: http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-ST… Caleb 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%')") #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.). Now my question is: Are there any remedies against this? MySQL configuration statements that prevent this kind of behaviour?

Reply

Caleb James DeLisle

18 Jan 18 Jan

7:06 a.m.

I must confess that this was nothing more than the result of a little Googling. My instincts tell me that there must be something to stop long running queries. I run postgres and have set it to log all queries which take over 1/4 second but I know very little about mysql. Caleb Paul Libbrecht wrote:

...

Caleb, Very interesting but... The last release is of 2004, are you using this with a contemporary mysql? paul Le 17-janv.-10 à 16:49, Caleb James DeLisle a écrit :

I think at the database level you can tell it to cease if there is a query which goes on for more than x milliseconds. http://mtop.sourceforge.net/ mkill can kill long running mysql queries. In postgres it's a configuration parameter: http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-ST… Caleb 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%')") #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.). Now my question is: Are there any remedies against this? MySQL configuration statements that prevent this kind of behaviour?

_______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users

Reply

Sergiu Dumitriu

1:06 p.m.

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.

...

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

-- Sergiu Dumitriu http://purl.org/net/sergiu/

Reply

Paul Libbrecht

19 Jan 19 Jan

4:54 p.m.

Shouldn't there be a setting in DBCP configs? paul Le 18-janv.-10 à 13:44, Anca Luca a écrit :

...

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

_______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users

Reply

Paul Libbrecht

18 Sep 18 Sep

9:19 p.m.

Sorry to respond to myself but I really feel I had this information earlier. The summary: MySQL has no protection on queries that may return a zillion of documents... it honestly tries to accumulate things. This happens if you have a "wrong" query which, for example, has a free variable, e.g., as an argument to searchDocuments: , BaseObject as obj where obj.name=doc.fullName and doc.web != 'AssetTemp' this is arguably false but happens if you fiddle too much with queries... and, as I said, this creates a MySQL that locks any other connection (at least of interest to xwiki). The solution is: never do wrong your queries. But well. The alternate solution is: restart your DB or let your sysadmin do it. Yet another solution But there's a better solution: MySQL's "show processlist". This is an SQL statement that shows you queries currently running, since when, etc. The "killer queries" that I encountered above could easily be identified: - long running - displaying status "copying to tmp table" - start of a flood of locked queries it was then sufficient to "kill xxxx" where xxxx is the id of the query. Before that you can "show full processlist" to get all of the SQL query and start to wonder which XWiki code has created it. I wonder if this is not something that XWiki could support as a tool to debug such issues, ideally with location bound to queries. paul Le 23 juil. 2010 à 00:40, Paul Libbrecht a écrit :

...

I'm back with this subject, it's really a haunting subject. When at this stage, I can only wait that a graceful person restarts the MySQL thus killing the "bad" query. I would wish a DBCP or hibernate configuration for a maximum time (which I'd put around 30 seconds because indeed, overload may cause some queries to be very slow, but that would still release my MySQL after those 30 seconds). In many cases, paging doesn't help solve this issue since sorting is often activated. Hints would be really welcome. thanks in advance paul Le 18-janv.-10 à 13:44, Anca Luca a écrit :

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.

_______________________________________________ users mailing list users(a)xwiki.org http://lists.xwiki.org/mailman/listinfo/users

Reply