Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6148

If ANALYZE TABLE with transient statistics ran more than 600 seconds the execution of a diagnostic query may cause a server exit.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.28-31
    • Fix Version/s: 5.7.29-32
    • Component/s: None
    • Labels:

      Description

      This ticket is the continuation of PS-6113. During fix of PS-6113 it was discovered that following setup causes the same problem. 

      It will be solved as the separate ticket (and separate PR), to limit changes of code for solving 1 problem per Jira ticket (2 issues are similar, but handled by different parts of code)

      Steps to reproduce:

      1. create table with STATS_PERSISTENT=0 clause (or alter table used in PS-6113 to use transient statistics (I used following SQL to create table where ANALYZE TABLE takes 30 secs - not enough for crash standard server, but enough to investigate after hacking srv_fatal_semaphore_wait_threshold variable in InnoDB code)
      drop procedure load_test;
      drop table test;
      create table test (id int, txt varchar(128), index idx_id(id), index idx_txt(txt), index idx_id_txt(id, txt)) ENGINE=INNODB, STATS_PERSISTENT=0;
      delimiter //
      CREATE PROCEDURE load_test() begin declare v_iterations int default 0;
      START TRANSACTION;
      while v_iterations < 4096000 do insert into test (id, txt) values (v_iterations, (SELECT SHA2(RAND(), 256)));
      set v_iterations=v_iterations+1;
      end while;
      COMMIT;
      end//
      delimiter ;
      call load_test(); 
      1. set global innodb_stats_transient_sample_pages=30000 (it has to be huge value)
      2. SELECT ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH),COUNT(ENGINE),SUM(DATA_LENGTH),SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC; 

       

      Current behavior:

      Server crashes

      Expected behavior:

      Server does not crash. Select query works.

       

       

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                kamil.holubicki Kamil Holubicki
                Reporter:
                kamil.holubicki Kamil Holubicki
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 1 day, 1 hour, 25 minutes
                    1d 1h 25m