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?
  • 1
    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? Commented Dec 22, 2016 at 7:42

1 Answer 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 );

  • 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? Commented Feb 23, 2017 at 14:30
  • JIRA issue is probably this one issues.civicrm.org/jira/browse/CRM-20165 Nicolas at cividesk, can you confirm?
    – TwoMice
    Commented Mar 5, 2022 at 23:11
  • Yes it is, thanks @TwoMice.
    – cividesk
    Commented Mar 7, 2022 at 2:45
  • I just installed a fresh 5.72.3 under WordPress and the date column remains NULL. I modified in MySQL table structure putting current_timestamp() in default value and ON UPDATE CURRENT_TIMESTAMP() in Extra. Now the date is populated, but is this correct? Commented May 31 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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