Answers.Body: <p>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.</p>
  
  <p>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:</p>
  
  <p><code>-- from http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n/8303440#8303440
  DELETE FROM</code>civicrm_job_log<code>
    WHERE id &lt;= (
      SELECT id
      FROM (
        SELECT id
        FROM</code>civicrm_job_log<code>
        ORDER BY id DESC
        LIMIT 1 OFFSET 1000 -- keep this many records
      ) foo
    );
  </code></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-23T14:30:04.963Z
Answers.Text: 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?
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-03-05T23:11:19.893Z
Answers.Text: JIRA issue is probably this one https://issues.civicrm.org/jira/browse/CRM-20165 Nicolas at cividesk, can you confirm?
Answers.UserDisplayName: TwoMice
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-03-07T02:45:40.913Z
Answers.Text: Yes it is, thanks @TwoMice.
Answers.UserDisplayName: cividesk
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-05-31T03:20:42.483Z
Answers.Text: 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?
Answers.UserDisplayName: Francesco Garganese
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-23T05:55:49.163Z
Answers.LastActivityDate: 2017-02-23T05:55:49.163Z
Answers.UserDisplayName: cividesk
Body: <p>Currently, my civicrm_job_log is the largest table in our database. There was a post <a href="https://civicrm.stackexchange.com/questions/10239/can-i-safely-trim-civicrm-job-log-and-civicrm-log">Can I safely trim civicrm_job_log and civicrm_log?</a> 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]</p>
  
  <ol>
  <li>How do I get non-NULL run_times in my job log table?</li>
  <li>How can I best manage civicrm_log and civicrm_job_log?</li>
  </ol>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-12-22T07:42:26.287Z
Comments.Text: 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?
Comments.UserDisplayName: Karl Rudnick
Tags: <mysql><logs><scheduled-job>
Title: No run_time timestamp in civicrm_job_log
ThreadText: # No run_time timestamp in civicrm_job_log
  
  (Asked by: username_0 on 2016-12-07)
  
  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?](https://civicrm.stackexchange.com/questions/10239/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?
  
  **Tags:** mysql, logs, scheduled-job
  
  > **Comments:**
  > **username_0**: 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?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-02-23. Score: 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 FROM`civicrm\_job\_log`WHERE id <= (
  SELECT id
  FROM (
  SELECT id
  FROM`civicrm\_job\_log`ORDER BY id DESC
  LIMIT 1 OFFSET 1000 -- keep this many records
  ) foo
  );`
  
  > **Comments:**
  > **username_0**: 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?  
  > **username_2**: JIRA issue is probably this one https://issues.civicrm.org/jira/browse/CRM-20165 Nicolas at username_1, can you confirm?  
  > **username_1**: Yes it is, thanks @username_2.  
  > **username_3**: 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?  
  
  ---
  
  