June 2015 - developers - lists.mariadb.org

Re: [Maria-developers] 3eceb51: MDEV-7990: ERROR 1526 when procedure executed for second time ALTER TABLE partition ... pMAX values less than MAXVALUE
by Sergei Golubchik 05 Aug '15

05 Aug '15

Hi, Sanja! On May 06, sanja(a)mariadb.com wrote: > revision-id: 3eceb51e697d5a514e70c374a06e7eef6b5a37a0 > parent(s): aa5095627e2619bdad7916d33d1016802a84a9e1 > committer: Oleksandr Byelkin > branch nick: server > timestamp: 2015-05-06 13:19:22 +0200 > message: > > MDEV-7990: ERROR 1526 when procedure executed for second time ALTER > TABLE partition ... pMAX values less than MAXVALUE > > Added cleaning partition data which are saved in LEX structure to make > possible correct re-execution of a SP or PS using the code. No, this is wrong. See prep_alter_part_table(). The first thing it does it to copy thd->lex->part_info to thd->work_part_info. It tries to do a deep copy to be able to modify it freely, but apparently the copy isn't deep enough. I see two options 1. Do a deeper copy. Duplicate all value lists. 2. Make part_column_list_val elements immutable. That would be preferred, of course, but I'm not sure it's possible. Regards, Sergei

2 2

[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

[Maria-developers] Buildbot branch 10.0-custombld
by Kristian Nielsen 02 Jul '15

02 Jul '15

Hi Rasmus, The branch 10.0-custombld seems to no longer build binaries (after the transition to git?): https://github.com/MariaDB/server/tree/10.0-custombld https://buildbot.askmonty.org/buildbot/grid?branch=10.0-custombld&category=… Can you please fix Buildbot so that this branch works again for producing custom builds of all packages? This is urgent, as it is blocking further progress of MDEV-8302. Thanks, - Kristian.

1 1

Re: [Maria-developers] MDEV-8356 analysis and ideas
by Sergey Petrunia 29 Jun '15

29 Jun '15

Hi Diwas, Nice analysis. Please find my comments below. On Mon, Jun 29, 2015 at 10:01:56AM +0530, Diwas Joshi wrote: > hello sergey, so i was reading further on MDEV-8356 last night. There are > two main things here: type of sf and return type of sf. Right now the > create function... is giving errors at > > store_failed= store_failed || > table->field[MYSQL_PROC_MYSQL_TYPE]-> > store((longlong)type, TRUE); > > in sf_create_routine(), which is right since we have been been using > TYPE_ENUM_TABLE for m_type so far and mysql.proc takes > enum('FUNCTION','PROCEDURE') for type. This can be solved by either > changing this enum in mysql.proc definition and including TABLE, or storing > table_valued functions as FUNCTION as well. I think first option is better, > including TABLE in the enum, since this will help us in distinguishing > these functions while executing later if needed. It is not clear for me which option is better. On one hand, there is your argument that adding 'TABLE' into the enum will make it easier to tell table functions from regular function. On the other hand, try grepping for 'TYPE_ENUM_FUNCTION' in sql/*.{h,cc}. You find a lot of code that checks if m_type == TYPE_ENUM_FUNCTION. You will need to review each check to see what should be done for TYPE_ENUM_FUNCTION. If we can't decide towards one option or another, let's use whatever you prefer right now. If we discover that that works poorly, we will be able to reverse the decision later on. > The second thing is about return type of function. Right now > sp_returns_type() method is being used to store the return type of > functions. But in our case we have a list of create_fields: > List<Create_field> m_cols_list keeping list of attribute names and their > types for the table. We need to store all these, for this we can either > alter mysql.proc definition to make a field appropriate for storing this > list. The type of the field storing return type right now is longblob which > as i read further on it is a binary large object that can hold a variable > amount of data and are treated as binary strings (byte strings). Maybe we > can try to find a way to store all the attribute information in this field > only, maybe putting in some delimiters between different field types or > some other way since after all its a string. I am still not sure about this > problem. I tried creating this function: CREATE FUNCTION f2(a INT, b VARCHAR(11)) RETURNS VARCHAR(32) DETERMINISTIC BEGIN set @a=3; RETURN @a; END| and mysql.proc.returns has: returns: varchar(32) CHARSET latin1 So, it is a text defition of the return type. It seems natural if for table functions we Is it a problem to do similar thing for Table functions? e.g for a function CREATE FUNCTION f1(a INT, b VARCHAR(11)) RETURNS TABLE t1(id INT, name VARCHAR(11)) BEGIN set @a=3; END| store 'TABLE t1(id INT, name VARCHAR(11)' ? I guess it depends on where the value of mysql.proc.returns is used. Please research this. > > Also, another thing is to store the table alias m_table_alias, because > queries inside the function will be using it. There is no field present > specifically for this in mysql.proc. So either we can create one. There is > a field specific_name in it, as i saw in the code is right now storing the > same thing as name field. So maybe we can use this field to store the table > alias. I would prefer that m_table_alias was stored as part of the expression in mysql.proc.returns field. Can we try that option first? If we figure that can't work, we can think of other options. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB multi-source replication testing at Booking.com
by Karoly Nagy 29 Jun '15

29 Jun '15

Dear MariaDB developers, I'm Károly Nagy working for Booking.com currently testing multi-source replication functionality of MariaDB. Kristian suggested I should reach out to you on this mailing list regarding my questions. We're seeing very high and fluctuating mutex contentions while replicating from two sources (Oracle MySQL 5.6) to a single MariaDB slave. You can see that on the graphs below. The spin waits are relatively [1] aligned but the mutex rounds [2] are 5-10 times higher than it is on the two sources combined together and not consistent. The sources have a relatively constant pattern while the target has dips around 2.5k and spikes up to 8k. The os waits are in completely different order of magnitude [3]. The scenario where values were captured: * Multi-source target is replicating the full dataset of `source 2` and a subset of `source 1` (the hot data) - MariaDB 10.0.16 * Both sources are MySQL 5.6 being part of their replication chain as slaves with log_slave_updates * Source 2 is in normal mode - Oracle MySQL 5.6.17 * Source 1 is catching up from a 1 day replication delay - Oracle MySQL 5.6.24 * All the slaves are warm having the buffer pool fully populated Is this behavior expected? Could you give us some insights on why we could see these results? If there is any more information you need please let me know. Thank you for your help in advance! Every metric is on /10 seconds basis. [1] Mutex spin waits [2] Mutex rounds [3] Mutex OS waits Best regards, -- Károly Nagy System engineer Booking.com <http://booking.com/> BV Rembrandt Square Office, Herengracht 597, 1017 CE Amsterdam Direct +31 (0)20 715 8403

3 4

Re: [Maria-developers] [Commits] 132935a: MDEV-8339 - Server crash during table cache eviction
by Sergei Golubchik 27 Jun '15

27 Jun '15

Hi, Sergey! On Jun 25, svoj(a)mariadb.org wrote: > revision-id: 132935a3ef6578f5fabc765ff6ac966cd3798819 > parent(s): 1f4a89b10ca8df61ec51fcc87e1203dc72579759 > committer: Sergey Vojtovich > branch nick: mariadb > timestamp: 2015-06-25 14:21:16 +0400 > message: > > MDEV-8339 - Server crash during table cache eviction > > TDC_element::free_tables_back() had pre-lfhash leftover code, which referenced > TDC_element::free_tables via TDC_element::share. This is not correct as share > may be NULL (newly inserted or to be removed), instead access free_tables > directly. ok to push Regards, Sergei

1 0

[Maria-developers] MDEV-8214 Asian MB2 charsets: compare broken bytes as "greater than any non-broken character"
by Alexander Barkov 26 Jun '15

26 Jun '15

Hi Sergei, For simplicity I decided to split: MDEV-8036 Fix all collations to compare broken bytes as "greater than any non-broken character" into sub-tasks. Please review a patch for the first sub-task MDEV-8214. The overall plan is: - MDEV-8214, for the Asian charsets with mbmaxlen=2 - MDEV-8215, for the Asian charsets with mbmaxlen=3, based on MDEV-8214 - MDEV-8036, the rest (Unicode character sets) Thanks.

2 4

[Maria-developers] MDEV-7502 Automatic provisioning of slave - error reporting
by Martin Kaluznik 26 Jun '15

26 Jun '15

Hello Kristian, with general idea working (sending of database / table structures + table data), my next plan is to improve / introduce error handling in existing code along with error handling on slave side (ddl events not from provisioning, ignore errors when updating not yet existing rows, ...). What are general guidelines for introduction of new error codes? Is there anything else I need to do except adding it to /sql/share/errmsg-utf8.txt ? I would create only one new error code - ER_PROVISIONING_FAILED - and place specific messages inside formatted text - same way as ER_MASTER_FATAL_ERROR_READING_BINLOG works. It doesn't go well with translations, but... you can decide. Maybe create different error codes at least for errors, where we are expecting user to fix them - table without index, statement mode. Also, how to tell slave from master that provisioning finished? Is introduction of new error code ER_PROVISIONING_DONE acceptable solution? The error itself would never produce error message in logs. Alternatively I can reuse / create similar event to Stop_log_event, note - provisioning is completed after SQL thread processes whole relay log, not when IO thread receives this event, so even reuse would require additional code. And side question, is there any log event other than Query_log_event which can produce ddl statement? Martin

2 1

Re: [Maria-developers] [Commits] 2cf747e: MDEV-8219: enforce_storage_engine cannot be set globally
by Sergei Golubchik 26 Jun '15

26 Jun '15

Hi, Jan! On Jun 26, Jan Lindström wrote: > revision-id: 2cf747e885603122b2adbefc131516cd12dc953c > parent(s): d3b7eb7b99ef7dae79c20b5beb63acdfdf6d8046 > committer: Jan Lindström > branch nick: 10.1-bugs > timestamp: 2015-06-26 08:56:50 +0300 > message: > > MDEV-8219: enforce_storage_engine cannot be set globally > > Change session only variable enforce_storage_engine to be > session variable and make sure that also global value > is used if session variable is not set. Ok to push, thanks! Regards, Sergei

1 0

[Maria-developers] Please review: MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
by Alexander Barkov 25 Jun '15

25 Jun '15

Hi Sergei, Please review a patch for mdev-7824. It's based on a MySQL patch for http://bugs.mysql.com/bug.php?id=68041 and is a blocker for: MDEV-3929 Add full support for auto-initialized/updated timestamp and datetime Thanks.

2 9