Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34154

Userstat: Updates to system tables via DDL are reflected in client/user statistics

    XMLWordPrintable

Details

    Description

      In the scope of MDEV-33151 / MDEV-33152 implementation and further as a fix for MDEV-33881, userstat was made to ignore tables from mysql schema.
      However, they are still sometimes reflected in client/user statistics.

      In the test case below, rows_inserted, key_read_hits and key_read_misses are updated in user/client statistics when a procedure is created or re-created (re-creation causes a key hit, and creation of a new procedure causes a key miss; inserted rows are incremented in both cases). However, an explicit query from mysql.proc does have such effect.

      set @userstat.save= @@userstat;
      		 
      set global userstat= 1;
      		 
      flush user_statistics, table_statistics;
      		 
       
      		 
      create procedure pr() begin end;
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
       
      		 
      create or replace procedure pr() begin end;
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
       
      		 
      select type from mysql.proc where name = 'pr';
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
       
      		 
      # Cleanup
      		 
      set global userstat= @userstat.save;
      		 
      drop procedure pr;

      bb-11.5-monty e8c8d04ec094e491d52bd22b1d00a146bdff2dad

      		 
      create procedure pr() begin end;
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      user	rows_inserted	rows_updated	key_read_hits	key_read_misses
      		 
      root	1	0	0	1
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
      table_name	rows_changed	rows_inserted	rows_updated	key_read_hits	key_read_misses


      create or replace procedure pr() begin end;
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      user	rows_inserted	rows_updated	key_read_hits	key_read_misses
      		 
      root	2	0	1	1
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
      table_name	rows_changed	rows_inserted	rows_updated	key_read_hits	key_read_misses


      select type from mysql.proc where name = 'pr';
      		 
      type
      		 
      PROCEDURE
      		 
      select user, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.user_statistics;
      		 
      user	rows_inserted	rows_updated	key_read_hits	key_read_misses
      		 
      root	2	0	1	1
      		 
      select table_name, rows_changed, rows_inserted, rows_updated, key_read_hits, key_read_misses from information_schema.table_statistics;
      		 
      table_name	rows_changed	rows_inserted	rows_updated	key_read_hits	key_read_misses

      Same applies to stat tables, etc.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33151 Add more columns to TABLE_STATISTICS and USER STATS

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.