← Back to team overview

maria-developers team mailing list archive

  1. maria-developers team
  2. Mailing list archive
  3. Message #09131

Re: [Commits] 59fcd7f: MDEV-9118 ANALYZE TABLE for Engine independent status fetchs blob/text columns without use

  Thread Previous • Date Previous • • Thread Next 
Hi, Sergei!

On 22.12.2015 12:42, Sergey Petrunia wrote:

Hi Sanja,

So, the patch produces warnings whenever a column is not collected.

I think they should be produced only if the columns were explicitly specified
in ANALYZE TABLE ... COLUMNS (col1, ...) syntax.

The idea is that we should issue a warning when we're clearly not doing what
the user has requested.

ANALYZE TABLE .. PERSISTENT FOR ALL should not produce a warning. "ALL" here
means "ALL that statsistics is can be collected for", so it is reasonable that
the server will not collect statistics which it is unable to collect (e.g.
blobs).

Ok to push after this has been addressed.
IMHO warning should be issued each time field is skipped. For me (as well as many users ALL means all fields (BTW manual also sais so)). 



On Fri, Dec 18, 2015 at 12:23:45PM +0100, OleksandrByelkin wrote:

revision-id: 59fcd7ff2315d007045eb987da5f21abbea6f6f1 (mariadb-10.1.9-20-g59fcd7f)
parent(s): 953d5680a3c050273a8f29253f7386984679f92b
committer: Oleksandr Byelkin
timestamp: 2015-12-18 12:23:45 +0100
message:

MDEV-9118 ANALYZE TABLE for Engine independent status fetchs blob/text columns without use

Do not include BLOB fields by default.

---
  mysql-test/r/analyze.result                     |  2 +
  mysql-test/r/mysqlcheck.result                  | 60 ++++++++++++++++++++-----
  mysql-test/r/statistics.result                  | 26 ++++++++++-
  mysql-test/r/statistics_index_crash-7362.result |  2 +
  mysql-test/t/statistics.test                    | 11 +++++
  sql/share/errmsg-utf8.txt                       |  3 ++
  sql/sql_admin.cc                                | 31 ++++++++++---
  7 files changed, 117 insertions(+), 18 deletions(-)

diff --git a/mysql-test/r/analyze.result b/mysql-test/r/analyze.result
index 9dff94a..5a05d1c 100644
--- a/mysql-test/r/analyze.result
+++ b/mysql-test/r/analyze.result
@@ -34,9 +34,11 @@ create table t1 (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_g
  insert into t1 values ('hello');
  analyze table t1;
  Table	Op	Msg_type	Msg_text
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'a'
  test.t1	analyze	status	OK
  analyze table t1;
  Table	Op	Msg_type	Msg_text
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'a'
  test.t1	analyze	status	Table is already up to date
  drop table t1;
  CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result
index 7d646dc..e69653f 100644
--- a/mysql-test/r/mysqlcheck.result
+++ b/mysql-test/r/mysqlcheck.result
@@ -7,19 +7,34 @@ mtr.test_suppressions                              OK
  mysql.column_stats                                 OK
  mysql.columns_priv                                 OK
  mysql.db                                           OK
-mysql.event                                        OK
+mysql.event
+Warning  : Engine-independent statistics are not collected for column 'body'
+Warning  : Engine-independent statistics are not collected for column 'body_utf8'
+status   : OK
  mysql.func                                         OK
  mysql.gtid_slave_pos                               OK
-mysql.help_category                                OK
+mysql.help_category
+Warning  : Engine-independent statistics are not collected for column 'url'
+status   : OK
  mysql.help_keyword                                 OK
  mysql.help_relation                                OK
-mysql.help_topic                                   OK
+mysql.help_topic
+Warning  : Engine-independent statistics are not collected for column 'description'
+Warning  : Engine-independent statistics are not collected for column 'example'
+Warning  : Engine-independent statistics are not collected for column 'url'
+status   : OK
  mysql.host                                         OK
  mysql.index_stats                                  OK
  mysql.innodb_index_stats                           OK
  mysql.innodb_table_stats                           OK
  mysql.plugin                                       OK
-mysql.proc                                         OK
+mysql.proc
+Warning  : Engine-independent statistics are not collected for column 'param_list'
+Warning  : Engine-independent statistics are not collected for column 'returns'
+Warning  : Engine-independent statistics are not collected for column 'body'
+Warning  : Engine-independent statistics are not collected for column 'comment'
+Warning  : Engine-independent statistics are not collected for column 'body_utf8'
+status   : OK
  mysql.procs_priv                                   OK
  mysql.proxies_priv                                 OK
  mysql.roles_mapping                                OK
@@ -31,7 +46,12 @@ mysql.time_zone_leap_second                        OK
  mysql.time_zone_name                               OK
  mysql.time_zone_transition                         OK
  mysql.time_zone_transition_type                    OK
-mysql.user                                         OK
+mysql.user
+Warning  : Engine-independent statistics are not collected for column 'ssl_cipher'
+Warning  : Engine-independent statistics are not collected for column 'x509_issuer'
+Warning  : Engine-independent statistics are not collected for column 'x509_subject'
+Warning  : Engine-independent statistics are not collected for column 'authentication_string'
+status   : OK
  mtr.global_suppressions                            Table is already up to date
  mtr.test_suppressions                              Table is already up to date
  mysql.column_stats                                 OK
@@ -69,19 +89,34 @@ mysql.user                                         OK
  mysql.column_stats                                 OK
  mysql.columns_priv                                 OK
  mysql.db                                           OK
-mysql.event                                        OK
+mysql.event
+Warning  : Engine-independent statistics are not collected for column 'body'
+Warning  : Engine-independent statistics are not collected for column 'body_utf8'
+status   : OK
  mysql.func                                         OK
  mysql.gtid_slave_pos                               OK
-mysql.help_category                                OK
+mysql.help_category
+Warning  : Engine-independent statistics are not collected for column 'url'
+status   : OK
  mysql.help_keyword                                 OK
  mysql.help_relation                                OK
-mysql.help_topic                                   OK
+mysql.help_topic
+Warning  : Engine-independent statistics are not collected for column 'description'
+Warning  : Engine-independent statistics are not collected for column 'example'
+Warning  : Engine-independent statistics are not collected for column 'url'
+status   : OK
  mysql.host                                         OK
  mysql.index_stats                                  OK
  mysql.innodb_index_stats                           OK
  mysql.innodb_table_stats                           OK
  mysql.plugin                                       OK
-mysql.proc                                         OK
+mysql.proc
+Warning  : Engine-independent statistics are not collected for column 'param_list'
+Warning  : Engine-independent statistics are not collected for column 'returns'
+Warning  : Engine-independent statistics are not collected for column 'body'
+Warning  : Engine-independent statistics are not collected for column 'comment'
+Warning  : Engine-independent statistics are not collected for column 'body_utf8'
+status   : OK
  mysql.procs_priv                                   OK
  mysql.proxies_priv                                 OK
  mysql.roles_mapping                                OK
@@ -93,7 +128,12 @@ mysql.time_zone_leap_second                        OK
  mysql.time_zone_name                               OK
  mysql.time_zone_transition                         OK
  mysql.time_zone_transition_type                    OK
-mysql.user                                         OK
+mysql.user
+Warning  : Engine-independent statistics are not collected for column 'ssl_cipher'
+Warning  : Engine-independent statistics are not collected for column 'x509_issuer'
+Warning  : Engine-independent statistics are not collected for column 'x509_subject'
+Warning  : Engine-independent statistics are not collected for column 'authentication_string'
+status   : OK
  mysql.column_stats                                 Table is already up to date
  mysql.columns_priv                                 Table is already up to date
  mysql.db                                           Table is already up to date
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
index bd6a084..fdda572 100644
--- a/mysql-test/r/statistics.result
+++ b/mysql-test/r/statistics.result
@@ -1212,6 +1212,7 @@ test	t2	PRIMARY	1	1.0000
  ANALYZE TABLE t1;
  Table	Op	Msg_type	Msg_text
  test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
  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
@@ -1225,7 +1226,6 @@ test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000	0	NULL	NULL
  test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000	0	NULL	NULL
  test	t1	e	0.01	0.112	0.2250	8.0000	6.2000	0	NULL	NULL
  test	t1	f	1	5	0.2000	1.0000	6.4000	0	NULL	NULL
-test	t1	b	NULL	NULL	0.2000	17.1250	NULL	NULL	NULL	NULL
  SELECT * FROM mysql.index_stats;
  db_name	table_name	index_name	prefix_arity	avg_frequency
  test	t1	idx2	1	7.0000
@@ -1265,6 +1265,7 @@ set use_stat_tables='never';
  ANALYZE TABLE t1 PERSISTENT FOR ALL;
  Table	Op	Msg_type	Msg_text
  test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
  test.t1	analyze	status	Table is already up to date
  SELECT * FROM mysql.table_stats;
  db_name	table_name	cardinality
@@ -1276,7 +1277,6 @@ test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000	0	NULL	NULL
  test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000	0	NULL	NULL
  test	t1	e	0.01	0.112	0.2250	8.0000	6.2000	0	NULL	NULL
  test	t1	f	1	5	0.2000	1.0000	6.4000	0	NULL	NULL
-test	t1	b	NULL	NULL	0.2000	17.1250	NULL	NULL	NULL	NULL
  SELECT * FROM mysql.index_stats;
  db_name	table_name	index_name	prefix_arity	avg_frequency
  test	t1	PRIMARY	1	1.0000
@@ -1291,6 +1291,28 @@ test	t1	idx4	3	NULL
  DELETE FROM mysql.table_stats;
  DELETE FROM mysql.column_stats;
  DELETE FROM mysql.index_stats;
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES();
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
+test.t1	analyze	status	Table is already up to date
+ANALYZE TABLE t1 PERSISTENT FOR columns(a,b) INDEXES();
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
+test.t1	analyze	status	Table is already up to date
+ANALYZE TABLE t1 PERSISTENT FOR columns(b) indexes(idx2);
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
+test.t1	analyze	status	Table is already up to date
+ANALYZE TABLE t1 PERSISTENT FOR columns() indexes(idx2);
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
  DROP TABLE t1,t2;
  set names utf8;
  CREATE DATABASE world;
diff --git a/mysql-test/r/statistics_index_crash-7362.result b/mysql-test/r/statistics_index_crash-7362.result
index 99f65d7..c213fa4 100644
--- a/mysql-test/r/statistics_index_crash-7362.result
+++ b/mysql-test/r/statistics_index_crash-7362.result
@@ -3,6 +3,7 @@ INSERT INTO t1 VALUES (unhex('3E0D0A4141414142334E7A6143317963324541414141424977
  ANALYZE TABLE t1 PERSISTENT FOR ALL;
  Table	Op	Msg_type	Msg_text
  test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'a'
  test.t1	analyze	status	OK
  SELECT * FROM mysql.index_stats WHERE index_name='a' AND table_name='t1';
  db_name	table_name	index_name	prefix_arity	avg_frequency
@@ -13,6 +14,7 @@ INSERT INTO t1 VALUES (unhex('3E0D0A4141414142334E7A6143317963324541414141424977
  ANALYZE TABLE t1 PERSISTENT FOR ALL;
  Table	Op	Msg_type	Msg_text
  test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'a'
  test.t1	analyze	status	OK
  SELECT * FROM mysql.index_stats WHERE index_name='a' AND table_name='t1';
  db_name	table_name	index_name	prefix_arity	avg_frequency
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
index 2c8dec3..3b57b8f 100644
--- a/mysql-test/t/statistics.test
+++ b/mysql-test/t/statistics.test
@@ -494,6 +494,17 @@ DELETE FROM mysql.table_stats;
  DELETE FROM mysql.column_stats;
  DELETE FROM mysql.index_stats;
+ 
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES();
+ANALYZE TABLE t1 PERSISTENT FOR columns(a,b) INDEXES();
+ANALYZE TABLE t1 PERSISTENT FOR columns(b) indexes(idx2);
+ANALYZE TABLE t1 PERSISTENT FOR columns() indexes(idx2);
+
+DELETE FROM mysql.table_stats;
+DELETE FROM mysql.column_stats;
+DELETE FROM mysql.index_stats;
+
+
  DROP TABLE t1,t2;
set names utf8; 
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 59908dc..178d1dd 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7136,3 +7136,6 @@ ER_KILL_QUERY_DENIED_ERROR
          eng "You are not owner of query %lu"
          ger "Sie sind nicht Eigentümer von Abfrage %lu"
          rus "Вы не являетесь владельцем запроса %lu"
+ER_NO_EIS_FOR_FIELD
+        eng "Engine-independent statistics are not collected for column '%s'"
+        ukr "Незалежна від типу таблиці статистика не збирається для стовбця '%s'"
diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
index 0787aa9..d8ca863 100644
--- a/sql/sql_admin.cc
+++ b/sql/sql_admin.cc
@@ -692,10 +692,20 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
        }
if (!lex->column_list) 
-      {
-        uint fields= 0;
-        for ( ; *field_ptr; field_ptr++, fields++) ;
-        bitmap_set_prefix(tab->read_set, fields);
+      {
+        bitmap_clear_all(tab->read_set);
+        for (uint fields= 0; *field_ptr; field_ptr++, fields++)
+        {
+          enum enum_field_types type= (*field_ptr)->type();
+          if (type < MYSQL_TYPE_MEDIUM_BLOB ||
+              type > MYSQL_TYPE_BLOB)
+            bitmap_set_bit(tab->read_set, fields);
+          else
+            push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                               ER_NO_EIS_FOR_FIELD,
+                               ER_THD(thd, ER_NO_EIS_FOR_FIELD),
+                               (*field_ptr)->field_name);
+        }
        }
        else
        {
@@ -713,8 +723,17 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables,
              compl_result_code= result_code= HA_ADMIN_INVALID;
              break;
            }
-          bitmap_set_bit(tab->read_set, pos-1);
-        }
+          pos--;
+          enum enum_field_types type= tab->field[pos]->type();
+          if (type < MYSQL_TYPE_MEDIUM_BLOB ||
+              type > MYSQL_TYPE_BLOB)
+            bitmap_set_bit(tab->read_set, pos);
+          else
+            push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                               ER_NO_EIS_FOR_FIELD,
+                               ER_THD(thd, ER_NO_EIS_FOR_FIELD),
+                               column_name->str);
+        }
          tab->file->column_bitmaps_signal();
        }
_______________________________________________ 
commits mailing list
commits@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

References

  Thread Previous • Date Previous • • Thread Next