Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The client stats table has an "empty queries" column. What does that mean? Simple googling just tells me "The number of times this client's connections sent empty queries to the server." /facepalm

share|improve this question
    
MySQL 5.5 has 37 tables in the information_schema. None of them are named CLIENT_STATISTICS. There is a table called STATISTICS but that contains the layout of all indexes to be used as output for the SHOW INDEXES FROM command. –  RolandoMySQLDBA Oct 6 '11 at 17:43
    
I will withdraw my answer on this one until I can get a Percona Server to artifically increment empty_queries. –  RolandoMySQLDBA Oct 6 '11 at 20:05
    
Probably better to just ask the question what is an empty query in SHOW [GLOBAL] STATUS. Client statistics is just a broken down version per user which was born as part of Google's userstats feature (now in Percona Server, MariaDB). –  Morgan Tocker Oct 6 '11 at 21:41

2 Answers 2

Unfortunately this is not documented in the MySQL manual - the page would be this one. I didn't know the answer, but I did manage to debug it from the 5.5 source:

It is queries to the server that do not produce SQL queries. i.e.

laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'Com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 2     |
+-----------------+-------+
laptop1113-2:~ morgant$ mysql --comments -e '-- sql comment'
laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 3     |
+-----------------+-------+

(You need to add --comments, because the command line utility by default will strip them.)


How to debug something like this in MySQL:

  • The COM_ variables mean commands - this is a clue that it's some sort of statement. The code that generates SHOW GLOBAL STATUS is in ./sql/mysqld.cc (you could have found this if you didn't know by grepping for query and empty on the same line). At ~line 3025:

    {"drop_view",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_VIEW]), SHOW_LONG_STATUS},
    {"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
    {"execute_sql",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EXECUTE]), SHOW_LONG_STATUS},
    
  • So this tells us that show global status is just reading the value of SQLCOM_EMPTY_QUERY. We can then grep for this string:

    laptop1113-2:5.5 morgant$ grep -riF 'SQLCOM_EMPTY_QUERY' .
    ./mysql-test/r/implicit_commit.result:# SQLCOM_EMPTY_QUERY
    ./mysql-test/t/implicit_commit.test:--echo # SQLCOM_EMPTY_QUERY
    ./sql/mysqld.cc:  {"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
    ./sql/sql_lex.h:  SQLCOM_SHOW_WARNS, SQLCOM_EMPTY_QUERY, SQLCOM_SHOW_ERRORS,
    ./sql/sql_parse.cc:  case SQLCOM_EMPTY_QUERY:
    ./sql/sql_yacc.yy:            thd->lex->sql_command= SQLCOM_EMPTY_QUERY;
  • Ignore the tests, and the file we just looked at. The only other files mentioned here are those that relate to the parser (yacc). If we open sql_parse.cc, we just see a return with nothing of interest:

    case SQLCOM_EMPTY_QUERY:
    my_ok(thd);
    break;
    
  • If we open sql_yacc.yy, we can see that this somehow relates to being at the END_OF_INPUT, and no query was found:

    query:
          END_OF_INPUT
          {
            THD *thd= YYTHD;
            if (!thd->bootstrap &&
              (!(thd->lex->select_lex.options & OPTION_FOUND_COMMENT)))
            {
              my_message(ER_EMPTY_QUERY, ER(ER_EMPTY_QUERY), MYF(0));
              MYSQL_YYABORT;
            }
            thd->lex->sql_command= SQLCOM_EMPTY_QUERY;
            YYLIP->found_semicolon= NULL;
          }
        | verb_clause
    
  • From here, I pretty much made a guess. Everything is handled at the parser, and never enters other components of mysqld.

share|improve this answer
    
Morgan, confirmed that triggers it. So are comments the only thing that triggers it? –  Derek Downey Oct 6 '11 at 21:14
    
I think so. I thought white space might too, but I can't seem to trigger it. –  Morgan Tocker Oct 6 '11 at 21:16
    
Does anyone mind helping edit the code samples? - my wiki skills fail me. –  Morgan Tocker Oct 6 '11 at 21:34
    
done. Just needed a few more spaces since it was part of the bullet points. Also, that's pretty much how I debugged, but my 'guess' was wrong! :) or rather, I didn't know how to mimic the comment so it didn't strip out as you mentioned...also, no idea what the bootstrap portion does. –  Derek Downey Oct 6 '11 at 22:41
    
That strip comments behavior is such an anti-feature. History here fwiw bugs.mysql.com/bug.php?id=26215 –  Morgan Tocker Oct 6 '11 at 23:03

As Rolando points out, the CLIENT_STATISTICS table is not a MySQL table.

However, I'm fairly sure it would be mapped to the 'Com_empty_query' status variable:

mysql> SHOW STATUS LIKE '%empty%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 0     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%empty%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 0     |
+-----------------+-------+
1 row in set (0.00 sec)

But I cannot seem to trigger this counter at all.

At first I thought just issuing a ; command would work:

mysql> ;
ERROR: 
No query specified

Or a query that returned an empty result set:

mysql> SELECT * FROM foo WHERE chk=2;
Empty set (0.09 sec)

But none of those are triggering it. I delved into the source code, but it wasn't very helpful in my 10 minutes of looking, and the only thing I can see is that it would indicate an issue with client buffers sending commands.

share|improve this answer
    
You went into the source code for this one ? Now, that's a GLADIATOR. +1 !!! –  RolandoMySQLDBA Oct 6 '11 at 18:34
    
@rolando I can't seem to trigger it, even with your example WHERE 1=2. So it made me curious enough :) –  Derek Downey Oct 6 '11 at 18:48
    
I tried everything. I even tried SET @sql = 'SELECT * FROM mysql.user WHERE 0'; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;. NOTHING !!!! –  RolandoMySQLDBA Oct 6 '11 at 19:37

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.