Kuali Rice Development
  1. Kuali Rice Development
  2. KULRICE-5901

MySQL sequence generation is resulting in a new row in the sequence table for each value retrieved

    Details

    • Type: Improvement Improvement
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.0.3.3, 2.0.0-m9
    • Fix Version/s: 3.0
    • Component/s: Development
    • Security Level: Public (Public: Anyone can view)
    • Labels:
    • Similar issues:
      KULRICE-3853JPA - Proof of concept with sequence / identity/ prePersist
      KULRICE-2040Ensure all mysql tables/sequences are in the ddl
      KULRICE-11089Use of ExpressionFunction #sequence in maintenance documents results in skipped sequence numbers
      KULRICE-3592adding permission to role and saving throws RuntimeException: Error retrieving next option id for action list from sequence.
      KULRICE-7417Collections - table layout sequence column is editable
      KULRICE-11414Extension framework does not work with objects using sequences for primary key
      KULRICE-3880Don't swallow "caused-by" exceptions in MySQL sequence generator
      KULRICE-12026KRAD Library sequence column value in collection changes on delete
      KULRICE-9194Remove unused 'property' attribute of @Sequence annotation
      KULRICE-9384Analyze usage of SequenceAccessorService and determine if we can refactor away the need to "prefetch" sequence identifier values
    • Rice Module:
      Rice Core
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required

      Description

      I was surprised when I looked at one of my sequence tables today and saw a bunch of rows in it. Then I looked at MySQLDatabasePlatform.java and saw that it is doing this for each call to getNextValSQL:

      INSERT INTO sequenceTableName VALUES (NULL);
      SELECT LAST_INSERT_ID();
      

      If you look at the mysql docs here (search for "simulate sequences") you'll see that their suggested method doesn't insert a new row per value. Instead they do the following:

      UPDATE sequenceTableName SET id=LAST_INSERT_ID(id+1);
      SELECT LAST_INSERT_ID();
      

      The only reason I can think of for doing it the way we are is that we have trouble pre-populating our sequence tables with a row at creation time for some reason. On the surface it seems like a simple thing to address.

        Activity

        Hide
        Eric Westfall added a comment -

        A couple of things:

        1. The JPA work we did back in January 2010 resulted in a branch of impex that inserted a starting value in the sequence table, so merging a change in like that would address that particular issue that Peter highlights.
        2. When we originally implemented platform support for mysql we tried something along the lines of the incrementing of the value similar to what the mysql docs suggest. But if I recall that resulted in row contention issues, so that's definitely something to test.
        Show
        Eric Westfall added a comment - A couple of things: The JPA work we did back in January 2010 resulted in a branch of impex that inserted a starting value in the sequence table, so merging a change in like that would address that particular issue that Peter highlights. When we originally implemented platform support for mysql we tried something along the lines of the incrementing of the value similar to what the mysql docs suggest. But if I recall that resulted in row contention issues, so that's definitely something to test.
        Hide
        Eric Westfall added a comment -

        One other thing, we wouldn't be able to change the current scheme for tables which are accessed directly from client application database connections (like the kew document tables and the kim tables) because that would break compatibility.

        Show
        Eric Westfall added a comment - One other thing, we wouldn't be able to change the current scheme for tables which are accessed directly from client application database connections (like the kew document tables and the kim tables) because that would break compatibility.

          People

          • Assignee:
            Unassigned
            Reporter:
            Peter Giles (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Structure Helper Panel