February 2018 - developers - lists.mariadb.org

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

13 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