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

Scheduled task failed: You can't specify target table 'qu' for update in FROM clause

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Note, I have not been able to test this myself.

      This needs to be tested on MySQL 5.6 and MySQL 5.7.

      Manual tests
      1. Go into the question bank and preview one question.
      2. Run php admin/tool/task/cli/schedule_task.php --execute=\\core\\task
        question_cron_task
        and verify that the part about cleaning up the question bank runs without any SQL errors.
      Automated tests
      1. Run mysql unit tests on lowest versions of mysql and highest
      Performance test (optional)
      1. Create a selection of questions of different types
      2. Run the attached generator script in a loop:

        for x in `seq 1 100000`; do php mdl_52339.php; done
        

      3. Wait several hours for script to complete
      4. Put the attempts and steps into the past:

        UPDATE mdl_question_attempts SET timemodified = timemodified - 86400;
        UPDATE mdl_question_attempt_steps SET timecreated = timecreated - 86400;
        

      5. Run php admin/tool/task/cli/schedule_task.php --execute="\core\task\question_cron_task"
        1. Confirm that the cron completed in a reasonable amount of time (for me on 110,000 attempts it takes 16 seconds
      Show
      Note, I have not been able to test this myself. This needs to be tested on MySQL 5.6 and MySQL 5.7. Manual tests Go into the question bank and preview one question. Run php admin/tool/task/cli/schedule_task.php --execute=\\core\\task question_cron_task and verify that the part about cleaning up the question bank runs without any SQL errors. Automated tests Run mysql unit tests on lowest versions of mysql and highest Performance test (optional) Create a selection of questions of different types Run the attached generator script in a loop: for x in `seq 1 100000`; do php mdl_52339.php; done Wait several hours for script to complete Put the attempts and steps into the past: UPDATE mdl_question_attempts SET timemodified = timemodified - 86400; UPDATE mdl_question_attempt_steps SET timecreated = timecreated - 86400; Run php admin/tool/task/cli/schedule_task.php --execute="\core\task\question_cron_task" Confirm that the cron completed in a reasonable amount of time (for me on 110,000 attempts it takes 16 seconds
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_29_STABLE, MOODLE_30_STABLE
    • Pull Master Branch:
      MDL-52339-master
    • Sprint:
      3.1 Sprint 4
    • Issue size:
      Medium

      Description

      Using MySQL 5.7.8 the follow error occurred during the cron job execution for 2.8.9 (Build: 20151109), 2.9.3 (Build: 20151109), 3.0 (Build: 20151116). It occurs without needing a course setup.

      Cleaning up old question previews...... used 2 dbqueries
      ... used 0.016892194747925 seconds
      Scheduled task failed: Background processing for question engine,Error writing to database
      Debug info:
      You can't specify target table 'qu' for update in FROM clause

      DELETE qu, qa, qas, qasd
      FROM mdl_28_question_usages qu
      JOIN mdl_28_question_attempts qa ON qa.questionusageid = qu.id
      LEFT JOIN mdl_28_question_attempt_steps qas ON qas.questionattemptid = qa.id
      LEFT JOIN mdl_28_question_attempt_step_data qasd ON qasd.attemptstepid = qas.id
      WHERE qu.id IN (SELECT * FROM (SELECT quba.id FROM mdl_28_question_usages quba WHERE quba.component = ?
      AND NOT EXISTS (
      SELECT 1
      FROM mdl_28_question_attempts subq_qa
      JOIN mdl_28_question_attempt_steps subq_qas ON subq_qas.questionattemptid = subq_qa.id
      JOIN mdl_28_question_usages subq_qu ON subq_qu.id = subq_qa.questionusageid
      WHERE subq_qa.questionusageid = quba.id
      AND subq_qu.component = ?
      AND (subq_qa.timemodified > ?
      OR subq_qas.timecreated > ?)
      )
      ) AS hack_subquery_alias)
      [array (
      0 => 'core_question_preview',
      1 => 'core_question_preview',
      2 => 1448545549,
      3 => 1448545549,
      )]
      Backtrace:

      • line 974 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 956 of /question/engine/datalib.php: call to mysqli_native_moodle_database->execute()
      • line 910 of /question/engine/datalib.php: call to question_engine_data_mapper->delete_usage_records_for_mysql()
      • line 118 of /question/engine/lib.php: call to question_engine_data_mapper->delete_questions_usage_by_activities()
      • line 356 of /question/previewlib.php: call to question_engine::delete_questions_usage_by_activities()
      • line 423 of /question/engine/bank.php: call to question_preview_cron()
      • line 49 of /lib/classes/task/question_cron_task.php: call to question_bank::cron()
      • line 74 of /lib/cronlib.php: call to core\task\question_cron_task->execute()
      • line 81 of /admin/cron.php: call to cron_run()

      Execute scheduled task: Site registration
      ... started 09:45:49. Current memory use 59MB.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  14/Mar/16