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

This widget could not be displayed.

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?

This widget could not be displayed.

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

This widget could not be displayed.

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 Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted yesterday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

76 views 1 0
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