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

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.

3 answers

1 accepted

1 vote
Accepted answer

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
Jason Hinch Atlassian Team Nov 19, 2012

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.

Jason Hinch Atlassian Team Nov 19, 2012

I would also suggest contacting support.atlassian.com

Bryan Turner Atlassian Team Nov 19, 2012

Kelly,

In looking at the configuration settings you've put together yourself above, one thing I notice is that you're adding ";domain=company.com". Stash will not create a URL with that in it; it only includes the database name. Is that portion of the URL necessary in order for you to make a connection?

As Jason has indicated, Stash creates a table called "STA_CS_TEST" containing a single column called "TEST_COLUMN". It then inserts a mixed-case row into that table and runs a select statement with a strictly lowercase value to verify that no rows are returned.

As you sure the database name you're providing in the URL is being honored? My guess would be that Stash is actually connecting to JIRA's database, rather than the one you're specifying. As JIRA requires a case-insensitive database, I'd assume you've got that database configured for that collation. That would explain both why Stash appears willing to connect to a database that doesn't actually exist _and_ why it continues to insist the database is case-insensitive even when you've changed the collation. I can assure you Stash does _not_ verify case-insensitivity at the SQL Server instance level.

It's worth noting that verifying the case-sensitivity is _several_ steps into the verification process. Naturally in order to get that far Stash has opened a JDBC connection to the database (which would be impossible if you were specifying a non-existent database _and it was being honored_). It has also used the database's metadata to verify it does not already contain Stash tables, that the user it connected as can create tables and that the database supports UTF-8 characters. The fact that all 4 of those steps are passing indicates Stash is definitely getting a real database connection to _something_.

If you're really interested in helping us solve this issue, raising a ticket on support.atlassian.com would be a big help.

Hope this helps,
Bryan Turner

I do need domain specified - as I said in my answer, I need Windows authentication. Atlassian doesn't support Windows authentication in their database connection screens, so it's been a workaround by the community to append ;domain=whatever.com to your jdbc connection url in all your applications, usually after the server name.

I'm "sure" the database name I'm providing in the actual config file is being honored, because I can see growth in the database as I work and I can do things like change the instance name in Stash Administration and see the change in the app_property table.

I'm not "sure" the database name I'm providing in the database migration wizard screen is being honored. If I don't add ;domain=whatever.com to the hostname field, I can't log in (SQL server treats it like a local account and I need to use a domain account). If I mispell the domain (i.e. ;domain=whateverX.com) the login failure exception is "untrusted domain". If I spell the domain correctly, I get "your database is case insensitive".

I'll agree with the possibility of most of your hypothesis - that Stash could be connecting with something, but it's not connecting to the Jira database; that I cannot believe. How would it know the Jira database name? It's not "Jira". If you wrote Stash to use a default database name of "Jira" when it tries to make a connection but doesn't have a name to connect to, that's pretty crazy development, and I doubt you did that.

If you're really interested in solving this issue, try out the steps I clearly outlined rather than endlessly hypothesizing while looking at raw code.

-Kelly

Also, you (Atlassian) stated we are not required to edit the Stash properties file in 1.2+ - if I click "change password" in the database administration screen in Stash 1.31, I am directed to this article:

https://confluence.atlassian.com/display/STASH/How+do+I+change+the+external+database+password

The supported way to change the database password is to hand-edit the Stash properties file.

1 vote
Jason Hinch Atlassian Team Nov 19, 2012

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.

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

Jason Hinch Atlassian Team Nov 19, 2012

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.

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 Atlassian Team Mar 14, 2014

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 06, 2018 in Bitbucket

Upgrade Best Practices

Hello! My name is Mark Askew and I am a Premier Support Engineer for products Bitbucket Server/Data Center, Fisheye & Crucible. Today, I want to bring the discussion that Jennifer, Matt, and ...

451 views 6 9
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you