Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-60208

Background processing for question engine (core\task\question_cron_task) failed with error ORA-01795



    • Bug
    • Status: Closed
    • Minor
    • Resolution: Won't Do
    • 3.2.3
    • None
    • Questions


      The scheduled task "Background processing for question engine" (core\task\question_cron_task) failed with the error message:

      Scheduled task failed: Background processing for question engine (core\task\question_cron_task),Error writing to database Debug info: ORA-01795: maximum number of expressions in a list is 1000 DELETE FROM m_question_response_analysis WHERE id IN (:o_param1, [...] 

      The problem lies within the function  where_clause_list and will be solved by MDL-53735 but maybe a different approach will be better. I think it will be better if we change the question_usage_statistics_cron() in /question/engine/statisticslib.php and we call delete_records_select()  with a subquery in the where clause instead of the function delete_records_list() with a list of ids that could be really long.

       I change the code like this and it works perfectly :

      --- statisticslib_original.php 2017-09-21 14:36:05.423594131 0200++ statisticslib.php 2017-09-21 14:45:09.927592980 0200@@ 37,14 +37,11 @@mtrace("\n Cleaning up old question statistics cache records...", '');$DB>delete_records_select('question_statistics', 'timemodified < ?', array($expiretime));- $responseanlysisids = $DB->get_records_select_menu('question_response_analysis',- 'timemodified < ?',- array($expiretime),- 'id',- 'id, id AS id2');- $DB->delete_records_list('question_response_analysis', 'id', $responseanlysisids);- $DB->delete_records_list('question_response_count', 'analysisid', $responseanlysisids); $responseanlysissubquery = "analysisid IN (SELECT id FROM {question_response_analysis} WHERE timemodified < ?)";+ $DB->delete_records_select('question_response_count', $responseanlysissubquery, array($expiretime));+ $DB->delete_records_select('question_response_analysis', 'timemodified < ?', array($expiretime));+mtrace('done.');return true;

      If you think this will be a better solution. I can submit the change.

      To reproduce the problem you'll need more than 1000 records older than 5 hours in the table question_response_analysis or question_response_count. You'll get the error in the cron's log or you can execute just the task core\task\question_cron_task.


      A workaround for this problem, if you have access to the database, will be to execute the next two sentences:

      DELETE FROM m_question_response_count WHERE analysisid IN (SELECT id FROM {question_response_analysis} WHERE timemodified < ?) DELETE FROM m_question_response_analysis WHERE timemodified < ?

      where the timemodified is 5 hours older than now. It's important the order.


        Issue Links



              Unassigned Unassigned
              yordonez Yolanda Ordoñez Rufat
              Safat Shahin, Tim Hunt, Ilya Tregubov, Kevin Percy, Mathew May, Mihail Geshoski, Shamim Rezaie
              1 Vote for this issue
              4 Start watching this issue