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; } } }

Wrong Jira database collation issue. Trouble getting new database to link up with our Jira.

S_ Toyo

Trying to follow this guide. But we are unsure how to run the script against our newly created database.

Accepted answers

All comments

Pavel Junek

Hi Sumi,

The easiest way to fix a bad collation DB is this:
1) Create a new DB with the correct collation - Connecting Jira applications to SQL Server 2012)
2) in Jira create XML backup (Backup system)
3) shut down Jira and remove dbconfig.xml from Jira Home
4) start Jira and connect it to a new DB (and install Jira)
5) Start the Restore system

Pavel

S_ Toyo

@Pavel JunekStep 4: We completely delete the dbconfig.xml file? Will Jira create another one? Or how would I possibly access Jira URL front-end without this dbconfig.xml file?

Someone on another forum told me to fix the collation I need to follow this guide. https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-microsoft-sql-server-jira-database-776646810.html?_ga=2.259843018.1784675570.1563820692-45231134.1562974092

But I don't know how to 'run the script against my newly created database' as stated in the photo above. And after following THIS guide I can't access Jira from the browser anymore. Confusing.

Pavel Junek

Yes, you can rename this file or move it to different location. After start Jira create new dbconfig.xml file. You can access Jira via the GUI - you will see a dialog box for installing fresh, new Jira.

You can return data to Jira (to a new DB) using the XML Restore system.

Yes, you can use the procedure from another forum, it is also possible, but in my opinion unnecessarily complicated.

Pavel

S_ Toyo

@Pavel JunekWe currently already have Jira installed and we already have an existing DB with data in it.

Yes! Following the other guide is pretty complicated..... And using the other guide we were unable to get our Jira to come back online after trying to switch to a brand new database and editing our dbconfig.xml with the new database name.

S_ Toyo

@Pavel Junekwe trying to upgrade our Jira from 7.2 to the most current 8.x but we are also having difficulties with this. And no one on the forums seems to be helping. We tried following this guide: https://confluence.atlassian.com/adminjiraserver/upgrading-jira-server-installer-938846937.html

But we don't understand this step:

What do we do with these files? Is it asking us to take the copies from our backup and copy+paste them back in after the upgrade......? We are a little confused. Because we skip this step, our Jira URL is not accessible anymore. Could this be why?

Pavel Junek

We currently already have Jira installed and we already have an existing DB with data in it.
Yes! Following the other guide is pretty complicated..... And using the other guide we were unable to get our Jira to come back online after trying to switch to a brand new database and editing our dbconfig.xml with the new database name.

Yes, I also experienced problems connecting Jira to a new DB if I just changed the connection in dbconfig.xml. Therefore, I recommend renaming this file (and leaving it as a backup), Jira creates a new one. Subsequently, in Jira GUI to connect to the new, empty DB.

But we don't understand this step:
What do we do with these files? Is it asking us to take the copies from our backup and copy+paste them back in after the upgrade......? We are a little confused. Because we skip this step, our Jira URL is not accessible anymore. Could this be why?

In Jira 7.12.x the server.xml file has changed, see https://confluence.atlassian.com/jirasoftware/jira-software-7-12-x-upgrade-notes-955173978.html

The files like server.xml, dbconfig.xml and jira-config.properties may change with the new version of Jira, so it is not recommended to copy (rewrite) these files from the previous version of Jira, but only edit these files and re-apply all changes manually.

Changes are meant for example Integrating JIRA with Nginx when you must change server.xml (add new line proxyName="<a href="http://www.atlassian.com" target="_blank" rel="nofollow noopener noreferrer">www.atlassian.com</a>" proxyPort="443" scheme="https" secure="true")

<!-- OPTIONAL,Nginx Proxy Connector with https -->
<Connector port="8081" maxThreads="150" minSpareThreads="25" connectionTimeout="20000" enableLookups="false" maxHttpHeaderSize="8192" protocol="HTTP/1.1" useBodyEncodingForURI="true" redirectPort="8443" acceptCount="100" disableUploadTimeout="true"
proxyName="www.atlassian.com" proxyPort="443" scheme="https" secure="true"/>

What problem do you have after starting with Jira? What does the atlassian-jira.log or catalina.out log say?

S_ Toyo

@Pavel JunekHi Pavel, let me try your steps first. And then I will again to upgrade our Jira to the current version again.

Going to start off by following these steps that you provided first before trying to upgrade.

1) Create a new DB with the correct collation - Connecting Jira applications to SQL Server 2012)
2) in Jira create XML backup (Backup system)
3) shut down Jira and remove dbconfig.xml from Jira Home
4) start Jira and connect it to a new DB (and install Jira)
5) Start the Restore system

S_ Toyo

@Pavel JunekCouple questions. For step 2, we logged into our Jira and did a back. And on our server in our Jira home > Export folder. We see it dumped out these files. Is this the XML file we use for step 5 to restore our into the NEWLY created DB?

And for step 3 and step 4 what is the proper steps to do this? Is this by opening up the Services/Task manager in Windows and stopping the Jira service?

S_ Toyo

@Pavel JunekWe followed all of your steps. And when we tried to access our Jira URL we had to go through some setup steps again. It asked us for our MS SQL server info. We clicked next and now we can't do anything.

Pavel Junek

@S_ Toyo ,

For step 2, we logged into our Jira and did a back. And on our server in our Jira home > Export folder. We see it dumped out these files. Is this the XML file we use for step 5 to restore our into the NEWLY created DB?

Yes, that is correct, but for import you must use *.zip file (do not unpack the file!).

And for step 3 and step 4 what is the proper steps to do this? Is this by opening up the Services/Task manager in Windows and stopping the Jira service?

Yes, exactly - stop/start Jira Service.

About the error "Database id locked" - it looks like to problem with configuration DB - JIRA Fails to Start due to Invalid Database Configuration. Please check everything according to Atlassian mode.

Pavel

S_ Toyo

@Pavel JunekWe tried to upgrade our Jira and it looked like it installed. Or so we thought.... We tried to access our Jira URL and got this error

Pavel Junek

Hi Sumi,

Do you have in OS set the owner jira:jira on the Jira Install and Jira Home folders?

Can you please send me link from button Learn more (from this error)?

Thanks

Pavel

S_ Toyo

@Pavel JunekIt took awhile but I figured it out ^____^

But now we see this screen. We tried looking at the newly created server.xml file and our backup server.xml file from the previous JIRA version and we aren't 100% sure what we need to copy over to the new server.xml file.

Pavel Junek

Hi Sumi,

For example, it depends on whether you are using SSL or not. You can of course use the Ignore all warnings button to start Jira and continue. Use System -> Atlassian support tools and troubleshooting to see if everything is OK or not.

Pavel

S_ Toyo

@Pavel Junekcould I send/show you our current server.xml file? Would that help?

This is the new error we are getting. Sorry for all these questions. You have been a GREAT help to me so far.

S_ Toyo

@Pavel JunekHere is our new upgraded server.xml file that got generated after upgrading Jira from 7.2 to 8.3. We tried to match what we saw was different in our old server.xml file.

<?xml version="1.0" encoding="utf-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<Server port="8005" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.startup.VersionLoggerListener"/>
<Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on"/>
<Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
<Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener"/>

<Service name="Catalina">
<!--
==============================================================================================================
DEFAULT - Direct connector with no proxy for unproxied access to Jira.

If using a http/https proxy, comment out this connector.
==============================================================================================================
-->

<Connector port="8080" relaxedPathChars="[]|" relaxedQueryChars="[]|{}^\`"<>"
maxThreads="150" minSpareThreads="25" connectionTimeout="20000" enableLookups="false"
maxHttpHeaderSize="8192" protocol="HTTP/1.1" useBodyEncodingForURI="true" redirectPort="9443"
acceptCount="100" disableUploadTimeout="true" bindOnInit="false"/>

<!--
==============================================================================================================
HTTP - Proxying Jira via Apache or Nginx over HTTP

If you're proxying traffic to Jira over HTTP, uncomment the below connector and comment out the others.
Ensure the proxyName and proxyPort are updated with the appropriate information if necessary as per the docs.

See the following for more information:

Apache - https://confluence.atlassian.com/x/4xQLM
nginx - https://confluence.atlassian.com/x/DAFmGQ
==============================================================================================================
-->

<!--
<Connector port="8080" relaxedPathChars="[]|" relaxedQueryChars="[]|{}^\`"<>"
maxThreads="150" minSpareThreads="25" connectionTimeout="20000" enableLookups="false"
maxHttpHeaderSize="8192" protocol="HTTP/1.1" useBodyEncodingForURI="true" redirectPort="8443"
acceptCount="100" disableUploadTimeout="true" bindOnInit="false" scheme="http"
proxyName="<subdomain>.<domain>.com" proxyPort="80"/>

-->
<!--
==============================================================================================================
HTTPS - Proxying Jira via Apache or Nginx over HTTPS

If you're proxying traffic to Jira over HTTPS, uncomment the below connector and comment out the others.
Ensure the proxyName and proxyPort are updated with the appropriate information if necessary as per the docs.

See the following for more information:

Apache - https://confluence.atlassian.com/x/PTT3MQ
nginx - https://confluence.atlassian.com/x/DAFmGQ
==============================================================================================================
-->

<!--
==============================================================================================================
AJP - Proxying Jira via Apache over HTTP or HTTPS

If you're proxying traffic to Jira using the AJP protocol, uncomment the following connector line
See the following for more information:

Apache - https://confluence.atlassian.com/x/QiJ9MQ
==============================================================================================================
-->

</Host>
<Valve className="org.apache.catalina.valves.AccessLogValve"
pattern="%a %{jira.request.id}r %{jira.request.username}r %t "%m %U%q %H" %s %b %D "%{Referer}i" "%{User-Agent}i" "%{jira.request.assession.id}r""/>
</Engine>
</Service>
</Server>

Pavel Junek

Hi @S_ Toyo

ahaaaa, Did you use the Tomcat SSL (https://confluence.atlassian.com/adminjiraserver073/running-jira-applications-over-ssl-or-https-861253906.html) settings and JIRA configuration tool right? Atlassian has a bug in this configuration application - there is no Tomcat characters.)

Replace:

<Connector SSLEnabled="true" URIEncoding="UTF-8" acceptCount="100" clientAuth="false" connectionTimeout="20000" disableUploadTimeout="true" enableLookups="false" keyAlias="jira" keystoreFile="C:\Program Files\Atlassian\Application Data\jira\keystorePKCS12" keystorePass="AceP0werRules10101010101010!" keystoreType="JKS" maxHttpHeaderSize="8192" maxSpareThreads="75" maxThreads="150" minSpareThreads="25" port="9443" protocol="org.apache.coyote.http11.Http11NioProtocol" scheme="https" secure="true" sslEnabledProtocols="TLSv1.2" sslProtocol="TLS" useBodyEncodingForURI="true"/>

to:

<Connector SSLEnabled="true" URIEncoding="UTF-8" acceptCount="100" clientAuth="false" connectionTimeout="20000" disableUploadTimeout="true" enableLookups="false" keyAlias="jira" keystoreFile="C:\Program Files\Atlassian\Application Data\jira\keystorePKCS12" keystorePass="AceP0werRules10101010101010!" keystoreType="JKS" maxHttpHeaderSize="8192" maxSpareThreads="75" maxThreads="150" minSpareThreads="25" port="9443" protocol="org.apache.coyote.http11.Http11NioProtocol" scheme="https" secure="true" sslEnabledProtocols="TLSv1.2" sslProtocol="TLS" useBodyEncodingForURI="true" relaxedQueryChars="[]|{}^&#x5c;&#x60;&quot;&lt;&gt;"/>

You may still have to comment () on the settings:

<Connector port="8080" relaxedPathChars="[]|" relaxedQueryChars="[]|{}^&#x5c;&#x60;&quot;&lt;&gt;"
maxThreads="150" minSpareThreads="25" connectionTimeout="20000" enableLookups="false"
maxHttpHeaderSize="8192" protocol="HTTP/1.1" useBodyEncodingForURI="true" redirectPort="9443"
acceptCount="100" disableUploadTimeout="true" bindOnInit="false"/>

Pavel

S_ Toyo

@Pavel JunekPerfect! This worked and we were able to get into our JIRA url. But it is now giving us a health check error saying gadget URL. I read the "Show me how to fix this" and that guide was confusing and didn't seem to help.....

Any ideas?

Pavel Junek

Super

If you use your own self-sign SSL certificate, you need to import it into Java Trustore for Jira to recognize it. Please see How to import a public SSL certificate into a JVM

Consider this:

<JAVA_HOME>/bin/keytool -import -alias <server_name> -keystore <JAVA_HOME>/jre/lib/security/cacerts -file public.crt

If you have a standard Jira installation, then Java home is /opt/atlassian/jira/jre/

Pavel

S_ Toyo

@Pavel JunekYes I believe we have one. I wasn't the previous user who setup though. So I guess I need to re-install the certificate? Can I still use the same guide you provided? Does this have something to do with the keystorepassword stuff that I copy+pasted from my previous SERVER.xml to my new SERVER.XML file?

S_ Toyo

@Pavel JunekWhat do I do with this

<span><JAVA_HOME></span><span>/bin/</span>keytool -import -alias <span><server_name></span> -keystore <span><JAVA_HOME></span><span>/jre/</span>lib<span>/security/</span>cacerts -file public.crt

Is this a CMD line that I run?

Pavel Junek

@S_ Toyo The certificate does not need to be reinstalled for the entire server or changed in server.xml.
Yes, you need to use CMD (terminal) and execute the command:

<span>/opt/atlassian/jira/bin/</span>keytool -import -alias <span><server_name></span> -keystore <span>/opt/atlassian/jira</span><span>/jre/</span>lib<span>/security/</span>cacerts -file ssl_cert_public.crt

S_ Toyo

@Pavel JunekSo to fix the gadget URL health check issue we just have to run the following CMD that you provided us?

S_ Toyo

@Pavel JunekThis is the error that we get.

Pavel Junek

So to fix the gadget URL health check issue we just have to run the following CMD that you provided us?

Yes

This is the error that we get.

About this error - I think your file is probably not named only "public.crt". You must write the full path to the file (ssl cer).

Aha, you use Windows....

For Windows

 C:\Program Files\Atlassian\JIRA\jre\bin\<br>C:\keytool.exe -importcert -file "C:\Users\Administrator\Desktop\cert\cert.pem" -alias tomcat -keystore "C:\Program Files\Atlassian\JIRA\jre\lib\security\cacerts" -storepass changeitcd

S_ Toyo

@Pavel JunekSorry for not letting you know from the start that we have a Windows server environment! I will try running your CMD today. And let you know the outcome.

Thanks SO much!

S_ Toyo

@Pavel JunekLong shot, but you have been SUPER helpful to me thus far.

But we have to meeting NIST compliance. And we need to make sure that things are encrypted. So we need the DBCONFIG.xml encrypted instead of being exposed.

We are trying to follow this guide for "Advanced Encryption" and it doesn't work for us.

https://confluence.atlassian.com/adminjiraserver/advanced-encryption-974378813.html

The error that we encounter is that it asks us to set up Jira all over again. We fill out the DB into but get the error that we cannot use a Database that has data in it. And that it needs to be a completely blank database. So we edit the DBCONFIG.xml file back to the exposed password and we are able to get back into our Jira dashboard and see all of our data.

S_ Toyo

@Pavel JunekThis is the outcome of the CMD that we ran. It's a totally different error.

Pavel Junek

Hi @S_ Toyo ,
do you remember my previous post with XML backup? You can do that in this case:

create a new, empty DB
create Jira XML backup
follow the encryption manual for new, empty DB
run Jira and "install it" like new, fresh and empty
Import data from XML backup

Pavel Junek

@S_ Toyo based on CMD error.

Do you run CDM as an administrator?
Error say: "File Not Found" for C:\Users\Administrator\Desktop\cert\cert.pem, do you right file path?
Maybe Windows Defender blocking access to the cacerts file

Pavel Junek

Hi @S_ Toyo,

sorry, Somehow I wasn't notified.

- So I guess I need to re-install the certificate? Can I still use the same guide you provided?

Yes

- Does this have something to do with the keystorepassword stuff that I copy+pasted from my previous SERVER.xml to my new SERVER.XML file?

No, Please make new settings, the server.xml file may have changed since the original version.

Pavel Junek

Yes, exactly!

Welcome!

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

Quick Links

Find more posts tagged with

server

jira

jira-software

jira-software-server