eZ Community » Forums » Install & configuration » MySQL Limit error during notification...
expandshrink

MySQL Limit error during notification cronjob

MySQL Limit error during notification cronjob

Monday 02 March 2015 10:57:51 pm - 5 replies

Dear all,

Shortly after upgrading to 2014.11.1 in a purely ezlegacy install = ez4 we noticed that notification after content change does not work any longer. After some digging it turns out that the error is due to an SQL statement which does not run through as follows:

 SELECT DISTINCT ezcontentobject.contentclass_id, ezcontentobject.current_version, ezcontentobject.id, ezcontentobject.initial_language_id, ezcontentobject.language_mask, ezcontentobject.modified, ezcontentobject.owner_id, ezcontentobject.published, ezcontentobject.remote_id AS object_remote_id, ezcontentobject.section_id, ezcontentobject.STATUS, ezcontentobject_tree.contentobject_is_published, ezcontentobject_tree.contentobject_version, ezcontentobject_tree.depth, ezcontentobject_tree.is_hidden, ezcontentobject_tree.is_invisible, ezcontentobject_tree.main_node_id, ezcontentobject_tree.modified_subnode, ezcontentobject_tree.node_id, ezcontentobject_tree.parent_node_id, ezcontentobject_tree.path_identification_string, ezcontentobject_tree.path_string, ezcontentobject_tree.priority, ezcontentobject_tree.remote_id, ezcontentobject_tree.sort_field, ezcontentobject_tree.sort_order, ezcontentclass.serialized_name_list AS class_serialized_name_list, ezcontentclass.identifier AS class_identifier, ezcontentclass.is_container AS is_container , ezcontentobject_name.name, ezcontentobject_name.real_translation   FROM ezcontentobject_tree INNER JOIN ezcontentobject ON (ezcontentobject_tree.contentobject_id = ezcontentobject.id) INNER JOIN ezcontentclass ON (ezcontentclass.version = 0 AND ezcontentclass.id = ezcontentobject.contentclass_id) INNER JOIN ezcontentobject_name ON (     ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id AND     ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version )     WHERE ezcontentobject_tree.parent_node_id = 2066 AND       
 ( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
     ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
   + ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) )
   <
     ( ezcontentobject_name.language_id & 1 )
   + ( ( ezcontentobject_name.language_id & 2 ) )
 ) 
 AND ezcontentobject_tree.is_invisible = 0  AND ((ezcontentobject.contentclass_id IN (28, 29, 30, 31, 32, 33, 40) AND ezcontentobject.section_id IN (3)) OR (ezcontentobject.section_id IN (1, 7)))   AND 
 ezcontentobject.language_mask & 3 > 0 
  ORDER BY ezcontentobject.published DESC
LIMIT 0, -1

When changing LIMIT 0, -1 to something awfully high I get up to 27 entries. Could anybody point me to where this might kick off?

Best wishes from Algeria

Sebastian

Modified on Monday 02 March 2015 10:58:51 pm by Sebastian Schoeller

Tuesday 03 March 2015 1:59:06 am

Hello Sebastian,

I'm not so certain that we have enough details to troubleshoot your problems.

What notification handlers are you using? The default and most common is ezsubtree.

The sql you share actually comes from class method: eZContentObjectTreeNode::subTreeByNodeID https://github.com/ezsystems/ezpublish-legacy/blob/master/kernel/classes/ezcontentobjecttreenode.php#L1924

I learn this from searching for matches to 'ezcontentobject_tree.contentobject_id = ezcontentobject.id' (among other factors).

This class method does not appear to be used by the notification system specifically.

Lets study and follow the code execution and debug it specifically in greater detail.

https://github.com/ezsystems/ezpu...aae872/cronjobs/notification.php#L15

https://github.com/ezsystems/ezpu...on/eznotificationeventfilter.php#L29

https://github.com/ezsystems/ezpublish-legacy/blob/97e7c5ef727ceb1c4be977dff5ad4e6bffaae872/kernel/classes/notification/eznotificationevent.php#L179

https://github.com/ezsystems/ezpu...eznotificationcollectionitem.php#L78

https://github.com/ezsystems/ezpu...r/ezsubtree/ezsubtreehandler.php#L57

https://github.com/ezsystems/ezpu...r/ezsubtree/ezsubtreehandler.php#L72
https://github.com/ezsystems/ezpublish-legacy/blob/master/kernel/classes/notification/handler/ezsubtree/ezsubtreehandler.php#L222

My point in following the execution (besides learning how it works in practice) is to point out that I see no use eZContentObjectTreeNode::subTreeByNodeID directly or indirectly in the code execution. No I may have mistakenly overlooked it's use somewhere along the line or you may be using custom template overrides which use fetch functions to call this method (as the default templates in question do not do this) or you may just be confused.

https://github.com/ezsystems/ezpublish-legacy/blob/master/design/standard/templates/notification/handler/ezsubtree/view/plain.tpl

All this said I am not yet convinced that your evaluation of the situation is correct or at least accurate.

I think we need you to debug the notification process in greater detail and provide much more detailed informations on what results your getting specifically, etc.

Another thought, is this a new server? have you verified by hand that emails are correctly being sent and received by eZ on this server in question? This is a common problem with missing notifications (not the cronjob execution which is quite stable and has been for over 5 years). It's just a thought worth validating.

I ran out of time with creating this post but if you share more information in your next post I will try harder to help you solve your problem.

I hope this helps!

Cheers,
Heath

Tuesday 03 March 2015 7:05:12 am

Dear Heath,

 

thanks for the quick response. When executing on the command line

 $ php runcronjobs.php -d frequent
Using siteaccess  for cronjob
Running cronjob part 'frequent'
Running cronjobs/notification.php at: 03.03.2015 06:56
Starting notification event processing
Timing: ()
Script cronjobs/notification.php starting
 
Error: (eZMySQLiDB)
Query error (1064):

I then get the SQL output in the mentioned in the initial post. Email sending did work before on this machine. It could be related to an upgrade but must not necessarily. When I try the SQL from a client I find that the statement is wrong due to the last part "LIMIT 0, -1", where "-1" does not seem to be read correctly. The notification configuration has been overtaken by an upgraded installation and it could be that I have made an upgrade mistake which I did not notice at that time. The content editor is complaining about that he is not receiving notifications any longer. I cannot deliver more debug information at the moment, since my civil engineering job is drawing the attention.

Best

Sebastian

Wednesday 04 March 2015 2:09:53 am

Hello Sebastian,

Please include the full and complete cronjob execution (with full sql and sql error message) as you have not yet provided the sql error in question and it would really help me troubleshoot this problem.

What version of eZ Publish are you using? Oh yeah, 2014.11.1

I clearly understand your evaluation but I have not yet been able to trace it to an actual code problem, etc.

In MySQL "LIMIT 0, 1" would indicate that your query should be run with an offset of 0 and a result limit of 1. In your situation it seems that something is using -1 as the result limit (parameter in php code) which I think should not happen but without an actual sql error or more specific code execution diagnosis to prove the error is the fault of the default notification cronjob I'm still at a loss to explain your results further.

BTW, remember that notification cronjob email attempts are a one shot deal, if the cronjob is run and the emails are not properly sent / received the process is still over and done with. You would have to generate more notification actions and re-run the cronjob (depending on handler settings, re: ezdigest) again to test it again (ie: more than once).

EDIT: To help prove that eZContentObjectTreeNode::subTreeByNodeID is not called by default within the code execution of the notification cronjob, I edited just one vanilla copy of of ezp and added a die('fin') on the first line of this class method. Then I cleared all caches and ran the notification cronjob (with a db full of notifications to be processed).

https://github.com/ezsystems/ezpu...es/ezcontentobjecttreenode.php#L1926

Result: Notification cronjob ran without any error what so ever, which to me proves the error is coming from some part of your installation's code which is customized (non, default) as eZContentObjectTreeNode::subTreeByNodeID is not called by the notification cronjob in my evaluation by default.

I hope this helps!

Cheers,
Heath

Modified on Wednesday 04 March 2015 2:37:29 am by // Heath

Friday 06 March 2015 11:39:47 pm

Hi Heath,

after reviewing the ezworkflow_process table I found a workflow which was not part any longer of the installation. After removing that the cronjobs completed error free. Situation remediated. Thanks for the input and for taking your time. I am sorry for having interrupted. It's work in passing which creates the most troubles (for others) as usual.

Best wishes from Algeria

Sebastian

Saturday 07 March 2015 1:36:52 am

Hello Sebastian,

I'm very pleased to have been able to help you troubleshoot this problem and that you have indeed solved the actual problem you were having.

No worries at all for the back and forth. I'm very used to this answering questions in the forums.

Take it eZ!

Cheers,
Heath

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from