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

Oleksandr Byelkin sanja at montyprogram.com
Tue Dec 22 14:03:49 EET 2015


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



More information about the commits mailing list