- developers - lists.mariadb.org

Re: [Maria-developers] MDEV-8389 patch review
by Sergey Petrunia 03 Aug '15

03 Aug '15

On Sun, Aug 02, 2015 at 08:51:44PM +0530, Diwas Joshi wrote: > So what would be the expected result of query select f1(1,'aa'). since it > will be a table shouldn't we use select * from f1(1,'aa'). I know it > shouldn't crash but what should be the output of that query? > My first guess is that since we are storing this is as functions too(as we > decided earlier) it is crashing so we'll have to check for table functions > in the execution cycle of functions Agree. When a table function is invoked in the context where a scalar value is expected, the statement should fail with an error (Please look in sql/share/errmsg.txt and pick an error code. If none works, please add a new error code/message). However, this is not what I wanted to complain about (I pasted the wrong example). Let's consider a testcase where a table function is used in the scalar context (and should return an empty table): == example starts == delimiter | CREATE FUNCTION f1(param1 VARCHAR(11)) RETURNS TABLE t1(name VARCHAR(11)) deterministic BEGIN INSERT INTO t1 SELECT a FROM t2 WHERE id = param1; END| DELIMITER ; select 1 from f1('aaa'); == example ends == This crashes for me, too. The crash is in your function: Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffff7eb8700 (LWP 12653)] 0x0000555555b4c56b in Object_creation_ctx::set_n_backup (this=0xb, thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/table.cc:83 (gdb) wher #0 0x0000555555b4c56b in Object_creation_ctx::set_n_backup (this=0xb, thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/table.cc:83 #1 0x0000555555a6d280 in parse_sql (thd=0x55555ac4aeb0, parser_state=0x7ffff7eb4580, creation_ctx=0xb, do_pfs_digest=false) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9100 #2 0x0000555555a6dcbb in check_and_process_table_functions (thd=0x55555ac4aeb0, table_list=0x7fff5c007630) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:9336 #3 0x0000555555a5ab77 in mysql_execute_command (thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:2471 #4 0x0000555555a6942e in mysql_parse (thd=0x55555ac4aeb0, rawbuf=0x7fff5c007408 "select 1 from f1('aaa')", length=23, parser_state=0x7ffff7eb7100) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:7174 #5 0x0000555555a5838b in dispatch_command (command=COM_QUERY, thd=0x55555ac4aeb0, packet=0x55555ac51f71 "select 1 from f1('aaa')", packet_length=23) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1470 #6 0x0000555555a570a5 in do_command (thd=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1093 #7 0x0000555555b95929 in do_handle_one_connection (thd_arg=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_connect.cc:1347 #8 0x0000555555b9566e in handle_one_connection (arg=0x55555ac4aeb0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_connect.cc:1258 #9 0x00007ffff691de9a in start_thread (arg=0x7ffff7eb8700) at pthread_create.c:308 #10 0x00007ffff604e3fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112 #11 0x0000000000000000 in ?? () Please check what's going on. (If you don't get the crash, compile and run under valgrind). > Also i removed the test case SELECT * FROM mysql.proc WHERE db = database() > and name = 'f1' because it was failing as it is writing time to result file > so the test passes if we use --record since it writes new file but fails > for ./mysql-test-run sp.test with differences in reject and result file Good point. However, the solution should be not to remove the testcase, but rather make the testcase output not depend on the date/time when the testcase was run. Why did .result file have time/date. Let's look: -SELECT * FROM mysql.proc WHERE db = database() and name = 'f1'; ... -definer root@localhost -created 2015-07-03 16:36:36 -modified 2015-07-03 16:36:36 -sql_mode -comment -character_set_client latin1 ... It did so, because the SELECT selects 'created' and 'modified' columns from mysql.proc. The testcase doesn't really need these columns, please make the SELECT not to select them. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 1

Re: [Maria-developers] MDEV-8389 patch review
by Sergey Petrunia 02 Aug '15

02 Aug '15

Hi Diwas, I've got a crash when trying the first example. The example was: CREATE FUNCTION f1(a INT, b VARCHAR(11)) RETURNS TABLE t1(id INT, name VARCHAR(11)) DETERMINISTIC BEGIN INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; END|; delimiter ; select f1(1,'aa'); The crash was: Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 0x7ffff7eb8700 (LWP 24655)] 0x0000555555c6a82a in Field_str::Field_str (this=0x7fff5c007d30, ptr_arg=0x0, len_arg=4294967295, null_ptr_arg=0x55555644299c "", null_bit_arg=1 '\001', unireg_check_arg=Field::NONE, field_name_arg=0x7fff5c006780 "f1(1,'aa')", charset_arg=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/field.cc:1845 (gdb) wher #0 0x0000555555c6a82a in Field_str::Field_str (this=0x7fff5c007d30, ptr_arg=0x0, len_arg=4294967295, null_ptr_arg=0x55555644299c "", null_bit_arg=1 '\001', unireg_check_arg=Field::NONE, field_name_arg=0x7fff5c006780 "f1(1,'aa')", charset_arg=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/field.cc:1845 #1 0x0000555555ae6820 in Field_longstr::Field_longstr (this=0x7fff5c007d30, ptr_arg=0x0, len_arg=4294967295, null_ptr_arg=0x55555644299c "", null_bit_arg=1 '\001', unireg_check_arg=Field::NONE, field_name_arg=0x7fff5c006780 "f1(1,'aa')", charset_arg=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/field.h:1200 #2 0x0000555555c7d85c in Field_blob::Field_blob (this=0x7fff5c007d30, ptr_arg=0x0, null_ptr_arg=0x55555644299c "", null_bit_arg=1 '\001', unireg_check_arg=Field::NONE, field_name_arg=0x7fff5c006780 "f1(1,'aa')", share=0x7fff5c0062a0, blob_pack_length=4, cs=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/field.cc:7415 #3 0x0000555555c85725 in make_field (share=0x7fff5c0062a0, ptr=0x0, field_length=2779096485, null_pos=0x55555644299c "", null_bit=1 '\001', pack_flag=2779096485, field_type=MYSQL_TYPE_NULL, field_charset=0x0, geom_type=Field::GEOM_GEOMETRY, srid=0, unireg_check=Field::NONE, interval=0x0, field_name=0x7fff5c006780 "f1(1,'aa')") at /home/psergey/dev-git/10.1-gsoc-repo/sql/field.cc:9861 #4 0x0000555555db4d35 in sp_head::create_result_field (this=0x7fff5c01c398, field_max_length=0, field_name=0x7fff5c006780 "f1(1,'aa')", table=0x7fff5c005710) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sp_head.cc:885 #5 0x0000555555d0d2b3 in Item_func_sp::init_result_field (this=0x7fff5c0055e0, thd=0x55555acf2800) at /home/psergey/dev-git/10.1-gsoc-repo/sql/item_func.cc:6593 #6 0x0000555555d0dc99 in Item_func_sp::fix_fields (this=0x7fff5c0055e0, thd=0x55555acf2800, ref=0x7fff5c006778) at /home/psergey/dev-git/10.1-gsoc-repo/sql/item_func.cc:6844 #7 0x00005555559fd1ed in setup_fields (thd=0x55555acf2800, ref_pointer_array=0x7fff5c007c90, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fff5c007a80, allow_sum_func=true) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_base.cc:7925 #8 0x0000555555aa6a83 in JOIN::prepare (this=0x7fff5c007750, rref_pointer_array=0x55555acf6b28, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x55555acf68b0, unit_arg=0x55555acf61c0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:782 #9 0x0000555555aafa67 in mysql_select (thd=0x55555acf2800, rref_pointer_array=0x55555acf6b28, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff5c007730, unit=0x55555acf61c0, select_lex=0x55555acf68b0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:3301 #10 0x0000555555aa5d93 in handle_select (thd=0x55555acf2800, lex=0x55555acf60f8, result=0x7fff5c007730, setup_tables_done_option=0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_select.cc:371 #11 0x0000555555a65e98 in execute_sqlcom_select (thd=0x55555acf2800, all_tables=0x0) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:5798 #12 0x0000555555a5c151 in mysql_execute_command (thd=0x55555acf2800) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:2939 #13 0x0000555555a6942e in mysql_parse (thd=0x55555acf2800, rawbuf=0x7fff5c0051b8 "select f1(1,'aa')", length=17, parser_state=0x7ffff7eb7100) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:7174 #14 0x0000555555a5838b in dispatch_command (command=COM_QUERY, thd=0x55555acf2800, packet=0x55555acf9b41 "select f1(1,'aa')", packet_length=17) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1470 #15 0x0000555555a570a5 in do_command (thd=0x55555acf2800) at /home/psergey/dev-git/10.1-gsoc-repo/sql/sql_parse.cc:1093 (gdb) list 1840 const char *field_name_arg, CHARSET_INFO *charset_arg) 1841 :Field(ptr_arg, len_arg, null_ptr_arg, null_bit_arg, 1842 unireg_check_arg, field_name_arg) 1843 { 1844 field_charset= charset_arg; 1845 if (charset_arg->state & MY_CS_BINSORT) 1846 flags|=BINARY_FLAG; 1847 field_derivation= DERIVATION_IMPLICIT; 1848 } 1849 (gdb) p charset_arg $10 = (CHARSET_INFO *) 0x0 Another piece of feedback: Please don't change old tests into new tests. Tests that were made for previous MDEVs should stay. New tasks should add new tests. I'll continue to look at the patch. On Sun, Aug 02, 2015 at 06:51:26PM +0530, Diwas Joshi wrote: > I am attaching the patch for MDEV-8389. As discussed earlier, I am creating > tables in the query corresponding to table functions before they are being > accessed. I am checking for each table if its a table-function using > sp_find_routine. If it is then we make a table for it. Going by the same > mechanism used in sp_find_routine to parse the function, i parse a create > table query which initializes create_info and alter_info which i later use > in mysql_create_table() to create temporary tables. I also added a test > case for it. Seems to be working fine. > > I guess we'll also need a function that will delete the tables create after > use. I am working on it right now, but i guess that won't be needed in this > patch. > Please review the patch. > > regards > Diwas Joshi > diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result > index cacfeea..4373925 100644 > --- a/mysql-test/r/sp-error.result > +++ b/mysql-test/r/sp-error.result > @@ -2866,7 +2866,3 @@ SELECT @msg; > DROP FUNCTION f1; > DROP FUNCTION f2; > DROP TABLE t1; > -SELECT * FROM f1('xyz'); > -ERROR 42000: This version of MariaDB doesn't yet support 'Queries using Table Functions' > -SELECT * FROM t1, f1('xyz',t1.a); > -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 > diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result > index f06939b..d0f6578 100644 > --- a/mysql-test/r/sp.result > +++ b/mysql-test/r/sp.result > @@ -7943,29 +7943,7 @@ RETURNS TABLE t1(id INT, name VARCHAR(11)) > BEGIN > INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > END| > -SELECT * FROM mysql.proc WHERE db = database() and name = 'f1'; > -db test > -name f1 > -type FUNCTION > -specific_name f1 > -language SQL > -sql_data_access CONTAINS_SQL > -is_deterministic NO > -security_type DEFINER > -param_list a INT, b VARCHAR(11) > -returns TABLE t1(id int(11), name varchar(11)) > -body BEGIN > -INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > -END > -definer root@localhost > -created 2015-07-03 16:36:36 > -modified 2015-07-03 16:36:36 > -sql_mode > -comment > -character_set_client latin1 > -collation_connection latin1_swedish_ci > -db_collation latin1_swedish_ci > -body_utf8 BEGIN > -INSERT INTO t1 SELECT id, name FROM t2 WHERE id = a; > -END > +SELECT * FROM f1(10,'XYZ'); > +id name > +DROP TABLE f1; > DROP FUNCTION f1; > diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test > index ccf5b98..c1a21c8 100644 > --- a/mysql-test/t/sp-error.test > +++ b/mysql-test/t/sp-error.test > @@ -3845,8 +3845,3 @@ SELECT @msg; > DROP FUNCTION f1; > DROP FUNCTION f2; > DROP TABLE t1; > - > ---error 1235 > -SELECT * FROM f1('xyz'); > ---error 1064 > -SELECT * FROM t1, f1('xyz',t1.a); > diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test > index 5c42df0..906c91c 100644 > --- a/mysql-test/t/sp.test > +++ b/mysql-test/t/sp.test > @@ -9397,7 +9397,6 @@ END| > > DELIMITER ;| > > ---vertical_results > -SELECT * FROM mysql.proc WHERE db = database() and name = 'f1'; > - > +SELECT * FROM f1(10,'XYZ'); > +DROP TABLE f1; > DROP FUNCTION f1; > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 2998972..dd16c36 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2467,6 +2467,8 @@ static bool do_execute_sp(THD *thd, sp_head *sp) > /* should be assigned after making first tables same */ > all_tables= lex->query_tables; > /* set context for commands which do not use setup_tables */ > + if (lex->sql_command == SQLCOM_SELECT) > + check_and_process_table_functions(thd, all_tables); > select_lex-> > context.resolve_in_table_list_only(select_lex-> > table_list.first); > @@ -9222,3 +9224,142 @@ CHARSET_INFO *find_bin_collation(CHARSET_INFO *cs) > } > return cs; > } > + > +/* > + @brief > + Check for table functions and create corresponding tables to be used in query. > + > + @param thd Thread handle > + @param table_list A list of tables being processed in the query. > + > + @return > + False on success > + @retval > + TRUE on error > +*/ > +bool check_and_process_table_functions(THD *thd, TABLE_LIST *table_list) > +{ > + TABLE_LIST *tables = table_list; > + int err= 0; > + while(tables) > + { > + if (tables->table_name == NULL) > + { > + tables = tables->next_local; > + continue; > + } > + sp_head *sp; > + sp_name *name; > + LEX_STRING lex_db; > + LEX_STRING lex_name; > + lex_db.length= strlen(tables->db); > + lex_name.length= strlen(tables->table_name); > + lex_db.str= thd->strmake(tables->db, lex_db.length); > + lex_name.str= thd->strmake(tables->table_name, lex_name.length); > + name= new sp_name(lex_db, lex_name, true); > + name->init_qname(thd); > + Object_creation_ctx *creation_ctx; > + int err=0; > + > + if (!err && (sp = sp_find_routine(thd,TYPE_ENUM_FUNCTION, name, > + &thd->sp_proc_cache, FALSE)) && > + sp->m_type== TYPE_ENUM_TABLE) //check if the table is a table-valued function. > + { > + LEX *old_lex= thd->lex, newlex; > + thd->lex= &newlex; > + ulonglong sql_mode= 0 , saved_mode= thd->variables.sql_mode; > + String defstr; //query in defstr will be parsed to create temporary table. > + defstr.append(STRING_WITH_LEN("CREATE TEMPORARY TABLE ")); > + defstr.append(tables->table_name); > + defstr.append(STRING_WITH_LEN(" (")); > + List_iterator_fast<Create_field> it(sp->m_cols_list); > + > + TABLE table; > + TABLE_SHARE share; > + bzero((char*) &table, sizeof(table)); > + bzero((char*) &share, sizeof(share)); > + table.in_use= thd; > + table.s = &share; > + Create_field *cols_field; > + cols_field= it++; > + while (cols_field) > + { > + uint unused1= 0; > + sp_prepare_create_field(thd, cols_field); > + prepare_create_field(cols_field, &unused1, HA_CAN_GEOMETRY); > + uint field_length; > + Field *field; > + String tmp_result(512); > + field_length= cols_field->length; > + cols_field->flags= 0; > + field= make_field(table.s, /* TABLE_SHARE ptr */ > + (uchar*) 0, /* field ptr */ > + field_length, /* field [max] length */ > + (uchar*) "", /* null ptr */ > + 0, /* null bit */ > + cols_field->pack_flag, > + cols_field->sql_type, > + cols_field->charset, > + cols_field->geom_type, cols_field->srid, > + Field::NONE, /* unreg check */ > + cols_field->interval, > + cols_field->field_name); > + field->vcol_info= cols_field->vcol_info; > + field->stored_in_db= cols_field->stored_in_db; > + if (field) > + field->init(&table); > + defstr.append(cols_field->field_name); > + defstr.append(STRING_WITH_LEN(" ")); > + field->sql_type(tmp_result); > + defstr.append(tmp_result); > + cols_field= it++; > + if(cols_field) > + defstr.append(STRING_WITH_LEN(", ")); > + delete field; > + } > + defstr.append(STRING_WITH_LEN(")")); > + char saved_cur_db_name_buf[SAFE_NAME_LEN+1]; > + LEX_STRING saved_cur_db_name= { saved_cur_db_name_buf, > + sizeof(saved_cur_db_name_buf) }; > + newlex.create_info.DDL_options_st::init(); > + Stored_program_creation_ctx *creation_ctx; > + char definer_user_name_holder[USERNAME_LENGTH + 1]; > + LEX_STRING definer_user_name= { definer_user_name_holder, USERNAME_LENGTH }; > + char definer_host_name_holder[HOSTNAME_LENGTH + 1]; > + LEX_STRING definer_host_name= { definer_host_name_holder, HOSTNAME_LENGTH }; > + newlex.current_select= NULL; > + Parser_state parser_state; > + thd->variables.sql_mode= sql_mode; > + err= parser_state.init(thd, defstr.c_ptr_safe(), defstr.length()); > + if (!err) { > + lex_start(thd); > + err= parse_sql(thd, &parser_state, creation_ctx) || thd->lex == NULL; > + } > + if (!err) > + { > + Table_specification_st create_info(thd->lex->create_info); > + Alter_info alter_info(thd->lex->alter_info, thd->mem_root); > + thd->lex->create_info.default_table_charset= NULL; > + SELECT_LEX *select_lex= &thd->lex->select_lex; > + TABLE_LIST *create_table= select_lex->table_list.first; > + if (append_file_to_dir(thd, &create_info.data_file_name, > + create_table->table_name) || > + append_file_to_dir(thd, &create_info.index_file_name, > + create_table->table_name)) > + { > + err= 1; > + } > + if (!(create_info.used_fields & HA_CREATE_USED_ENGINE)) > + create_info.use_default_db_type(thd); > + > + err= mysql_create_table(thd, create_table, &create_info, &alter_info); > + thd->variables.option_bits|= OPTION_KEEP_LOG; > + } > + thd->variables.sql_mode= saved_mode; > + lex_end(thd->lex); > + thd->lex= old_lex; > + } > + tables = tables->next_local; > + } > + return err; > +} > \ No newline at end of file > diff --git a/sql/sql_parse.h b/sql/sql_parse.h > index 76e1b7a..6b89b35 100644 > --- a/sql/sql_parse.h > +++ b/sql/sql_parse.h > @@ -121,7 +121,7 @@ bool push_new_name_resolution_context(THD *thd, > Item *normalize_cond(Item *cond); > Item *negate_expression(THD *thd, Item *expr); > bool check_stack_overrun(THD *thd, long margin, uchar *dummy); > - > +bool check_and_process_table_functions(THD *thd, TABLE_LIST *table_list); > /* Variables */ > > extern const char* any_db; > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index d7d7d7b..dd1f9f6 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -10802,7 +10802,6 @@ table_factor: > Yacc_state *state= & thd->m_parser_state->m_yacc; > if (state->table_factor_part_type== 2) > { > - mysql_init_select(lex); > List_iterator_fast<Item> it(lex->value_list); > Item *item; > while ((item = it++)) > @@ -10817,9 +10816,6 @@ table_factor: > MYSQL_YYABORT; > sel->add_joined_table($$); > lex->pop_context(); > - > - my_error(ER_NOT_SUPPORTED_YET, MYF(0), > - "Queries using Table Functions"); //to be removed later > } > else > { -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] 2430d1d: MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd
by Oleksandr Byelkin 31 Jul '15

31 Jul '15

Hi! On 30.07.15 16:59, Sergey Vojtovich wrote: > revision-id: 2430d1d7ccfcf429e45dbfe655a24d57a79ebf6c (mariadb-5.5.44-14-g2430d1d) > parent(s): 392df76bc3a40a5dd1956b12628dd6489a37be36 > committer: Sergey Vojtovich > timestamp: 2015-07-30 18:51:44 +0400 > message: > > MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd > execution of PS > > GROUP_CONCAT() with ORDER BY column position may crash server on PS reexecution. > > The problem was that arguments array of GROUP_CONCAT() was adjusted to point to > temporary elements (resolved ORDER BY fields) during first execution. > > This patch expands rev. 08763096cb to restore original arguments array as well. OK to push! > > --- > mysql-test/r/func_gconcat.result | 16 ++++++++++++++++ > mysql-test/t/func_gconcat.test | 11 +++++++++++ > sql/item_sum.cc | 3 +++ > 3 files changed, 30 insertions(+) > > diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result > index f12a0c1..0bc31a5 100644 > --- a/mysql-test/r/func_gconcat.result > +++ b/mysql-test/r/func_gconcat.result > @@ -1103,3 +1103,19 @@ ORDER BY field; > field > c,c > drop table t3, t2, t1; > +# > +# MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd > +# execution of PS > +# > +CREATE TABLE t1(a INT); > +INSERT INTO t1 VALUES(1),(2); > +PREPARE stmt FROM "SELECT GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) FROM t1 AS t1a, t1 AS t1b GROUP BY t1a.a"; > +EXECUTE stmt; > +GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) > +1,1 > +2,2 > +EXECUTE stmt; > +GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) > +1,1 > +2,2 > +DROP TABLE t1; > diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test > index 42a3076..5550eeb 100644 > --- a/mysql-test/t/func_gconcat.test > +++ b/mysql-test/t/func_gconcat.test > @@ -821,3 +821,14 @@ FROM ( SELECT * FROM t2 ) AS sq2, t3 > ORDER BY field; > > drop table t3, t2, t1; > + > +--echo # > +--echo # MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd > +--echo # execution of PS > +--echo # > +CREATE TABLE t1(a INT); > +INSERT INTO t1 VALUES(1),(2); > +PREPARE stmt FROM "SELECT GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) FROM t1 AS t1a, t1 AS t1b GROUP BY t1a.a"; > +EXECUTE stmt; > +EXECUTE stmt; > +DROP TABLE t1; > diff --git a/sql/item_sum.cc b/sql/item_sum.cc > index d8970ca..a24307b 100644 > --- a/sql/item_sum.cc > +++ b/sql/item_sum.cc > @@ -3300,6 +3300,8 @@ void Item_func_group_concat::cleanup() > from Item_func_group_concat::setup() to point to runtime > created objects, we need to reset them back to the original > arguments of the function. > + > + The very same applies to args array. > */ > ORDER **order_ptr= order; > for (uint i= 0; i < arg_count_order; i++) > @@ -3307,6 +3309,7 @@ void Item_func_group_concat::cleanup() > (*order_ptr)->item= &args[arg_count_field + i]; > order_ptr++; > } > + memcpy(args, orig_args, sizeof(Item *) * arg_count); > DBUG_VOID_RETURN; > } > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] [MDEV-8554] Weird behavior of multi-table UPDATE and THD::save_prep_leaf_list
by Sergey Petrunia 30 Jul '15

30 Jul '15

Hello Igor, I was debugging https://mariadb.atlassian.net/browse/MDEV-8554 The patch is about save_prep_leaf_list is not being cleaned up by a statement. I committed a patch that makes that bug go away. However, the way that statements work with THD::save_prep_leaf_list look weird to me. Since it was you who added save_prep_leaf_list, I wanted to ask you: Consider an example: CREATE TABLE t1 (a INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); # Not necessary, the table can be empty CREATE TABLE t2 (b INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (3),(4); # Not necessary, the table can be empty CREATE TABLE t3 (c INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (5),(6); # Not necessary, the table can be empty CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; Now, let's debug how THD::save_prep_leaf_list changes and is used: UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); We see: 1. Breakpoint 4, THD::THD (this=0x59ff6f0) at /home/psergey/dev-git/10.0/sql/sql_class.cc:1062 save_prep_leaf_list= FALSE; 2. Breakpoint 1, THD::reset_for_next_command (this=0x59ff6f0) at /home/psergey/dev-git/10.0/sql/sql_parse.cc:6272 thd->save_prep_leaf_list= false; ^^^ I have added this line in my fix 3. Breakpoint 5, LEX::save_prep_leaf_tables (this=0x5a02f80) at /home/psergey/dev-git/10.0/sql/sql_lex.cc:4132 bool LEX::save_prep_leaf_tables() { if (!thd->save_prep_leaf_list) return FALSE; ^^^ This is where save_prep_leaf_list is used. 4. Breakpoint 2, mysql_derived_merge (thd=0x59ff6f0, lex=0x5a02f80, derived=0x7fff68008618) at /home/psergey/dev-git/10.0/sql/sql_derived.cc:379 if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || thd->lex->sql_command == SQLCOM_DELETE_MULTI) thd->save_prep_leaf_list= TRUE; ^^^ this is where save_prep_leaf_list is changed by this statement. 5. (no break). The statement finished. That is, the statement first checks save_prep_leaf_list, and then uses it. This looks weird. Any thoughts? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] 9f8a458: MDEV-7846: Server crashes in Item_subselect::fix_fields or fails with Thread stack overrun
by Sergei Golubchik 30 Jul '15

30 Jul '15

Hi, Sanja! On Apr 23, sanja(a)mariadb.com wrote: > revision-id: 9f8a458fb2d07298810bb5d9824ce728017bfb32 > parent(s): e540d023e2ec6f37efc9ab695ccdfd4a6744ad64 > committer: Oleksandr Byelkin > branch nick: server > timestamp: 2015-04-23 20:08:57 +0200 > message: > > MDEV-7846: Server crashes in Item_subselect::fix_fields or fails with > Thread stack overrun > > Substitute into transformed subselects original left expression and > than register its change in case it was substituted. Looks mostly ok to me. But please: 1. Add comments (the patch is attached) 2. Add a test case with row items 3. In your test case - if you remove GROUP BY you get a crash again. This might be a different bug, though. Regards, Sergei

1 0

[Maria-developers] mysql_stmt_prepare_start returns with error code 2006
by András Szabó 29 Jul '15

29 Jul '15

Hi Folks, I am currently working on a c++11 asio based async connector for mariadb. The library takes advantage of mariadb’s async interface (mysql_*_start, mysql_*_cont functions). The whole point would be to prevent blocking in mysql operations and make operations cancellable via a reasonable interface. Some example code: TEST_CASE("tmp3") { init_db(); asio::io_service io; amdb::connector conn(io); bool saved = false; entity e(42, 3.14, "our entity"); amdb::auth_info auth("root"); conn.async_connect(amdb::null_endpoint, "edbtest", auth, amdb::default_flags, [&] (const std::error_code& ec) { REQUIRE(!ec); conn.async_write_entity(e, [&] (const std::error_code &ec) { INFO(ec); REQUIRE(!ec); if (!ec) { saved = true; } io.stop(); }); }); io.run(); REQUIRE(saved); } async_write_entity() is an extra feature of the lib, which is generating an sql “batch” of prepared statements to persist hierarchical objects and executes that batch. Unfortunately I am receiving an error 2006 from my very first mysql_stmt_prepare call. This error roughly translates to "connection to mysql server is gone”. I cannot really spot the issue with my operation, could someone give me a hint about this error (typical causes, what to check, etc etc.)? I am a bit clueless here how to tackle this problem. Since the batch generation/execution code is a bit convoluted and complex, i would not paste it here now, but if someone can point me in the right direction i can share relevant parts. Thanks in advance Andras -- András Szabó Sent with Airmail

2 2

Re: [Maria-developers] 4728581: MDEV-7652 - More explanatory ERROR and WARNING messages when loading plugins
by Sergei Golubchik 29 Jul '15

29 Jul '15

Hi, Sergey! Looks ok, just a couple of comments about the messages. If you're agree - please, fix and push. On Jul 28, Sergey Vojtovich wrote: > revision-id: 4728581813000522d05ab589df8e5f8cb6ad1ee6 > parent(s): cf30074c3ff6815d85bbd864b28adfe7949d340c > committer: Sergey Vojtovich > branch nick: mariadb Please, update the git post-commit hook (bzr-pull from mariadb-tools). I've just changed it to report a branch better than just "mariadb". > timestamp: 2015-07-28 10:18:55 +0400 > message: > > MDEV-7652 - More explanatory ERROR and WARNING messages when loading plugins > with plugin-load-add that are already registered at mysql.plugin > > - issue just one error message, without this extra warning > - don't abuse ER_UDF_EXISTS, instead add a proper error message for plugins > - report started initialization for each plugin source > > --- > sql/share/errmsg-utf8.txt | 3 +++ > sql/sql_plugin.cc | 15 +++++++++++---- > 2 files changed, 14 insertions(+), 4 deletions(-) > > diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt > index 6954170..374e31f 100644 > --- a/sql/share/errmsg-utf8.txt > +++ b/sql/share/errmsg-utf8.txt > @@ -7111,3 +7111,6 @@ ER_SLAVE_SKIP_NOT_IN_GTID > eng "When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position." > ER_TABLE_DEFINITION_TOO_BIG > eng "The definition for table %`s is too big" > +ER_PLUGIN_EXISTS > + eng "Plugin '%-.192s' already exists" > + rus "Плагин '%-.192s' уже существует" May be "already installed" ? The command is INSTALL PLUGIN. For UDF it was CREATE FUNCTION, that's why "already exists", I suppose. > diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc > index dc40870..21efeb0 100644 > --- a/sql/sql_plugin.cc > +++ b/sql/sql_plugin.cc > @@ -1546,6 +1546,9 @@ int plugin_init(int *argc, char **argv, int flags) > /* > First we register builtin plugins > */ > + if (global_system_variables.log_warnings >= 9) > + sql_print_information("Initializing mandatory plugins"); > + > for (builtins= mysql_mandatory_plugins; *builtins || mandatory; builtins++) > { > if (!*builtins) Here you should've put "Initializing optional plugins". Actually, a better message would be "Initializing other built-in plugins" or " Initializing remaining built-in plugins". > @@ -1627,11 +1630,17 @@ int plugin_init(int *argc, char **argv, int flags) > { > I_List_iterator<i_string> iter(opt_plugin_load_list); > i_string *item; > + if (global_system_variables.log_warnings >= 9) > + sql_print_information("Initializinig plugins specified by command line"); "Initializinig plugins specified on the command line" > while (NULL != (item= iter++)) > plugin_load_list(&tmp_root, item->ptr); > > if (!(flags & PLUGIN_INIT_SKIP_PLUGIN_TABLE)) > + { > + if (global_system_variables.log_warnings >= 9) > + sql_print_information("Initializing installed plugins"); > plugin_load(&tmp_root); > + } > } Regards, Sergei

2 2

[Maria-developers] Using oqgraph/connect engines in mariadb-galera-server
by Nirbhay Choubey 28 Jul '15

28 Jul '15

Hi Serg, Svoj! I am looking into an issue where accessing oqgraph/connect engines from galera server leads to errors like "Unknown option '<table option>'" and segfaults. The issue being the differences in handlerton structure in galera and non-galera server codes. Since the connect/oqgraph packages are built from non-galera server, when they are loaded into galera server, the engine's handlerton gets improperly initialized. https://mariadb.atlassian.net/browse/MDEV-8240 I can think of the following potential solutions : a) Club connect/oqgraph and mariadb-galera-server into a single package. (this would mean adding new dependencies to mariadb-galera-server) b) New packages for connect/oqgraph out of mariadb-galera build (mariadb-connect-engine-galera-10.0, mariadb-oqgraph-engine-galera-10.0) Do you see any other option? Also, we should add some checks for plugin load to fail in first place in such scenarios. Best, Nirbhay

2 1

[Maria-developers] GTID: On the difference between master_use_gtid = slave_pos or current_pos
by Kristian Nielsen 27 Jul '15

27 Jul '15

I was asked about the difference between master_use_gtid = slave_pos or current_pos. This comes up often, and I thought the below text, which I used to answer the question earlier, might be helpful. Ian, do you think you could incorporate this in some form into the GTID documentation in the knowledgebase? Hope this helps, - Kristian. ----------------------------------------------------------------------- First, consider a slave S2 replicating from a master S1. We stop S2, and then later restart it. Where should it resume replication? Clearly at the point of the last transaction replicated before stopping, which is @@gtid_slave_pos. Now suppose we take down S1 for maintenance and promote S2 as the new master. So we are doing direct transactions on S2. Later we take S1 back up, and now we connect S1 as a slave to S2. Where should S1 start replicating? Clearly at the point of the last transaction in S1's binlog, which is the last transaction made while S1 was the master. This is @@gtid_binlog_pos. Now suppose the user does STOP SLAVE on S1, does a direct transaction on the slave S1, then START SLAVE. That direct transaction was only intended locally, and it is not on the master S2. So when S1 reconnects, it needs to connect at its @@gtid_slave_pos this time, not at the @@gtid_binlog_pos. So we see, only the user knows if a direct transaction made on a server is meant as a cluster-wide transaction, or a local transaction only. And the "correct" behaviour is _different_ depending on this. Normally, direct local transactions in the slave binlog are considered sloppy, because of all the possibilities for errors they introduce. So it is recommended to run slaves in read-only mode, and set @@sql_log_bin=0 if doing local transactions. And gtid_strict_mode=1 is provided to enforce this recommended mode of operation. In this case, user can use current_pos, which will automatically use either @@gtid_slave_pos or @@gtid_binlog_pos, whatever is appropriate. However, some (or many) users like to "be sloppy" and do direct transactions on a slave not intended as master transactions. And for those we provide gtid_strict_mode=0 and master_use_gtid=slave_pos. This makes slave connect always use the position of the last replicated transaction, ignoring any local transactions in the binlog. In this case, when a master is changed to be a slave, it is necessary to manually set @@gtid_slave_pos=@@gtid_binlog_pos. Explicitly marking the updates on the old master to be intended as master transactions, not local transactions. -----------------------------------------------------------------------

2 1

[Maria-developers] PLEASE REVIEW: (MDEV-7574) Security definer views don't work with CONNECT ODBC tables
by Alexander Barkov 26 Jul '15

26 Jul '15

Hello Sergei, Please review a patch that fixes the problem described in MDEV-7574. The idea is that a SELECT from a view over a CONNECT table now checks FILE privileges of the view definer (unless CREATE VIEW states SQL SECURITY INVOKER). It looks like a very good idea and gives more possible security options. The administrator can create a VIEW and give access to it to some user, without giving FILE privilege to this user. I'm not really sure about the patch, but it seems to do the trick :) From what I understood, FILE_ACL is written (among the other privileges) into thd->security_ctx.privilege in TABLE_LIST::prepare_security(). In case of a DEFINER view, thd->security_ctx.privilege is filled exactly with the definer privileges, and to the invoker privileges otherwise. So inside ha_connect::check_privileges() the fact that there is FILE_ACL in thd->security_ctx.privilege means that TABLE_LIST::prepare_security() was previously called and FILE_ACL is set to DEFINER or INVOKER, according to the view definition. This is exactly what we need. I'm not sure about the opposite: if there is no FILE_ACL in thd->security_ctx.privilege, what does it mean? Does it mean that there is no FILE_ACL for the effective user? Or can it also mean that TABLE_LIST::prepare_security() was not called? Thanks. On 02/12/2015 04:04 AM, Olivier Bertrand wrote: > Hi Alexander, > > Can you take care of this issue? I know you wrote the > ha_connect::check_privileges function and I personnally have not the > faintest idea about how this must be checked. > > Thanks, > Olivier > > > -------- Message transféré -------- > Sujet : [JIRA] (MDEV-7574) Security definer views don't work with > CONNECT ODBC tables > Date : Wed, 11 Feb 2015 23:21:00 +0200 (EET) > De : Elena Stepanova (JIRA) <jira(a)mariadb.atlassian.net> > Pour : bertrandop(a)gmail.com > > > > [https://mariadb.atlassian.net/browse/MDEV-7574?page=com.atlassian.jira.plug… ] > > Elena Stepanova reassigned MDEV-7574: > ------------------------------------- > > Assignee: Olivier Bertrand > Fix Version/s: 10.0 > >> Security definer views don't work with CONNECT ODBC tables >> ---------------------------------------------------------- >> >> Key: MDEV-7574 >> URL:https://mariadb.atlassian.net/browse/MDEV-7574 >> Project: MariaDB Server >> Issue Type: Bug >> Components: Storage Engine - Connect >> Affects Versions: 10.0.16 >> Reporter: Geoff Montee >> Assignee: Olivier Bertrand >> Labels: connect-engine >> Fix For: 10.0 >> >> >> One possible way to get around the requirement for having the FILE privilege to access ODBC tables with CONNECT would be to have them called indirectly via a security definer view. However, it does not currently work. >> Create a security definer view to access the ODBC table, then create a new user: >> {code} >> [gmontee@localhost ~]$ mysql -u root tmp >> Reading table information for completion of table and column names >> You can turn off this feature to get a quicker startup with -A >> Welcome to the MariaDB monitor. Commands end with ; or \g. >> Your MariaDB connection id is 16 >> Server version: 10.0.15-MariaDB-log MariaDB Server >> Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. >> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. >> MariaDB [tmp]> SHOW CREATE TABLE datetime_table; >> +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >> | Table | Create Table | >> +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >> | datetime_table | CREATE TABLE `datetime_table` ( >> `id` int(10) NOT NULL, >> `modifiedon` datetime DEFAULT NULL >> ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=connect_test_azure;UID=connect_test;PWD=Password1' `TABLE_TYPE`='ODBC' `TABNAME`='dbo.datetime_table' | >> +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >> 1 row in set (0.00 sec) >> MariaDB [tmp]> DROP USER 'connecttest'@'localhost'; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> CREATE OR REPLACE >> -> DEFINER = CURRENT_USER >> -> SQL SECURITY DEFINER >> -> VIEW datetime_view >> -> AS SELECT * FROM datetime_table; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> CREATE USER 'connecttest'@'localhost'; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> GRANT SELECT ON datetime_view TO 'connecttest'@'localhost'; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> \q >> Bye >> {code} >> Now connect with the new user, and try to use the view: >> {code} >> [gmontee@localhost ~]$ mysql -u connecttest tmp >> Reading table information for completion of table and column names >> You can turn off this feature to get a quicker startup with -A >> Welcome to the MariaDB monitor. Commands end with ; or \g. >> Your MariaDB connection id is 17 >> Server version: 10.0.15-MariaDB-log MariaDB Server >> Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. >> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. >> MariaDB [tmp]> SELECT * FROM datetime_view; >> ERROR 1045 (28000): Access denied for user 'connecttest'@'localhost' (using password: NO) >> MariaDB [tmp]> \q >> Bye >> {code} >> It didn't work, so give the user privileges on the underlying ODBC table: >> {code} >> [gmontee@localhost ~]$ mysql -u root tmp >> Reading table information for completion of table and column names >> You can turn off this feature to get a quicker startup with -A >> Welcome to the MariaDB monitor. Commands end with ; or \g. >> Your MariaDB connection id is 18 >> Server version: 10.0.15-MariaDB-log MariaDB Server >> Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. >> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. >> MariaDB [tmp]> GRANT FILE ON *.* TO 'connecttest'@'localhost'; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> GRANT SELECT ON datetime_table TO 'connecttest'@'localhost'; >> Query OK, 0 rows affected (0.00 sec) >> MariaDB [tmp]> \q >> Bye >> {code} >> Now try using the view again: >> {code} >> [gmontee@localhost ~]$ mysql -u connecttest tmp >> Reading table information for completion of table and column names >> You can turn off this feature to get a quicker startup with -A >> Welcome to the MariaDB monitor. Commands end with ; or \g. >> Your MariaDB connection id is 19 >> Server version: 10.0.15-MariaDB-log MariaDB Server >> Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. >> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. >> MariaDB [tmp]> SELECT * FROM datetime_view; >> +----+---------------------+ >> | id | modifiedon | >> +----+---------------------+ >> | 1 | 2014-01-01 00:00:00 | >> | 2 | 2016-01-01 00:00:00 | >> +----+---------------------+ >> 2 rows in set (0.24 sec) >> {code} > > > > -- > This message was sent by Atlassian JIRA > (v6.4-OD-14-082#64012) > > >

2 9