nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Stash - Case Sensitive Collation at the database level vs server level

Kelly Schoenhofen

Using MS-SQL 2008 & Stash 1.31 and the database migration wizard (after a clean install using the embedded hsql db), a new DB for stash set to SQL_Latin1_General_CP1_CI_AS for collation (CI = case insensitive) made the database migration wizard spit out the following error when I clicked "Test":

Stash requires a case sensitive database, but the target database is case insensitive. For information about configuring databases for use with Stash, please refer to the Stash documentation.

I set the database to SQL_Latin1_General_CP1_CS_AS, yet Stash's DB migration wizard continues to insist it's still case insensitive. I restarted Stash, it still says the DB is case insensitive. I took the DB offline and then back online, and Stash still says it's case insensitive.

I put some dummy chars in the name of the database, and Stash's DB migration wizard still says the "the target database is case insensitive". So I'm thinking it's not querying the database, it's querying the server default. And since this is a multi-tenant DB server, the owners prefer it to be SQL_Latin1_General_CP1_CI_AS by default. Regardless, this server also hosts a Jira database, which has to be set case insensitive.

Your products should work together a little better, Atlassian.

Any idea on what I can do with Stash? I can't go forward with this tool until I have it using a real database.

Accepted answers

Kelly Schoenhofen

I'm answering my own question, but it's not an optimal answer.

Stash apparently stores its external database configuration in a stash-config.properties file, in the root of the stash_home folder. When you install Stash using HSQL, it doesn't create the file. The absence of the file in fact prompts Stash to use its embedded HSQL DB.

Atlassian documentation (https://confluence.atlassian.com/display/STASH/Stash+config+properties) gives an example for a Postgres file - I looked at a Fisheye config file that uses MS-SQL and cobbled together one for Stash that also uses Windows authentication, like thus:

jdbc.url=jdbc:jtds:sqlserver://servername:1433;databaseName=database;domain=company.com;

jdbc.user=jirauser

jdbc.password=password

jdbc.driver=net.sourceforge.jtds.jdbc.Driver

I saved the file, restarted Stash and the web UI was back on the initial setup questions, this time populating a MS-SQL DB instead of HSQL.

So I'm "working", but there still seems to be a bug here. The migration wizard appears to be querying the server's collation, not the database's collation. Blindly crafting a config file to bypass faulty logic != acceptable workaround.

I'll mark it as answered. Someone else can create a defect

-Kelly

All comments

Kelly Schoenhofen

I'm answering my own question, but it's not an optimal answer.

jdbc.url=jdbc:jtds:sqlserver://servername:1433;databaseName=database;domain=company.com;

jdbc.user=jirauser

jdbc.password=password

jdbc.driver=net.sourceforge.jtds.jdbc.Driver

I saved the file, restarted Stash and the web UI was back on the initial setup questions, this time populating a MS-SQL DB instead of HSQL.

I'll mark it as answered. Someone else can create a defect

-Kelly

jhinch (Atlassian)

Stash tests for case sensitivity by creating a test table in the database, adding one row into it and querying for it in a case sensivity and case insensivity manner. If the row matches in both cases we determine that the database is case insensivity.

Kelly Schoenhofen

Really? So when I changed the database name in the migration wizard to "Unicorns_and_Fairies" and the migration wizard still reported I was using a case insensitive database, it must have created a test table in an imaginary database, because there's no "Unicorns_and_Fairies" database on the server (still).

Have you verified the behavior you are describing? That when the database is indeed set to a case sensitive collation and the server has an insensitive collation default, the migration wizard still creates a test table?

I wrestled with this for most of a day, so being told my solution is less than optimal without any validation of my findings, or any sort of a constructive responds is slightly frustrating

I get what you are saying - by manually creating a config (which your documentation shows how to do, by the way), I am bypassing a safety check in the software. However, I really do promise that Microsoft SQL Service Management Studio 2012 is insisting the Stash database is using collation type SQL_Latin1_General_CP1_CS_AS. And I really can change the database name during the migration wizard to _anything I want_ and it still insists the database is case insensitive.

-Kelly

jhinch (Atlassian)

Unfortunately this isn't the correct way to get around this issue. You are not required to edit the properties file in Stash 1.2+ and Stash does not validate case sensitively on db configurations supplied in the properties file. Stash may not work as expected because of this.

jhinch (Atlassian)

I would also suggest contacting support.atlassian.com

jhinch (Atlassian)

I would also suggest contacting href="stash-case-sensitive-collation-at-the-database-level-vs-server-level">https://support.atlassian.com

jhinch (Atlassian)

Kelly,

I understand your frustration. I highly suggest you contact support.atlassian.com. As a sidenote I'm a developer on Stash and I have verified from the code that my answer is what Stash is doing. It sounds like something else is going wrong in the process though. We recently migrated our internal development server to SQL Server so we have validated on a live system as well has running database tests to check that this works.

Tim Hidalgo

I had the same error happening

I checked the stash db collation with:

SELECT SERVERPROPERTY('Collation');

it was set to:

SQL_Latin1_General_CP1_CI_AS

This was for a new install of Stash.

I went ahead and created a new db for stash and set the collation to:Latin1_General_CS_AS

Here is the interesting part, after the database was successfully created, stash installed no problem. When I run SELECT SERVERPROPERTY('Collation'); it returns SQL_Latin1_General_CP1_CI_AS

I am also running sql server 2012

Bryan Turner

Tim,

SELECT SERVERPROPERTY does not check the database's collation, it checks the server's collation (You can confirm that here). As mentioned before Stash does not require the entire server to be SQL_Latin1_General_CP1_CS_AS, only its specific database (Steps to confirm the database's collation are described here). When you created the database with SQL_Latin1_General_CP1_CS_AS, Stash's test insert ran as expected, returning no rows, and the system allowed you to migrate.

That's the reason why our installation instructions do not state you need to change the server collation, only that you need to create or alter the database's collation. I'm glad doing so worked for you without issue.

Best regards,
Bryan Turner
Atlassian Stash

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

bitbucket-server