- developers - lists.mariadb.org

[Maria-developers] Oracle strings functions compatibility : substr
by jerome brauge 17 Aug '17

17 Aug '17

Hello Alexander, Here is a patch for function SUBSTR in sql_mode=oracle (If position is 0, then it is treated as 1). Regards, Jérôme.

1 0

[Maria-developers] sys_vars.sysvars_server_embedded test failure
by Alexander Barkov 15 Aug '17

15 Aug '17

Hello Igor, sys_vars.sysvars_server_embedded is failing. It looks like it's related to your recent optimizer change. -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_grouping_derived=on +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown= Can you please fix this? Thanks!

1 0

[Maria-developers] Valgrind reports uninitialized value on "if (!with_two_phase_optimization..."
by Alexander Barkov 15 Aug '17

15 Aug '17

Hello Igor, Please check the output of "mtr --valgrind main.sp" I'm getting these warnings: line ==6490== Thread 6: ==6490== Conditional jump or move depends on uninitialised value(s) ==6490== at 0x7762A1: JOIN::optimize() (sql_select.cc:1123) ==6490== by 0x77F2A7: mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:3721) ==6490== by 0x773994: handle_select(THD*, LEX*, select_result*, unsigned long) (sql_select.cc:376) ==6490== by 0x73F055: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:6431) ==6490== by 0x7354C9: mysql_execute_command(THD*) (sql_parse.cc:3631) ==6490== by 0x742A48: mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) (sql_parse.cc:7886) ==6490== by 0x730684: dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) (sql_parse.cc:1814) ==6490== by 0x72EFF7: do_command(THD*) (sql_parse.cc:1372) ==6490== by 0x883380: do_handle_one_connection(CONNECT*) (sql_connect.cc:1354) ==6490== by 0x883100: handle_one_connection (sql_connect.cc:1260) ==6490== by 0x10D6329: pfs_spawn_thread (pfs.cc:1862) ==6490== by 0x4E3C619: start_thread (in /usr/lib64/libpthread-2.22.so) ==6490== by 0x6DB75FC: clone (in /usr/lib64/libc-2.22.so) ^ Found warnings in /home/bar/maria-git/server.10.2-ext/mysql-test/var/6/log/mysqld.1.err Thanks!

1 0

[Maria-developers] history of lf_hash
by MARK CALLAGHAN 14 Aug '17

14 Aug '17

Comments for lf_hash are sparse. Does it implement a well-known algorithm? https://github.com/mysql/mysql-server/blob/8.0/mysys/lf_hash.cc https://github.com/mysql/mysql-server/blob/8.0/include/lf.h -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-developers] On Recursive CTE and CONNECT BY
by Sergey Petrunia 12 Aug '17

12 Aug '17

Hi Vicentiu, This is a followup to the Wednesday discussion. Background: see attached file for two queries. One uses a Recursive-CTE, another uses CONNECT-BY syntax. They produce different results for some reason. Initial guess about the cause was depth-first vs breadth-first traversal. Short answer: No it's not. The reason is that Recursive-CTE query is not equivalent to the CONNECT-BY query. Results from the recursive-CTE query: ID N P CYCLE PATH 1 A 0 /1A 2 B A 0 /1A/2B 3 C A 0 /1A/3C 4 D B 0 /1A/2B/4D 5 D C 0 /1A/3C/5D 6 E D 0 /1A/3C/5D/6E 6 E D 0 /1A/2B/4D/6E 7 F E 0 /1A/2B/4D/6E/7F 7 F E 0 /1A/3C/5D/6E/7F 8 C F 0 /1A/3C/5D/6E/7F/8C 8 C F 0 /1A/2B/4D/6E/7F/8C 5 D C 0 /1A/2B/4D/6E/7F/8C/5D 12 rows selected. Results from CONNECT-BY query: ID N P CYCLE Path 1 A 0 /1A 2 B A 0 /1A/2B 4 D B 0 /1A/2B/4D 6 E D 0 /1A/2B/4D/6E 7 F E 0 /1A/2B/4D/6E/7F 8 C F 1 /1A/2B/4D/6E/7F/8C 3 C A 0 /1A/3C 5 D C 0 /1A/3C/5D 6 E D 0 /1A/3C/5D/6E 7 F E 1 /1A/3C/5D/6E/7F 10 rows selected. The rows that are missing are: ID N P CYCLE PATH 8 C F 0 /1A/3C/5D/6E/7F/8C 5 D C 0 /1A/2B/4D/6E/7F/8C/5D (denote this as MISSING-ROWS) Another thing to note is that the CTE query has returned CYCLE=0 for all rows, while the CONNECT BY one has returned two rows with CYCLE=1: 8 C F 1 /1A/2B/4D/6E/7F/8C 7 F E 1 /1A/3C/5D/6E/7F Note that these rows are parents of rows in MISSING-ROWS. I don't think the issue is caused by breadth-first vs depth-first traversal differences. The issue is caused by us not understanding how Oracle's NOCYCLE clause works. Take a look at the original-dataset.jpg, and PATH columns of MISSING-ROWS We see that the path forms the entire loop (or "cycle"). Oracle's NOCYCLE seems to be about *not* constructing the entire loop. To rule out breadth-first vs depth-first differences, let's make an example where there's just one loop: create table g3(a int, b int); insert into g3 values (1, 2); insert into g3 values (2, 3); insert into g3 values (3, 4); insert into g3 values (4, 5); insert into g3 values (5, 6); insert into g3 values (6, 7); insert into g3 values (7, 4); select a, CONNECT_BY_ISCYCLE as cycle, SYS_CONNECT_BY_PATH(a, '/') "Path" from g3 start with a=1 connect by NOCYCLE prior b= a; A CYCLE Path 1 0 /1 2 0 /1/2 3 0 /1/2/3 4 0 /1/2/3/4 5 0 /1/2/3/4/5 6 1 /1/2/3/4/5/6 The row with a=7 is not listed. If it did, the path would have had a complete cycle. It is a bit counter intuitive that /1/2/3/4/5/6 has CYCLE=1, while actually it does not form a cycle. It's one step short of doing so. The documentation is vague about this, but here's somebody thinking the same on the internet: http://www.sql.ru/forum/255418/zadachka-anagrammy-na-sql?hl=%e0%ed%e0%e3%f0… : While posting messages on MetaLink, I think I finally got the logic of NOCYCLE. Clue was right there in the name itself. NOCYCLE means do not cycle. So if you have a hierarchical query without NOCYCLE and a cycle is detected Oracle does not know how to handle it and throws ORA-01436. If NOCYCLE is used Oracle simply cuts of part of a branch right at loop point without raising an error (another word it simply does not follow the loop). And that explains why CONNECT_BY_ISCYCLE is set to 1 for the last row in first iteration of the loop. Otherwise, we would not get 1 at all. Now next logical step would be to implement CYCLE. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Win32 - Sysvar 'optimizer_switch' failed 'def_val < my_set_bits(typelib.count)'
by jerome brauge 11 Aug '17

11 Aug '17

Hi All, Build of last 10.2-ext fail on Win32 with message : Sysvar 'optimizer_switch' failed 'def_val < my_set_bits(typelib.count)' The problem seems to be in sys_vars.ic (Sys_var_set) : > > { > option.var_type|= GET_SET; > global_var(ulonglong)= def_val; > SYSVAR_ASSERT(typelib.count > 0); > SYSVAR_ASSERT(typelib.count <= 64); > SYSVAR_ASSERT(def_val <= my_set_bits(typelib.count)); Patch b14e2b04 introduce a new optimizer_switch and now 'optimizer_switch' has 32 values. my_set_bits works with unit32. A new function like this static inline uint64 my_set_bits64(int n) { return (((1ULL << (n - 1)) - 1) << 1) | 1; } might solve this issue. Regards.

1 0

[Maria-developers] Virtual columns in sql_mode=ORACLE
by jerome brauge 11 Aug '17

11 Aug '17

Hello Alexander, Something seems to be wrong in sql_yacc_ora.yy. (10.2-ext branch) We can't create a table with a virtual column because the grammar try to reduce the rule sp_cursor_name_and_offset. CREATE TABLE t1 (c1 INTEGER, c2 INTEGER AS (c1 MOD 10) VIRTUAL) ENGINE=InnoDb; ERROR 1324 (42000): Undefined CURSOR: c1 Regards, Jérôme.

2 1

[Maria-developers] Default value on column in sql_mode=ORACLE
by jerome brauge 11 Aug '17

11 Aug '17

Hello Alexander, Do you know why STRICT_TRANS_TABLES is not set in sql_mode=ORACLE ? The following script should not work: set sql_mode=oracle; create table t1 (c1 integer not null,c2 integer not null); insert into t1 (c1) values(1); Query OK, 1 row affected, 1 warning Regards, Jérôme.

2 1

Re: [Maria-developers] MDEV-13439: Database permissions are not enough to run a subquery with GROUP BY within a view
by Sergei Golubchik 09 Aug '17

09 Aug '17

Hi, Oleksandr! On Aug 07, Oleksandr Byelkin wrote: > revision-id: cf66c30ce592ad88d447568272bdced2046f2906 (mariadb-10.2.7-20-gcf66c30ce59) > parent(s): bcc10a5a447805ce64aa13ee6a037c1618219616 > committer: Oleksandr Byelkin > timestamp: 2017-08-07 13:42:35 +0200 > message: > > MDEV-13439: Database permissions are not enough to run a subquery with GROUP BY within a view > > The bug is result adding ability to have derived tables inside views. > Fixed checks should be a switch between view/derived or select derived and information schema. > > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 77f502d3a7b..9bffcbe6a9e 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -7594,8 +7594,11 @@ bool check_grant(THD *thd, ulong want_access, TABLE_LIST *tables, > /* > It is subquery in the FROM clause. VIEW set t_ref->derived after > table opening, but this function always called before table opening. > + > + NOTE: is_derived() can't be used here bacause subquery in this case typo "bacause" > + the FROM clase (derived tables) can be not be marked yet. > */ > - if (!t_ref->referencing_view) > + if (t_ref->is_anonymous_derived_table() || t_ref->schema_table) > { > /* > If it's a temporary table created for a subquery in the FROM > diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc > index 649f745fdc4..8f8d051a976 100644 > --- a/sql/sql_derived.cc > +++ b/sql/sql_derived.cc > @@ -822,10 +822,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) > table->derived_select_number= first_select->select_number; > table->s->tmp_table= INTERNAL_TMP_TABLE; > #ifndef NO_EMBEDDED_ACCESS_CHECKS > - if (derived->referencing_view) > + if (derived->is_view()) > table->grant= derived->grant; > else > { > + DBUG_ASSERT(derived->is_derived()); > + DBUG_ASSERT(derived->is_anonymous_derived_table()); Hmm, if derived->is_derived() is always true here, why there's an if() below? If it's redundant - please, remove it. > table->grant.privilege= SELECT_ACL; > if (derived->is_derived()) > derived->grant.privilege= SELECT_ACL; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] commit_checkpoint_request() vs. thd_get_durability_property() (in relation to MDEV-11937)
by Kristian Nielsen 08 Aug '17

08 Aug '17

Monty asked me to fix MDEV-11937. This particular one is a performance regression in InnoDB commit. But there is a wider problem that I thought I should explain, so it can be perhaps avoided in the future. MariaDB and MySQL use different mechanisms for storage engines to avoid having to fsync during commit when binlog is enabled. In MariaDB, storage engines implement the commit_checkpoint_request() handlerton method. In MySQL, storage engines call thd_get_durability_property() to check if they can avoid fsync. The problem is that somehow the thd_get_durability_property() function was introduced into MariaDB code, but it is completely non-functional. So now there is code in InnoDB, TokuDB and RocksDB that calls this function and does not work correctly. This lead to performance regression due to extra fsync() calls. This seems to me a serious problem. Now new code can be merged and compile fine, where in reality it is wrong. There really should not be two separate and different mechanisms for the same thing, and certainly not with one of them non-functional. The "expected" approach would be to remove thd_get_durability_property() and update storage engines to use the corresponding MariaDB APIs (commit_ordered() and commit_checkpoint_request()). This should not be hard. A simple commit_checkpoint_request() implementation can just fsync all transactions immediately (similar to what MySQL does). A more detailed implementation can avoid any extra fsyncs, and just asynchroneously notify the upper layer with commit_checkpoint_notify_ha() later when such fsync happens normally (this is what InnoDB does). See comments in handler.h for details. Or was the intention to eventually replace the whole MariaDB binlog group commit implementation with the MySQL one, to make MariaDB less divergent? This would require a number of changes to MariaDB binlog and replication. The binlog recovery code should be replaced (MySQL does not have the ability to recover from more than one binlog). The binlog group commit code must be replaced as well, and the commit_ordered() mechanism removed. I think this would also require a re-design of the MariaDB in-order parallel replication. MySQL has some optional mechanism for in-order, but my understanding is that it is not sufficient to do optimistic parallel replication. I am not intimately familiar with the MySQL code though. I hope this helps. The wider problem behind MDEV-11937 is one of policy more than one of code bugs, so not too much else I can do to address it. ----------------------------------------------------------------------- Incidentally, I noticed some code in InnoDB trx0trx.cc: /* We set the HA_IGNORE_DURABILITY during prepare phase of binlog group commit to not flush redo log for every transaction here. So that we can flush prepared records of transactions to redo log in a group right before writing them to binary log during flush stage of binlog group commit. */ So the idea is to do group prepare with the same group of transactions that will later group commit to the binlog. In MariaDB, this concept does not exist. Storage engine prepares are allowed to run in parallel and in any order compared to binlog commit. So the InnoDB group prepare can include more transactions than participate in the binlog commit (but also less, of course). IIRC, the important thing is to ensure that all transactions are durably prepared in storage engines before being written to the binlog. In MariaDB, there is MYSQL_LOG_BIN::group_commit_queue that holds the list of transactions to be group committed to the binlog. A similar mechanism in MariaDB might use the group_commit_queue as the set of transactions to send to storage engines for group prepare. But if we wait for a prepare fsync() after building this list, some transactions that could prepare during this fsync may be unnecesarily delayed to the next binlog commit. The MySQL 5.7 code grabs the list of transactions to binlog group commit, and then flushes the log in _all_ storage engines, unconditionally. That just seems horrybly wrong, but I can't see any other way to read the code (from MYSQL_BIN_LOG::process_flush_stage_queue()): ha_flush_logs(NULL, true); It even does so while holding LOCK_log :-( I guess the MySQL idea is that there is only one storage engine anyway, InnoDB. - Kristian.

3 3