March 2016 - developers - lists.mariadb.org

[Maria-developers] Window functions: why some functions are not in select_lex->window_funcs?
by Sergey Petrunia 24 Mar '16

24 Mar '16

Hello Igor, I was trying to move JOIN::process_window_functions() from using join->fields_list to using join->select_lex->window_funcs. And I discovered that join->select_lex->window_funcs does not contain window function items that use window definition. Is this intentional? Example: create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (pk int, c int); insert into t1 select a+1,1 from t0; update t1 set c=2 where pk not in (1,2,3,4); select pk, c, count(*) over (partition by c order by pk rows between 2 preceding and 2 following) as CNT from t1 Here: #1 0x0000555555af5db6 in AGGR_OP::end_send (this=0x7fff4c0086c8) (gdb) p join->select_lex->window_funcs $69 = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fff4c005d18, last = 0x7fff4c005d18, elements = 1}, <No data fields>} But: select pk, c, count(*) over w1 as CNT from t1 window w1 as (partition by c order by pk rows between 2 preceding and 2 following); (gdb) p join->select_lex->window_funcs $74 = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x555556d48820, last = 0x55555ab10b70, elements = 0}, <No data fields>} BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 1

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob
by Sachin Setia 23 Mar '16

23 Mar '16

Hello Sergei Actually I was prototyping for blob and varchar for aria and myisam storage engine. My prototype worked for complex definations like craete table(abc int primary key, blob_col blob unique, varchar_col varchar(1000) unique) engine=myisam; Solved the issue of frm file incosistance. As you suggested for doing it for innodb i am current working on it.Innodb does not natively support hash based index. when we run select distinct column from tbl; it use create_internal_tmp_table() which uses maria storage engine for creating tmp table. But query like this works MariaDB [sachin]> create table iu2(abc blob unique); Query OK, 0 rows affected (0.04 sec) MariaDB [sachin]> insert into iu2 values(1); Query OK, 1 row affected (0.03 sec) MariaDB [sachin]> insert into iu2 values(1); ERROR 1062 (23000): Duplicate entry '1' for key 'abc' this query does not use hash but it simply compares values Will write a proposal shortly. On Tue, Mar 22, 2016 at 4:20 PM, Sachin Setia <sachinsetia1001(a)gmail.com> wrote: > Hello Sergi > Actually I was prototyping for blob and varchar for aria and myisam > storage engine. > My prototype worked for complex definations like > craete table(abc int primary key, blob_col blob unique, varchar_col > varchar(1000) unique) engine=myisam; > Solved the issue of frm file incosistance. > > As you suggested for doing it for innodb i am current working on it.Innodb > does not natively support hash based index. > when we run select distinct column from tbl; > it use create_internal_tmp_table() which uses maria storage engine for > creating tmp table. > But query like this works > MariaDB [sachin]> create table iu2(abc blob unique); > Query OK, 0 rows affected (0.04 sec) > > MariaDB [sachin]> insert into iu2 values(1); > Query OK, 1 row affected (0.03 sec) > > MariaDB [sachin]> insert into iu2 values(1); > ERROR 1062 (23000): Duplicate entry '1' for key 'abc' > this query does not use hash but it simply compares values > Will write a proposal shortly. > > > Regards > sachin > > On Sat, Mar 19, 2016 at 1:52 AM, Sergei Golubchik <serg(a)mariadb.org> > wrote: > >> Hi, Sachin! >> >> On Mar 18, Sachin Setia wrote: >> > >> > ERROR 1030 (HY000): Got error 190 "Incompatible key or row definition >> > between the MariaDB .frm file and the information in the storage engine. >> > You have to dump an" from storage engine MyISAM >> > >> > We are getting this becuase in mi_create for each unique_def it creates >> one >> > keydef and writes it.And this creates two problem >> > 1. frm keydef algorithm is different from saved kefdef algorithm(always >> > zero) for the time I have solved this problem . >> > >> > 2.Mismatch between keydef's keysegs the reason for this is when >> mi_create >> > creates keyseg for unique_def it did not keeps the orignal uniquedef's >> > keyseg parameters in mind like language start length which creates >> problem >> > in check_definition function in ha_myisam.cc.I am currently working on >> it >> > Once again sorry for this foolish mistake. >> > Regars >> > sachin >> >> No problem, everyone makes mistakes :) >> >> It's a prototype, after all. It's much more important that you >> understand how the code works and why it doesn't work. >> >> Regards, >> Sergei >> Chief Architect MariaDB >> and security(a)mariadb.org >> > >

2 3

[Maria-developers] More memory allocation for key seg
by Sachin Setia 23 Mar '16

23 Mar '16

Hello Devlopers I was debugging the source code of mariadb. My query was MariaDB [sachin]> create table pro2(abc int primary key,xyz int , ass int ,unique(xyz,ass))engine=myisam; In table2myisam function of file ha_myisam.cc i found that we are allocating more memory for keysegments if (!(my_multi_malloc(MYF(MY_WME), recinfo_out, (share->fields * 2 + 2) * sizeof(MI_COLUMNDEF), keydef_out, share->keys * sizeof(MI_KEYDEF), &keyseg, (share->key_parts + share->keys) * sizeof(HA_KEYSEG), ^ here why this NullS))) And we donot use these extra key segments because suceeding for loop use only total number of share->key_parts keysegments. And same in ha_maria file.Is this a bug or I am missing something Regards sachin

2 2

[Maria-developers] Please review MDEV-9604 crash in Item::save_in_field with empty enum value
by Alexander Barkov 22 Mar '16

22 Mar '16

Hi Sergei, It fixes the reported crash, and additionally inconsistencies when comparing temporal columns to empty strings: In some cases it returned rows, in other cases it returned no rows, depending on: - the engine - the type of comparison arguments (columns vs literals). - presence of an index Now everything works in the same way. Note, this additional problem is not 10.1 specific, it existed in 10.0. But 10.0 and 10.1 worked differently in certain cases. Thanks!

2 4

Re: [Maria-developers] Aggregate stored functions [MDEV-7773]
by Sergei Golubchik 22 Mar '16

22 Mar '16

Hi, Varun! On Mar 21, Varun Gupta wrote: > Hi Sergei, > > 4) :create an Item_sum_sp to be able to use aggregate stored functions in > queries. > Does this mean we can have the aggregate stored functions in the SELECT , > WHERE etc clauses?can you give me more details about this. Yes, of course. That's how functions are used, right? :) SELECT AVG(x), COUNT(y), MAX(z) FROM t1 WHERE aaa=12 GROUP BY bbb; for example. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] 29e99a7: MDEV-717 LP:1003679 - Wrong binlog order on concurrent DROP schema and
by Sergei Golubchik 21 Mar '16

21 Mar '16

Hi, Alexey! In general the patch is pretty good. But I've had few comments, mostly about bad choice of error messages. It's a historical behavoir that you decided not to change. If you'd prefer to leave it as is, that's ok, but then, please, create a new MDEV about these error messages. And add test cases :) On Mar 20, Alexey Botchkov wrote: > revision-id: 29e99a701817da56499df9126830d9533e4d66f3 (mariadb-10.1.12-26-g29e99a7) > parent(s): 7cb16dc2a369058b31df1b3999989f6beff94d07 > committer: Alexey Botchkov > timestamp: 2016-03-20 23:57:26 +0400 > message: > > MDEV-717 LP:1003679 - Wrong binlog order on concurrent DROP schema and > CREATE function. > The cause of the issue is when DROP DATABASE takes > metadata lock and is in progress through it's > execution, a concurrently running CREATE FUNCTION checks > for the existence of database which it succeeds and then it > waits on the metadata lock. Once DROP DATABASE writes to > BINLOG and finally releases the metadata lock on schema > object, the CREATE FUNCTION waiting on metadata lock gets > in it's code path and succeeds and writes to binlog. Please structure the commit comment as <first line - the subject> <empty line> <the rest - the body> That's the convention and various tools, for example, github itself, use it to parse and display the commit subject and body. > --- > sql/events.cc | 29 ++++++++++++++------ > sql/sp.cc | 81 +++++++++++++++++++++++++++++++++++++------------------- > sql/sp.h | 2 +- > sql/sql_parse.cc | 56 +++++++-------------------------------- What, no test cases at all? Please, add tests for everything you've fixed: - create event - update event with moving to a new database > diff --git a/sql/sp.cc b/sql/sp.cc > index 6ec5914..11ba186 100644 > --- a/sql/sp.cc > +++ b/sql/sp.cc > @@ -1040,7 +1045,22 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) > > /* Grab an exclusive MDL lock. */ > if (lock_object_name(thd, mdl_type, sp->m_db.str, sp->m_name.str)) > - DBUG_RETURN(SP_OPEN_TABLE_FAILED); > + { > + my_error(ER_SP_STORE_FAILED, MYF(0), SP_TYPE_STRING(type), sp->m_name.str); No better error message here? May be ER_BAD_DB_ERROR? I understand that the old behavior is ER_SP_STORE_FAILED here. > + DBUG_RETURN(TRUE); > + } > + > + /* > + Check that a database directory with this name > + exists. Design note: This won't work on virtual databases > + like information_schema. > + */ > + if (check_db_dir_existence(sp->m_db.str)) > + { > + my_error(ER_BAD_DB_ERROR, MYF(0), sp->m_db.str); > + DBUG_RETURN(TRUE); > + } > + > > /* Reset sql_mode during data dictionary operations. */ > thd->variables.sql_mode= 0; > @@ -1049,7 +1069,9 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) > thd->count_cuted_fields= CHECK_FIELD_WARN; > > if (!(table= open_proc_table_for_update(thd))) > - ret= SP_OPEN_TABLE_FAILED; > + { > + my_error(ER_SP_STORE_FAILED, MYF(0), SP_TYPE_STRING(type),sp->m_name.str); better error message, may be? > + } > else > { > /* Checking if the routine already exists */ > @@ -1268,7 +1289,8 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) > &(thd->lex->definer->host), > saved_mode)) > { > - ret= SP_INTERNAL_ERROR; > + my_error(ER_SP_STORE_FAILED, MYF(0), > + SP_TYPE_STRING(type), sp->m_name.str); That's OOM, can be a better error message too. Btw, could you reverse the return value of show_create_sp() please? > goto done; > } > /* restore sql_mode when binloging */ > @@ -1277,9 +1299,14 @@ sp_create_routine(THD *thd, stored_procedure_type type, sp_head *sp) > if (thd->binlog_query(THD::STMT_QUERY_TYPE, > log_query.ptr(), log_query.length(), > FALSE, FALSE, FALSE, 0)) > - ret= SP_INTERNAL_ERROR; > + { > + my_error(ER_SP_STORE_FAILED, MYF(0), > + SP_TYPE_STRING(type), sp->m_name.str); that's very confusing. "Failed to CREATE %s %s" when the creation has actually succeeded. The same applies to the OOM error above. > + goto done; > + } > thd->variables.sql_mode= 0; > } > + ret= FALSE; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] Randomness in TokuDB builds
by Otto Kekäläinen 21 Mar '16

21 Mar '16

Hello TokuDB experts! There is some randomness, maybe some unique build id in every TokuDB build that breaks the reproducable builds of it in Debian. Check out: https://tests.reproducible-builds.org/dbd/unstable/amd64/mariadb-10.0_10.0.… I can't find any mentions of a build id or similar in the sources. Can somebody help me figure out where the randomness stems from, and how I should patch the source to make the build deterministic? - Otto

2 1

[Maria-developers] Please review MDEV-9516 type error when setting session variable
by Alexander Barkov 21 Mar '16

21 Mar '16

Hello Sergei, Please review a patch for MDEV-9516. Thanks.

1 0

Re: [Maria-developers] 8d675d5: MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND
by Sergei Golubchik 21 Mar '16

21 Mar '16

Hi, Alexey! On Mar 21, Alexey Botchkov wrote: > revision-id: 8d675d5764e7d8ecad19fafdd167bee9a41d1dc7 (mariadb-10.1.12-27-g8d675d5) > parent(s): 94768542115289272996f6450b7d3cafa75dcead > committer: Alexey Botchkov > timestamp: 2016-03-21 13:02:09 +0400 > message: > > MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND > LOG-SLOW-SLAVE-STATEMENTS NOT DISPLAYED. > These parameters were moved from the command line options to the > system variables section. Treatment of the > opt_log_slow_slave_statements changed to let the > dynamic change of the variable. Thanks! Looks good, just one question: > diff --git a/mysql-test/suite/rpl/r/rpl_slow_query_log.result b/mysql-test/suite/rpl/r/rpl_slow_query_log.result > index 79b83b4..871722e 100644 > --- a/mysql-test/suite/rpl/r/rpl_slow_query_log.result > +++ b/mysql-test/suite/rpl/r/rpl_slow_query_log.result > @@ -76,6 +76,18 @@ ALTER TABLE t1 ADD INDEX id1(a); > INSERT INTO t1 values(1, sleep(3)); > ### Assertion is good. Both Master and Slave exhibit the > ### same number of queries in slow log: 1 > +******************************************************************** > +**** TRUNCATE the slow log then check whether runtime changes of > +**** log_slow_slave_statements work without slave restart. > +******************************************************************** > +SET @old_log_slow_slave_statements= @@global.log_slow_slave_statements; > +SET @@global.log_slow_slave_statements = off; > +TRUNCATE mysql.slow_log; > +INSERT INTO t1 values(1, sleep(3));; > +0 > +SET @@global.log_slow_slave_statements = on; > +INSERT INTO t1 values(1, sleep(3));; > +1 > SET @@global.log_output= @old_log_output; > SET @@global.long_query_time= @old_long_query_time; > DROP TABLE t1; > diff --git a/mysql-test/suite/rpl/t/rpl_slow_query_log.test b/mysql-test/suite/rpl/t/rpl_slow_query_log.test > index faf037a..3505883 100644 > --- a/mysql-test/suite/rpl/t/rpl_slow_query_log.test > +++ b/mysql-test/suite/rpl/t/rpl_slow_query_log.test > @@ -299,6 +299,39 @@ if ($master_slow_query == $slave_slow_query) > -- echo ### same number of queries in slow log: $master_slow_query > } > > +-- echo ******************************************************************** > +-- echo **** TRUNCATE the slow log then check whether runtime changes of > +-- echo **** log_slow_slave_statements work without slave restart. > +-- echo ******************************************************************** > + > +SET @old_log_slow_slave_statements= @@global.log_slow_slave_statements; > +SET @@global.log_slow_slave_statements = off; > +TRUNCATE mysql.slow_log; > + > +-- connection master > + > +--disable_warnings > +-- eval $slow_query; > +--enable_warnings > +sync_slave_with_master; > + > +-- connection slave > + > +eval SELECT sql_text FROM mysql.slow_log WHERE sql_text like '$slow_query'; > + > +SET @@global.log_slow_slave_statements = on; > + > +-- connection master > + > +--disable_warnings > +-- eval $slow_query; > +--enable_warnings > +sync_slave_with_master; > + > +-- connection slave > + > +eval SELECT sql_text FROM mysql.slow_log WHERE sql_text like '$slow_query'; Did you run this test? It seems the result doesn't match the test. The result looks like as if you'd have eval SELECT COUNT(*) FROM ... Please, run the full test suite before pushing. > + > -- connection master > SET @@global.log_output= @old_log_output; > SET @@global.long_query_time= @old_long_query_time; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] MDEV-9369 IN operator with ( num, NULL ) gives inconsistent result
by Sergei Golubchik 21 Mar '16

21 Mar '16

Hi, Alexander! On Feb 08, Alexander Barkov wrote: > Hello Sergei, > > Please review a patch for MDEV-9369. > > Briefly, it fixes cmp_item::cmp() to return three possible values: > FALSE, TRUE, UNKNOWN. These values are then recursively pulled to > the very top level, to the owner Item_xxx. > > The owner Item_func_in (as well as Item_func_case and Item_func_equal) > then further "translate" these tree-values logic into the traditional > val_int()+null_value combination using some additional information. > > For example, Item_func_in::val_int() uses "negated" as an additional > information. > > The patch looks Ok for me. Looks ok to me too. A couple of stylistic comments, see below. ok to push afterwards. > diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc > index d5f5087..39003de 100644 > --- a/sql/item_cmpfunc.cc > +++ b/sql/item_cmpfunc.cc > @@ -3837,15 +3843,15 @@ void cmp_item_decimal::store_value(Item *item) > /* val may be zero if item is nnull */ > if (val && val != &value) > my_decimal2decimal(val, &value); > + set_null_value(item->null_value); > } > > > int cmp_item_decimal::cmp(Item *arg) > { > my_decimal tmp_buf, *tmp= arg->val_decimal(&tmp_buf); > - if (arg->null_value) > - return 1; > - return my_decimal_cmp(&value, tmp); > + return (m_null_value || arg->null_value) ? if you access m_null_value directly, you don't need a setter for it. it'll be less confusing if you replace set_null_value(X) with m_null_value=X. alternatively, you can add a getter is_null_value(). But I personally wouldn't do it, cmp_xxx is a small set of simple classes, no need to get too enterprisey there. > + UNKNOWN : (my_decimal_cmp(&value, tmp) != 0); > } > > > @@ -3892,10 +3900,10 @@ cmp_item *cmp_item_datetime::make_same() > } > > > -bool Item_func_in::nulls_in_row() > +bool Item_func_in::list_contains_null() I found the old name clear enough. But ok, whatever... > { > Item **arg,**arg_end; > - for (arg= args+1, arg_end= args+arg_count; arg != arg_end ; arg++) > + for (arg= args + 1, arg_end= args+arg_count; arg != arg_end ; arg++) > { > if ((*arg)->null_inside()) > return 1; > @@ -4010,6 +4018,32 @@ void Item_func_in::fix_length_and_dec() > } > } > > + /* > + First conditions for bisection to be possible: > + 1. All types are similar, and > + 2. All expressions in <in value list> are const > + */ > + bool bisection_possible= > + type_cnt == 1 && // 1 > + const_itm; // 2 > + if (bisection_possible) > + { > + /* > + In the presence of NULLs, the correct result of evaluating this item > + must be UNKNOWN or FALSE. To achieve that: > + - If type is scalar, we can use bisection and the "have_null" boolean. > + - If type is ROW, we will need to scan all of <in value list> when > + searching, so bisection is impossible. Unless: > + 3. UNKNOWN and FALSE are equivalent results > + 4. Neither left expression nor <in value list> contain any NULL value > + */ > + > + if (cmp_type == ROW_RESULT && > + !((is_top_level_item() && !negated) || // 3 > + (!list_contains_null() && !args[0]->maybe_null))) // 4 > + bisection_possible= false; I think the condition will be easier to understand if you remove the negation of that complex condition in parentheses: if (cmp_type == ROW_RESULT && ((!is_top_level_item() || negated) && // 3 (list_contains_null() || args[0]->maybe_null))) // 4 I find this ^^^ much easier to understand. > + } > + > if (type_cnt == 1) > { > if (cmp_type == STRING_RESULT && Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1