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.
