- developers - lists.mariadb.org

[Maria-developers] Blackhole, Archive and others only as loadable plugins in 10.1 - intentional change?
by Hartmut Holzgraefe 15 Feb '17

15 Feb '17

In MariaDB 10.1 binary packages several storage engines, e.g BLACKHOLE and ARCHIVE, are no longer compiled in by default but only come as loadable plugins that need to be loaded into the server explicitly. This is mentioned on the knowledge base pages for these engines, e.g. https://mariadb.com/kb/en/mariadb/blackhole/ and https://mariadb.com/kb/en/mariadb/archive/ but is not mentioned in the release notes and the detailed changelog pages for 10.1.x releases at all. I also searched my "maria-developers" and "maria-discuss" mail archives but could not find any discussion or announcement on this topic either. Same for Jira, can't find any ticket that would describe that sort of change there either. Problems with the current situation: * comes as a surprise for installation using these storage engines when upgraded to 10.1 * mysql_upgrade should handle this, but doesn't: https://jira.mariadb.org/browse/MDEV-11942 * it is not even easily possible to identify affected tables after an upgrade to 10.1. For BLACKHOLE tables I_S.TABLES only shows these under specific conditions, and for ARCHIVE it seems to be impossible to identify affected tables at all if the plugin is not loaded https://jira.mariadb.org/browse/MDEV-11943 I have submitted a a fix for MDEV-11942, but without having a fix for MDEV-11943 it only works for BLACKHOLE so far, not for ARCHIVE (haven't tested FEDERATED or other potentially affected engines yet). https://github.com/MariaDB/server/pull/304 -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/

2 1

[Maria-developers] Fwd: 41f885f64fe: MDEV-10859: Wrong result of aggregate window function in query with HAVING and no ORDER BY
by Vicențiu Ciorbaru 14 Feb '17

14 Feb '17

Hi Igor, Sergey! I need a review on this patch that fixes a problem with Window Functions being computed over too many rows. The details are in the commit message. Thank you! Vicențiu ---------- Forwarded message --------- From: vicentiu <vicentiu(a)mariadb.org> Date: Tue, 14 Feb 2017 at 14:08 Subject: 41f885f64fe: MDEV-10859: Wrong result of aggregate window function in query with HAVING and no ORDER BY To: <commits(a)mariadb.org> revision-id: 41f885f64fe4c02e03ec655f225b1dc01e9d5376 (mariadb-10.2.3-252-g41f885f64fe) parent(s): d731ce21a7bc25a49958789d24b3f0eec5845aea author: Vicențiu Ciorbaru committer: Vicențiu Ciorbaru timestamp: 2017-02-14 14:02:29 +0200 message: MDEV-10859: Wrong result of aggregate window function in query with HAVING and no ORDER BY Window functions need to be computed after applying the HAVING clause. An optimization that we have for regular, non-window function, cases is to apply having only during sending of the rows to the client. This allows rows that should be filtered from the temporary table used to store aggregation results to be stored there. This behaviour is undesireable for window functions, as we have to compute window functions on the result-set after HAVING is applied. Storing extra rows in the table leads to wrong values as the frame bounds might capture those -to be filtered afterwards- rows. --- mysql-test/r/win.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 23 +++++++++++++++++++++++ sql/sql_select.cc | 11 ++++++++--- sql/sql_window.cc | 6 ++++++ 4 files changed, 70 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index fd3aea80083..b3adf358be6 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -2925,3 +2925,36 @@ WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) ); a MAX(a) AVG(a) OVER (PARTITION BY b) NULL NULL NULL DROP TABLE t1,t2,t3; +# +# MDEV-MDEV-10859: Wrong result of aggregate window function in query +# with HAVING and no ORDER BY +# +create table empsalary (depname varchar(32), empno smallint primary key, salary int); +insert into empsalary values +('develop', 1, 5000), ('develop', 2, 4000),('sales', 3, '6000'),('sales', 4, 5000); +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; +depname empno salary avg(salary) OVER (PARTITION BY depname) +develop 1 5000 4500.0000 +develop 2 4000 4500.0000 +sales 3 6000 5500.0000 +sales 4 5000 5500.0000 +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname; +depname empno salary avg(salary) OVER (PARTITION BY depname) +develop 1 5000 4500.0000 +develop 2 4000 4500.0000 +sales 3 6000 5500.0000 +sales 4 5000 5500.0000 +# +# These last 2 should have the same row results, ignoring order. +# +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1; +depname empno salary avg(salary) OVER (PARTITION BY depname) +develop 2 4000 4000.0000 +sales 3 6000 5500.0000 +sales 4 5000 5500.0000 +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1 ORDER BY depname; +depname empno salary avg(salary) OVER (PARTITION BY depname) +develop 2 4000 4000.0000 +sales 3 6000 5500.0000 +sales 4 5000 5500.0000 +drop table empsalary; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index aa636f7a294..90bbf41688f 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1711,3 +1711,26 @@ SELECT a, MAX(a), AVG(a) OVER (PARTITION BY b) FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) ); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-MDEV-10859: Wrong result of aggregate window function in query +--echo # with HAVING and no ORDER BY +--echo # + +create table empsalary (depname varchar(32), empno smallint primary key, salary int); +insert into empsalary values + ('develop', 1, 5000), ('develop', 2, 4000),('sales', 3, '6000'),('sales', 4, 5000); + +--sorted_result +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; +--sorted_result +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname; +--echo # +--echo # These last 2 should have the same row results, ignoring order. +--echo # +--sorted_result +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1; +--sorted_result +SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1 ORDER BY depname; + +drop table empsalary; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5c7ae1e88c1..473270f8a69 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2411,13 +2411,18 @@ bool JOIN::make_aggr_tables_info() If having is not handled here, it will be checked before the row is sent to the client. + + In the case of window functions however, we *must* make sure to not + store any rows which don't match HAVING within the temp table, + as rows will end up being used during their computation. */ if (having && - (sort_and_group || (exec_tmp_table->distinct && !group_list))) + (sort_and_group || (exec_tmp_table->distinct && !group_list) || + select_lex->have_window_funcs())) { - // Attach HAVING to tmp table's condition + /* Attach HAVING to tmp table's condition */ curr_tab->having= having; - having= NULL; // Already done + having= NULL; /* Already done */ } /* Change sum_fields reference to calculated fields in tmp_table */ diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 37095ad78ca..2bdac89f293 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -2840,6 +2840,12 @@ bool Window_funcs_computation::setup(THD *thd, order_window_funcs_by_window_specs(window_funcs); SQL_SELECT *sel= NULL; + /* + If the tmp table is filtered during sorting + (ex: SELECT with HAVING && ORDER BY), we must make sure to keep the + filtering conditions when we perform sorting for window function + computation. + */ if (tab->filesort && tab->filesort->select) { sel= tab->filesort->select;

1 0

[Maria-developers] bb-10.2-compatibility - MDEV 10585 or 10866
by jerome brauge 14 Feb '17

14 Feb '17

Hello Alexander, Attached script fail on my local bb-10.2-compatibility branch on the following assert : /* If called from a stored procedure, ensure that we won't rollback external changes when cleaning up after validation. */ DBUG_ASSERT(thd->change_list.is_empty()); Stack trace : mysqld.exe!my_sigabrt_handler(int sig) Line 477 C mysqld.exe!raise(int signum) Line 516 C++ mysqld.exe!abort() Line 64 C++ mysqld.exe!common_assert_to_stderr_direct(const wchar_t * const expression, const wchar_t * const file_name, const unsigned int line_number) Line 124 C++ mysqld.exe!common_assert_to_stderr<wchar_t>(const wchar_t * const expression, const wchar_t * const file_name, const unsigned int line_number) Line 138 C++ mysqld.exe!common_assert<wchar_t>(const wchar_t * const expression, const wchar_t * const file_name, const unsigned int line_number, void * const return_address) Line 383 C++ mysqld.exe!_wassert(const wchar_t * expression, const wchar_t * file_name, unsigned int line_number) Line 404 C++ > mysqld.exe!Prepared_statement::prepare(const char * packet, unsigned int packet_len) Line 3848 C++ mysqld.exe!Prepared_statement::execute_immediate(const char * query, unsigned int query_len) Line 4740 C++ mysqld.exe!mysql_sql_stmt_execute_immediate(THD * thd) Line 2863 C++ mysqld.exe!mysql_execute_command(THD * thd) Line 3722 C++ mysqld.exe!sp_instr_stmt::exec_core(THD * thd, unsigned int * nextp) Line 3335 C++ mysqld.exe!sp_lex_keeper::reset_lex_and_exec_core(THD * thd, unsigned int * nextp, bool open_tables, sp_instr * instr) Line 3098 C++ mysqld.exe!sp_instr_stmt::execute(THD * thd, unsigned int * nextp) Line 3251 C++ mysqld.exe!sp_head::execute(THD * thd, bool merge_da_on_success) Line 1265 C++ mysqld.exe!sp_head::execute_procedure(THD * thd, List<Item> * args) Line 2090 C++ mysqld.exe!do_execute_sp(THD * thd, sp_head * sp) Line 2871 C++ mysqld.exe!mysql_execute_command(THD * thd) Line 5900 C++ mysqld.exe!mysql_parse(THD * thd, char * rawbuf, unsigned int length, Parser_state * parser_state, bool is_com_multi, bool is_next_command) Line 7990 C++ mysqld.exe!dispatch_command(enum_server_command command, THD * thd, char * packet, unsigned int packet_length, bool is_com_multi, bool is_next_command) Line 1809 C++ mysqld.exe!do_command(THD * thd) Line 1367 C++ mysqld.exe!threadpool_process_request(THD * thd) Line 346 C++ mysqld.exe!tp_callback(TP_connection * c) Line 192 C++ mysqld.exe!tp_callback(_TP_CALLBACK_INSTANCE * instance, void * context) Line 377 C++ mysqld.exe!work_callback(_TP_CALLBACK_INSTANCE * instance, void * context, _TP_WORK * work) Line 451 C++ Can you reproduce this error ? Regards, Jérôme.

2 1

Re: [Maria-developers] [Commits] 822aa21672b: MDEV-11170: MariaDB 10.2 cannot start on MySQL 5.7 datadir
by Sergei Golubchik 13 Feb '17

13 Feb '17

Hi, Vicentiu! Looks fine, thanks. Few comments below. On Feb 13, vicentiu wrote: > revision-id: 822aa21672ba6650a31a3ec05ab5c1bce7a80c1e (mariadb-10.2.3-198-g822aa21672b) > parent(s): b745ea489f0698c328383d9b8ec20d2f9777b1b8 > author: Vicențiu Ciorbaru > committer: Vicențiu Ciorbaru > timestamp: 2017-02-13 14:44:00 +0200 > message: > > MDEV-11170: MariaDB 10.2 cannot start on MySQL 5.7 datadir > > PART 2 of the fix adds the logic of not using password column, unless it > exists. If password column is missing we attempt to use plugin && > authentication_string columns. > > diff --git a/mysql-test/r/no_password_column-mdev-11170.result b/mysql-test/r/no_password_column-mdev-11170.result > new file mode 100644 > index 00000000000..c3920a1b530 > --- /dev/null > +++ b/mysql-test/r/no_password_column-mdev-11170.result > @@ -0,0 +1,170 @@ ... > +# Reset to final original state. > +# > +drop table mysql.user; > +create table mysql.user like backup_user; > +insert into mysql.user select * from backup_user; you could RENAME TABLE backup_user to mysql.user. > +drop table backup_user; > +flush privileges; > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 568aff9ac92..7e03253ca98 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -2071,7 +2103,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) > 8, 8, MYF(0)); > > /* check default role, if any */ > - if (!is_role && user_table.num_fields() > DEFAULT_ROLE_COLUMN_IDX) > + if (!is_role && user_table.default_role()) all these changes could've went into the first patch... > { > user.default_rolename.str= > get_field(&acl_memroot, user_table.default_role()); > @@ -4030,11 +4092,16 @@ static int replace_user_table(THD *thd, const User_table &user_table, > > rights= user_table.get_access(); > > - DBUG_PRINT("info",("table fields: %d",user_table.num_fields())); > - if (combo.pwhash.str[0]) > + DBUG_PRINT("info",("table fields: %d", user_table.num_fields())); > + /* If we don't have a password column, we'll use the authentication_string > + column later. */ > + if (combo.pwhash.str[0] && user_table.password()) > user_table.password()->store(combo.pwhash.str, combo.pwhash.length, > system_charset_info); > - if (user_table.num_fields() >= 31) /* From 4.0.0 we have more fields */ > + /* We either have the password column, the plugin column, or both. Otherwise > + we have a corrupt user table. */ > + DBUG_ASSERT(user_table.password() || user_table.plugin()); you cannot assert the table structure. ALTER TABLE shouldn't crash the server. > + if (user_table.ssl_type()) /* From 4.0.0 we have more fields */ > { > /* We write down SSL related ACL stuff */ > switch (lex->ssl_type) { Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

Re: [Maria-developers] b745ea489f0: MDEV-11170: MariaDB 10.2 cannot start on MySQL 5.7 datadir
by Sergei Golubchik 13 Feb '17

13 Feb '17

Hi, Vicentiu! Pretty nice. I like the how it looks now. Thanks! Few small comments below, really minor. On Feb 13, vicentiu wrote: > revision-id: b745ea489f0698c328383d9b8ec20d2f9777b1b8 (mariadb-10.2.3-197-gb745ea489f0) > parent(s): 52e0b585aaedd0f8f7191e0c0768d4be3c1c3496 > author: Vicențiu Ciorbaru > committer: Vicențiu Ciorbaru > timestamp: 2017-02-13 12:13:36 +0200 > message: > > MDEV-11170: MariaDB 10.2 cannot start on MySQL 5.7 datadir > > PART 1 of the fix requires a bit of refactoring to not use hard-coded > field indices any more. Create classes that express the grant tables structure, > without exposing the underlying field indices. > > Most of the code is converted to use these classes, except parts which > are not directly affected by the MDEV-11170. These however are TODO > items for subsequent refactoring. > > --- > sql/sql_acl.cc | 1457 ++++++++++++++++++++++++++++++++++++++------------------ > sql/structs.h | 2 +- > 2 files changed, 996 insertions(+), 463 deletions(-) > > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index b8a06c22b62..568aff9ac92 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > +#ifdef HAVE_REPLICATION > + if (lock_type >= TL_WRITE_ALLOW_WRITE && thd->slave_thread && !thd->spcont) > + { > + /* > + GRANT and REVOKE are applied the slave in/exclusion rules as they are > + some kind of updates to the mysql.% tables. > + */ > + // TODO(remove-after-review) Note: there was a bug here in previous > + // open_grant_tables version. The linking of tables to be opened was > + // done according to a mask, starting from the end of the TABLES_LIST[TABLES_MAX] > + // array. rpl_filter->tables_ok(0, tables) would always start from > + // TABLES_LIST[0] and lookup the chain from there. If we would not open > + // the User table (first table in the array) > + // (as it happened in grant_reload()), the chain would stop there, hence > + // the rpl_filter check would not lookup all the tables opened I see. Right, it's a bug. > + // > + // This is the reason for the first_table_in_list member variable. > + Rpl_filter *rpl_filter= thd->system_thread_info.rpl_sql_info->rpl_filter; > + if (rpl_filter->is_on() && > + !rpl_filter->tables_ok(0, &first_table_in_list->tl)) > + DBUG_RETURN(1); > + } > +#endif > + if (open_and_lock_tables(thd, &first_table_in_list->tl, FALSE, > + MYSQL_LOCK_IGNORE_TIMEOUT)) > + DBUG_RETURN(-1); > + > + /* The privilge columns vary based on MariaDB version. Figure out > + how many we have after we've opened the table. */ > + if (which_tables & Table_user) > + m_user_table.compute_num_privilege_cols(); > + if (which_tables & Table_db) > + m_db_table.compute_num_privilege_cols(); > + if (which_tables & Table_tables_priv) > + m_tables_priv_table.compute_num_privilege_cols(); > + if (which_tables & Table_columns_priv) > + m_columns_priv_table.compute_num_privilege_cols(); > + if (which_tables & Table_host) > + m_host_table.compute_num_privilege_cols(); > + if (which_tables & Table_procs_priv) > + m_procs_priv_table.compute_num_privilege_cols(); > + if (which_tables & Table_proxies_priv) > + m_proxies_priv_table.compute_num_privilege_cols(); > + if (which_tables & Table_roles_mapping) > + m_roles_mapping_table.compute_num_privilege_cols(); couldn't you just walk the list (from first_table_in_list) and do compute_num_privilege_cols() on every table? Like ((Grant_tables*)tl)->compute_num_privilege_cols(); > + DBUG_RETURN(0); > + } > + > + inline const User_table& user_table() const > + { > + return m_user_table; > + } I personally would just make user_table/etc members public, instead of creating accessors like that. > + > + inline const Db_table& db_table() const > + { > + return m_db_table; > + } > + > + > + inline const Tables_priv_table& tables_priv_table() const > + { > + return m_tables_priv_table; > + } > + > + inline const Columns_priv_table& columns_priv_table() const > + { > + return m_columns_priv_table; > + } > + > + inline const Host_table& host_table() const > + { > + return m_host_table; > + } > + > + inline const Procs_priv_table& procs_priv_table() const > + { > + return m_procs_priv_table; > + } > + > + inline const Proxies_priv_table& proxies_priv_table() const > + { > + return m_proxies_priv_table; > + } > + > + inline const Roles_mapping_table& roles_mapping_table() const > + { > + return m_roles_mapping_table; > + } > + > + private: > + User_table m_user_table; > + Db_table m_db_table; > + Tables_priv_table m_tables_priv_table; > + Columns_priv_table m_columns_priv_table; > + Host_table m_host_table; > + Procs_priv_table m_procs_priv_table; > + Proxies_priv_table m_proxies_priv_table; > + Roles_mapping_table m_roles_mapping_table; > + > + /* Bitmap of which tables we want to open. */ > + const int which_tables; you don't need it if you walk the list in open_and_lock() > + /* Type of lock we want to acquire for the tables we are oppening. */ > + const enum thr_lock_type lock_type; not needed here, it's stored in every TABLE_LIST anyway > + /* The grant tables are set-up in a linked list. We keep the head of it. */ > + Grant_table_base *first_table_in_list; > + /** > + Chain two grant tables' TABLE_LIST members. > + */ > + static void link_tables(Grant_table_base *from, Grant_table_base *to) > + { > + DBUG_ASSERT(from); > + if (to) > + from->tl.next_local= from->tl.next_global= &to->tl; > + else > + from->tl.next_local= from->tl.next_global= NULL; > + } > +}; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] Server crashes in inline_mysql_mutex_lock
by Michael Widenius 10 Feb '17

10 Feb '17

Hi! > Thread 1 (Thread 0x7fa0f7549700 (LWP 21346)): > #0 0x00007fa0f9511101 in __pthread_kill (threadid=<optimized out>, > signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61 > #1 0x00000000013f4d28 in my_write_core (sig=11) at > /data/src/bb-10.0-monty/mysys/stacktrace.c:478 > #2 0x00000000009f6fc3 in handle_fatal_signal (sig=11) at > /data/src/bb-10.0-monty/sql/signal_handler.cc:285 > #3 <signal handler called> > #4 0x000000000093123c in inline_mysql_mutex_lock (that=0x0, > src_file=0x14df108 "/data/src/bb-10.0-monty/sql/rpl_parallel.cc", > src_line=1278) at > /data/src/bb-10.0-monty/include/mysql/psi/mysql_thread.h:662 > #5 0x0000000000936842 in handle_rpl_parallel_thread (arg=0x7fa0df2d3ee8) at > /data/src/bb-10.0-monty/sql/rpl_parallel.cc:1278 > #6 0x00007fa0f950c0a4 in start_thread (arg=0x7fa0f7549700) at > pthread_create.c:309 > #7 0x00007fa0f76c487d in clone () at > ../sysdeps/unix/sysv/linux/x86_64/clone.S:111 The problem is flush tables with read locks combined with a stop slave of the only replication thread that forces a resize of thread pool. This can get the replication thread into a wrong state where it tries to delete itself before waiting for the flush tables, which the thread pool doesn't like. Fixed by adding a check that we are not in flush tables with read lock when we delete ourselves from the pool. Regards, Monty

1 0

Re: [Maria-developers] [Commits] fb49b98: MDEV-10436 non-deterministic vcol does not force rbr
by Sergei Golubchik 10 Feb '17

10 Feb '17

Hi, Sachin! On Jan 24, sachin wrote: > revision-id: fb49b98a85dc28846c1fdd893ff9791803873aad (mariadb-10.2.3-91-gfb49b98) > parent(s): 0d107a85b3dd6969e66cc9cb4bd29e1cc92a7d18 > author: Sachin Setiya > committer: Sachin Setiya > timestamp: 2017-01-24 17:58:23 +0530 > message: > > MDEV-10436 non-deterministic vcol does not force rbr > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index f78382d..50d767d 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -5824,6 +5826,16 @@ int THD::decide_logging_format(TABLE_LIST *tables) > table->table->file->ht) > multi_access_engine= TRUE; > > + /* > + Loop through each table field if it is virtual field check if it is deterministic > + or not. > + */ > + for (field_ptr= table->table->field; *field_ptr; field_ptr++) > + { > + field= *field_ptr; > + if (field->vcol_info && field->vcol_info->flags & VCOL_NOT_STRICTLY_DETERMINISTIC) > + lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_NON_DETER_VCOL); > + } > prev_access_table= table->table; > } 1. don't iterate table->table->field, use table->table->vfield - it only contains generated columns. 2. your check is too strong, in INSERT t1 VALUES (...) it does not matter if some of virtual columns are not strictly deterministic, so BINLOG_STMT_UNSAFE_NON_DETER_VCOL should not be raised. It only should be used when such a table is selected from (or in a WHERE condition of an UPDATE or DELETE). And only when such a column is used in a statement. There is nothing non-deterministing in INSERT t1 SELECT 1 FROM vcol_table 3. It might be not possible to do a per-column check, if read_set bitmaps are not ready yet. In that case, ok, do it per table, but then the result for the table is always the same, I mean where a table has not strictly deterministic columns is a property of a table. So, don't calculate it every time here, do it once and store in the TABLE_SHARE structure. Much like non_determinstic_insert is. But I'm removing non_determinstic_insert now (it's always false, dead code) so add another flag. Or - better - add a 'uint vcol_flags' member which is OR of vcol_info->flag for all fields. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

[Maria-developers] Doublewrite block number in XtraDB
by Hank Lyu 10 Feb '17

10 Feb '17

Hello: In XtraDB, if we adjust the size of doublewrite buffer and relative variable (i.e. srv_doublewrite_batch_size) , in theory, we can get better throughput when buffer pool flush to disk. I wonder that why doublewrite buffer size is 2 block and each flush page number is 120 (decided by srv_doublewrite_batch_size), instead doublewrite size is 8 and each flush page number is 500 or more? Is it worry about that when doing flush will occupy too much resource or other reason? Best Regard Hank Lyu

3 4

Re: [Maria-developers] [Commits] 15fde78: MDEV-10731: Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242)
by Sergey Petrunia 09 Feb '17

09 Feb '17

Hi Varun, On Thu, Feb 09, 2017 at 10:37:17PM +0530, Varun wrote: > revision-id: 15fde78ce812c8db91340936941b986fb28ce1ad (mariadb-10.1.20-82-g15fde78) > parent(s): 99b2de92c6214ddd73eba35c935f490eedf87a26 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-09 22:36:03 +0530 > message: > > MDEV-10731: Wrong NULL match results in "Subquery returns more than 1 row" (error code 1242) > > Post review fixes As far as I understand it's not Post-review fixes, it's a complete patch. Please use an appropriate comment. > > --- > mysql-test/r/update_innodb.result | 26 ++++++++++++++++++++++++++ > mysql-test/t/update_innodb.test | 26 ++++++++++++++++++++++++++ > sql/sql_select.cc | 19 +++++++++++-------- > 3 files changed, 63 insertions(+), 8 deletions(-) > > diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result > index 88c86c5..9ba6699 100644 > --- a/mysql-test/r/update_innodb.result > +++ b/mysql-test/r/update_innodb.result > @@ -29,3 +29,29 @@ CREATE ALGORITHM=UNDEFINED VIEW `v1` AS select `t4`.`c1` AS `c1`,`t4`.`c2` AS `c > UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON vw.c2 = del.c2 SET a.c2 = ( SELECT max(t.c1) FROM t3 t, v1 i WHERE del.c2 = t.c2 AND vw.c3 = i.c3 AND t.c3 = 4 ) WHERE a.c2 IS NULL OR a.c2 < '2011-05-01'; > drop view v1; > drop table t1,t2,t3,t4; > +# > +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt > +# > +CREATE TABLE t1 ( > +a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, > +b_id INT(20) UNSIGNED NULL DEFAULT NULL, > +c_id VARCHAR(255) NULL DEFAULT NULL, > +PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; > +CREATE TABLE t2 ( > +b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, > +c_id VARCHAR(255) NULL DEFAULT NULL, > +PRIMARY KEY (b_id), > +INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; > +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); > +INSERT INTO t2 (c_id) VALUES (NULL); > +INSERT INTO t2 (c_id) VALUES (NULL); > +SELECT * FROM T1; > +a_id b_id c_id > +1 NULL NULL > +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; > +b_id > +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); > +SELECT * FROM T1; > +a_id b_id c_id > +1 NULL NULL > +drop table t1,t2; > diff --git a/mysql-test/t/update_innodb.test b/mysql-test/t/update_innodb.test > index 67c356c..d4fb8b7 100644 > --- a/mysql-test/t/update_innodb.test > +++ b/mysql-test/t/update_innodb.test > @@ -37,3 +37,29 @@ UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON v > > drop view v1; > drop table t1,t2,t3,t4; > + > +--echo # > +--echo # MDEV-10232 Scalar result of subquery changes after adding an outer select stmt > +--echo # > + > +CREATE TABLE t1 ( > + a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, > + b_id INT(20) UNSIGNED NULL DEFAULT NULL, > + c_id VARCHAR(255) NULL DEFAULT NULL, > + PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; > + > +CREATE TABLE t2 ( > + b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, > + c_id VARCHAR(255) NULL DEFAULT NULL, > + PRIMARY KEY (b_id), > + INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; > + > +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); > +INSERT INTO t2 (c_id) VALUES (NULL); > +INSERT INTO t2 (c_id) VALUES (NULL); > + > +SELECT * FROM T1; > +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; > +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); > +SELECT * FROM T1; > +drop table t1,t2; > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index e37c1b8..bc9a80f 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -9367,8 +9367,6 @@ static void add_not_null_conds(JOIN *join) > UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); > not_null_item is the t1.f1, but it's referred_tab is 0. > */ > - if (!referred_tab) > - continue; > if (!(notnull= new (join->thd->mem_root) > Item_func_isnotnull(join->thd, item))) > DBUG_VOID_RETURN; > @@ -9380,16 +9378,21 @@ static void add_not_null_conds(JOIN *join) > */ > if (notnull->fix_fields(join->thd, &notnull)) > DBUG_VOID_RETURN; > - DBUG_EXECUTE("where",print_where(notnull, > - referred_tab->table->alias.c_ptr(), > - QT_ORDINARY);); > + > + if(referred_tab) > + DBUG_EXECUTE("where",print_where(notnull, > + referred_tab->table->alias.c_ptr(), > + QT_ORDINARY);); > + else > + DBUG_EXECUTE("where",print_where(notnull, > + "outer_ref_cond",QT_ORDINARY);); Please take the re-factoring further and have one DBUG_EXECUTE call with referred_tab? referred_tab->table->alias.c_ptr(): "outer_ref_cond" expression as the parameter. > if (!tab->first_inner) > - { > - COND *new_cond= referred_tab->join == join ? > + { Does the above break indentation or it has a tab character? > + COND *new_cond= (referred_tab && referred_tab->join == join) ? > referred_tab->select_cond : > join->outer_ref_cond; > add_cond_and_fix(join->thd, &new_cond, notnull); > - if (referred_tab->join == join) > + if (referred_tab && referred_tab->join == join) > referred_tab->set_select_cond(new_cond, __LINE__); > else > join->outer_ref_cond= new_cond; OK to push after the above is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] bb-10.2-compatibility
by jerome brauge 09 Feb '17

09 Feb '17

Hello, I come back with a question on functions. On Oracle, there is only one difference between a function and a stored procedure : the return code. On Mariadb, stored functions are more limited : - Recursive stored functions are not allowed - Explicit or implicit commit is not allowed in stored function - in out parameter are not allowed (MDEV-10654) - no dynamic sql (execute immediate) Have you planned to remove these limits? If not, we'll have to find the best workarounds to use stored procedures (and find a solution to mimic a return code) Best regards, Jérôme.

2 3