1

Currently, my civicrm_job_log is the largest table in our database. There was a post Can I safely trim civicrm_job_log and civicrm_log? that mentioned an extension that would keep this table trimmed based on an age. However, in my civicrm_job_log all the run_time timestamps are NULL. How can I get these recorded correctly so that I can keep this log trimmed? fwiw, the timestamps in civicrm_log, noting changes to the database, are filled in correctly [this is also a rather bloated table that I would like to trim]. So, two questions [sorry]

  1. How do I get non-NULL run_times in my job log table?
  2. How can I best manage civicrm_log and civicrm_job_log?
  • I might add that in Administer > System Settings > Scheduled Jobs, if I go to ANY job and View Job Log, the Date column is blank, just as observed by looking at the database directly. The date-time the last job was run is correct. It is just not being inserted into the database. Is there a function that does it? Does it rely on any stored MySQL routines in the database itself? I have imported MySQL backups on a few occasions as we work through a few glitches in our first CiviCRM setup. Although, I'm sure the DB structure and tables are getting filled in, perhaps I lost something along the way? – Karl Rudnick Dec 22 '16 at 7:42
1

Funny thing, while the Date column is filled out with a value in CiviCRM 4.6, it is empty in CiviCRM 4.7. I have checked on my customer's instances and the run_time column is in fact present but empty on 4.7. I will file a JIRA issue on this topic.

If your main concern is cleaning-up this table, you can easily do this by only keeping the last 1,000 or so entries in this table. This is easily done as the civicrm_job_log table, like many CiviCRM table, as an id column that is auto-increment. So you would do this to keep that last 1,000 entries:

-- from http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n/8303440#8303440 DELETE FROMcivicrm_job_log WHERE id <= ( SELECT id FROM ( SELECT id FROMcivicrm_job_log ORDER BY id DESC LIMIT 1 OFFSET 1000 -- keep this many records ) foo );

| improve this answer | |
  • I was planning to use the id to keep the last 1000 entries. I appreciate the reference to the exact query to do that. Seems like that should be part of the cleanup function that we schedule daily. How can I track the JIRA issue? – Karl Rudnick Feb 23 '17 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.