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

Error ORA-01795 in calendar when there's more than 1000 groups in a course

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.6.3, 2.7.2
    • Fix Version/s: 2.6.6, 2.7.3
    • Component/s: Calendar
    • Labels:
    • Database:
      Oracle
    • Testing Instructions:
      Hide
      1. Test on Oracle and pg/mysql: make sure you can see events in the calendar.
      2. Manually create an event that is limited to one group only. Make sure only users in this group (or teacher who can see all groups) can see this event.

      More exciting test for people who want Dan kudos points:

      1. Use oracle
      2. Create a course with shortname MDL-47466
      3. Upload the attached user upload CSV file - this should create 1000 users and enrol them to your course
      4. Go to course MDL-47466 > groups > Auto-create groups and autocreate groups based on 1 member per group
      5. You might encounter MDL-48081 - that would be a cool thing to fix, huh? If so create 1001 groups using groups import feature
      6. Create a course event limited to group
      7. Visit the course calendar
      8. VERIFY: you can see it correctly
      9. Ensure the upcoming event/calendar block on course page
      10. Revert this patch and see that course is completely broken to prove to yourself it was worth the effort of this testing
      Show
      Test on Oracle and pg/mysql: make sure you can see events in the calendar. Manually create an event that is limited to one group only. Make sure only users in this group (or teacher who can see all groups) can see this event. More exciting test for people who want Dan kudos points: Use oracle Create a course with shortname MDL-47466 Upload the attached user upload CSV file - this should create 1000 users and enrol them to your course Go to course MDL-47466 > groups > Auto-create groups and autocreate groups based on 1 member per group You might encounter MDL-48081 - that would be a cool thing to fix, huh? If so create 1001 groups using groups import feature Create a course event limited to group Visit the course calendar VERIFY: you can see it correctly Ensure the upcoming event/calendar block on course page Revert this patch and see that course is completely broken to prove to yourself it was worth the effort of this testing
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Fixed Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-47466-master

      Description

      Only happens in installations with the Oracle database
      In a course with more than 1000 groups you get a dmlreadexception ('Error reading from database') when trying to see a calendar. It's even worse if you have the calendar block or the upcoming events block because you can't access the course page (the activities or resources are still accessible directly from the navigation block).

      The problem comes from the function calendar_get_events() that construct a sql with the IN clause for the groups. When the limit of Oracle 1000 expressions in a list is reached we get the error.

      FULL STEPS

      • In a Moodle installation with Oracle as a database
      • Create a course
      • Go to Users > Groups and create more than 1000 grups. I used a txt file with 1001 and the import option.
      • Go to the calendar for that course

      You expected to see the calendar but actually you get the error:

      Error reading from database

      More information about this error

      Debug info: ORA-01795: maximum number of expressions in a list is 1000
      SELECT * FROM m_event WHERE (timestart >= 1409522400 OR timestart + timeduration > 1409522400) AND timestart <= 1412114399 AND ( (userid = 4 AND courseid = 0 AND groupid = 0) OR groupid IN (/** a list of more than 1000 ids **/) OR (groupid = 0 AND courseid IN (16042,1))) AND visible = 1 ORDER BY timestart
      [array (
      )]
      Error code: dmlreadexception
      Stack trace:
      line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 271 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
      line 1122 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
      line 1257 of /lib/dml/moodle_database.php: call to oci_native_moodle_database->get_records_sql()
      line 790 of /calendar/lib.php: call to moodle_database->get_records_select()
      line 261 of /calendar/lib.php: call to calendar_get_events()
      line 185 of /calendar/renderer.php: call to calendar_get_mini()
      line 2772 of /calendar/lib.php: call to core_calendar_renderer->fake_block_threemonths()
      line 122 of /calendar/view.php: call to calendar_information->add_sidecalendar_blocks()

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              yordonez Yolanda Ordoñez Rufat
              Reporter:
              yordonez Yolanda Ordoñez Rufat
              Peer reviewer:
              Frédéric Massart
              Integrator:
              Dan Poltawski
              Tester:
              Andrew Nicols
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                10/Nov/14