32bit counters in user statistics

[Peter Zaitsev]

Hi,

Percona Server 11.6 on RHEL5 64bit (from RPM)

Note the numbers for s lot of counters from SHOW USER STATISTICS are showing as max 32bit integer.

I'm observing same behavior for show table_statistics for number of rows x number of indexes. show index_statistics is also affected

                  User: #mysql_system#
     Total_connections: 1
Concurrent_connections: 0
        Connected_time: 2084781
             Busy_time: 1104079
              Cpu_time: 268182
        Bytes_received: 0
            Bytes_sent: 0
  Binlog_bytes_written: 2147483647
          Rows_fetched: 0
          Rows_updated: 2147483647
       Table_rows_read: 796477
       Select_commands: 0
       Update_commands: 2147483647
        Other_commands: 2147483647
   Commit_transactions: 2147483647
 Rollback_transactions: 0
    Denied_connections: 0
      Lost_connections: 0
         Access_denied: 0
         Empty_queries: 0

I

[Laurynas Biveinis (laurynas-biveinis)]

What exact commands should reproduce this?
If I do
SET GLOBAL userstat_running=ON;
then immediately
SHOW USER_STATISTICS;
SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS;

I see zeros and not 32-bit uint max values there, same for other stat tables.

[Olaf van Zandwijk (olafz)]

This issue still exists in 5.5.18.

Howto reproduce: after enabling userstats and waiting for a while (on a busy server it might not take that long ;)) several counters run to the 32bit counter maximum:

mysql> SELECT ... FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE (...)\G
*************************** 1. row ***************************
BYTES_RECEIVED: 2147483647
BYTES_SENT: 2147483647
TABLE_ROWS_READ: 2147483647

etc.

[Laurynas Biveinis (laurynas-biveinis)]

Thank you. Originally I misunderstood the issue to be that counters have 32bit maxints upon server startup.

[Raghavendra D Prabhu (raghavendra-prabhu)]

The issue seems to be that even though the structures are unlonglong and longlong is stored, the schema seems to be MYSQL_TYPE_LONG

  {"BYTES_RECEIVED", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_received", SKIP_OPEN_TABLE},
  {"BYTES_SENT", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Bytes_sent", SKIP_OPEN_TABLE},
  {"BINLOG_BYTES_WRITTEN", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, "Binlog_bytes_written", SKIP_OPEN_TABLE},

Also,

      table->field[6]->store((longlong)user_stats->bytes_received);
      table->field[7]->store((longlong)user_stats->bytes_sent);
      table->field[8]->store((longlong)user_stats->binlog_bytes_written);

So, MYSQL_TYPE_LONGLONG needs to used instead of MYSQL_TYPE_LONG and ulonglong instead of longlong in ->store()

Same for other fields and other similar tables.