CiviCRM Community Forums (archive)

If your listed CiviCRM version is correct, then you need to upgrade for this. See here:

http://civicrm.org/blogs/lobo/recap-consulting-project-automated-reminder-emails-and-lot-more

 

I'll try to update the docs when I manage to get the cronjob running correctly. However when I run:

/usr/bin/wget -O - -q -t 1 --post-data='name=someuser&pass=somepass&key=somekey' 'http://foo.org.uk/sites/all/modules/civicrm/bin/action.cronjob.php'

It returns html to the console, when I open this in a browser I see:

Sorry. A non-recoverable error has occurred.
DB Error: no such table
Error Details
Additional Details:
Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] =>
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.source_contact_id as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.source_contact_id AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (1) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110623111720 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1146 ** Table 'hash_civicrm.civicrm_action_log' doesn't exist]
    [type] => DB_Error
    [user_info] =>
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.source_contact_id as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.source_contact_id AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (1) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110623111720 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1146 ** Table 'foo_civicrm.civicrm_action_log' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::handle prefix="" info="
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT e.source_contact_id as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id = e.source_contact_id AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (1) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110623111720 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1146 ** Table 'foo_civicrm.civicrm_action_log' doesn't exist]"]
)

The rable foo_civicrm.civicrm_action _log indeed doesn't exist and I was under the impression that this table only existed if the new logging feature was enabled?

Thanks

Bails

Hi Xavier

The site in question is running CiviCRM 3.4.3

Yes we ran the civicrm/upgrade?reset=1 without any errors.

I can see:


CREATE TABLE civicrm_action_log (


     id int unsigned NOT NULL AUTO_INCREMENT  ,
     contact_id int unsigned    COMMENT 'FK to Contact ID',
     entity_id int unsigned NOT NULL   COMMENT 'FK to id of the entity that the action was performed on. Pseudo - FK.',
     entity_table varchar(255)    COMMENT 'name of the entity table for the above id, e.g. civicrm_activity, civicrm_participant',
     action_schedule_id int unsigned NOT NULL   COMMENT 'FK to the action schedule that this action originated from.',
     action_date_time datetime    COMMENT 'date time that the action was performed on.',
     is_error tinyint   DEFAULT 0 COMMENT 'Was there any error sending the reminder?',
     message text    COMMENT 'Description / text in case there was an error encountered.',
     repetition_number int unsigned NOT NULL   COMMENT 'Keeps track of the sequence number of this repetition.'
,
    PRIMARY KEY ( id )
 
 
,     
     CONSTRAINT FK_civicrm_action_log_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE CASCADE,     
     CONSTRAINT FK_civicrm_action_log_action_schedule_id FOREIGN KEY (action_schedule_id) REFERENCES civicrm_action_schedule(id) ON DELETE CASCADE 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

In civicrm/sql/civicrm.mysql

Should I create the this table by hand?

ARGHHHHH

bug in upgrade script. this is not included. Can you please file an issue, will be fixed for the next release

in the meantime, u can create the table manually from a clean 3.4.4 mysql

lobo

Hi Just tried setting another reminder on another 3.4.4 site only to receive the following when cron runs.


    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT  as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id =  AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (2) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110624143029 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as actio' at line 2]
    [type] => DB_Error
    [user_info] =>
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT  as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id =  AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (2) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110624143029 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as actio' at line 2]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
INSERT INTO civicrm_action_log (contact_id, entity_id, entity_table, action_schedule_id)
SELECT  as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as action_schedule_id
FROM civicrm_activity e

LEFT JOIN civicrm_action_log reminder ON reminder.contact_id =  AND
reminder.entity_id    = e.id AND
reminder.entity_table = 'civicrm_activity' AND
reminder.action_schedule_id = 1
WHERE e.activity_type_id IN (2) AND e.status_id IN (1) AND reminder.id IS NULL AND 20110624143029 >= DATE_SUB(e.activity_date_time, INTERVAL 1 hour) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as contact_id, e.id as entity_id, 'civicrm_activity' as entity_table, 1 as actio' at line 2]"]
)

Reminder set was:

activity > phone call > scheduled > when 1hr before activity date time > recipients = activity assignees

Is this issue is resolved or still in working condition? please let me know becuase i got the same issue too.
thanks

> do i have to setup the cronjobs for schedule reminders first.

Yes.

Lobo wrote:

> the cronjob is: bin/action.cronjob.php

So you need a cron job for that. The syntax ist the same as the CiviMail cron, just the "civimail.cronjob.php" changes to action.cronjob.php. See here:

http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviMail+Installation#CiviMailInstallation-SettingUptheCronJob

for the basic idea.