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.

[Pavel Junek]

Hi @S_ Toyo,

aaaaha. That's what I understand.

You may want to mark this thread as Accepted answer and create a new thread where other users can contribute.
Thank you very much, Pavel

[S_ Toyo]

@Pavel JunekI'm so sorry! That makes sense...Duh. Sorry.

I will try all of your steps today. Thank you again SO much for all of your help!

[Pavel Junek]

@S_ Toyo based on CMD error.

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

[Pavel Junek]

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

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

[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

[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="www.atlassian.com" 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?

[Pavel Junek]

Hi,
this is because this thread is too long, the Accepted Answer button will be somewhere at the top :))

Thanks! Pavel

[S_ Toyo]

@Pavel JunekHi I do not see a "Accepted Answer" button. I only see it for questions but not for discussions.

[S_ Toyo]

@Pavel JunekI did not generate the SSL cert. I see, I wish the previous person before me left his notes! :(

We are also trying to upgrade our confluence as well. And our confluence server.xml file also has something very similar in the file. Only thing different is that the keystorepassword is different of course.

But we upgraded our confluence and now no one is able to login anymore. It also killed out LDAP logins. Boo. :(

[Pavel Junek]

Hi,

I'm sorry it's not working yet. 

Yes, it looks like you did all the steps correctly, including copy + paste to the server.xml file, no reimport this file from old Jira. Did you generate the SSL certificate yourself? To be trusted by a browser, it would have to be issued by a trusted CA (certification authority) or use SSL free Let's Encrypt certificate. If you use your company certificate, it must be trusted in your infrastructure.

[S_ Toyo]

@Pavel JunekHave not looked at the atlassian-jira.log file but yes, I have restarted our Jira. But problem still exists and nothing change. >_<

 But not sure our cert is being trusted? Because in all of our browsers it says "Not trusted"

This all has to do with this portion that I pasted from my previous server.xml file correct? I copy+pasted into my new server.xml after I upgraded.

<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="[KEYSTORE PASSWORD GOES HERE]" 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"/>

The previous person before me set all this up. So not sure what he did to get this into our old server.xml file. But I'm guessing copy+pasting it into the new server.xml won't cut it. And I have to re-import it whenever I upgrade Jira???

[Pavel Junek]

Hi @S_ Toyo, 

Great news! Excellent :)

Did you restart Jira after import certificate? What tell atlassian-jira.log file? Any errors?

Do you have this error - How to fix gadget titles showing as __MSG_gadget? 

Sometimes is causing problem bad set Base URL. On the page How to fix gadget titles showing as __MSG_gadget are describe other possible solutions.

Pavel

[S_ Toyo]

@Pavel JunekWe finally had success running the CMD you gave us. Is there any other steps that we need to take? We still see the gadgetURL error.

[Pavel Junek]

Hi @S_ Toyo

Path C:\Users\Administrator\Desktop\cert\cert.pem is just an example! Instead of this path, fill in the path to your file - the certificate you need to import into cacerts.

If you fail to import the certificate, you can do the following:
1) Backup (or rename) the current cacerts file
2) Take your old, backed up cacerts file where the certificate has already been imported and copy it to the new Jira (to C:\Program Files\Atlassian\JIRA\jre\lib\security\cacerts)

Pavel

[S_ Toyo]

@Pavel JunekTried running as administrator and it really cannot find that path. C:\Users\Administrator\Desktop\cert\cert.pem

[S_ Toyo]

@Pavel Junek  I tried to view this file path C:\Users\Administrator\Desktop\cert\cert.pem and there was nothing there. I looked in the JRE/bin and I do see a cacerts file in there? My previous jira folder backup also has a cacerts file in the same location.


I will also try again but this time running as administrator. But I do not think that will make any difference.

[S_ Toyo]

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

[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 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!

[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

cd C:\Program Files\Atlassian\JIRA\jre\bin\
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 changeit

[S_ Toyo]

@Pavel JunekThis is the error that we get. :(

[S_ Toyo]

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

[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:

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

[S_ Toyo]

@Pavel JunekWhat do I do with this

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

Is this a CMD line that I run?

[Pavel Junek]

Yes, exactly!

[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?

[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.