Uploaded image for project: 'Jackrabbit Oak'
  1. Jackrabbit Oak
  2. OAK-1266 DocumentStore implementation for relational databases
  3. OAK-8966

RDBBlobStore: warn when ID columns are using legacy "SQL Server collation"

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Technical task
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: rdbmk
    • Labels:
      None

      Description

      This is primarily a fix for OAK-8918, which was intended to warn users of a configuration (when using Microsoft SQL Server) that can lead to degraded performance. Unfortunately, the code committed for that issue does not actually check for this configuration directly, and hence can produce false positives and negatives. This can be especially confusing to users to receive the warning when it is coincidentally true, make the recommended changes, but then continue to receive the warning message.

       

      The issue is that the changes made in OAK-8918 check the default collation of the database, yet that particular collation is only a factor when the tables – DATASTORE_DATA and DATASTORE_META – are created (because the COLLATE clause is not specified in the CREATE TABLE statements, but that's being fixed via OAK-8963). Once the tables exist, then the default collation of the database is no longer a factor since it's the collation of the ID columns that affects performance. Thus, the check performed in OAK-8918 can be incorrect in two scenarios:

      1. No warning message is produced if the database is using a non-"SQL_" collation yet the ID columns are set to a "SQL_" collation (which should produce the warning message).
      2. A warning message is produced if the database is using a "SQL_" collation yet the ID columns are not set to a "SQL_" collation (which should not produce the warning message). And this will be the case once users make the recommended change.

       

      This can be fixed by checking the collation of each ID column (instead of checking the collation of the database):

       

      SELECT col.[collation_name]
      FROM   sys.columns col
      WHERE  col.[object_id] = OBJECT_ID(?)
      AND    col.[name] = N'ID'

       

      where the "?" is set to "tableName". Just call it twice, once for each table.

       

        Attachments

        Issue Links

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Solomon.Rutzky Solomon Rutzky

              Dates

              • Created:
                Updated:

                Issue deployment