How to run SQL commands?

[Jennifer Osborn]

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!

[rrudnicki]

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

[Jaime S]

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

[Norman Abramovitz]

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.

[Jennifer Osborn]

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!