From 4886d14827c69877e8d089ae7c7f178a9a54ad7a Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Fri, 7 Dec 2018 02:12:22 +0530 Subject: MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY The problem here is EITS statistics does not calculate statistics for the partitions of the table. So a temporary solution would be to not read EITS statistics for partitioned tables. Also disabling reading of EITS for columns that participate in the partition list of a table. --- mysql-test/r/partition.result | 100 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/partition.test | 61 ++++++++++++++++++++++++++ sql/opt_range.cc | 19 ++++---- sql/partition_info.cc | 17 +++++++ sql/partition_info.h | 1 + sql/sql_statistics.cc | 45 +++++++++++++++++++ sql/sql_statistics.h | 1 + 7 files changed, 235 insertions(+), 9 deletions(-) diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index c6669176b3d..6732782c5f7 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2645,3 +2645,103 @@ Warnings: Note 1517 Duplicate partition name p2 DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +part_key int, +a int, +b int +) partition by list(part_key) ( +partition p0 values in (0), +partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3), +partition p4 values in (4) +); +insert into t2 +select mod(a,5), a/100, mod(a,5) from t1; +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +# +# Tests using stats provided by the storage engine +# +explain extended select * from t2 where part_key=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`part_key` = 1) +explain partitions select * from t2 where part_key=1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where +explain extended select * from t2 where part_key in (1,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`part_key` in (1,2)) +explain partitions select * from t2 where part_key in (1,2); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where +explain extended select * from t2 where b=5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 5) +explain partitions select * from t2 where b=5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where +explain extended select * from t2 partition(p0) where b=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` PARTITION (`p0`) where (`test`.`t2`.`b` = 1) +set @save_histogram_size=@@histogram_size; +set @@histogram_size=100; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=4; +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +# +# Tests using EITS +# +# filtered should be 100 +explain extended select * from t2 where part_key=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`part_key` = 1) +explain partitions select * from t2 where part_key=1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p1 ALL NULL NULL NULL NULL 200 Using where +# filtered should be 100 +explain extended select * from t2 where part_key in (1,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 400 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`part_key` in (1,2)) +explain partitions select * from t2 where part_key in (1,2); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p1,p2 ALL NULL NULL NULL NULL 400 Using where +explain extended select * from t2 where b=5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 19.80 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 5) +explain partitions select * from t2 where b=5; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1000 Using where +explain extended select * from t2 partition(p0) where b=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 19.80 Using where +Warnings: +Note 1003 select `test`.`t2`.`part_key` AS `part_key`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` PARTITION (`p0`) where (`test`.`t2`.`b` = 1) +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set @@histogram_size= @save_histogram_size; +drop table t0,t1,t2; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 1c8cd0375d6..b6a5db2db7c 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2897,3 +2897,64 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; +--echo # +--echo # MDEV-17032: Estimates are higher for partitions of a table with @@use_stat_tables= PREFERABLY +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + + +create table t2 ( + part_key int, + a int, + b int +) partition by list(part_key) ( + partition p0 values in (0), + partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3), + partition p4 values in (4) +); +insert into t2 +select mod(a,5), a/100, mod(a,5) from t1; + +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +--echo # +--echo # Tests using stats provided by the storage engine +--echo # +explain extended select * from t2 where part_key=1; +explain partitions select * from t2 where part_key=1; +explain extended select * from t2 where part_key in (1,2); +explain partitions select * from t2 where part_key in (1,2); +explain extended select * from t2 where b=5; +explain partitions select * from t2 where b=5; +explain extended select * from t2 partition(p0) where b=1; + + +set @save_histogram_size=@@histogram_size; +set @@histogram_size=100; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=4; +analyze table t2; +--echo # +--echo # Tests using EITS +--echo # +--echo # filtered should be 100 +explain extended select * from t2 where part_key=1; +explain partitions select * from t2 where part_key=1; +--echo # filtered should be 100 +explain extended select * from t2 where part_key in (1,2); +explain partitions select * from t2 where part_key in (1,2); +explain extended select * from t2 where b=5; +explain partitions select * from t2 where b=5; +explain extended select * from t2 partition(p0) where b=1; + +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set @@histogram_size= @save_histogram_size; +drop table t0,t1,t2; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index a3943cbe3ff..005ae92a665 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3322,14 +3322,17 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, { Field **field_ptr; TABLE *table= param->table; + partition_info *part_info= NULL; + #ifdef WITH_PARTITION_STORAGE_ENGINE + part_info= table->part_info; + #endif uint parts= 0; for (field_ptr= table->field; *field_ptr; field_ptr++) { - Column_statistics* col_stats= (*field_ptr)->read_stats; - if (bitmap_is_set(used_fields, (*field_ptr)->field_index) - && col_stats && !col_stats->no_stat_values_provided() - && !((*field_ptr)->type() == MYSQL_TYPE_GEOMETRY)) + Field *field= *field_ptr; + if (bitmap_is_set(used_fields, field->field_index) && + is_eits_usable(field)) parts++; } @@ -3347,12 +3350,10 @@ bool create_key_parts_for_pseudo_indexes(RANGE_OPT_PARAM *param, uint max_key_len= 0; for (field_ptr= table->field; *field_ptr; field_ptr++) { - if (bitmap_is_set(used_fields, (*field_ptr)->field_index)) + Field *field= *field_ptr; + if (bitmap_is_set(used_fields, field->field_index)) { - Field *field= *field_ptr; - Column_statistics* col_stats= field->read_stats; - if (field->type() == MYSQL_TYPE_GEOMETRY || - !col_stats || col_stats->no_stat_values_provided()) + if (!is_eits_usable(field)) continue; uint16 store_length; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index 52bda560c1c..9d7d0d92686 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -3164,6 +3164,23 @@ void partition_info::print_debug(const char *str, uint *value) DBUG_PRINT("info", ("parser: %s", str)); DBUG_VOID_RETURN; } + +bool partition_info::field_in_partition_expr(Field *field) const +{ + uint i; + for (i= 0; i < num_part_fields; i++) + { + if (field->eq(part_field_array[i])) + return TRUE; + } + for (i= 0; i < num_subpart_fields; i++) + { + if (field->eq(subpart_field_array[i])) + return TRUE; + } + return FALSE; +} + #else /* WITH_PARTITION_STORAGE_ENGINE */ /* For builds without partitioning we need to define these functions diff --git a/sql/partition_info.h b/sql/partition_info.h index f250c5496bf..10b8954ace7 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -384,6 +384,7 @@ private: bool is_full_part_expr_in_fields(List &fields); public: bool has_unique_name(partition_element *element); + bool field_in_partition_expr(Field *field) const; }; uint32 get_next_partition_id_range(struct st_partition_iter* part_iter); diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index cb75a5c2176..0c359a29431 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -30,6 +30,7 @@ #include "opt_range.h" #include "my_atomic.h" #include "sql_show.h" +#include "sql_partition.h" /* The system variable 'use_stat_tables' can take one of the @@ -3589,6 +3590,22 @@ void set_statistics_for_table(THD *thd, TABLE *table) (use_stat_table_mode <= COMPLEMENTARY || !table->stats_is_read || read_stats->cardinality_is_null) ? table->file->stats.records : read_stats->cardinality; + + /* + For partitioned table, EITS statistics is based on data from all partitions. + + On the other hand, Partition Pruning figures which partitions will be + accessed and then computes the estimate of rows in used_partitions. + + Use the estimate from Partition Pruning as it is typically more precise. + Ideally, EITS should provide per-partition statistics but this is not + implemented currently. + */ + #ifdef WITH_PARTITION_STORAGE_ENGINE + if (table->part_info) + table->used_stat_records= table->file->stats.records; + #endif + KEY *key_info, *key_info_end; for (key_info= table->key_info, key_info_end= key_info+table->s->keys; key_info < key_info_end; key_info++) @@ -3904,3 +3921,31 @@ bool is_stat_table(const char *db, const char *table) } return false; } + +/* + Check wheter we can use EITS statistics for a field or not + + TRUE : Use EITS for the columns + FALSE: Otherwise +*/ + +bool is_eits_usable(Field *field) +{ + partition_info *part_info= NULL; + #ifdef WITH_PARTITION_STORAGE_ENGINE + part_info= field->table->part_info; + #endif + /* + (1): checks if we have EITS statistics for a particular column + (2): Don't use EITS for GEOMETRY columns + (3): Disabling reading EITS statistics for columns involved in the + partition list of a table. We assume the selecticivity for + such columns would be handled during partition pruning. + */ + Column_statistics* col_stats= field->read_stats; + if (col_stats && !col_stats->no_stat_values_provided() && //(1) + field->type() != MYSQL_TYPE_GEOMETRY && //(2) + (!part_info || !part_info->field_in_partition_expr(field))) //(3) + return TRUE; + return FALSE; +} diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f28d56e4a69..a891bef3164 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -109,6 +109,7 @@ double get_column_range_cardinality(Field *field, key_range *max_endp, uint range_flag); bool is_stat_table(const char *db, const char *table); +bool is_eits_usable(Field* field); class Histogram { -- cgit v1.2.1 From 8aef7f2bb925b7496d0157165e33b12eef2812a3 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Mon, 10 Dec 2018 00:34:41 +0530 Subject: MDEV-17778: Alter table leads to a truncation warning with ANALYZE command Alter statement changed the THD structure by setting the value to FIELD_CHECK_WARN and then not resetting it back. This led ANALYZE to throw a warning which previously it didn't. --- mysql-test/main/alter_table.result | 21 +++++++++++++++++++++ mysql-test/main/alter_table.test | 19 +++++++++++++++++++ mysql-test/main/selectivity_innodb.result | 1 - mysql-test/main/statistics.result | 10 ---------- mysql-test/suite/gcol/r/gcol_keys_innodb.result | 2 -- sql/sql_table.cc | 12 +++++++----- 6 files changed, 47 insertions(+), 18 deletions(-) diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 49c42479516..8e54e882979 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -2469,3 +2469,24 @@ DROP TABLE t1; # # End of 10.2 tests # +# +# MDEV-17778: Alter table leads to a truncation warning with ANALYZE command +# +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +create table t1 (a int)engine=InnoDB; +insert into t1 values (1),(1),(2),(3); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +alter table t1 change a b int; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index dfa8e2e148b..829f4013cb3 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -2021,3 +2021,22 @@ DROP TABLE t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # MDEV-17778: Alter table leads to a truncation warning with ANALYZE command +--echo # + +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; + +create table t1 (a int)engine=InnoDB; +insert into t1 values (1),(1),(2),(3); + +analyze table t1; +alter table t1 change a b int; +analyze table t1; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 921bd20fc69..93917065722 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1150,7 +1150,6 @@ alter table t1 change column a a int; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t1 analyze status OK flush table t1; explain extended select * from t1 where a between 5 and 7; diff --git a/mysql-test/main/statistics.result b/mysql-test/main/statistics.result index 574eb5f4727..34a17cf049c 100644 --- a/mysql-test/main/statistics.result +++ b/mysql-test/main/statistics.result @@ -1086,9 +1086,6 @@ test t2 idx4 3 1.1304 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency @@ -1149,11 +1146,6 @@ test t2 idx4 4 1.0000 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected -test.t2 analyze Note Data truncated for column 'avg_length' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency @@ -1179,8 +1171,6 @@ test t2 idx3 1 8.5000 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 -test.t2 analyze Note Data truncated for column 'avg_frequency' at row 1 test.t2 analyze status OK SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result index 577b3255620..de88b745292 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result @@ -696,8 +696,6 @@ Warning 1264 Out of range value for column 'b' at row 1 SELECT * FROM t WHERE c = '0'; a b c 1 127 0 -Warnings: -Warning 1264 Out of range value for column 'b' at row 1 DROP TABLE t; # # Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD() diff --git a/sql/sql_table.cc b/sql/sql_table.cc index b9fc431feb1..ba91b208d03 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7401,11 +7401,6 @@ static bool mysql_inplace_alter_table(THD *thd, bool reopen_tables= false; bool res; - /* - Set the truncated column values of thd as warning - for alter table. - */ - thd->count_cuted_fields = CHECK_FIELD_WARN; DBUG_ENTER("mysql_inplace_alter_table"); /* @@ -9694,9 +9689,16 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db, if (use_inplace) { table->s->frm_image= &frm; + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; + /* + Set the truncated column values of thd as warning + for alter table. + */ + thd->count_cuted_fields = CHECK_FIELD_WARN; int res= mysql_inplace_alter_table(thd, table_list, table, altered_table, &ha_alter_info, inplace_supported, &target_mdl_request, &alter_ctx); + thd->count_cuted_fields= save_count_cuted_fields; my_free(const_cast(frm.str)); if (res) -- cgit v1.2.1 From ac31ff6275cfb5de74c0069a53e5575dac317225 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Mon, 10 Dec 2018 23:31:54 +0100 Subject: MDEV-15760 - don't build mariabackup with -DPLUGIN_INNOBASE=DYNAMIC --- storage/innobase/CMakeLists.txt | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) diff --git a/storage/innobase/CMakeLists.txt b/storage/innobase/CMakeLists.txt index e0bc7006770..0c56008a57a 100644 --- a/storage/innobase/CMakeLists.txt +++ b/storage/innobase/CMakeLists.txt @@ -196,8 +196,6 @@ IF(MSVC) TARGET_COMPILE_OPTIONS(innobase PRIVATE "/wd4065") ENDIF() -ADD_SUBDIRECTORY(${CMAKE_SOURCE_DIR}/extra/mariabackup ${CMAKE_BINARY_DIR}/extra/mariabackup) - -IF(TARGET innobase) - ADD_DEPENDENCIES(innobase GenError) +IF(NOT (PLUGIN_INNOBASE STREQUAL DYNAMIC)) + ADD_SUBDIRECTORY(${CMAKE_SOURCE_DIR}/extra/mariabackup ${CMAKE_BINARY_DIR}/extra/mariabackup) ENDIF() -- cgit v1.2.1 From d956709b4be67f96a869d0854c75d10cd502172b Mon Sep 17 00:00:00 2001 From: Eugene Kosov Date: Tue, 11 Dec 2018 22:03:44 +0300 Subject: MDEV-17833 ALTER TABLE is not enforcing prefix index size limit ha_innobase::prepare_inplace_alter_table(): check max column length for every index in a table, not just added in this particular ALTER TABLE with ADD INDEX ones. --- mysql-test/suite/innodb/r/innodb-index.result | 33 +++++++++++++++++++++++++++ mysql-test/suite/innodb/t/innodb-index.test | 30 ++++++++++++++++++++++++ storage/innobase/handler/handler0alter.cc | 5 ++-- storage/xtradb/handler/handler0alter.cc | 5 ++-- 4 files changed, 67 insertions(+), 6 deletions(-) diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index 3d5a0f840c1..681b07249b6 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -1180,3 +1180,36 @@ t2c CREATE TABLE `t2c` ( KEY `t2a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1,t2,t2c,t2i; +SET @save_format = @@GLOBAL.innodb_file_format; +SET @save_prefix = @@GLOBAL.innodb_large_prefix; +SET GLOBAL innodb_file_format=barracuda; +SET GLOBAL innodb_large_prefix=ON; +CREATE TABLE t1 (c VARCHAR(1024), +c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL, +c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL, +c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL, +ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL, +cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL, +d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL, +d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL, +d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL, +d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL, +dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL, +UNIQUE KEY(c)) +ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''), +(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy; +ERROR HY000: Index column size too large. The maximum column size is 767 bytes. +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +SET GLOBAL innodb_file_format=@save_format; +SET GLOBAL innodb_large_prefix=@save_prefix; diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 8598647de66..d28930de815 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -563,3 +563,33 @@ DROP TABLE t1,t2,t2c,t2i; eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; --enable_query_log + +SET @save_format = @@GLOBAL.innodb_file_format; +SET @save_prefix = @@GLOBAL.innodb_large_prefix; +SET GLOBAL innodb_file_format=barracuda; +SET GLOBAL innodb_large_prefix=ON; +CREATE TABLE t1 (c VARCHAR(1024), +c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL, +c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL, +c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL, +ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL, +cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL, +d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL, +d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL, +d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL, +d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL, +dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL, +UNIQUE KEY(c)) +ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''), +(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''); +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace; +--error ER_INDEX_COLUMN_TOO_LONG +ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +DROP TABLE t1; +SET GLOBAL innodb_file_format=@save_format; +SET GLOBAL innodb_large_prefix=@save_prefix; diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 367601e1b33..6d9255a628e 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -3582,9 +3582,8 @@ check_if_ok_to_rename: /* Check each index's column length to make sure they do not exceed limit */ - for (ulint i = 0; i < ha_alter_info->index_add_count; i++) { - const KEY* key = &ha_alter_info->key_info_buffer[ - ha_alter_info->index_add_buffer[i]]; + for (ulint i = 0; i < ha_alter_info->key_count; i++) { + const KEY* key = &ha_alter_info->key_info_buffer[i]; if (key->flags & HA_FULLTEXT) { /* The column length does not matter for diff --git a/storage/xtradb/handler/handler0alter.cc b/storage/xtradb/handler/handler0alter.cc index 0b7588918bc..9508adbbd12 100644 --- a/storage/xtradb/handler/handler0alter.cc +++ b/storage/xtradb/handler/handler0alter.cc @@ -3596,9 +3596,8 @@ check_if_ok_to_rename: /* Check each index's column length to make sure they do not exceed limit */ - for (ulint i = 0; i < ha_alter_info->index_add_count; i++) { - const KEY* key = &ha_alter_info->key_info_buffer[ - ha_alter_info->index_add_buffer[i]]; + for (ulint i = 0; i < ha_alter_info->key_count; i++) { + const KEY* key = &ha_alter_info->key_info_buffer[i]; if (key->flags & HA_FULLTEXT) { /* The column length does not matter for -- cgit v1.2.1 From 56d3a0e73be6213765274d6efcc31d9c9a9cbcd1 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 7 Dec 2018 00:33:51 +0100 Subject: MDEV-17967 Add a solution of the 8 queens problem to the regression test for CTE --- mysql-test/main/cte_recursive.result | 38 ++++++++++++++++++++++++++++++++++++ mysql-test/main/cte_recursive.test | 34 ++++++++++++++++++++++++++++++++ 2 files changed, 72 insertions(+) diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 0417cf0a7f0..f2ae9929145 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1608,6 +1608,44 @@ id name dob father mother 8 Grandpa Ben 1940-10-21 NULL NULL 6 Grandgrandma Martha 1923-05-17 NULL NULL drop table my_ancestors; +WITH RECURSIVE +positions(i) AS ( +VALUES(0) +UNION SELECT ALL +i+1 FROM positions WHERE i < 4*4-1 +), +solutions(board, n_queens) AS ( +SELECT REPEAT('-', 4*4), 0 +FROM positions +UNION +SELECT +concat(substr(board, 1, i),'*',substr(board, i+2)),n_queens + 1 AS n_queens +FROM positions AS ps, solutions +WHERE n_queens < 4 +AND substr(board,1,i) != '*' + AND NOT EXISTS ( +SELECT 1 FROM positions WHERE +substr(board,i+1,1) = '*' AND +( +i % 4 = ps.i % 4 OR +i div 4 = ps.i div 4 OR +i div 4 + (i % 4) = ps.i div 4 + (ps.i % 4) OR +i div 4 - (i % 4) = ps.i div 4 - (ps.i % 4) +) +) +) +SELECT regexp_replace(board,concat('(',REPEAT('.', 4),')'),'\\1\n') n_queens FROM solutions WHERE n_queens = 4; +n_queens +-*-- +---* +*--- +--*- + +--*- +*--- +---* +-*-- + # # MDEV-10883: execution of prepared statement from SELECT # with recursive CTE that renames columns diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 0ed9c2d56e3..483e1ea8c7a 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -1200,6 +1200,40 @@ select * from my_ancestors; drop table my_ancestors; +# +# MDEV-17967 Add a solution of the 8 queens problem to the regression test for CTE +# +# adapted to MariaDB from https://rosettacode.org/wiki/N-queens_problem#SQL +# +let $N=4; # 8 takes too long for a test +eval WITH RECURSIVE + positions(i) AS ( + VALUES(0) + UNION SELECT ALL + i+1 FROM positions WHERE i < $N*$N-1 + ), + solutions(board, n_queens) AS ( + SELECT REPEAT('-', $N*$N), 0 + FROM positions + UNION + SELECT + concat(substr(board, 1, i),'*',substr(board, i+2)),n_queens + 1 AS n_queens + FROM positions AS ps, solutions + WHERE n_queens < $N + AND substr(board,1,i) != '*' + AND NOT EXISTS ( + SELECT 1 FROM positions WHERE + substr(board,i+1,1) = '*' AND + ( + i % $N = ps.i % $N OR + i div $N = ps.i div $N OR + i div $N + (i % $N) = ps.i div $N + (ps.i % $N) OR + i div $N - (i % $N) = ps.i div $N - (ps.i % $N) + ) + ) + ) +SELECT regexp_replace(board,concat('(',REPEAT('.', $N),')'),'\\\\1\\n') n_queens FROM solutions WHERE n_queens = $N; + --echo # --echo # MDEV-10883: execution of prepared statement from SELECT --echo # with recursive CTE that renames columns -- cgit v1.2.1 From dce2cc1c6a82d1ed9f4db9c39bc9cb1d3815a019 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 12 Dec 2018 11:35:21 +0100 Subject: fix handler test failures on s390x keyinfo->name is a LEX_CSTRING also, fix the location comment (that applied to ext_key_part_map) --- sql/sql_handler.cc | 2 +- sql/structs.h | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc index 69ea04a170c..817447fe917 100644 --- a/sql/sql_handler.cc +++ b/sql/sql_handler.cc @@ -670,7 +670,7 @@ mysql_ha_fix_cond_and_key(SQL_HANDLER *handler, (HA_READ_NEXT | HA_READ_PREV | HA_READ_RANGE)) == 0)) { my_error(ER_KEY_DOESNT_SUPPORT, MYF(0), - table->file->index_type(handler->keyno), keyinfo->name); + table->file->index_type(handler->keyno), keyinfo->name.str); return 1; } diff --git a/sql/structs.h b/sql/structs.h index be9abbf4613..5cc392c33b7 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -109,8 +109,8 @@ typedef struct st_key { pk2 is explicitly present in idx1, it is not in the extension, so ext_key_part_map.is_set(1) == false */ - LEX_CSTRING name; key_part_map ext_key_part_map; + LEX_CSTRING name; uint block_size; enum ha_key_alg algorithm; /* -- cgit v1.2.1 From e0aebf5cf1c705739a94fe2fecf0b0ed7eb4ba3a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 12 Dec 2018 13:10:52 +0200 Subject: MDEV-17815 Assertion failed in btr_node_ptr_max_size for CHAR(0) btr_node_ptr_max_size(): Treat CHAR(0) from SQL as a special case. The InnoDB internal SQL parser maps the type "CHAR" to DATA_VARCHAR, but MariaDB does allow CHAR(0) with an empty value, and does enforce the length limitation. --- mysql-test/suite/innodb/r/data_types.result | 13 ++++++++++++- mysql-test/suite/innodb/t/data_types.test | 13 ++++++++++++- storage/innobase/btr/btr0cur.cc | 19 +++++++++++++++++++ 3 files changed, 43 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/innodb/r/data_types.result b/mysql-test/suite/innodb/r/data_types.result index 446d37527e5..4e919e37cee 100644 --- a/mysql-test/suite/innodb/r/data_types.result +++ b/mysql-test/suite/innodb/r/data_types.result @@ -75,10 +75,13 @@ t1_VARCHAR_10_BINARY VARCHAR(10) BINARY, t1_VARCHAR_500 VARCHAR(500), t1_VARCHAR_500_BINARY VARCHAR(500) BINARY, t1_YEAR_2 YEAR(2), -t1_YEAR_4 YEAR(4) +t1_YEAR_4 YEAR(4), +t1_CHAR_0 CHAR(0), +t1_MYSQL_0 CHAR(0) CHARACTER SET utf8 ) ENGINE=InnoDB; Warnings: Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +INSERT INTO t1 () VALUES (); SELECT name, CASE mtype @@ -109,6 +112,7 @@ t1_BINARY_100 DATA_FIXBINARY t1_BIT_2 DATA_FIXBINARY UNSIGNED t1_BIT_20 DATA_FIXBINARY UNSIGNED t1_BLOB DATA_BLOB +t1_CHAR_0 DATA_CHAR t1_CHAR_100 DATA_CHAR t1_CHAR_100_BINARY DATA_MYSQL t1_DATE DATA_INT @@ -131,6 +135,7 @@ t1_MEDIUMBLOB DATA_BLOB t1_MEDIUMINT DATA_INT t1_MEDIUMINT_UNSIGNED DATA_INT UNSIGNED t1_MEDIUMTEXT DATA_BLOB +t1_MYSQL_0 DATA_MYSQL t1_SET DATA_INT UNSIGNED t1_SET_9 DATA_INT UNSIGNED t1_SET_BINARY DATA_INT UNSIGNED @@ -153,3 +158,9 @@ t1_VARCHAR_500_BINARY DATA_VARMYSQL t1_YEAR_2 DATA_INT UNSIGNED t1_YEAR_4 DATA_INT UNSIGNED DROP TABLE t1; +# +# MDEV-17815 Assertion failed in btr_node_ptr_max_size for CHAR(0) +# +CREATE TABLE t1 (c CHAR(0), KEY(c)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (''); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/data_types.test b/mysql-test/suite/innodb/t/data_types.test index 0978146361c..c8e80c9db27 100644 --- a/mysql-test/suite/innodb/t/data_types.test +++ b/mysql-test/suite/innodb/t/data_types.test @@ -88,9 +88,13 @@ CREATE TABLE t1 t1_VARCHAR_500 VARCHAR(500), t1_VARCHAR_500_BINARY VARCHAR(500) BINARY, t1_YEAR_2 YEAR(2), - t1_YEAR_4 YEAR(4) + t1_YEAR_4 YEAR(4), + t1_CHAR_0 CHAR(0), + t1_MYSQL_0 CHAR(0) CHARACTER SET utf8 ) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (); + SELECT name, CASE mtype @@ -116,3 +120,10 @@ WHERE name LIKE "t1\_%" ORDER BY name; DROP TABLE t1; + +--echo # +--echo # MDEV-17815 Assertion failed in btr_node_ptr_max_size for CHAR(0) +--echo # +CREATE TABLE t1 (c CHAR(0), KEY(c)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (''); +DROP TABLE t1; diff --git a/storage/innobase/btr/btr0cur.cc b/storage/innobase/btr/btr0cur.cc index 373328939c2..3f26803d14b 100644 --- a/storage/innobase/btr/btr0cur.cc +++ b/storage/innobase/btr/btr0cur.cc @@ -753,6 +753,25 @@ static ulint btr_node_ptr_max_size(const dict_index_t* index) field_max_size = dict_col_get_max_size(col); if (UNIV_UNLIKELY(!field_max_size)) { + switch (col->mtype) { + case DATA_CHAR: + case DATA_MYSQL: + /* CHAR(0) is a possible data type. + The InnoDB internal SQL parser maps + CHAR to DATA_VARCHAR, so DATA_CHAR (or + DATA_MYSQL) is only coming from the + MariaDB SQL layer. */ + if (comp) { + /* Add a length byte, because + fixed-length empty field are + encoded as variable-length. + For ROW_FORMAT=REDUNDANT, + these bytes were added to + rec_max_size before this loop. */ + rec_max_size++; + } + continue; + } /* SYS_FOREIGN.ID is defined as CHAR in the InnoDB internal SQL parser, which translates into the incorrect VARCHAR(0). InnoDB does -- cgit v1.2.1 From 91173f986373e4f5f134a2351a80d8763e5157e8 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 12 Dec 2018 13:30:40 +0200 Subject: fts_is_charset_cjk(): Avoid referencing global symbols References to global symbols prevent InnoDB from being built as a dynamic plugin on Windows. Refer to CHARSET_INFO::number, because that is what InnoDB is already persistently storing in its data dictionary. --- storage/innobase/include/fts0types.ic | 26 ++++++++++++++------------ 1 file changed, 14 insertions(+), 12 deletions(-) diff --git a/storage/innobase/include/fts0types.ic b/storage/innobase/include/fts0types.ic index 18bc87213fc..6c5542309bb 100644 --- a/storage/innobase/include/fts0types.ic +++ b/storage/innobase/include/fts0types.ic @@ -108,19 +108,21 @@ innobase_strnxfrm( @param[in] cs charset @retval true if the charset is cjk @retval false if not. */ -UNIV_INLINE -bool -fts_is_charset_cjk( - const CHARSET_INFO* cs) +inline bool fts_is_charset_cjk(const CHARSET_INFO* cs) { - return cs == &my_charset_gb2312_chinese_ci - || cs == &my_charset_gbk_chinese_ci - || cs == &my_charset_big5_chinese_ci - || cs == &my_charset_ujis_japanese_ci - || cs == &my_charset_sjis_japanese_ci - || cs == &my_charset_cp932_japanese_ci - || cs == &my_charset_eucjpms_japanese_ci - || cs == &my_charset_euckr_korean_ci; + switch (cs->number) { + case 24: /* my_charset_gb2312_chinese_ci */ + case 28: /* my_charset_gbk_chinese_ci */ + case 1: /* my_charset_big5_chinese_ci */ + case 12: /* my_charset_ujis_japanese_ci */ + case 13: /* my_charset_sjis_japanese_ci */ + case 95: /* my_charset_cp932_japanese_ci */ + case 97: /* my_charset_eucjpms_japanese_ci */ + case 19: /* my_charset_euckr_korean_ci */ + return true; + default: + return false; + } } /** Select the FTS auxiliary index for the given character by range. -- cgit v1.2.1