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

Analytics cleanup() causes error for large moodle installations under postgres

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Major Major
    • 3.6.7, 3.7.3
    • 3.7.1, 3.8
    • Analytics
    • PostgreSQL, Microsoft SQL, Oracle
    • MOODLE_37_STABLE, MOODLE_38_STABLE
    • MOODLE_36_STABLE, MOODLE_37_STABLE
    • MDL-66498_master
    • Hide

      Manual testing instructions for 37 and master. The changes are unit tested.

      1. Go to Site Administration > Users > Accounts > Upload Users
      2. Upload the provided file to create 70.000 users.
      3. Start the cron (This will create the predictions for the "Upcoming activities due" model
      4. The task core\task\analytics_cleanup_task should not fail
      Show
      Manual testing instructions for 37 and master. The changes are unit tested. Go to Site Administration > Users > Accounts > Upload Users Upload the provided file to create 70.000 users. Start the cron (This will create the predictions for the "Upcoming activities due" model The task core\task\analytics_cleanup_task should not fail

      The analytics API regularly cleans up the prediction and training samples. For that, it creates an sql statement, which has a NOT IN within its where clause. For this NOT IN it includes all ids of the analyzable of the model as parameters. For our installation and the analyzable core_user this are nearly 100,000 entries. However, the postgres implementation in php only allows 65536 parameters. Other DBMS have similar issues. I could not find a limit for mysql, but SQL Server is at 32767 (source ). Thus, the query fails

        1. 70000Users-Upload.csv
          3.59 MB
          Tobias Reischmann
        2. screenshot-1.png
          76 kB
          Janelle Barcega
        3. Screenshot from 2019-09-05 09-41-19.png
          34 kB
          David Monllaó

            dmonllao David Monllaó
            tobias.reischmann Tobias Reischmann
            Tobias Reischmann Tobias Reischmann
            Andrew Lyons Andrew Lyons
            Janelle Barcega Janelle Barcega
            Votes:
            5 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 days, 2 hours, 25 minutes
                2d 2h 25m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.