SQL syntax error in cron_event_log_expire on key word SECONDS

Bug #1399311 reported by David Ligne on 2014-12-04
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Medium
Aaron Wells
1.10
Medium
Aaron Wells
1.8
Medium
Aaron Wells
1.9
Medium
Aaron Wells
15.04
Medium
Aaron Wells

Bug Description

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) wrote :

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

tags: added: cron
tags: added: mysql
Aaron Wells (u-aaronw) wrote :

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) wrote :

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

David Ligne (david-ligne) wrote :

Thank you Aaron!

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) wrote :

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

Mahara Bot (dev-mahara) wrote :

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

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) wrote :

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) wrote :

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

Robert Lyon (robertl-9) on 2015-04-17
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers