maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09130
Re: [Commits] 59fcd7f: MDEV-9118 ANALYZE TABLE for Engine independent status fetchs blob/text columns without use
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.
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
--
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Follow ups
