February 2018 - developers - lists.mariadb.org

[Maria-developers] Windows line endings in MariaDB sources again (
by Otto Kekäläinen 23 Jul '19

23 Jul '19

Hello! While trying to release 10.0.26 for Debian and Ubuntu I noticed git chokes again on Windows line endings. As our .gitattributes states that by default git should clean up the line endings, why do we keep getting new Windows line endings in new code? mariadb-10.0$ cat .gitattributes # Set the default behavior, in case people don't have core.autocrlf set. * text=auto ... storage/connect/mysql-test/connect/std_data/*.txt -text mariadb-10.0.26$ find . | xargs file | grep CRLF ./storage/connect/mysql-test/connect/std_data/boyswin.txt: ASCII text, with CRLF line terminators ./storage/connect/mysql-test/connect/std_data/expenses.txt: ASCII text, with CRLF line terminators ./storage/connect/mysql-test/connect/std_data/emp.txt: ASCII text, with CRLF line terminators ./mysql-test/std_data/loaddata7.dat: ASCII text, with CRLF line terminators ./mysql-test/r/loadxml.result: ASCII text, with CRLF, LF line terminators ./mysql-test/r/perror-win.result: ASCII text, with CRLF, LF line terminators ./mysql-test/r/mysql_binary_mode.result: ASCII English text, with CRLF, LF line terminators ./mysql-test/r/func_regexp_pcre.result: UTF-8 Unicode C++ program text, with CRLF, CR, LF line terminators ./pcre/testdata/grepoutputN: ASCII text, with CRLF, CR, LF line terminators ./pcre/testdata/greppatN4: ASCII text, with CRLF line terminators This leads for me to: dpkg-source: info: building mariadb-10.0 using existing ./mariadb-10.0_10.0.26.orig.tar.gz dpkg-source: info: local changes detected, the modified files are: mariadb-10.0/storage/connect/mysql-test/connect/std_data/boyswin.txt mariadb-10.0/storage/connect/mysql-test/connect/std_data/emp.txt mariadb-10.0/storage/connect/mysql-test/connect/std_data/expenses.txt dpkg-source: error: aborting due to unexpected upstream changes, see /tmp/mariadb-10.0_10.0.26-1.diff.Yzwwhw Which means I have to do extra work with importing the upstream sources.. - Otto

2 4

[Maria-developers] 10.2 and merges (e.g. TokuDB)
by Phil Sweeney 28 Aug '18

28 Aug '18

Hi there, It seems 10.2.6 is still shipping with TokuDB from PS 5.6.35-80 (Feb 8th 2017). I can't see a jira issue for a 10.2 merge since 10.2.4. Perhaps intentional to help stabilise 10.2 for release? 10.0.30 also shipped with the same Feb version (in March). 10.1.23 however was released in May and still has this version from Feb, which seems unfortunate, but I know it's because it has to flow through 10.0 first. I see 10.0.31 is due to get TokuDB from PS 5.6.36-82 And that should flow into 10.1.24 soon too. However, 10.2.7 isn't due until July, so by then TokuDB version will be 5 months old in 10.2 I'm waiting on a fairly important row count fix that should appear in the next Percona Server release. When it does, if the current merging strategy continues, do I have to first wait for 10.0.32 to get it in late July, then 10.1.26 in August, then 10.2 after that? Or with 10.0, 10.1 and 10.2 all being supported and needing merges, is there a new strategy on the cards to shorten this lag? Thanks! Phil

3 7

[Maria-developers] MDEV-13118 Wrong results with LOWER and UPPER and subquery
by Alexander Barkov 12 Jul '18

12 Jul '18

Hello Sergei, Please review a patch for MDEV-13118. Thanks!

2 4

Re: [Maria-developers] 980a5c31880: MDEV-14572: Assertion `! is_set()' failed in Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS
by Sergei Golubchik 19 Jun '18

19 Jun '18

Hi, Oleksandr! On Jan 28, Oleksandr Byelkin wrote: > Hi, Sergei! > > Flag in SELECT_LEX options dropped at the end of execution (in our case > prepare). All commands where EXPLAIN is possible restore the flag in main > SELECT_LEX (in mysql_explain_union()) except UPDATE (because it do > not use mysql_explain_union() > for explain). Without the flag it first try to send OK (as part of update) > then EOF as part of EXPLAIN. Ok. 1. Why does it not use mysql_explain_union() ? 2. Why EXPLAIN flag is need to be removed and restored anyway? 3. Please append the following to the commit comment: " Without it, mysql_update() didn't know that there will be EXPLAIN result set and was sending OK at the end of the update, which conflicted with the EOF sent later by EXPLAIN. " > On Sun, Jan 28, 2018 at 2:45 PM, Sergei Golubchik <serg(a)mariadb.org> wrote: > > > Hi, Oleksandr! > > > > On Dec 22, Oleksandr Byelkin wrote: > > > revision-id: 980a5c318803d6aa7355e019869597976dcf19bf (mariadb-10.0.33-37-g980a5c31880) > > > parent(s): 042f763268c0f209e7c12e0a6a72bb5d204dfe29 > > > author: Oleksandr Byelkin > > > committer: Oleksandr Byelkin > > > timestamp: 2017-12-22 14:00:10 +0100 > > > message: > > > > > > MDEV-14572: Assertion `! is_set()' failed in Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS > > > > > > Restore EXPAIN flag in SELECT_LEX before execution multi-update by flag in LEX > > > (the same but in other way made before INSERT/DELETE/SELECT) > > > > Please explain what was the reason for the bug, why the status was set > > twice, and how your change fixes it. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] Mdev-10664 Add statuses about optimistic parallel replication stalls.
by Sachin Setiya 19 Apr '18

19 Apr '18

Hi Kristian! Sorry for late reply , I was on vacations. On Tue, Jan 10, 2017 at 7:31 PM, Kristian Nielsen <knielsen(a)knielsen-hq.org> wrote: > Sachin Setiya <sachin.setiya(a)mariadb.com> writes: > > >> Right, but you still need _some_ way to synchronise multiple threads > > > I used atomic builtins my_atomic_add64_explicit. > > That seems fine (but see comment below). > > > Documentation:- > > > > Slave_DDL_Events:- > > > > Description: Number of DDL statements slave have executed in > > replication channel X. > > Scope: Per Replication Channel > > Data Type: numeric > > Introduced: MariaDB 10.1 > > > > Slave_Non_Transactional_Events:- > > > > Description: Number of Non Transactional "no idea what should be the > > next word " slave have executed in replication channel X. > > Scope: Per Replication Channel > > Data Type: numeric > > Introduced: MariaDB 10.1 > > I checked, the term "event group" is used already multiple places in the > documentation, so I think it can be used here also. An event group can be a > transaction (possibly with multiple transactional DML statements), or a DDL > or non-transactional statement. > > What you are doing is introducing status variables to help understand > optimistic (and aggressive) parallel replication modes. > > In optimistic parallel replication, a non-transactional event group is not > safe to run in parallel with other non-transactional events. So that event > is made to wait for all prior event groups to reach commit state, before > being allowed to execute. However, other transactional events are not > delayed by the non-transactional event group. > > Slave_Non_Transactional_Events counts the occurences of this. > > Then in case of a DDL statement, this can potentially be unsafe to run in > parallel even with other transactional events. So for a DDL, the DDL has to > wait for all prior event groups, _and_ subsequent event groups have to wait > for the DDL. > > Slave_DDL_Events counts these occurences. > > This is what the documentation, and commit messages, should explain, I > think. > > I think the status variables describe the values for > @@default_master_connection?, this should also be explained in the > documentation. > > Why did you decide to put this information into a status variable? > Normally, > slave status is seen in SHOW SLAVE STATUS, which supports showing status > for > a particular connection without using @@default_master_connection. > > Sorry for this , I guess It was a confusion I was looking at Slave_Running this is avaliable in show status as well as show slave status. So I thought for if I want to show some information in SHOW slave status this has to be in show status. And this is wrong. Now we can see non trans events / ddl events in show slave status. Show I remove this vars from Show Status, or it is okay ? > > diff --git a/sql/log_event.h b/sql/log_event.h > > index 90900f6..579607f 100644 > > --- a/sql/log_event.h > > +++ b/sql/log_event.h > > @@ -50,6 +50,7 @@ > > #endif > > > > #include "rpl_gtid.h" > > +#include "my_atomic.h" > > Why include my_atomic.h in the header file, when you only use it in the .cc > file? > Changed. > > > + mysql_mutex_lock(&LOCK_active_mi); > > + if (master_info_index) > > + { > > + mi= master_info_index-> > > + get_master_info(&thd->variables.default_master_connection, > > + Sql_condition::WARN_LEVEL_NOTE); > > + if (mi) > > + tmp= mi->total_ddl_events; > > > + if (mi) > > + tmp= mi->total_non_trans_events; > > Better use an atomic primitive here to read the value. Taking a mutex does > not help synchronise against my_atomic_add64_explicit(). > > Changed. > > + } > > + mysql_mutex_unlock(&LOCK_active_mi); > > + if (mi) > > + *((longlong *)buff)= tmp; > > + else > > + var->type= SHOW_UNDEF; > > + > > + return 0; > > +} > > #endif /* HAVE_REPLICATION */ > > > > static int show_open_tables(THD *thd, SHOW_VAR *var, char *buff, > > > index 9365c06..a474aa5 100644 > > --- a/sql/rpl_mi.h > > +++ b/sql/rpl_mi.h > > @@ -303,6 +303,12 @@ class Master_info : public > Slave_reporting_capability > > > > /* The parallel replication mode. */ > > enum_slave_parallel_mode parallel_mode; > > + > > + /* No of DDL statements */ > > + long total_ddl_events; > > + > > + /* No of non-transactional statements*/ > > + long total_non_trans_events; > > "long" is not the appropriate type here. Certainly not since you are using > my_atomic_add64_explicit(). Use a type that is the same size on all > platforms (and why use a signed type?). > > Changed to uint64. > - Kristian. > Attached a newer version of patch , please review it. -- Regards Sachin

3 7

[Maria-developers] CREATE SEQUENCE is coming
by Michael Widenius 18 Apr '18

18 Apr '18

Hi! For those that are trying to port applications to MariaDB from other databases or need more features than the current AUTO_INCREMENT provides, the following may be of interest: First version of sequence is pushed to bb-10.2-sequence. It should be merged to bb-10.2-compatilbity and then to 10.3 shortly (1-2 weeks). What is working for the moment: CREATE OR REPLACE [TEMPORARY] SEQUENCE [IF NOT EXISTS] name [ INCREMENT [ BY | = ] increment ] [ MINVALUE [=] minvalue | NO MINVALUE ] [ MAXVALUE [=] maxvalue | NO MAXVALUE ] [ START [ WITH | = ] start ] [ CACHE [=] cache ] [ [ NO ] CYCLE ] ENGINE=xxx COMMENT=".." SELECT NEXT VALUE FOR sequence_name; SELECT NEXTVAL(sequence_name); SELECT PREVIOUS VALUE FOR sequence_name; SELECT LASTVAL(sequence_name); SHOW CREATE SEQUENCE sequence_name; SHOW CREATE TABLE sequence_name; CREATE TABLE sequence-structure ... SEQUENCE=1 ALTER TABLE sequence RENAME TO sequence2; RENAME TABLE sequence RENAME TO sequence2; DROP [TEMPORARY] SEQUENCE sequence_name [IF EXISTS] See https://jira.mariadb.org/browse/MDEV-10139 for progress. See commit messages in bb-10.2-sequence for what is still to be done. Some documentation can be found at: https://mariadb.com/kb/en/mariadb/create-sequence/ This will be improved as the work progress. One of the goals with the SEQUENCE implementation is that all old tools, like mysqldump, should work unchanged, while still keeping normal usage of sequence standard compatibly. The make this possible, the sequence is currently implemented as a table with a few exclusive properties. The main disadvantage of having sequence as a table is that it uses the same name space as tables. In other words, you can't have a table and a sequence of the same name. The benefit is that sequences shows up in 'show tables', one can create a sequence also with 'create table' and drop it with 'drop table'. One can select from it as from any other table. This ensures that all old tools that works with tables should work with sequences. The special properties for sequence tables are: - A sequence table has always one row. - When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE, one row will be inserted. - Normal table options works for CREATE SEQUENCE. One can use ENGINE=xxx, COMMENT=xxx etc. - If one tries to insert into a sequence table, the single row will be updated. This allows mysqldump to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications can of course also use ALTER SEQUENCE. - Updates to the sequence table will change the single row. - Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The column 'next_value' shows the next value not reserved by the cache. - Truncate on a sequence table will give an error. - Alter table and rename works on sequences. - If one creates a sequence with INCREMENT 0, then the sequence will use auto_increment_increment and auto_increment_offset for the sequence, just like AUTO_INCREMENT. This allows sequences to work reliable in a master-master environment and with Galera. Internally sequence tables are created as a normal table without rollback (InnoDB, Aria and MySAM supports this) with is wrapped by a sequence engine. This allowed me to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if binary log is enabled). MariaDB 10.3 will support both the ANSI SQL and Oracle syntax for creating and accessing sequences. As ANSI SQL doesn't have an easy access to the last generated value, 103 also supports 'PREVIOUS VALUE FOR sequence_name', like IBM DB2, and LASTVAL(sequence_name) as PostgreSQL. I want to thank Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL which gave me ideas and inspiration for this work. I also want to thank Marko Mäkelä for his help in the InnoDB part of the code. There is still a lot of work to fix edge cases, but in the current implementation most major things seams to work... Comments, suggestions or questions? Regards, Monty

2 1

[Maria-developers] MariaDB 5.5, Jemalloc, TokuDB, RHEL
by Michal Schorm 08 Mar '18

08 Mar '18

Hello, so far we packed MariaDB 5.5 for RHEL 7 without TokuDB and without Jemalloc. Recently, a customer asked about the availability of TokuDB, so I'm doing a research, if its possible to pack it without any issues for the users updating. I have a several questions: 1) Is Jemmaloc used elsewhere from TokuDB ? 2) If yes, what all would be affected? Could any issues arise during update from build without Jemalloc to build with it? Any expected performance changes? 3) Would be the use of the bundled Jemalloc (on of the) best option(s)? 4) Anything else that I missed? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 1

Re: [Maria-developers] [Commit] MDEV-12746 out-of-order retry
by Kristian Nielsen 17 Feb '18

17 Feb '18

andrei.elkin(a)pp.inet.fi writes: > commit 3cebb54e6387a7eace1757c82ed0efd6e11590b9 > Author: Andrei Elkin <andrei.elkin(a)mariadb.com> > Date: Fri Feb 9 15:00:23 2018 +0200 > > MDEV-12746 rpl.rpl_parallel_optimistic_nobinlog fails committing > out of order at retry > > The 2nd issue was out of order commit by transactions that > are ordered after an erroring out transaction. The latter was possible > in the test thanks to the above retry failure. > Out-of-order committing was done despite the slave was stopping. Let me see if I understand this correctly: Normally, a transaction in optimistic parallel replication cannot commit out of order because it either waits for the prior transaction to commit first, or, if the prior transaction has stopped with an error earlier, then the following transaction will not even start. But in this case, during retry, there was missing the check that the prior transaction stopped with an error. Therefore, the following transaction's wait_for_prior_commit() does nothing, and it wrongly goes ahead and runs and even commits, even though an earlier transaction failed and replication is stopping. Correct? That's a nice catch of a subtle, but potentially serious bug. I wonder how this is possible though. As I recall, when a transaction fails, this failure is marked in the wait_for_prior_commit data structures. So the retrying transaction should have gotten an error in wait_for_prior_commit() and aborted the retries. So why does this not happen in this case? > The test failures were of two sorts. One is that the number of potential > temporary errors actually exceeds the default value of the slave retry > option. > Increased @@global.slave_transaction_retries fixes that part. Hm, this shouldn't be needed, I think. The retry waits for all prior transactions to commit first, so after the first retry no further retries should be caused by other parallel replication workers. So I would expect this problem to be a side-effect of the other, real issue, and this increase should not be needed (and might hide another problem later, if left in). > diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc > index 34f9113f7fe..9f1d23a1161 100644 > --- a/sql/rpl_parallel.cc > +++ b/sql/rpl_parallel.cc > @@ -745,7 +761,25 @@ retry_event_group(rpl_group_info *rgi, rpl_parallel_thread *rpt, > for (;;) > { > mysql_mutex_lock(&entry->LOCK_parallel_entry); > - register_wait_for_prior_event_group_commit(rgi, entry); > + if (entry->stop_on_error_sub_id == (uint64) ULONGLONG_MAX || > +#ifndef DBUG_OFF > + (DBUG_EVALUATE_IF("simulate_mdev_12746", 1, 0)) || > +#endif > + rgi->gtid_sub_id < entry->stop_on_error_sub_id) > + { > + register_wait_for_prior_event_group_commit(rgi, entry); > + } > + else > + { > + /* > + An earlier transaction by another worker may have not had not > + have the current one in subsequent committers list and thus > + the actual induced error status could not have been sumbitted. > + So it should be set by ourselves now. > + */ This comment is hard to understand - what do you mean here? > + if (!rgi->worker_error) > + rgi->worker_error= 1; > + } I would have expected an abort (goto err) here (or well just below, after unlocking the mutex). Is this not needed, and if so, why not? > @@ -716,12 +724,20 @@ retry_event_group(rpl_group_info *rgi, rpl_parallel_thread *rpt, > unregistering (and later re-registering) the wait. > */ > if(thd->wait_for_commit_ptr) > - thd->wait_for_commit_ptr->unregister_wait_for_prior_commit(); > + thd->wait_for_commit_ptr->unregister_wait_for_prior_commit(); Indentation? - Kristian.

2 5

[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 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 03 Feb '18

03 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