no problems with statement based
show master status; set binlog_format=statement;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE INTO TABLE mysql.table_stats;
|
This is actually expected. Persistent statistic updates are not replicated (and cannot be, really); so the contents of the tables can easily be different. If you update the table directly, which you normally shouldn't, and if you aren't careful enough to set sql_log_bin to 0 while doing that, bad things will happen.
While the replication on persistent statistics makes the assumption that the data is the same on the slaves, which is the goal for many replication architectures, I'm generally willing to take that risk compared to the consequence of a replicating analyze table blocking writes to tables for what can be 30mins+ for big tables and using a far amount of IO in the meantime. Is there another consequence to direct manipulation that I'm missing?
Would having engine independent stats table as engine=innodb with STATS_PERSISTENT=0 like innodb_index_stats/innodb_table_stats help?
CREATE TABLE `innodb_index_stats` (
|
...
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
|
Perhaps this should be a feature request for ANALYZE RESULTS_TO_BINLOG TABLE .... Is that more palatable?
Well, there are lots of consequences of replicating updates to persistent stats.
First, a slave might be not MariaDB 10.0/10.1. It can be for example MySQL 5.6 – while it's not our main focus to maintain downgrade/crossgrade replication, we wouldn't want to break it without a good reason.
Second, stat tables are currently MyISAM; updating them in a transaction with InnoDB tables creates a "transactional-non-transactional" mix, which the traditional replication is consistently bad at (also, I can't say right away how exactly it will affect parallel replication, but I don't expect anything good).
Third, replicating stat table events won't really solve anything – ANALYZE TABLE has always been replicated as a statement, statistics or not, so you'll just get both row events and the actual ANALYZE.
I can probably continue. I had at least one other reason, but I removed it because it doesn't sound right and I forgot what I meant while I was writing it.
Of course, all arguments concern certain different replication setups. If you talk about an idealistic one – replication is strictly between the same versions or up; you always use the same binlog format; you don't do any writes on slave and the data is exactly the same; settings (statistics-wise) are exactly the same – then yes, the current implementation is definitely not optimal. Unfortunately, we can't support ideal scenarios only.
If you can think of a solution which will improve the current approach without breaking various scenarios we need to support, you are more than welcome to request the feature... or even implement it and submit a patch 
According to the comment above, closing as "not a bug" for now.
Daniel has created a new task MDEV-7901 for re-implementing ANALYZE TABLE.