Slave hung with state "Checking table" after restore

[monty solomon]

The slave is hung with the state "Checking table" after restoring from backup.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: stivers
                  Master_User: SUSR_Repl
                  Master_Port: 3306
                Connect_Retry: 15
              Master_Log_File: bin.010274
          Read_Master_Log_Pos: 18179070
               Relay_Log_File: relay.000017
                Relay_Log_Pos: 277
        Relay_Master_Log_File: bin.010271
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 47383631
              Relay_Log_Space: 84939061
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 9722
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3213587493
                  Master_UUID: 66a819f2-ac37-11e4-ab2f-0a1d40e05ab8
             Master_Info_File: /opt/mysql/dbdata1/email-p0/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Checking table
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

[monty solomon (monty+launchpad)]

The backup is created using innobackupex, pigz, and tar

/usr/bin/env innobackupex --slave-info --stream=tar ./ | pigz - > $file

The restored database is created using tar and innobackupex

tar xipf
innobackupex --apply-log --use-memory=4G

xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

CentOS release 6.6 (Final)

Linux twilight-salad 3.18.19-61.el6.x86_64 #1 SMP Mon Jul 27 11:04:37 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Please send output of SHOW PROCESSLIST, taken in time when issue was observable.

[monty solomon (monty+launchpad)]

mysql> show processlist;
+-----+----------------+---------------------+-----------+---------+-------+----------------------------------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+-----+----------------+---------------------+-----------+---------+-------+----------------------------------+------------------+-----------+---------------+
| 1 | SUSR_Checksum | 172.16.31.129:42881 | NULL | Sleep | 0 | | NULL | 0 | 0 |
| 3 | system user | | NULL | Connect | 795 | Waiting for master to send event | NULL | 0 | 0 |
| 4 | system user | | Email | Connect | 60027 | Checking table | eventDetails | 0 | 0 |
| 6 | SUSR_Monitor | 172.16.28.209:28860 | mysql | Sleep | 94 | | NULL | 0 | 0 |
| 8 | root | localhost | NULL | Sleep | 2 | | NULL | 0 | 0 |
| 12 | SUSR_Heartbeat | 127.0.0.1:35943 | NULL | Sleep | 0 | | NULL | 0 | 0 |
| 15 | SUSR_Heartbeat | 127.0.0.1:35962 | NULL | Sleep | 0 | | NULL | 403 | 403 |
| 933 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
| 938 | SUSR_Heartbeat | localhost | heartbeat | Sleep | 0 | | NULL | 1 | 1 |
+-----+----------------+---------------------+-----------+---------+-------+----------------------------------+------------------+-----------+---------------+
9 rows in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

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