July 2015 - developers - lists.mariadb.org

[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

Re: [Maria-developers] [Commits] 0b82e6d: MDEV-8399 - [PATCH] Missing Sanity Checks for memory allocation in MariaDB
by Sergei Golubchik 23 Jul '15

23 Jul '15

Hi, Sergey! On Jul 16, Sergey Vojtovich wrote: > revision-id: 0b82e6d85a18fb5fa9cc54ad7a854cb2eecfce03 > parent(s): 94bc50650eb490e8d844acfc700f75992857509a > committer: Sergey Vojtovich > branch nick: mariadb > timestamp: 2015-07-16 16:20:17 +0400 > message: > > MDEV-8399 - [PATCH] Missing Sanity Checks for memory allocation in MariaDB > > - since param is quite small store it on stack > - fixed a few memory leaks I'm not going to review that, I don't care about missing sanity checks or memory leaks in mysys test programs :) Do whatever you want with it (it = the code inside #ifdef MAIN ... #endif) Regards, Sergei

1 0

Re: [Maria-developers] [Commits] a6ab8ef: MDEV-5997 - MySQL bug#11759114 - '51401: GRANT TREATS NONEXISTENT
by Sergei Golubchik 23 Jul '15

23 Jul '15

Hi, Sergey! On Jul 23, Sergey Vojtovich wrote: > revision-id: a6ab8ef9d7d465a3f0bc1cff6034ca97575629dd > parent(s): d897015d74d82464a21ed154f606adc69503c372 > committer: Sergey Vojtovich > branch nick: mariadb > timestamp: 2015-07-23 16:45:20 +0400 > message: > > MDEV-5997 - MySQL bug#11759114 - '51401: GRANT TREATS NONEXISTENT > FUNCTIONS/PRIVILEGES DIFFERENTLY' ok to push Regards, Sergei

1 0

Re: [Maria-developers] [Commits] 74b4ead: MDEV-5096 - Wrong error message on attempt to kill somebody else's query ID
by Sergei Golubchik 23 Jul '15

23 Jul '15

Hi, Sergey! On Feb 09, svoj(a)mariadb.org wrote: > revision-id: 74b4ead8ef32eb2bb96b17dafac514ffba28963a > parent(s): f13939061d468a47985dee0268652a6fe2db7862 > committer: Sergey Vojtovich > branch nick: 10.1 > timestamp: 2015-02-09 13:41:24 +0400 > message: > > MDEV-5096 - Wrong error message on attempt to kill somebody else's query ID > > Attempting to kill query owned by different user now returns better error: > "You are not owner of query N" instead of "You are not owner of thread N". Ok to push! Nice test. Regards, Sergei

1 0

[Maria-developers] MariaDB parallel replication
by Kristian Nielsen 18 Jul '15

18 Jul '15

Some interesting work is starting to be done by users to test parallel replication on real workloads. One issue that comes up is the performance on workloads that have a relatively high number of lock conflicts between transactions. I made some patches (against latest 10.1) to help investigate and improve the issues, and I wanted to mention them here in case someone wants to comment on or experiment with them: 1. Adding status variables for parallel replication. http://lists.askmonty.org/pipermail/commits/2015-July/008131.html This patch adds new status variables that measure the time spent by parallel replication worker threads being - Idle (waiting for work from the SQL thread). - Processing relay log events. - Waiting for a prior group commit to finish (measuring the overhead of insufficient parallelism recorded on the master, in conservative mode, or the overhead of serialisation around DDL, in optimistic/aggressive modes). - Waiting for the immediately prior transaction to commit (measuring the overhead of in-order parallel replication). - Rolling back and re-executing events due to deadlocks. It would be interesting to see how the different numbers compare on various workloads and parallel replication modes. 2. More aggressive retry of conflicting transactions. http://lists.askmonty.org/pipermail/commits/2015-July/008133.html When we get a deadlock and a transaction retry, the current MariaDB waits for all prior transactions to commit before retrying. The logic is that since we already got a conflict, there is a high risk that an immediate retry will just give another conflict. So if we have T1 T2 T3 T4, and T4 conflicts with T1, we roll back T4, wait for T3 to commit, and only then retry T4. If we have many such conflicts, we could end up wasting a lot of times on such waits. This patch changes aggressive mode so that T4 will only wait for T1 to commit, then it will retry. This allows T4 to run in parallel with T2 and T3. It will be interesting to see if this improves throughput in aggressive mode in some workloads, and also to see if/how much it increases the number of transaction retries and associated overhead. 3. Debug patch to log all row lock waits. http://lists.askmonty.org/pipermail/commits/2015-July/008132.html This is not a patch suitable for production use. It adds an option --gtid-log-all-lock-conflicts. When enabled, whenever one parallel replication transaction needs to wait on the InnoDB row lock of another, it will log a line to the error log, including the GTIDs of the transactions. This could be used to correlate back with the binlog and study exactly which transactions it is that are conflicting with each other. Such results could again be highly interesting. - Kristian.

1 1

[Maria-developers] MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column
by Alexander Barkov 15 Jul '15

15 Jul '15

Hi Sanja, Please review a patch for MDEV-8441. Thanks.

2 1

[Maria-developers] MDEV-4682 plugin - qc_info - add Query Cache flags - not-GSoc2015 patch for review
by Daniel Black 14 Jul '15

14 Jul '15

This expands out all the query cache info from the flags that wasn't previously available in the information_schema.query_cache_info table by the query_cache_info plugin. https://github.com/MariaDB/server/pull/67 -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.

1 0

Re: [Maria-developers] Likely a dead optimizer related code
by Michael Widenius 13 Jul '15

13 Jul '15

Hi! > Hi Igor, > > I noticed that during Item_cond::fix_fields() and > Item_func_between::fix_fields() update these optimizer > related SELECT_LEX members: > > thd->lex->current_select->cond_count and > thd->lex->current_select->between_count > > The purpose of these members is to allocate optimizer related buffers > in: > - update_ref_and_keys() in sql_select.cc and > - check_func_dependency() in opt_table_elimination,cc > and it seems they have no any other purposes.. Correct. > From my understanding, this is a dead code and the collected values are > never used: > > - cond_count and_between_count are later always initialized to 0 > before the walk(count_sargable_conds) call in update_ref_and_keys(). And the call to check_func_dependency() is done after this call. > - moreover, using the values collected during fix_fields() would not be > correct to allocate optimized related buffers, because the code > in fix_fields() does not distinguish between different query parts, > so the items can be just a part of the SELECT list rather than a > part of WHERE/HAVING/ON. This would note be a problem as the count was supposed to be an upper bound. As it's not normal that you have many IF or BETWEEN in the SELECT part, this was not a problem. I looked at the patch and it's ok to commit. Regards, Monty

2 1

Re: [Maria-developers] Probably in a bug in simple_pred()
by Michael Widenius 09 Jul '15

09 Jul '15

Hi! > From: Alexander Barkov <bar(a)mariadb.org> > Hi Sergey, > > I probably found a bug in simple_pred() in opt_sum.cc. > > In this piece of code: > >> case 3: >> /* field BETWEEN const AND const */ >> item= func_item->arguments()[0]->real_item(); >> if (item->type() == Item::FIELD_ITEM) >> { >> args[0]= item; >> for (int i= 1 ; i <= 2; i++) >> { >> item= func_item->arguments()[i]->real_item(); >> if (!item->const_item()) >> return 0; >> args[i]= item; >> if (check_item1_shorter_item2(args[0], args[1])) > > > Should't it be > > if (check_item1_shorter_item2(args[0], args[i])) > > ? > >> return 0; >> } >> } > > > If this is a bug, can you please help to make an SQL scrip which would > be affected by this bug? yes, it's a bug. Here is a script that shows that: drop table if exists t1; create table t1 (a varchar(10), key (a)); insert into t1 values("bar"),("Cafe"); explain select min(a) from t1 where a between "a" and "Cafe2"; explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee"; explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; The output is: 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | For the two first cases and: | 1 | SIMPLE | t1 | index | a | a | 13 | NULL | 2 | Using where; Using index | for the last The second case should have the same output as the third. Will fix and push into 10.1 Regards, Monty

1 0