Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I wanted to fetch only the value from status variables. Suppose the query is

show status where Variable_name='Aborted_clients'

It returns

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 4     |
+-----------------+-------+

From here I need to fetch only the value of column "Value". Is there any way. When I am trying to this query as sub query, it is throwing error.

I am using MySQL version 5.0.24. And OS is windows

share|improve this question
add comment

3 Answers

Go to information_schema database and do a select from the global_status table instead:

mysql> use information_schema;
Database changed
mysql> select * from global_status where variable_name='aborted_clients';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| ABORTED_CLIENTS | 22             |
+-----------------+----------------+
1 row in set (0.01 sec)

mysql> select variable_value from global_status where variable_name='aborted_clients';
+----------------+
| variable_value |
+----------------+
| 22             |
+----------------+
1 row in set (0.01 sec)

mysql> 
share|improve this answer
    
I am using MySQL version 5.0.24, so the table global_status is not present in information_schema –  Phanindra Oct 11 '11 at 14:05
add comment
$ mysql -u root -p --skip-column-name -e \
    "show status where variable_name='Aborted_clients';" | awk '{ print $2 }'

If you want to do it in a shell script, put a ~/.my.cnf file in $HOME with belows content:

[mysql]
user     = root
password = pa$$w0rd
host     = localhost

If you are running MySQL on Windows, take a look at gawk:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql.exe \
    --skip-column-name -u root -p -e \
    "show status where variable_name='aborted_clients';" | \
        "C:\Program Files(x86)\GnuWin32\bin\awk.exe" "{ print $2 }"
share|improve this answer
    
I am working on windows based, also I need a MySQL query to get the output which will be used in our application. –  Phanindra Oct 11 '11 at 14:06
1  
You can use gawk: gnuwin32.sourceforge.net/packages/gawk.htm –  quanta Oct 11 '11 at 14:50
add comment

Of course, you can use the following:

C:\> mysql -u... -p... -A -N -e"show global status like 'Aborted_clients'" > abc.txt

This will echo two tokens

  • Aborted_clients
  • the number

I am not a Windows expert, but I do know something you will have to emulate from Linux.

The awk command is what you would need.

C:\> mysql -u... -p... -A -N -e"show global status like 'Aborted_clients'" | awk '{print $2}'

The problem is that awk does not exist in Windows batch language or in PowerShell. However, here is a nice blogpost on how to emulate it in Windows PowerShell.

I hope it helps.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.