SQL syntax error in cron_event_log_expire on key word SECONDS

[David Ligne]

Mahara v1.10.0, Linux, MySQL.

We run Mahara on MySQL, and we get this error with our Cron :

{code}
[INF] b8 (lib/cron.php:137) Running core cron cron_event_log_expire
[WAR] b8 (lib/errors.php:745) Failed to get a recordset: mysqli error: [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 'SECONDS' at line 1] in EXECUTE("DELETE FROM "mhr_event_log" WHERE time < CURRENT_DATE - INTERVAL 31536000 SECONDS")
[WAR] b8 (lib/errors.php:745) Command was: DELETE FROM "mhr_event_log" WHERE time < CURRENT_DATE - INTERVAL 31536000 SECONDS
Call stack (most recent first):
  * log_message("Failed to get a recordset: mysqli error: [1064: Yo...", 8, true, true) at /app/apache2/htdocs/portfolio/lib/errors.php:95
  * log_warn("Failed to get a recordset: mysqli error: [1064: Yo...") at /app/apache2/htdocs/portfolio/lib/errors.php:745
  * SQLException->__construct("Failed to get a recordset: mysqli error: [1064: Yo...") at /app/apache2/htdocs/portfolio/lib/dml.php:959
  * delete_records_sql("DELETE FROM "mhr_event_log" WHERE time < CURRENT_D...", null) at /app/apache2/htdocs/portfolio/lib/dml.php:936
  * delete_records_select("event_log", "time < CURRENT_DATE - INTERVAL 31536000 SECONDS") at /app/apache2/htdocs/portfolio/lib/mahara.php:3746
  * cron_event_log_expire() at /app/apache2/htdocs/portfolio/lib/cron.php:142

In the cron_event_log_expire() method of /lib/mahara.php, the key word "SECONDS" is used; it should be "SECOND", shouldn't it?

[Aaron Wells (u-aaronw)]

Hi David,

You are correct. It should be keyword SECOND not keyword SECONDS. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

Looks like this slipped through code review when masquerade logging was added, in Mahara 1.7. I'll upload a patch.

Cheers,
Aaron

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/4084

[Aaron Wells (u-aaronw)]

Testing instructions:

1. Install Mahara in MySQL
2. Log in as admin
3. Go to Administration -> Configure site -> Site options -> Logging settings
4. Set "Event log expiry" to 1 day (or any amount of time, it doesn't matter)
5. Log in to your DB and clear the "nextrun" time for the "cron_event_log_expire" task so that it'll run on the next cron run. (Otherwise it only runs once a day.): UPDATE cron SET nextrun=NULL WHERE callfunction='cron_event_log_expire';
6. Run the Mahara cron.

Expected result: You should see "Running core cron cron_event_log_expire" in the cron output
Actual result: The error stack posted in the bug description

[Aaron Wells (u-aaronw)]

I also tested this in Postgres, and verified that there is no problem with the existing code there.

[David Ligne (david-ligne)]

Thank you Aaron!

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/4084
Committed: http://gitorious.org/mahara/mahara/commit/7d8afff1176a12638b7477d6fd602b9aa2a745c3
Submitter: Aaron Wells (<email address hidden>)
Branch: master

commit 7d8afff1176a12638b7477d6fd602b9aa2a745c3
Author: Aaron Wells <email address hidden>
Date: Fri Dec 12 11:05:34 2014 +1300

Fix MySQL syntax error in cron task

Bug 1399311

Change-Id: I8d66b266d6de3dedb9069f852c5540a139ebb1e5

[Mahara Bot (dev-mahara)]

Patch for "1.8_STABLE" branch: https://reviews.mahara.org/4088

[Mahara Bot (dev-mahara)]

Patch for "1.9_STABLE" branch: https://reviews.mahara.org/4089

[Mahara Bot (dev-mahara)]

Patch for "1.10_STABLE" branch: https://reviews.mahara.org/4090

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/4088
Committed: http://gitorious.org/mahara/mahara/commit/132d2586743f260101286d9c9bb0da9d29d7ea7e
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.8_STABLE

commit 132d2586743f260101286d9c9bb0da9d29d7ea7e
Author: Aaron Wells <email address hidden>
Date: Fri Dec 12 11:05:34 2014 +1300

Fix MySQL syntax error in cron task

Bug 1399311

Change-Id: I8d66b266d6de3dedb9069f852c5540a139ebb1e5

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/4089
Committed: http://gitorious.org/mahara/mahara/commit/36345b8e40f35cbc09ed9f2e1a754a03854011e4
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.9_STABLE

commit 36345b8e40f35cbc09ed9f2e1a754a03854011e4
Author: Aaron Wells <email address hidden>
Date: Fri Dec 12 11:05:34 2014 +1300

Fix MySQL syntax error in cron task

Bug 1399311

Change-Id: I8d66b266d6de3dedb9069f852c5540a139ebb1e5

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/4090
Committed: http://gitorious.org/mahara/mahara/commit/ab7895f68b17bcc3048c763fc8fbbfc71d48a71c
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.10_STABLE

commit ab7895f68b17bcc3048c763fc8fbbfc71d48a71c
Author: Aaron Wells <email address hidden>
Date: Fri Dec 12 11:05:34 2014 +1300

Fix MySQL syntax error in cron task

Bug 1399311

Change-Id: I8d66b266d6de3dedb9069f852c5540a139ebb1e5