Description:
In MySQL 8.0 (tested on 8.0.44), the replication of column histograms via ANALYZE TABLE ... UPDATE HISTOGRAM does not work as expected if the Replica is in read_only mode.
While standard DML (INSERT/UPDATE/DELETE) is applied correctly by the replication applier thread despite the read_only flag, the ANALYZE statement for histograms is blocked.
Crucial point: The GTID of the transaction is still incremented on the Replica, suggesting a successful execution. However, the information_schema.column_statistics table remains empty on the Replica.
How to repeat:
Set up a Master-Replica pair (GTID-based).
On Replica, enable read-only mode:
SET GLOBAL read_only = 1;
On Master, generate a histogram for any table:
ANALYZE TABLE my_table UPDATE HISTOGRAM ON my_column;
Verify on Master that information_schema.column_statistics is populated:
SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.column_statistics
WHERE TABLE_NAME = 'my_table';
On Replica, check @@global.gtid_executed. It will show that the transaction from Master was received and "applied".
On Replica, check information_schema.column_statistics.
Actual Results:
The column_statistics table is empty. If you manually run the same ANALYZE command on the Replica, it fails with Error 1290 (HY000): The MySQL server is running with the --read-only option.
In events_errors_summary_by_thread_by_error, there is also a record with this error which was probably created by the replication applier thread:
SELECT * FROM performance_schema.events_errors_summary_by_thread_by_error
WHERE ERROR_NUMBER = 1290;
Expected Results:
The replication applier thread should have the same privileges for updating histograms as it does for DML/DDL. If the GTID is incremented, the state of the Replica's metadata should match the Master.
Note:
When the replica is not read_only, the replication of histograms works properly.
Suggested fix:
Ensure that the replication applier thread (worker) bypasses the read_only check for ANALYZE TABLE ... UPDATE HISTOGRAM and DROP HISTOGRAM statements, similar to how it handles other replicated events.
Also check the behavior in super_read_only mode.
