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@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits