Change the color scheme via SQL or some other automation

From time-to-time we copy our PRD data down to a non-PRD instance. We then manually update the non-PRD color scheme so that we don't mistakenly work in the wrong environment.
We would like to be able to make this change via SQL or some other automation. We recognize this might be risky in a PRD environment, but we would be applying this change to non-PRD only.

This question applies to JIRA and Confluence.

3 answers

1 accepted

1 vote

Normally, I'd yell "don't touch the database", but a colour scheme modification done via SQL during the migration into a non-production system before you start the services is actually going to be fine. (Never adjust a database while an Atlassian product is running, that way lies madness, or rather screaming for backup tapes)

I don't know what the SQL is for Confluence off the top of my head, but I imagine it's similar.

For JIRA, have a look in propertyentry and propertyvalue. When you change the colour scheme away from the default, Jira adds one line to each of those. I was mucking with this the other day, and these four changes represent "change from default menu header blue to grey, then bright-kermit green". You can probably hack through the rest for yourself :-)

INSERT INTO PROPERTYENTRY VALUES(11508,'jira.properties',1,'jira.lf.top.bgcolour',5)

INSERT INTO PROPERTYSTRING VALUES(11508,'#aabbcc')

DELETE FROM PROPERTYSTRING WHERE ID=11508

INSERT INTO PROPERTYSTRING VALUES(11508,'#00ff00')

Any thoughts on how to disable outbound email programmatically?

Think it's stuff in propertystring again, but I prefer to use command line switches

See the mail stuff on https://confluence.atlassian.com/display/JIRA/Restoring+Data

Ah, that's helpful, thank you. In the meantime I did this (required a restart):

NOW=$(python -c "import time; print(int(time.time() * 1000));")
echo "update propertynumber set propertyvalue = 1 where id = (select id from propertyentry where property_key = 'jira.mail.send.disabled');" | psql jira
echo "update propertystring set propertyvalue = 'sysadmin' where id = (select id from propertyentry where property_key = 'mailsetting.jira.mail.send.disabled.modifiedBy');" | psql jira
echo "update propertystring set propertyvalue = '$NOW' where id = (select id from propertyentry where property_key = 'mailsetting.jira.mail.send.disabled.modifiedDate');" | psql jira

I'm trying to do the same thing, updating the bgcolor in the database, before starting up jira, after I have cloned a new dev env with my automated process. And this was very helpful.
Just one thing I need to know. The propertyID I assume is generated (because I see it is different on the systems I have tested this manually on) when the property is created once is is set the first time. And it will probably not be a good idea that I just create a ID value on my own. How do I know how to correctly creating this property in the database, with the correct ID for this system?

Worked nicely for JIRA. Was able to reset the admin password, too:

ENCRYPTED_PASSWORD=$(python -c "if True:
    import sys
    from passlib.hash import atlassian_pbkdf2_sha1
    print(atlassian_pbkdf2_sha1.encrypt(sys.argv[1]))
" $SYSADMIN_PASSWORD)
echo "update cwd_user set credential = '$ENCRYPTED_PASSWORD' where user_name = 'sysadmin';" | psql jira

You can perform this using API as well. Example as below.

##Header Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#003b48"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.bgcolour
##Header Highlight Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#708090"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.hilightcolour
##Header Separator Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://jira-staging.trifecta.com/rest/api/2/application-properties/jira.lf.top.separator.bgcolor
##Header Text Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.textcolour
##Menu Item Highlight Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#708090"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.menu.bgcolour
##Menu Item Highlight Text Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.menu.textcolour
##Button Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#c0c0c0"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.hero.button.base.bg.colour
##Set Title
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "TESTING ENVIRONMENT"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.title

For more properties, search for "jira.lf."  in the element tab in the developer tab for the page: http:/xxx/jira6411/secure/admin/LookAndFeel!default.jspa

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,331 views 14 20
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot