March 2019 - developers - lists.mariadb.org

Re: [Maria-developers] f6166101e0f: MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map
by Sergei Golubchik 20 Mar '19

20 Mar '19

Hi, Sachin! On Mar 20, Sachin Setiya wrote: > revision-id: f6166101e0f (mariadb-10.4.3-86-gf6166101e0f) > parent(s): 8076c594ce2 > author: sachinsetia1001(a)gmail.com <sachin.setiya(a)mariadb.com> > committer: Sachin <sachin.setiya(a)mariadb.com> > timestamp: 2019-03-19 16:43:43 +0530 > message: > > MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map > > We do not support long unique index in partition. Why not? I would think that the assert is wrong, there is no reason why ha_partition::index_read_idx_map() would always hit just one partition. ha_partition::index_read_map() doesn't have this assert. And if you check the history of the assert you'll find that the comment originally was /* How can it be more than one partition with the current use? */ So, apparently now you've introduced a new "use" and there can be more than one partition, and removing the assert looks justified. > +CREATE TABLE t1 (a int , unique(a) using hash) PARTITION BY HASH (a) PARTITIONS 2; > +ERROR HY000: Long unique index is not supported in partition Also - for the future - there are no "long unique" indexes from the user point of view. This is only an internal name. The SQL syntax does use "long unique" concept, documentation doesn't, error messages don't. That is, users simply won't understand what you mean here. If we'll need an error here, I'd suggest, for example, HASH UNIQUE indexes cannot be used in partitioned tables. but it's not strictly correct, if some engine would implement hash indexes natively. The more precise message could be HASH UNIQUE indexes cannot be used in par titioned MyISAM tables. or Partitioned MyISAM tables do not support HASH UNIQUE indexes. (where "MyISAM" is a parameter, of course). But, again, I am not yet convinced that we need this limitation at all. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] 500d909: MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly
by Sergey Petrunia 18 Mar '19

18 Mar '19

> revision-id: 500d909a795022255fb6f6c44cd310636afc0b5a (mariadb-10.3.6-226-g500d909) > parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a > author: Varun Gupta > committer: Varun Gupta > timestamp: 2019-03-11 20:20:35 +0530 > message: > > MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly > > Keys with multi-key parts were not being printed correctly, only the first key part > was getting printed. > Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts. > ... > diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result > index 4c3e2b3..a4c2b86 100644 > --- a/mysql-test/main/opt_trace.result > +++ b/mysql-test/main/opt_trace.result > @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { > set @@optimizer_switch= @save_optimizer_switch; > drop table t1,t2; > set optimizer_trace='enabled=off'; > +# > +# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly > +# > +create table t0(a int); > +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > +create table one_k (a int); > +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; > +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); > +insert into t1 select a,a,a,a from one_k; > +set optimizer_trace=1; > +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; ... > +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; This produces a lot of irrelevant output. Can you use something like select JSON_DETAILED(JSON_EXTRACT(a, '$**.range_analysis')) from optimizer_trace to select on the part of the trace that we need? > + "analyzing_range_alternatives": { > + "range_scan_alternatives": [ > + { > + "index": "a", > + "ranges": ["10 < a AND NULL < b < 10"], This is not a valid range. If one forces this quick select to be picked for the query plan, they can look into .trace and see the ranges: explain format=json select * from t1 force index (a) where a > 10 and b < 10; T@19 : | | | | | | | | | | >print_quick quick range select, key a, length: 5 10 < X other_keys: 0x0: T@19 : | | | | | | | | | | <print_quick (you can also try with a >=10 to see a different kind of range being produced). (One could argue that what is printed is a representation of the SEL_ARG graph, but in that case it is extremely confusing. I think, we should start with showing ranges, not SEL_ARG graphs). I think, we should not try to intrepret the SEL_ARG graph ourselves but rather use sel_arg_range_seq_init / sel_arg_range_seq_next to produce ranges that can be printed. Any objections to this? > + "rowid_ordered": false, > + "using_mrr": false, > + "index_only": false, > + "rows": 989, > + "cost": 1272.8, > + "chosen": false, > + "cause": "cost" > + }, > + { > + "index": "c", > + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], > + "rowid_ordered": true, > + "using_mrr": false, > + "index_only": false, > + "rows": 1, > + "cost": 2.3783, > + "chosen": true > + } > + ], BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] ASAN segfaults on vcol case
by Aleksey Midenkov 18 Mar '19

18 Mar '19

This case segfaults in ASAN code: delimiter ~~ create or replace procedure t1() begin create or replace table t1 ( b tinytext, v text as (b) virtual ); end~~ delimiter ; call t1; Both in 10.3 and 10.4 in different places. Does someone know about such ASAN faults? -- All the best, Aleksey Midenkov @midenok

2 2

Re: [Maria-developers] 21a15ea673c: MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase
by Sergei Golubchik 18 Mar '19

18 Mar '19

Hi, Oleksandr! On Mar 18, Oleksandr Byelkin wrote: > revision-id: 21a15ea673c (mariadb-10.2.22-72-g21a15ea673c) > parent(s): bb8c82c66ab > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2019-03-14 15:12:47 +0100 > message: > > MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase > > Set table in row ID position mode before using this function. > > diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result > index 5890fd24f5f..294ebfcebdf 100644 > --- a/mysql-test/r/multi_update_innodb.result > +++ b/mysql-test/r/multi_update_innodb.result > @@ -151,3 +151,43 @@ create table t2 like t1; > insert into t2 select * from t1; > delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; > drop table t1,t2; > +# > +# MDEV-16240: Assertion `0' failed in > +# row_sel_convert_mysql_key_to_innobase > +# > +SET @save_sql_mode=@@sql_mode; > +set sql_mode=''; > +CREATE TABLE `t3` ( > +`f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), > +`f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), > +`f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', > +`pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', > +`f4` datetime DEFAULT current_timestamp(), > +PRIMARY KEY (`pk`), > +UNIQUE KEY `f2k` (`f2`), > +KEY `f4k` (`f4`) > +) ENGINE=InnoDB; > +INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); > +CREATE VIEW `v1` AS > +SELECT `t3`.`pk` AS `pk`, > +`t3`.`f3` AS `f3`, > +`t3`.`f4` AS `f4`, > +`t3`.`f2` AS `f2`, > +`t3`.`f1` AS `f1` > +FROM `t3`; > +CREATE TABLE `t4` ( > +`f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), > +`f3` timestamp NULL DEFAULT NULL, > +`f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), > +`pk` int(11) NOT NULL, > +`f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), > +PRIMARY KEY (`pk`) > +) ENGINE=InnoDB; > +INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); > +UPDATE `v1` t1, `t4` t2 > +SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; > +ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k' actually, when I wrote "please, rename all columns in the test to have unique names" I meant _all_ columns, that is, in the t3 columnd could've been f1-f5, in v1 they could be called f6-f10, in t4 - f11-f15. but don't bother renaming them now, apparently the most important name was 'f2k' and it's unique now, so it's sufficiently clear, thanks. Still, for the future, it's generally helpful to have all names different to be able to understand what the error message refers to. > +DROP VIEW v1; > +DROP TABLE t3,t4; > +SET @@sql_mode=@save_sql_mode; > +# End of 10.2 tests > diff --git a/sql/handler.cc b/sql/handler.cc > index ae63640ae5c..d307433cf5d 100644 > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -2648,8 +2648,7 @@ int handler::ha_rnd_pos(uchar *buf, uchar *pos) > DBUG_ENTER("handler::ha_rnd_pos"); > DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || > m_lock_type != F_UNLCK); > - /* TODO: Find out how to solve ha_rnd_pos when finding duplicate update. */ > - /* DBUG_ASSERT(inited == RND); */ > + DBUG_ASSERT(inited == RND); > > TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_FETCH_ROW, MAX_KEY, 0, > { result= rnd_pos(buf, pos); }) > @@ -3307,6 +3306,10 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, > ulonglong nr; > int error; > MY_BITMAP *old_read_set; > + bool rnd_inited= (inited == RND); > + when is that true? > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 8a9dd083911..cc1649b3c92 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -5855,12 +5855,17 @@ Ordered_key::cmp_keys_by_row_data_and_rownum(Ordered_key *key, > } > > > -void Ordered_key::sort_keys() > +bool Ordered_key::sort_keys() > { > + /* Prepare table for random positioning (importent for innodb) */ 1) typo: "important" but 2) I don't think you need a comment here at all now. There's an assert in ha_rnd_pos(), it's rather more clear and more reliable anyway. > + if (tbl->file->ha_rnd_init(0)) > + return TRUE; > my_qsort2(key_buff, (size_t) key_buff_elements, sizeof(rownum_t), > (qsort2_cmp) &cmp_keys_by_row_data_and_rownum, (void*) this); > /* Invalidate the current row position. */ > cur_key_idx= HA_POS_ERROR; > + tbl->file->ha_rnd_end(); > + return FALSE; > } > > > diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc > index 307473cecbd..5593580dbca 100644 > --- a/sql/sql_insert.cc > +++ b/sql/sql_insert.cc > @@ -3177,6 +3193,10 @@ bool Delayed_insert::handle_inserts(void) > max_rows= ULONG_MAX; // Do as much as possible > } > > + /* Prepare table for random positioning (importent for innodb) */ same > + if (table->file->ha_rnd_init(0)) > + goto err; what's that for? duplicate key error message? > + > /* > We can't use row caching when using the binary log because if > we get a crash, then binary log will contain rows that are not yet > @@ -3649,7 +3671,12 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) > > thd->cuted_fields=0; > if (info.ignore || info.handle_duplicates != DUP_ERROR) > + { > table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); > + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && > + table->file->ha_rnd_init(0)) > + DBUG_RETURN(1); here and in other places, you don't do my_error(). Is there some catch-all my_error somewhere up the stack or it'll be the usual assert in Diagnostic_area? > + } > if (info.handle_duplicates == DUP_REPLACE && > (!table->triggers || !table->triggers->has_delete_triggers())) > table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); > @@ -3929,6 +3959,11 @@ void select_insert::abort_result_set() { > if (thd->locked_tables_mode <= LTM_LOCK_TABLES) > table->file->ha_end_bulk_insert(); > > + if (table->file->inited) why not "if (table->file->ha_table_flags() & HA_DUPLICATE_POS)" ? > + table->file->ha_rnd_end(); > + table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); > + table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); > + > /* > If at least one row has been inserted/modified and will stay in > the table (the table doesn't have transactions) we must write to > diff --git a/sql/sql_load.cc b/sql/sql_load.cc > index 8c2f17dac3f..3283b962f65 100644 > --- a/sql/sql_load.cc > +++ b/sql/sql_load.cc > @@ -638,6 +638,13 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, > > thd->abort_on_warning= !ignore && thd->is_strict_mode(); > > + /* Prepare table for random positioning (importent for innodb) */ same > + if ((table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) && > + table_list->table->file->ha_rnd_init(0)) > + { > + error= 1; > + goto err; > + } > thd_progress_init(thd, 2); > if (table_list->table->validate_default_values_of_unset_fields(thd)) > { > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 91a6445c870..4c401ea9011 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -20317,6 +20317,12 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), > table->file->print_error(error,MYF(0)); /* purecov: inspected */ > DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ > } > + /* Prepare table for random positioning */ > + bool rnd_inited= (table->file->inited == handler::RND); when is that true? and when false? > + if (!rnd_inited && > + ((error= table->file->ha_index_end()) || > + (error= table->file->ha_rnd_init(0)))) > + DBUG_RETURN(NESTED_LOOP_ERROR); > if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref)) > { > table->file->print_error(error,MYF(0)); /* purecov: inspected */ > diff --git a/sql/sql_update.cc b/sql/sql_update.cc > index 11ffa684216..6d4c11d494a 100644 > --- a/sql/sql_update.cc > +++ b/sql/sql_update.cc > @@ -211,6 +211,10 @@ static void prepare_record_for_error_message(int error, TABLE *table) > bitmap_union(table->read_set, &unique_map); > /* Tell the engine about the new set. */ > table->file->column_bitmaps_signal(); > + /* Prepare table for random positioning (importent for innodb) */ same > + if (table->file->ha_index_or_rnd_end() || > + table->file->ha_rnd_init(0)) > + DBUG_VOID_RETURN; > /* Read record that is identified by table->file->ref. */ > (void) table->file->ha_rnd_pos(table->record[1], table->file->ref); > /* Copy the newly read columns into the new record. */ > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 34098adf32b: Get rid of rea_create_table()
by Sergei Golubchik 14 Mar '19

14 Mar '19

Hi, Sergey! On Mar 13, Sergey Vojtovich wrote: > revision-id: 34098adf32b (mariadb-10.4.1-138-g34098adf32b) > parent(s): d953dc02d9a > author: Sergey Vojtovich <svoj(a)mariadb.org> > committer: Sergey Vojtovich <svoj(a)mariadb.org> > timestamp: 2019-02-07 20:04:51 +0400 > message: > > Get rid of rea_create_table() > > Moved rea_create_table() to the sole caller. > > Visible difference: regular ALTER TABLE doesn't create .frm for temporary > and discoverable tables anymore. > > Also ha_create_partitioning_metadata(CHF_CREATE_FLAG) does cleanup on > error now. > > Part of MDEV-17805 - Remove InnoDB cache for temporary tables. ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] d953dc02d9a: Fix inplace ALTER TABLE to not register tmp table
by Sergei Golubchik 14 Mar '19

14 Mar '19

Hi, Sergey! On Mar 13, Sergey Vojtovich wrote: > revision-id: d953dc02d9a (mariadb-10.4.1-137-gd953dc02d9a) > parent(s): 9496394002f > author: Sergey Vojtovich <svoj(a)mariadb.org> > committer: Sergey Vojtovich <svoj(a)mariadb.org> > timestamp: 2019-02-07 20:04:38 +0400 > message: > > Fix inplace ALTER TABLE to not register tmp table > > Do not register intermediate tables created by inplace ALTER TABLE in > THD::temporary_tables. > > For inplace ALTER TABLE moved .frm creation to > create_table_for_inplace_alter(). For regular ALTER TABLE it is (and was > redundantly) created by ha_create_table(). > > Removed open_in_engine argument of create_and_open_tmp_table() and > open_temporary_table(): it became unused after this patch. > > Part of MDEV-17805 - Remove InnoDB cache for temporary tables. Looks ok code wise. But I don't see what are you gaining by this transformation. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] Self Introduction (gsoc)
by shreya pohekar 14 Mar '19

14 Mar '19

Hey! I am Shreya Pohekar. I am currently in my 3rd year of BE in Computer Science and Engineering from University Institute of Technology, RGPV Bhopal. I have a conceptual proficiency on Java and C++. On scrolling through the projects of mariadb, i found most of the projects interesting. But i would like to contribute to the project, "Implement multi-table UPDATE/DELETE returning a result set".@Igor Babaev, If you can guide me on where to get started, that would be great. Looking forward to hearing from you. Thank you.

1 0

[Maria-developers] Self Introduction (gsoc)
by shreya pohekar 14 Mar '19

14 Mar '19

Hey! I am Shreya Pohekar. I am currently in my 3rd year of BE in Computer Science and Engineering from University Institute of Technology, RGPV Bhopal. I have a conceptual proficiency on Java and C++. On scrolling through the projects of mariadb, i found most of the projects interesting. But i would like to contribute to the project, "Implement multi-table UPDATE/DELETE returning a result set". If you can guide me on where to get started, that would be great. Looking forward to hearing from you. Thank you.

1 0

Re: [Maria-developers] 9496394002f: Removed redundant partitioning check
by Sergei Golubchik 14 Mar '19

14 Mar '19

Hi, Sergey! On Mar 13, Sergey Vojtovich wrote: > revision-id: 9496394002f (mariadb-10.4.1-136-g9496394002f) > parent(s): 311beaffa63 > author: Sergey Vojtovich <svoj(a)mariadb.org> > committer: Sergey Vojtovich <svoj(a)mariadb.org> > timestamp: 2019-02-06 13:56:42 +0400 > message: > > Removed redundant partitioning check > > This check was introduced in 602a222 and then became redundant in ad1553e, > where we attempt to open a table even for non-copy algorithms. > > Added missing test case from 602a222. > > Part of MDEV-17805 - Remove InnoDB cache for temporary tables. ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 311beaffa63: Simplified dd_recreate_table()
by Sergei Golubchik 14 Mar '19

14 Mar '19

Hi, Sergey! On Mar 13, Sergey Vojtovich wrote: > revision-id: 311beaffa63 (mariadb-10.4.1-135-g311beaffa63) > parent(s): 3323b013c13 > author: Sergey Vojtovich <svoj(a)mariadb.org> > committer: Sergey Vojtovich <svoj(a)mariadb.org> > timestamp: 2019-02-06 13:56:42 +0400 > message: > > Simplified dd_recreate_table() > > It is used only with persistent tables, so remove path argument, which > was introduced in 9594107f and became useless after ce6a63e. > > Part of MDEV-17805 - Remove InnoDB cache for temporary tables. ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0