Bug #98178 Restoring from Fullbackup(mysqldump --all-database) reset innodb_%_stats data.
Submitted: 10 Jan 5:34 Modified: 10 Jan 11:37
Reporter: Meiji Kimura Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version:8.0.18, 5.7.28, 5.6.46 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.6, 5.7, 8.0

[10 Jan 5:34] Meiji Kimura
Description:
When restoring all database from Fullbackup(mysqldump --all-databases),  all data of innodb_index_stats,  innodb_table_stats are reset. So need to analyze all InnoDB Tables for restore innodb_%_stats.

When making 1000-row table 'test.t1',

[Before restoring]
mysql [localhost] {msandbox} (mysql) > select * from innodb_table_stats where database_name = 'test';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2020-01-10 13:45:45 |   1000 |                    4 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mysql) > select * from innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_diff_pfx01 |       1000 |           3 | DB_ROW_ID                         |
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | n_leaf_pages |          3 |        NULL | Number of leaf pages in the index |
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:45:45 | size         |          4 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

[After restoring]
mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_table_stats where database_name = 'test';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2020-01-10 13:56:53 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > 
mysql [localhost] {msandbox} ((none)) > select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | t1         | GEN_CLUST_INDEX | 2020-01-10 13:56:53 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
3 rows in set (0.00 sec) 

How to repeat:
(1) Making 8.0.x environment with mysql sandbox, like this.

make_sandbox 8.0.17

(2) Making backup command like this. Replace /bin/mysql -> /bin/mysqldump (Line.24)

cp use backup
# and replace Line.24

(3) Activate mysql commanline tool, and making 'test.t1' table with 1000-row.

./use test
create table t1 SELECT @seq_no := 1 AS seq_no UNION SELECT @seq_no := @seq_no + 1 AS seq_no FROM information_schema.COLUMNS LIMIT 1000;

(4) Check statistics.

select * from mysql.innodb_table_stats where database_name = 'test';
select * from mysql.innodb_index_stats where database_name = 'test';

(5) Making backup and restore.

./backup --all-databases > fullbackup.sql
./use < fullbackup.sql

(6) Re-Check statistics.

select * from mysql.innodb_table_stats where database_name = 'test';
select * from mysql.innodb_index_stats where database_name = 'test';

Suggested fix:
[Cause]
When making backup,  in client/mysqldump.cc, these codes add for not modifying innodb_%_stats;

 693     if (stats_tables_included) {
 694       fprintf(sql_file,
 695               "/*!50606 SET "
 696               "@OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;\n");
 697       fprintf(sql_file,
 698               "/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;\n");
 699     } 

So dump file became as result.

/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;

(snip)

/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */; 

When restoring mysql database, it works,
But subsequent user tables, drop & create tables for --opt option(default), so all user innodb table's stats reset :(

[Workaround] Do one of these workaround.

(1) Change this line of dump/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
into /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */;

(2) After restoring, issue 'ANALYZE TABLE' for all InnoDB type tables.

(3) Truncate these tables, and re-INSERT from dump. (Need to edit a dump by hand.
| innodb_index_stats   |
| innodb_table_stats   |

[Suggested fix]
If backup file include user innodb tables and (stats_tables_included), add INNODB_STATS_AUTO_RECALC=ON instead of OFF.

/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */;

(snip)

/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;
[10 Jan 11:37] Umesh Shastry
Hello Meiji-San,

Thank you for the report and feedback.

regards,
Umesh