What is an “empty query” in information_schema.CLIENT_STATISTICS?

Question

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

Answer 1

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.

Answer 2

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.