- developers - lists.mariadb.org

[Maria-developers] Developers Unconference in New York
by Ian Gilfillan 16 Feb '18

16 Feb '18

A final reminder about the Developers Unconference happening in New York on Sat 24 and Sun 25 February, just before M|18. https://mariadb.org/2018-mariadb-developers-unconference/ Please sign up on the Meetup page if you are going to be attending: https://www.meetup.com/MariaDB-Developers/events/244375856/ Hope to see you there!

1 0

[Maria-developers] Google Summer of Code 2018 - MariaDB Foundation accepted
by Colin Charles 14 Feb '18

14 Feb '18

Hi, I just noticed this piece of good news — MariaDB Foundation is accepted to participate in the Google Summer of Code 2018 again — https://summerofcode.withgoogle.com/ There is an already healthy list of ideas: https://mariadb.com/kb/en/library/google-summer-of-code-2018/ Many already have mentors from MariaDB Corporation or MariaDB Foundation. Some are looking for mentors. Happy coding this summer! cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

1 0

Re: [Maria-developers] [Commits] f4f32ab71d5: MDEV-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918):
by Sergey Petrunia 14 Feb '18

14 Feb '18

Hi Varun, On Sat, Feb 03, 2018 at 06:37:21PM +0530, Varun wrote: > revision-id: f4f32ab71d5c96fbec87f8b6a5c057af2bc2398b (mariadb-10.2.5-413-gf4f32ab71d5) > parent(s): 9390ff53fc39e34976cf051ce33649f9316bb8e6 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-02-03 18:24:49 +0530 > message: > > MDEV-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): > Encountered illegal value '' when converting to DECIMAL > > The issue was that EITS data was allocated but then not read for some reason (one being to avoid a deadlock), > then the optimizer was using these bzero'ed buffers as EITS statistics. > This should not be allowed, we should use statistcs for a table only when we have successfully loaded/read > the stats from the statistical tables. > > --- > mysql-test/r/statistics.result | 16 ++++++++++++++++ > mysql-test/t/statistics.test | 19 +++++++++++++++++++ > sql/sql_statistics.cc | 23 ++++++++++++++++------- > 3 files changed, 51 insertions(+), 7 deletions(-) > > diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result > index ffaaf09acc8..1486a8ab5e6 100644 > --- a/mysql-test/r/statistics.result > +++ b/mysql-test/r/statistics.result > @@ -1716,5 +1716,21 @@ b > 0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 > DROP TABLE t1; > # > +# MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): Please change to "MDEV" > +# Encountered illegal value '' when converting to DECIMAL > +# > +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; > +set optimizer_use_condition_selectivity=3, use_stat_tables=preferably; > +create table a (id int(10),cost decimal(9,2)) engine=innodb; > +ANALYZE TABLE a PERSISTENT FOR ALL; > +Table Op Msg_type Msg_text > +test.a analyze status Engine-independent statistics collected > +test.a analyze status OK > +create temporary table b (id int); > +insert into b (id) select id from a where cost > 0; > +drop table a,b; Please follow the convention and use table names t1,t2,... Ok to push after this is addressed. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] MDEV-15167 Server crashes in in bitmap_bits_set upon REPAIR PARTITION ...
by Sergei Golubchik 10 Feb '18

10 Feb '18

Hi, Sachin! On Feb 09, Sachin Setiya wrote: > commit 3e49ebcacf9022931f0dfefcb44c13973128f8c4 > Author: Sachin Setiya <sachinsetia1001(a)gmail.com> > Date: Fri Feb 9 17:44:04 2018 +0530 > > MDEV-15167 Server crashes in in bitmap_bits_set upon REPAIR PARTITION > ... > > diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc > index a4d9406..3d53056 100644 > --- a/sql/sql_partition.cc > +++ b/sql/sql_partition.cc > @@ -4583,6 +4583,11 @@ uint prep_alter_part_table(THD *thd, TABLE *table, > Alter_info *alter_info, > Alter_info::ALTER_TABLE_REORG | > Alter_info::ALTER_REBUILD_PARTITION)) > { > + /* > + You can't add column when we are doing alter related to partition > + */ > + DBUG_EXECUTE_IF("test_pseudo_invisible",{DBUG_ASSERT(0);}); > + DBUG_EXECUTE_IF("test_completely_invisible",{DBUG_ASSERT(0);}); May be return error? Like with P_S? DBUG_EXECUTE_IF("test_pseudo_invisible", {DBUG_RETURN(1)}); but test it, may be you'll need my_error too, like DBUG_EXECUTE_IF("test_pseudo_invisible", { my_error(ER_INTERNAL_ERROR, MYF(0), "Don't to it with test_pseudo_invisible"); DBUG_RETURN(1) }); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] Oracle strings functions compatibility : substr / MDEV 10574
by jerome brauge 09 Feb '18

09 Feb '18

Hello Alexander, If you remember, there was still a problem with our function substr_oracle when length is less or equal to zero. Can you review this very small patch for this ? For MDEV-10574, If we overload only functions required for Oracle compatibility (ltrim/rtrim and lpad/rpad), could I make a similar patch ? (by adding functions ltrim_oracle, ... like for substr) It's for us the last blocking point. Regards, Jérôme. > -----Message d'origine----- > De : Alexander Barkov [mailto:bar@mariadb.org] > Envoyé : mardi 19 septembre 2017 13:16 > À : jerome brauge > Objet : Re: Oracle strings functions compatibility : substr > > Hi Jerome, > > I noticed one more difference in SUBSTR: > > > SELECT SUBSTR('aaa',1,-1) FROM DUAL; > > MariaDB returns an empty string. > Oracle returns NULL. > > Here's Oracle's documentation: > > https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm > > > I think we should do this in the same patch, to avoid behavior change in the > future. > > Would you like to try doing this additional change? > Or would you like me to make an incremental patch on top of yours? > > Thanks! > > On 09/19/2017 10:33 AM, jerome brauge wrote: > > Hello Alexander, > > It's done. > > > > Do you have news from Sergei for MDEV-12874, MDEV-13417 and MDEV- > 13418 ? > > I asked Sergei, awaiting for his answer. I'll let you know. > > > > > Thank you very much. > > > > > > > >> -----Message d'origine----- > >> De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 18 > >> septembre 2017 18:12 À : jerome brauge Objet : Re: Oracle strings > >> functions compatibility : substr > >> > >> Hello Jerome, > >> > >> > >> On 09/18/2017 05:14 PM, jerome brauge wrote: > >>> Hello Alexander, > >>> I don't understand how I've missed such an obvious solution ! > >>> Here is the new patch. > >> > >> Thanks. Now it looks simpler. > >> > >> Can you please do one small thing: > >> move get_position() from "public:" to "protected:". > >> > >> Ok to make a pull request after this change. > >> > >> Thank you very much! > >> > >> > >>> > >>> Regards. > >>> Jérôme. > >>> > >>> > >>>> -----Message d'origine----- > >>>> De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : lundi 18 > >>>> septembre 2017 11:30 À : jerome brauge Objet : Re: Oracle strings > >>>> functions compatibility : substr > >>>> > >>>> Hello Jerome, > >>>> > >>>> > >>>> On 08/17/2017 04:43 PM, jerome brauge wrote: > >>>>> Hello Alexander, > >>>>> Here is a patch for function SUBSTR in sql_mode=oracle (If > >>>>> position is 0, > >>>> then it is treated as 1). > >>>> > >>>> I'm thinking of how to get a smaller patch. > >>>> > >>>> Wouldn't it be possible to introduce: > >>>> > >>>> virtual longlong get_position(); > >>>> > >>>> > >>>> > >>>> longlong Item_func_substr::get_position() { > >>>> return args[1]->val_int(); > >>>> } > >>>> > >>>> longlong Item_func_substr_oracle::get_position() > >>>> { > >>>> longlong pos= args[1]->val_int(); > >>>> return pos == 0 ? 1 : pos; > >>>> } > >>>> > >>>> > >>>> and just replace all calls for args[1]->val_int() to get_position() ? > >>>> > >>>> Thanks! > >>>> > >>>> > >>>>> > >>>>> Regards, > >>>>> Jérôme. > >>>>>

1 1

Re: [Maria-developers] 9bc03294c89: MDEV-15033: Node fails to join mdb cluster with wsrep_sst_mariabackup due to dependency from socat
by Sergei Golubchik 06 Feb '18

06 Feb '18

Hi, Jan! No, sorry, you cannot do it. First, we cannot add new RPM dependencies in GA (we tried once and had to revert that change). Second, there was already socat dependency in RPM, added by Nirbhay in MDEV-6211 MariaDB-Galera-server uses 'socat', but 'socat' is not in the dependency list commit bd5ca5e6093046d7e69cc59f0950a7af2c0a7bf2 removed by Nirbhay in MDEV-7233: Fix issue with missing dependency socat when installing MariaDB-galera-server on RedhatEL/OracleEL/(Others?) RPM based commit 41367e4023d0823624a461597813e6902437f096 I don't see a good solution for this issue. Adding socat to mariabackup package aren't much better, as mariabackup users don't really need socat either. May be just a better error message in the script? On Feb 06, jan wrote: > revision-id: 9bc03294c89b16df5f556b7d9fa4cb1eadae451a (mariadb-10.1.30-31-g9bc03294c89) > parent(s): 1cd1db9602476ec6864f4a521de97d788fe93fac > author: Jan Lindström > committer: Jan Lindström > timestamp: 2018-02-06 10:59:27 +0200 > message: > > MDEV-15033: Node fails to join mdb cluster with wsrep_sst_mariabackup due to dependency from socat > > Add socat dependency to cpack_rpm.cmake. Similar dependency already > is on debian/control. > > diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake > index e80fb199f03..5b9c6726b5e 100644 > --- a/cmake/cpack_rpm.cmake > +++ b/cmake/cpack_rpm.cmake > @@ -170,7 +170,7 @@ SETA(CPACK_RPM_server_PACKAGE_REQUIRES > IF(WITH_WSREP) > SETA(CPACK_RPM_server_PACKAGE_REQUIRES > "galera" "rsync" "lsof" "grep" "gawk" "iproute" > - "coreutils" "findutils" "tar") > + "coreutils" "findutils" "tar" "socat") > IF (RPM MATCHES "sles11") > SETA(CPACK_RPM_server_PACKAGE_REQUIRES "util-linux") > ELSE() Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

[Maria-developers] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity
by Oliver Schonrock 05 Feb '18

05 Feb '18

Initially posted on "discuss" list... I have come across a very strange symptom with triangular FKs (see below) where a cascade delete of the central (member) record leaves the DB in a state with broken referential integrity. The FK structure is a little bit demanding and somewhat "triangular/circular", but has been in operation in our application for many years without problems. After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1). How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, default_address_id int(11) DEFAULT NULL, default_card_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY member_FI_2 (default_address_id), KEY member_FI_3 (default_card_id), CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO member VALUES (1,2,3); CREATE TABLE address ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, PRIMARY KEY (id), KEY address_FI_1 (member_id), CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO address VALUES (2,1); CREATE TABLE payment_method ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, cardholder_address_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY payment_method_FI_1 (member_id), KEY payment_method_FI_2 (cardholder_address_id), CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO payment_method VALUES (3,1,2); SET FOREIGN_KEY_CHECKS=1; SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ ------------ DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record. Am I missing something, or should this never happen? Thanks -- Oliver Schönrock

1 6

[Maria-developers] Partial indexes?
by Jared Beck 04 Feb '18

04 Feb '18

Dear Maria, :p Thanks for all your hard work! Any plans to implement partial indexes? (https://www.postgresql.org/docs/10/static/indexes-partial.html) I hope this is the right place to suggest new features. Please redirect me if not. -- Jared Beck • (607) 216-5373 • jared(a)jaredbeck.com

3 7

[Maria-developers] MDEV-14425: Is appending never slower than writing to a preallocated file?
by Marko Mäkelä 02 Feb '18

02 Feb '18

I would like to redesign the InnoDB redo log format for better performance in MariaDB 10.4. Part of this would involve minimizing write amplification and optimizing for journaled file systems. Jun Su from Microsoft suggested to me that regular writes (as opposed to appends) could cause some write amplification inside journaled file systems. InnoDB traditionally pre-allocates both data and log files. Maybe it was a good idea in 1994 when the code was initially conceived. But we have had journaled or copy-on-write file systems and also SSDs for quite some time now. I wrote two test programs that write a 2GiB file in 2KiB blocks, either pre-allocating the file upfront, or appending to the file. On the two SSDs that I tested (with ext4fs), appending was always faster. The programs are attached to https://jira.mariadb.org/browse/MDEV-14425 I would appreciate it if someone can provide a counterexample where writing to a preallocated file would be faster than appending, on a modern file system. Also, I would like to see how HDDs would perform. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB Corporation DON’T MISS M|18 MariaDB User Conference February 26 - 27, 2018 New York City https://m18.mariadb.com/

2 1

Re: [Maria-developers] [Commits] a0ea3a58224: MDEV-14857: problem with 10.2.11 server crashing when executing stored procedure
by Sergey Petrunia 31 Jan '18

31 Jan '18

Hi Sanja, Please find my review below. Lots of typos, but the two important comments are at the bottom, for the changes in sql_select.cc and sql_trigger.cc On Sat, Jan 27, 2018 at 11:39:10AM +0100, Oleksandr Byelkin wrote: > revision-id: a0ea3a5822486fa1702d6615f1be30684c3d412f (mariadb-10.1.30-26-ga0ea3a58224) > parent(s): 524221e7a34d42214e82dd868348b453f2ef1591 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2018-01-27 11:38:38 +0100 > message: > > MDEV-14857: problem with 10.2.11 server crashing when executing stored procedure > > Counter for select numbering made stored with the statement (before was global) > So now it does have always accurate value which does not depend on > interruption of statement prepare by errors like lack of table in > a view definition. > > --- > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 4556487bdfe..2dc2fb61e9d 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -1027,6 +1027,21 @@ class Statement: public ilink, public Query_arena > > LEX_STRING name; /* name for named prepared statements */ > LEX *lex; // parse tree descriptor > + /* > + LEX whith represent curent statement (conventional, SP or PS) s/whith/which/ s/curent/current/ s/represent/represents/ > + > + For example during view parsing THD::lex will point to the views LEX and > + THD::stmt_lex will point on LEX of the statement where the view will be s/point on/point to/ > + included > + > + Now used to have alwais correct select numbering inside statemet s/Now used/Currently it is used/ s/alwais/always/ s/statemet/statement/ > + (LEX::current_select_number) without storing and restoring a global > + counter which was THD::select_number. > + > + TODO: make some unified statement representation (now SP has different) > + to store such data like LEX::current_select_number. > + */ > + LEX *stmt_lex; > /* > Points to the query associated with this statement. It's const, but > we need to declare it char * because all table handlers are written > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index b57fba08b47..a2da6f672ca 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -727,7 +727,13 @@ class st_select_lex: public st_select_lex_node > Item *prep_having;/* saved HAVING clause for prepared statement processing */ > /* Saved values of the WHERE and HAVING clauses*/ > Item::cond_result cond_value, having_value; > - /* point on lex in which it was created, used in view subquery detection */ > + /* > + Point on lex in which it was created, used in view subquery detection. "Points to the LEX...". Also please shift the whole comment 1 position to the left. > + > + TODO: make also st_select_lex::parent_stmt_lex (see THD::stmt_lex) > + and use st_select_lex::parent_lex & st_select_lex::parent_stmt_lex > + instead of global (from THD) references where it is possible. > + */ > LEX *parent_lex; > enum olap_type olap; > /* FROM clause - points to the beginning of the TABLE_LIST::next_local list. */ > @@ -2452,7 +2458,7 @@ struct LEX: public Query_tables_list > /** SELECT of CREATE VIEW statement */ > LEX_STRING create_view_select; > > - uint number_of_selects; // valid only for view > + uint current_select_number; // valid for statmet LEX (not view) s/statmet/statement/ > > /** Start of 'ON table', in trigger statements. */ > const char* raw_trg_on_table_name_begin; > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 6084c59a257..8d3116cd8b3 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -6903,7 +6903,12 @@ void THD::reset_for_next_command(bool do_clear_error) > clear_error(1); > > thd->free_list= 0; > - thd->select_number= 1; > + /* > + The same thd->stmt_lex made in lex_start, but during bootstrup this "We also assign thd->stmt_lex in lex_start()" s/bootstrup/bootstrap > + code executed first code is executed > + */ > + thd->stmt_lex= &main_lex; thd->stmt_lex->current_select_number= 1; > + DBUG_PRINT("info", ("Lex %p stmt_lex: %p", thd->lex, thd->stmt_lex)); > /* > Those two lines below are theoretically unneeded as > THD::cleanup_after_query() should take care of this already. > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index b9fe8f3162a..7a6a028ee9c 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -2462,6 +2462,17 @@ void JOIN::save_explain_data(Explain_query *output, bool can_overwrite, > bool need_tmp_table, bool need_order, > bool distinct) > { > + /* > + If there is SELECT in this statemet with the same number it must be the > + same SELECT > + */ > + DBUG_ASSERT(select_lex->select_number == UINT_MAX || > + select_lex->select_number == INT_MAX || > + !output || Is it really possible that output==NULL? If yes, that's probably a bug. > + !output->get_select(select_lex->select_number) || > + output->get_select(select_lex->select_number)->select_lex == > + select_lex); > + > if (select_lex->select_number != UINT_MAX && > select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && > have_query_plan != JOIN::QEP_NOT_PRESENT_YET && > @@ -24601,6 +24612,11 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, > { > explain= new (output->mem_root) Explain_select(output->mem_root, > thd->lex->analyze_stmt); > + if (!explain) > + DBUG_RETURN(1); // EoM > +#ifndef DBUG_OFF > + explain->select_lex= select_lex; > +#endif > join->select_lex->set_explain_type(true); > > explain->select_id= join->select_lex->select_number; > diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc > index 0b4978b2862..878cd5a3b57 100644 > --- a/sql/sql_trigger.cc > +++ b/sql/sql_trigger.cc > @@ -1378,7 +1378,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db, > ulonglong save_sql_mode= thd->variables.sql_mode; > LEX_STRING *on_table_name; > > - thd->lex= &lex; > + thd->lex= thd->stmt_lex= &lex; > The same comment as before: we change thd->stmt_lex here. The function has code below that restores the value of thd->lex: // QQ: anything else ? lex_end(&lex); thd->lex= old_lex; But the value of thd->stmt_lex is not restored? > save_db.str= thd->db; > save_db.length= thd->db_length; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0