Bug #72368 Empty/zero statistics for imported tablespace until explicit ANALYZE TABLE
Submitted: 17 Apr 2014 12:15 Modified: 17 Apr 2014 14:13
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: analyze, IMPORT TABLESPACE, missing manual, persistent statistics, show
Triage: Needs Triage: D3 (Medium)

[17 Apr 2014 12:15] Valeriy Kravchuk
Description:
When somebody copies InnoDB table created with persistent statistics (explicitly defined or set that way because of innodb_stats_persistent=ON at the moment of creation) to other server or database based on the manual:

https://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

she ends up without table statistics for the imported table, like this:

mysql> alter table ti discard tablespace;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table ti import tablespace;
Query OK, 0 rows affected (0.46 sec)

mysql> select * from ti;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> show table status like 'ti'\G
*************************** 1. row *************************
           Name: ti
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-04-17 14:37:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

It seems to stay that way (with all the consequences for further queries you can imagine) until explicit ANALYZE TABLE is executed or until data changes happen that may cause recalculation. SHOW FIELDS or checking other metadata does NOT help, even if it should based on settings:

mysql> show variables like 'innodb_sta%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | ON          |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
| innodb_status_output                 | OFF         |
| innodb_status_output_locks           | OFF         |
+--------------------------------------+-------------+
9 rows in set (0.00 sec)

mysql> select * from ti;
+----+------+
| id | c1   |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> show table status like 'ti'\G
*************************** 1. row ***************************
           Name: ti
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-04-17 14:37:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

How to repeat:
With innodb_file_per_table=1 and settings like this:

mysql> show variables like 'innodb_sta%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | ON          |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
| innodb_status_output                 | OFF         |
| innodb_status_output_locks           | OFF         |
+--------------------------------------+-------------+
9 rows in set (0.00 sec)

create some simple InnoDB table like this:

create table ti(id int primary key, c1 varchar(10)) engine=InnoDB;
insert into ti values( 1,'a'), (2,'b');
analyze table ti;

Then export tablespace by the manual:

flush tables ti for export;
...

Then on the othjer system with the same settings related to persistent statistics create table of the same structure:

create table ti(id int primary key, c1 varchar(10)) engine=InnoDB;
alter table ti discard tablespace;

Copy .ibd and .cfg files to the database directory, then:

alter table ti import tablespace;
select * from ti;
show table status like 'ti'\G
show fields from ti;
...

Rows are there, but in metadata we see zero rows reported until explicit ANALYZE TABLE is executed.

Suggested fix:
Either update persistent statistics for the table when tablespace is imported, or make sure to explicitly document everywhere that one has to execute ANALYZE TABLE after importing to get proper statistics.
[17 Apr 2014 14:13] Godofredo Miguel Solorzano
Thank you for the bug report.
[14 Mar 2016 4:30] Shaohua Wang
Posted by developer:
 
Backport rb#7428(BUG#20125349 - PERSISTANT STATS IS NOT UPDATED WHEN TTS IS IMPORTED.) to 5.6