userstat.patch has several bugs (incorrectly ported from Google Tools for MySQL)

[Oleg Tsarev]

userstat.patch has several bugs:
 * WHERE and LIKE don't work with SHOW syntax (i.e. doesn't work (for example) SHOW CLIENT_STATISTICS LIKE 'client_name%')
 * Counter from *_STATISTICS don't update while connection live (updates from connection go to statistics just after connection close)
 * Incorrectly counted the kind of queries (Other/Select/etc)

[Raghavendra D Prabhu (raghavendra-prabhu)]

Tested on PS 5.5:

1)

SHOW CLIENT_STATISTICS like '%localhost%'\G
*************************** 1. row ***************************
                Client: localhost
     Total_connections: 2
Concurrent_connections: 0
        Connected_time: 24
             Busy_time: 0
              Cpu_time: 0
        Bytes_received: 243
            Bytes_sent: 0
  Binlog_bytes_written: 0
          Rows_fetched: 4
          Rows_updated: 0
       Table_rows_read: 0
       Select_commands: 4
       Update_commands: 0
        Other_commands: 1
   Commit_transactions: 0
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 0
 Total_ssl_connections: 0
1 row in set (0.00 sec)

mysql> SHOW CLIENT_STATISTICS like '%localhost%'\G
*************************** 1. row ***************************
                Client: localhost
     Total_connections: 2
Concurrent_connections: 0
        Connected_time: 122
             Busy_time: 0
              Cpu_time: 0
        Bytes_received: 289
            Bytes_sent: 0
  Binlog_bytes_written: 0
          Rows_fetched: 5
          Rows_updated: 0
       Table_rows_read: 0
       Select_commands: 5
       Update_commands: 0
        Other_commands: 1
   Commit_transactions: 0
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 0
 Total_ssl_connections: 0
1 row in set (0.00 sec)

mysql> SHOW CLIENT_STATISTICS like '%localhost%'\G
*************************** 1. row ***************************
                Client: localhost
     Total_connections: 2
Concurrent_connections: 0
        Connected_time: 124
             Busy_time: 0
              Cpu_time: 0
        Bytes_received: 335
            Bytes_sent: 0
  Binlog_bytes_written: 0
          Rows_fetched: 6
          Rows_updated: 0
       Table_rows_read: 0
       Select_commands: 6
       Update_commands: 0
        Other_commands: 1
   Commit_transactions: 0
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 0
 Total_ssl_connections: 0
1 row in set (0.00 sec)

2)

select * from table_statistics\G
*************************** 1. row ***************************
          TABLE_SCHEMA: mysql
            TABLE_NAME: user
             ROWS_READ: 7
          ROWS_CHANGED: 0
ROWS_CHANGED_X_INDEXES: 0
*************************** 2. row ***************************
          TABLE_SCHEMA: mysql
            TABLE_NAME: proxies_priv
             ROWS_READ: 2
          ROWS_CHANGED: 0
ROWS_CHANGED_X_INDEXES: 0
*************************** 3. row ***************************
          TABLE_SCHEMA: mysql
            TABLE_NAME: db
             ROWS_READ: 2
          ROWS_CHANGED: 0
ROWS_CHANGED_X_INDEXES: 0
3 rows in set (0.00 sec)

mysql> select * from mysql.user\G

..........
.........

mysql> select * from table_statistics\G
*************************** 1. row ***************************
          TABLE_SCHEMA: mysql
            TABLE_NAME: user
             ROWS_READ: 14
          ROWS_CHANGED: 0
ROWS_CHANGED_X_INDEXES: 0
*************************** 2. row *********...

[Fernando Ipar (fipar)]

I am not seeing the "Counter from *_STATISTICS don't update while connection live" part on 5.5.32-31.0.
I read a table from multiple threads and table_statistics.Rows_read reliably increases while the connections are open.

Is there a repeatable test case for this somewhere?

[Laurynas Biveinis (laurynas-biveinis)]

Fernando -

If it's not repeatable it could have been fixed in the last year and a half, feel free to review the release notes or bugs tagged with "userstat".

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1235