Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-41391

Polls > in an Oracle environment, poll options are not displayed in the order they're created

    Details

    • Type: Bug
    • Status: Verified
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 11.4, 12.5, 19.0, 20.0
    • Fix Version/s: 20.0
    • Component/s: Polls, Quartz Scheduler
    • Labels:
      None
    • Conversion Script Required:
      Yes
    • Test Plan:
      Hide

      NOTE: the relevant conversion script (either MySQL or Oracle) must be run on the database after this code has been deployed, and prior to performing the below testing steps.

      Testing of the Quartz job must be done in an environment that has existing data (sites with the Polls tool which contain polls and options that were created prior to this code being deployed to the server in question):

      1. As an admin, run the Quartz job ("Backfill all existing poll options' order based on their original ID")
      2. Verify the job completes successfully
      3. Verify that poll options are displayed correctly in any affected sites

      Test in both an Oracle and MySQL environment:

      1. In any site, add the Polls tool
      2. Create a Poll with several options
      3. Verify the options display (to both instructor and student) in the order in which they were created
      Show
      NOTE: the relevant conversion script (either MySQL or Oracle) must be run on the database after this code has been deployed, and prior to performing the below testing steps. Testing of the Quartz job must be done in an environment that has existing data (sites with the Polls tool which contain polls and options that were created prior to this code being deployed to the server in question ): As an admin, run the Quartz job ("Backfill all existing poll options' order based on their original ID") Verify the job completes successfully Verify that poll options are displayed correctly in any affected sites Test in both an Oracle and MySQL environment: In any site, add the Polls tool Create a Poll with several options Verify the options display (to both instructor and student) in the order in which they were created

      Description

      IMPLICATIONS
      1. Institutions must run the relevant conversion script
      2. Institutions must run the Quartz job once (this is required because the SQL statement now orders by the OPTION_ORDER column, and null values in this column will cause the query to explode)

      This bug only affects those who are running with an Oracle database. When creating options for a poll the display of these options (for both instructors and students) may appear in a "random" order, rather than the order in which they were created.

      The cause of this bug is that the code is doing an ORDER BY clause on the optionId column, which is the primary key of the table:

          public List<Option> getOptionsForPoll(Long pollId) {
              Poll poll;
      		try {
      			poll = getPollById(pollId, false);
      		} catch (SecurityException e) {
      			throw new SecurityException(e);
      		}
              if (poll == null) {
                  throw new IllegalArgumentException("Cannot get options for a poll ("+pollId+") that does not exist");
              }
              Search search = new Search();
              search.addRestriction(new Restriction("pollId", pollId));
              search.addOrder(new Order("optionId"));
              List<Option> optionList = dao.findBySearch(Option.class, search);
              return optionList;
          }
      

      In MySQL, this field is defined as an auto_increment field, which guarantees that the IDs are unique and ascending:

      mysql> describe poll_option;
      +----------------+--------------+------+-----+---------+----------------+
      | Field          | Type         | Null | Key | Default | Extra          |
      +----------------+--------------+------+-----+---------+----------------+
      | OPTION_ID      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
      | OPTION_POLL_ID | bigint(20)   | YES  |     | NULL    |                |
      | OPTION_TEXT    | longtext     | YES  |     | NULL    |                |
      | OPTION_UUID    | varchar(255) | YES  |     | NULL    |                |
      | DELETED        | bit(1)       | YES  |     | NULL    |                |
      | OPTION_ORDER   | int(11)      | YES  |     | NULL    |                |
      +----------------+--------------+------+-----+---------+----------------+
      

      So for those on MySQL, this bug never presents itself because the IDs of the options are always in the order in which they were created. However, in Oracle this field uses a sequence to generate the ID, and sequences do not behave in the same way an auto_increment field in MySQL does:

      do not rely on sequences being

      a) gap free
      b) sequential
      c) always increasing

      Only think of them as "unique - nothing more, nothing less"

      Source: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705

      Given these known limitations of Oracle's sequence, you can see how a situation can arise where the IDs of the options created by the user may or may not be in direct ascending order. The result is that the options may present themselves in a differing order than the user expects (a different order than they were created in).

      The use of the primary key determining the "order" of the option also has a severe limitation: introducing a "re-order" functionality can become tricky if you have to manipulate primary keys.

      The linked PR introduces a new, dedicated OPTION_ORDER column in the POLL_OPTION table to store the option's visual order. By doing so, it not only eliminates the Oracle bug, but it also lays the groundwork for introducing a "re-order" function for the end users (which will be coming in the future).

      It also introduces a new Quartz job (named "Backfill all existing poll options' order based on their original ID"), which is a run-only-once task to back-fill the OPTION_ORDER column for all existing poll options (maybe not all, but any Polls that are attached to an existing site). The job outputs logger statements to track the process. For example:

      28-Feb-2019 11:43:21.196 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Attempting to back-fill all existing Poll option orders...
      ...
      28-Feb-2019 11:43:22.106 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Poll ID 4 has options with null order, processing...
      ...
      28-Feb-2019 11:43:22.116 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Option 7 ---> new order == 1
      ...
      28-Feb-2019 11:44:04.234 INFO [QuartzScheduler_Worker-3] org.sakaiproject.component.app.scheduler.jobs.PollOrderOptionBackFillJob.execute Processing finished, modified 27 poll options
      

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  bjones86 Brian Jones
                  Reporter:
                  bjones86 Brian Jones
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration