How to run SQL commands?

More and more, I'm receiving requests from Atlassian Support to "send us to output from the following SQL command(s)." 

While I'm now quite proficient with the Support.zip files, how to I go about running the SQL commands?  Where do I go to do this? 

We have an on-prem installation on Linux servers.  (Crowd, Confluence, JIRA and then a Proxy host for external access)

Sorry for the newbie question.  I'm a Windows sys admin learning Linux - figuring this out one step at a time!

Thanks all!

4 answers

You should be using whatever tools your database supports for doing ad-hoc queries.   It is not done from within JIRA.  Which database are you using?   You should be able to tell from the system admin page.

 

1 votes

Hi, Jennifer.

 

SQL is a special language used to create commands to be run in a database.

 

This is not something provided by Atlassian products. Rather, it is basically used in any relational database. Since most of the Atlassian products use databases, you can run SQL to get and change information in the database.

 

The most common command you'll be asked to run is a 'SELECT' which fetches information. You can read more about it in the following Wikipedia page:

http://en.wikipedia.org/wiki/SQL

 

To run the SQL, you must have access to the database, so you must have access to your server.

 

How you'll access it depends on the database that is installed in you Server. The database generally allows you to use command line to access it.

 

There is also graphical environment, for example, if you're using postgres as your database, you can use pgAdmin. MySQL can be access, for example with MySQL Workbench.

 

Since this is specific to the database you're using, you'll need to search a bit to find out how to run SQL commands in your database.

 

Be careful, though. 'SELECT' commands won't change your database, however other commands may change it and, if you don't know what you're doing, they may corrupt it and your application may stop working.

 

I hope it was helpful.

 

Kind regards,

Jaime Kirch da Silveira

Atlassian Cloud Support

1 votes

Hi Jennifer,

No worries, we are always learning something new :).
When we request the output of a SQL command, basically what you need to do is:

* Loggin on the database (mysql, oracle, sqlserver or postgres) and then just paste the command that we've requested.
For example, if you have Confluence or JIRA or Stash running into a MySQL database, you basically needs to do this:
[root@lab ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use confluence564;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from groups limit 1;
Empty set (0.00 sec)

mysql> quit
Bye


In case are you running postgres, it is a very similar:
[root@lab ~]# psql -U postgres
psql (8.4.20, server 9.4.1)
WARNING: psql version 8.4, server version 9.4.
Some psql features might not work.
Type "help" for help.

postgres=# \c confluence57;
psql (8.4.20, server 9.4.1)
WARNING: psql version 8.4, server version 9.4.
Some psql features might not work.
You are now connected to database "confluence57".
confluence57=# select * from groups limit 1;
id | groupname
----+-----------
(0 rows)

confluence57=# \q
[root@lab ~]#

I don't have an oracle or sqlserver running, but the process is the same.

To sum up, what you need to do is:
# Access the database server
# Access the database (as you can see above)
# Select the database (each database has a different way to do this )
# Run the query (paste the command we've sent you through the ticket)
# Send us the output of the command and quit from database

Hope this helps :).

Best regards,
Renato Rudnicki

Thank you both! 

We're using MySQL for our Atlassian DB's.

This gives me a direction, and I'll bug our Linux SysAdmins to confirm my access to our DB server.

I'll be cautious for sure!

 

 

 

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

2,861 views 12 18
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