[ https://issues.apache.org/jira/browse/OAK-8966?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Solomon Rutzky updated OAK-8966: -------------------------------- 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. 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: # 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). # 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): {code:sql} SELECT col.[collation_name] FROM sys.columns col WHERE col.[object_id] = OBJECT_ID(?) AND col.[name] = N'ID'; {code} where the "{{?}}" is set to "{{tableName}}". Just call it twice, once for each table. > RDBBlobStore: warn when ID columns are using legacy "SQL Server collation" > -------------------------------------------------------------------------- > > Key: OAK-8966 > URL: https://issues.apache.org/jira/browse/OAK-8966 > Project: Jackrabbit Oak > Issue Type: Technical task > Components: rdbmk > Reporter: Solomon Rutzky > Assignee: Julian Reschke > Priority: Minor > > 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. 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: > # 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). > # 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): > > {code:sql} > SELECT col.[collation_name] > FROM sys.columns col > WHERE col.[object_id] = OBJECT_ID(?) > AND col.[name] = N'ID'; > {code} > > where the "{{?}}" is set to "{{tableName}}". Just call it twice, once for > each table. > -- This message was sent by Atlassian Jira (v8.3.4#803005)