[MDEV-26756] ANALYZE TABLE prints EITS statistics collected while it has not done it Created: 2021-10-04  Updated: 2023-10-03

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: eits


 Description   

ANALYZE TABLE code ignores any (or some?) errors it gets from updating stats tables.

As a result, One may get Engine-independent statistics collected message, while statistics has not been updated.

Example: a read-only setting prevents a non-root user from updating the stats, while the message is printed that stats were updated: (a question whether one should be allowed to update EITS stats when read_only=1 is a separate question): 

create user test@localhost;
 
grant CREATE, DROP, INSERT, SELECT on *.* to test@localhost;
 
 
 
create table t1 (a int);
 
insert into t1 values (1),(2),(3);
 
set global read_only=1;
 
# This will produce nothing
 
select * from mysql.column_stats;
 
select * from mysql.table_stats;
 
 
 
connect (con1,localhost,test,,test);
 
connection con1;
 
# This will show "Engine-independent statistics collected"
 
analyze table t1 persistent for all;
 
 
 
# But we can see that EITS stats were NOT collected:
 
select * from mysql.column_stats;
 
select * from mysql.table_stats;
 
 
 
disconnect con1;
 
connection default;
 
set global read_only=0;
 
drop table t1;

prints on current 10.6:

create user test@localhost;
 
grant CREATE, DROP, INSERT, SELECT on *.* to test@localhost;
 
create table t1 (a int);
 
insert into t1 values (1),(2),(3);
 
set global read_only=1;
 
select * from mysql.column_stats;
 
db_name table_name      column_name     min_value       max_value       nulls_ratio     avg_length      avg_frequency   hist_size       hist_type       histogram
 
select * from mysql.table_stats;
 
db_name table_name      cardinality
 
connect  con1,localhost,test,,test;
 
connection con1;
 
analyze table t1 persistent for all;
 
Table   Op      Msg_type        Msg_text
 
test.t1 analyze status  Engine-independent statistics collected
 
test.t1 analyze status  OK
 
select * from mysql.column_stats;
 
db_name table_name      column_name     min_value       max_value       nulls_ratio     avg_length      avg_frequency   hist_size       hist_type       histogram
 
select * from mysql.table_stats;
 
db_name table_name      cardinality
 
disconnect con1;
 
connection default;
 
set global read_only=0;
 
drop table t1;



 Comments   
Comment by Sergei Petrunia [ 2021-10-04 ]

Another testcase: it shows how wrong definition of mysql.column_stats causes
ANALYZE TABLE to collect the statistics but then silently discard it:

use mysql;
 
alter table column_stats modify histogram blob;
 
flush tables;


use test;
 
create table t10 (a int); 
insert into t10 values (1),(2),(3);
 
analyze table t10 persistent for all; 
+----------+---------+----------+-----------------------------------------+
 
| Table    | Op      | Msg_type | Msg_text                                |
 
+----------+---------+----------+-----------------------------------------+
 
| test.t10 | analyze | status   | Engine-independent statistics collected |
 
| test.t10 | analyze | status   | OK                                      |
 
+----------+---------+----------+-----------------------------------------+
 
2 rows in set (0.006 sec)

In the server stderr, one can see this:

  2021-10-04 21:05:18 5 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type varbinary(255), found type blob.
 
  2021-10-04 21:05:26 5 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type varbinary(255), found type blob.

And nothing was written into the stat tables:

MariaDB [test]> select * from mysql.column_stats\G
 
Empty set (0.001 sec)
 
 
 
MariaDB [test]> select * from mysql.table_stats\G
 
Empty set (0.001 sec)

Comment by Sergei Petrunia [ 2021-10-04 ]

I've attempted to fix this in 10.7 with this:

diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
 
index 3a1a03d162b..5680c9ada00 100644
 
 
 
@@ -2880,7 +2880,7 @@ int update_statistics_for_table(THD *thd, TABLE *table)
 
 
   start_new_trans new_trans(thd);
 
 
-  if (open_stat_tables(thd, tables, TRUE))
 
+  if ((rc= open_stat_tables(thd, tables, TRUE)))
 
     DBUG_RETURN(rc);
 
    
   save_binlog_format= thd->set_current_stmt_binlog_format_stmt();

It has caused a failure in binlog.read_only (see exchange on Slack).
The failure is trivial - a failure to open EITS table for writing when running ANALYZE TABLE in read-only mode is now visible. The interesting part is that the failure is not visible when running the whole test suite.

Generated at Thu Feb 08 09:47:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.