- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] 4b4267288cb: MDEV-14743: Server crashes in Item_func_match::init_search
by Sergei Golubchik 12 Jan '18

12 Jan '18

Hi, Oleksandr! Two thoughts. 1. Please use if (!ifm->fixed) then you'll only need one #ifdef. 2. How does it work with the iterator? if you remove the current element from the list and then iterator with jump to the next one, won't it cause one element to be skipped? On Jan 10, Oleksandr Byelkin wrote: > revision-id: 4b4267288cb2668717cc70537eb7e79cecaff58a (mariadb-5.5.58-24-g4b4267288cb) > parent(s): a408e881cf73d06fc92097fce6ef9584e16edf77 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-01-10 12:22:56 +0100 > message: > > MDEV-14743: Server crashes in Item_func_match::init_search > > Remove non prepared (and so belonging to removed clauses FT functions) from the list. > > in later version it will be fixed by building the list during preparation. > > diff --git a/sql/sql_base.cc b/sql/sql_base.cc > index c06c4fcff29..26e302ded35 100644 > --- a/sql/sql_base.cc > +++ b/sql/sql_base.cc > @@ -9550,7 +9550,19 @@ int init_ftfuncs(THD *thd, SELECT_LEX *select_lex, bool no_order) > DBUG_PRINT("info",("Performing FULLTEXT search")); > > while ((ifm=li++)) > - ifm->init_search(no_order); > +#if MYSQL_VERSION_ID < 100213 > + if (ifm->fixed) > +#endif > + ifm->init_search(no_order); > +#if MYSQL_VERSION_ID < 100213 > + else > + /* > + it mean that clause where was FT function was removed, so we have > + to remove the function from the list. > + */ > + li.remove(); > +#endif > + > } > return 0; > } Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

[Maria-developers] Eliminating or replacing the InnoDB row_prebuilt_t::fetch_cache
by Marko Mäkelä 12 Jan '18

12 Jan '18

The handler API in MySQL and MariaDB is operating on a single record at a time. There are some ‘batched’ primitives that in my understanding were developed for the NDB cluster, but generally InnoDB does not use them. The only nontrivial thing that InnoDB does during reads is the Index Condition Pushdown (ICP) and the end-of-range detection (DsMrr) that was ported from the never-released MySQL 6.0 or 5.2 to MySQL 5.6. To improve the performance of range scans, InnoDB uses a caching mechanism to prefetch results. Starting with the 4th consecutive read on the same ha_innobase handle, InnoDB would prefetch 8 records into row_prebuilt_t::fetch_cache. There are several drawbacks of this: - The parameters of this cache are hard-coded, and these caches can make wrong guesses (for SELECT * FROM t LIMIT 5, it would unnecessarily read 8 records, possibly causing unnecessary page accesses) - The caches waste memory, especially on partitioned tables, where each partition handle would have its own cache. (The main benefit of ha_innopart or InnoDB native partitioning in MySQL 5.7 is that the fetch_cache of the table is shared between partitions. It is only a benefit of saving memory, because the cache will be emptied when switching partitions.) As far as I understand, the entire purpose of the prebuilt->fetch_cache is to reduce the cost of acquiring the page latch and repositioning the cursor on each read request. If we did not call mtr_t::commit() between each request, maybe we could remove this cache altogether. When I was in the InnoDB team at MySQL, I did bring up a few times the idea of eliminating the fetch_cache, but the optimizer developers were in a separate team, and to do this, we would have needed support from both managers. I am hoping that with MariaDB this would merely be a technical challenge to first write a proof-of-concept prototype, and then to polish and test it. When I discussed this with the late Olav Sandstå some years ago, there was some thinking that we should stop hard-coding the TABLE::record[0] in various parts of the code, and the storage engine should be able to return multiple records to a larger TABLE::record[] buffer. I wonder if a simpler approach would work, and if we could do without any prefetching: 1. Remove row_sel_dequeue_cached_row_for_mysql(), prebuilt->fetch_cache, and related code. 2. Add prebuilt->mtr and let it be reused across multiple subsequent calls to row_search_mvcc(), until the query executor stops the current read batch. 3. A call to btr_pcur_store_position() and mtr_t::commit() will be necessary before any potentially long delay, such as when the result is being sent to a client connection, or when the query executor is going to switch to read records from another table. 4. The "end of read batch" could be signalled by invoking handler::extra() with a parameter. With such changes, less copying would take place, and there should be less contention on dict_index_t::lock and the upper-level buf_block_t::lock, due to fewer calls to btr_pcur_restore_position(). While that function does have a fast path ("optimistic restore"), it cannot switch to the next page without a key lookup. Range scans would be more efficient if we can always simply advance to the right leaf page and release the lock on the left page. I would love to do this in InnoDB. But I think that the effort should be lead from the optimizer side. Someone should first implement the "end of batched read" calls and some DBUG_ASSERT() that would catch any wrong-doing. That is, whenever we start to send data to a client or start to write into a temporary table, or switch to read records from another cursor, we must assert that the "end of batched read" flag is set on all cursor handles. Similarly, at the end and start of a statement, and at commit or rollback, all cursor handles must be at "end of batched read". Who would take the challenge? Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation DON’T MISS M|18 MariaDB User Conference February 26 - 27, 2018 New York City https://m18.mariadb.com/

1 0

[Maria-developers] Check constraints
by Martijn Tonies (Upscene Productions) 10 Jan '18

10 Jan '18

Hi, Where can I find meta information for check constraints on tables? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

3 4

[Maria-developers] subscribe
by Martijn Tonies (Upscene Productions) 09 Jan '18

09 Jan '18

subscribe With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] [Commits] fbe0fc2: Fixed mdev-14879 Lost rows for query using recursive CTE
by Oleksandr Byelkin 09 Jan '18

09 Jan '18

Hi, Igor! Am 06.01.2018 um 18:32 schrieb IgorBabaev: > revision-id: fbe0fc2d87cb1bdb9f7040d44ad3c58a94857e20 (mariadb-10.2.12-14-gfbe0fc2) > parent(s): 15b1840f43be8c660382458826e9d5a47d148a67 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-06 09:32:47 -0800 > message: > > Fixed mdev-14879 Lost rows for query using recursive CTE > with recursive reference in subquery > > If a recursive CTE uses a subquery with recursive reference then > the virtual function reset() must be called after each iteration > performed at the execution of the CTE. OK to push. [skip]

1 0

Re: [Maria-developers] [Commits] 81965f0: Fixed mdev-14852 Fails to reopen temp table within standard CTE
by Oleksandr Byelkin 05 Jan '18

05 Jan '18

Am 05.01.2018 um 08:40 schrieb IgorBabaev: > revision-id: 81965f03065b84322f2d03d784893179e8b79fd9 (mariadb-10.2.12-6-g81965f0) > parent(s): 0f253d3e644d3027face26371eb1bb59e8280728 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-04 23:40:37 -0800 > message: > > Fixed mdev-14852 Fails to reopen temp table within standard CTE > > If the specification of a CTE contains a reference to a temporary table > then THD::open_temporary_table() must be called for this reference for > any occurrence of the CTE in the query. By mistake this was done only > for the first occurrences of CTEs. > The patch fixes this problem in With_element::clone_parsed_spec(). > It also moves there the call of check_dependencies_in_with_clauses() > to its proper place before the call of check_table_access(). > Additionally the patch optimizes the number of calls of the > function check_dependencies_in_with_clauses(). OK to push! > > --- > mysql-test/r/cte_nonrecursive.result | 30 ++++++++++++++++++++++++++++++ > mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++++++++++++ > sql/sql_cte.cc | 7 +++++++ > sql/sql_parse.cc | 22 +++++----------------- > sql/sql_prepare.cc | 11 +++-------- > 5 files changed, 74 insertions(+), 25 deletions(-) > > diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result > index b1b41b1..c1f4c9f 100644 > --- a/mysql-test/r/cte_nonrecursive.result > +++ b/mysql-test/r/cte_nonrecursive.result > @@ -1265,3 +1265,33 @@ a > 4 > deallocate prepare stmt; > drop table t1; > +# > +# MDEV-14852: CTE using temporary table in query > +# with two references to the CTE > +# > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > +i > +5 > +4 > +2 > +drop table t1; > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > +where c1.term is null); > +term term > +TERM02 TERM02 > +TERM03 TERM03 > +TERM01 NULL > +NULL TERM04 > +drop table t1,t2; > diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test > index 1f21dbc..9436665 100644 > --- a/mysql-test/t/cte_nonrecursive.test > +++ b/mysql-test/t/cte_nonrecursive.test > @@ -853,3 +853,32 @@ execute stmt; > deallocate prepare stmt; > > drop table t1; > + > +--echo # > +--echo # MDEV-14852: CTE using temporary table in query > +--echo # with two references to the CTE > +--echo # > + > +create temporary table t1 (i int); > +insert into t1 values (5),(4),(1),(2),(3); > + > +with > +c1 as (select i from t1), > +c2 as (select i from c1 where c1.i=2) > +select i from c1 where i > 3 union select i from c2; > + > +drop table t1; > + > +create table t1 (term char(10)); > +create temporary table t2 (term char(10)); > + > +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); > +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); > + > +with c1 as (select * from t1), c2 as (select * from t2) > +(select * from c1 left outer join c2 on c1.term = c2.term) > +union all > +(select * from c1 right outer join c2 on c1.term = c2.term > + where c1.term is null); > + > +drop table t1,t2; > diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc > index 601c192..d12948f 100644 > --- a/sql/sql_cte.cc > +++ b/sql/sql_cte.cc > @@ -817,12 +817,19 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, > parse_status= parse_sql(thd, &parser_state, 0); > if (parse_status) > goto err; > + > + if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > + goto err; > + > spec_tables= lex->query_tables; > spec_tables_tail= 0; > for (TABLE_LIST *tbl= spec_tables; > tbl; > tbl= tbl->next_global) > { > + if (!tbl->derived && !tbl->schema_table && > + thd->open_temporary_table(tbl)) > + goto err; > spec_tables_tail= tbl; > } > if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index f00c74b..6d068eb 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2990,6 +2990,9 @@ mysql_execute_command(THD *thd) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > } > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + DBUG_RETURN(1); > + > #ifdef HAVE_REPLICATION > if (unlikely(thd->slave_thread)) > { > @@ -3446,14 +3449,6 @@ mysql_execute_command(THD *thd) > ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL : > SELECT_ACL; > > - /* > - The same function must be called for DML commands > - when CTEs are supported in DML statements > - */ > - res= check_dependencies_in_with_clauses(thd->lex->with_clauses_list); > - if (res) > - break; > - > if (all_tables) > res= check_table_access(thd, > privileges_requested, > @@ -3879,8 +3874,7 @@ mysql_execute_command(THD *thd) > /* Copy temporarily the statement flags to thd for lock_table_names() */ > uint save_thd_create_info_options= thd->lex->create_info.options; > thd->lex->create_info.options|= create_info.options; > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list))) > - res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > + res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); > thd->lex->create_info.options= save_thd_create_info_options; > if (res) > { > @@ -4493,8 +4487,7 @@ mysql_execute_command(THD *thd) > > unit->set_limit(select_lex); > > - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list)) && > - !(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > + if (!(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > MYSQL_INSERT_SELECT_START(thd->query()); > /* > @@ -4821,9 +4814,6 @@ mysql_execute_command(THD *thd) > { > List<set_var_base> *lex_var_list= &lex->var_list; > > - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > - goto error; > - > if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE) > || open_and_lock_tables(thd, all_tables, TRUE, 0))) > goto error; > @@ -6376,8 +6366,6 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) > new (thd->mem_root) Item_int(thd, > (ulonglong) thd->variables.select_limit); > } > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > > if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0))) > { > diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc > index be78a76..390b708 100644 > --- a/sql/sql_prepare.cc > +++ b/sql/sql_prepare.cc > @@ -1573,8 +1573,6 @@ static int mysql_test_select(Prepared_statement *stmt, > lex->select_lex.context.resolve_in_select_list= TRUE; > > ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL; > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - goto error; > if (tables) > { > if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE)) > @@ -1841,9 +1839,6 @@ static bool mysql_test_create_table(Prepared_statement *stmt) > if (create_table_precheck(thd, tables, create_table)) > DBUG_RETURN(TRUE); > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - DBUG_RETURN(TRUE); > - > if (select_lex->item_list.elements) > { > /* Base table and temporary table are not in the same name space. */ > @@ -2234,9 +2229,6 @@ static bool mysql_test_insert_select(Prepared_statement *stmt, > if (insert_precheck(stmt->thd, tables)) > return 1; > > - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) > - return 1; > - > /* store it, because mysql_insert_select_prepare_tester change it */ > first_local_table= lex->select_lex.table_list.first; > DBUG_ASSERT(first_local_table != 0); > @@ -2339,6 +2331,9 @@ static bool check_prepared_statement(Prepared_statement *stmt) > if (tables) > thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); > > + if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) > + goto error; > + > if (sql_command_flags[sql_command] & CF_HA_CLOSE) > mysql_ha_rm_tables(thd, tables); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] de21526: This is a full cost-based implementation of the optimization that employs
by Oleksandr Byelkin 29 Dec '17

29 Dec '17

Hi, Igor! Am 29.12.2017 um 01:47 schrieb IgorBabaev: > revision-id: de215264682a9f3291ca028fb3ae052ecb191ca3 (mariadb-10.3.3-4-gde21526) > parent(s): 7a66e0ab8f52f3bd32850463daa05f9a2401e6b1 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2017-12-28 16:47:25 -0800 > message: > > This is a full cost-based implementation of the optimization that employs > splitting technique for equi-joins of materialized derived tables/views/CTEs. > (see mdev-13369 and mdev-13389). I have to make special mention of really good description. As we agreed OK to push after adding test of double execution. [skip]

1 0

Re: [Maria-developers] a01f9ff: MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in
by Sergei Golubchik 27 Dec '17

27 Dec '17

Hi, Sergey! On Dec 26, Sergey Vojtovich wrote: > revision-id: a01f9ff80b892bf32dd2754da17438e8884c003a (mariadb-10.2.11-51-ga01f9ff) > parent(s): c3bd0b0301936d0dcd0918bb8c966c272610d61b > committer: Sergey Vojtovich > timestamp: 2017-12-26 14:38:45 +0400 > message: > > MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in > Locked_tables_list::unlock_locked_tables > > Similarly to regular DROP TABLE, don't leave locked tables mode if CREATE OR > REPLACE dropped temporary table but failed to cerate new one. > > The problem is that there's no track of which temporary table was "locked" by > LOCK TABLES. Looks reasonable. Could you also add a test where t2 is locked and t2 is dropped? Just to show that pos_in_locked_tables works for temporary tables. Ok to push after that. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] Please document HOUR() vs EXTRACT(HOUR)
by Alexander Barkov 23 Dec '17

23 Dec '17

Hello Ian, Can you please document the difference between functions HOUR() and EXTRACT(HOUR)? Please see details here: https://jira.mariadb.org/browse/MDEV-14731 Thanks!

1 0

[Maria-developers] mysql and mariadb got different result
by 落寞回头不如华丽转身 21 Dec '17

21 Dec '17

mysql but mariadb

2 1