June 2017 - developers - lists.mariadb.org

[Maria-developers] Fwd: ecbad0a07a8: MDEV-13189: Window functions crash when using INTERVAL
by Vicențiu Ciorbaru 28 Jun '17

28 Jun '17

Hi Sergey, Can you please review this change that makes window functions work with INTERVAL function? Thanks, Vicentiu ---------- Forwarded message --------- From: vicentiu <vicentiu(a)mariadb.org> Date: Wed, 28 Jun 2017 at 21:08 Subject: ecbad0a07a8: MDEV-13189: Window functions crash when using INTERVAL To: <commits(a)mariadb.org> revision-id: ecbad0a07a848c1f9f474c4c749c87b6bfe7986c (mariadb-10.2.6-74-gecbad0a07a8) parent(s): 31ba0fa48d27715e82258b1e74401093e0ee17a2 author: Vicențiu Ciorbaru committer: Vicențiu Ciorbaru timestamp: 2017-06-28 21:07:55 +0300 message: MDEV-13189: Window functions crash when using INTERVAL Interval function makes use of Item_row. Item_row did not correctly mark with_window_func flag according to its arguments. Fix it by making Item_row aware of this flag. --- mysql-test/r/win.result | 14 ++++++++++++++ mysql-test/t/win.test | 9 +++++++++ sql/item_row.cc | 1 + 3 files changed, 24 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 952bd82ac7f..7f8777ed491 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3127,3 +3127,17 @@ NULL 1 0 1 0 1 2 0 1 drop table t1; +# +# MDEV-13189: Window functions crash when using INTERVAL function +# +create table t1(i int); +insert into t1 values (1),(2),(10),(20),(30); +select sum(i) over (order by i), interval(sum(i) over (order by i), 10, 20) +from t1; +sum(i) over (order by i) interval(sum(i) over (order by i), 10, 20) +1 0 +3 0 +13 1 +33 2 +63 2 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 45653b18682..aff717e3151 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1914,3 +1914,12 @@ select max(i) over (order by i), max(i) over (order by i) is not null from t1; drop table t1; + +--echo # +--echo # MDEV-13189: Window functions crash when using INTERVAL function +--echo # +create table t1(i int); +insert into t1 values (1),(2),(10),(20),(30); +select sum(i) over (order by i), interval(sum(i) over (order by i), 10, 20) +from t1; +drop table t1; diff --git a/sql/item_row.cc b/sql/item_row.cc index ddbb0736d54..fc484f560ee 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -63,6 +63,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref) } maybe_null|= item->maybe_null; with_sum_func= with_sum_func || item->with_sum_func; + with_window_func = with_window_func || item->with_window_func; with_field= with_field || item->with_field; with_subselect|= item->with_subselect; }

1 0

[Maria-developers] MDEV-13197 Parser refactoring for CREATE VIEW, TRIGGER, SP, UDF, EVENT
by Alexander Barkov 28 Jun '17

28 Jun '17

Hello Sanja, Can you please review a patch for MDEV-13197? It's a dependency change for MDEV-10591 Oracle-style packages, which I'm currently working on. The patch is for bb-10.2-ext. Thanks.

1 0

[Maria-developers] MDEV-12179: Per-engine mysql.gtid_slave_pos: auto-configuring/packaging
by Sergey Petrunia 26 Jun '17

26 Jun '17

Hello, Suppose there is a transactional storage engine that is shipped as a loadable module. The examples are MyRocks and TokuDB. I think it the default behavior for such engine after MDEV-12179 should be that the engine is listed in @@gtid_pos_auto_engines. That is, as soon as a transaction that uses the storage engine is replicated, mysql.slave_gtid_pos{_$engine} is created for it. gtid_pos_auto_engines makes it easy to do this manually, but it's difficult to achieve as default behavior. Here's an explanation why. Loadable storage engines are put into their separate packages, for example mariadb-plugin-rocksdb_10.2.7+maria-zesty_amd64.deb. The two important files in the package are : ./etc/mysql/mariadb.conf.d/rocksdb.cnf ./usr/lib/mysql/plugin/ha_rocksdb.so The first one is a .cnf that gets included into the server's cnf file. It would be nice to put there something like SET global gtid_pos_auto_engines= CONCAT(@@gtid_pos_auto_engines, 'rocksdb'); but that is not possible (it's a .cnf not .sql) Possible solutions: == [Ab]use the Storage Engine API == Make the storage engine put itself in @@gtid_pos_auto_engines. I think this is technically possible, but - It's a storage engine API abuse - how does one explain @@gtid_pos_auto_engines to the users? == Let gtid_pos_auto_engines include unknown engine == - Allow @@gtid_pos_auto_engines to list unknown engines - set the default to include all known transactional storage engines, that is it should be : gtid_pos_auto_engines='InnoDB,TokuDB,RocksDB' // (*) and the server should allow this even if it is built without TokuDB or RocksDB (*) -- Aria is not needed as it is not a "true" transactional storage engine as far as interplay with the binlog is concerned. == Let packaging use some other solution == I suppose it's possible for packaging system to run some script after a package has been installed. that script might find/adjust my.cnf file to have the desired @@gtid_pos_auto_engines setting. This solution might be complex and fragile, though. Any thoughts ? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] exact commands to produce similar mariadb release tarball
by Phil Sweeney 26 Jun '17

26 Jun '17

Hi there, I'm trying to do some custom builds of MariaDB for myself, but want to emulate the release builds as much as possible. I'm doing this on Ubuntu 16.04 LTS and following steps here: https://mariadb.com/kb/en/mariadb/creating-the-mariadb-binary-tarball/ However, my tarball ends up being 227MB compared to the downloads that are ~500MB, plus it won't start because it's missing jemalloc. Where can I find info about the environment and the commandline used to build the official releases e.g. mariadb-10.1.24-linux-glibc_214-x86_64.tar.gz (535.6MB) Thanks! Phil

2 2

Re: [Maria-developers] [Commits] ee73b33: MDEV-8840: ANALYZE FORMAT=JSON produces wrong data with BKA
by Sergey Petrunia 25 Jun '17

25 Jun '17

Hi Varun, On Sat, Jun 17, 2017 at 05:35:41PM +0530, Varun wrote: > revision-id: ee73b331d97d33ae7aa1403a4ac7312f2d261754 (mariadb-10.1.20-323-gee73b33) > parent(s): 056bab0880544d91ea67d18fe8db65b4f6625482 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-06-17 17:34:07 +0530 > message: > > MDEV-8840: ANALYZE FORMAT=JSON produces wrong data with BKA > > The issue was that r_loops, r_rows and r_filtered in ANALYZE FORMAT= JSON were not > calculated for the table on which we were performing the MRR scan in the BKA join > Fixed this by adding respective counter in the JOIN_CACHE_MRR::open and JOIN_CACHE::next > So I apply the patch and run this example (the dataset is from explain_json.test, also pasted below): MariaDB [j5]> analyze format=json select * from t3,t4 where t3.a=t4.a and (t4.c+1 < t3.b+1)\G *************************** 1. row *************************** ANALYZE: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.8859, "table": { "table_name": "t3", "access_type": "ALL", "r_loops": 1, "rows": 10, "r_rows": 10, "r_total_time_ms": 0.0202, "filtered": 100, "r_filtered": 100, "attached_condition": "t3.a is not null" }, "block-nl-join": { "table": { "table_name": "t4", "access_type": "ref", "possible_keys": ["a"], "key": "a", "key_length": "5", "used_key_parts": ["a"], "ref": ["j5.t3.a"], "r_loops": 1, "rows": 1, "r_rows": 11, "r_total_time_ms": 0.0379, "filtered": 100, "r_filtered": 90.909 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "attached_condition": "t4.c + 1 < t3.b + 1", "r_filtered": 0 } } } I think, these value are wrong: > "r_rows": 11, > "r_filtered": 90.909 r_rows is actually 10, r_filtered=100 (no attached condition) I guess wrong r_filtered is caused by r_rows being wrong. ## Dataset: set storage_engine=myisam; set default_storage_engine=myisam; create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2(a int); insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; create table t3(a int, b int); insert into t3 select a,a from t1; create table t4(a int, b int, c int, filler char(100), key (a,b)); insert into t4 select a,a,a, 'filler-data' from t2; set optimizer_switch='mrr=on'; set join_cache_level=6; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] 62021f3: MDEV-12070 - Introduce thd_query_safe() from MySQL 5.7
by Sergei Golubchik 23 Jun '17

23 Jun '17

Hi, Sergey! On Jun 22, Sergey Vojtovich wrote: > revision-id: 62021f391a42c5577190aa43cb8ad91e56235b46 (mariadb-10.2.6-57-g62021f3) > parent(s): 557e1bd472612848a42e772c1fb6f8ed32ab33b4 > committer: Sergey Vojtovich > timestamp: 2017-06-22 17:15:10 +0400 > message: > > MDEV-12070 - Introduce thd_query_safe() from MySQL 5.7 > > Merged relevant part of MySQL revision: > https://github.com/mysql/mysql-server/commit/565d20b44f24fcc855dc616164d87b… > > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index f8cf829..db65eb3 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -4529,6 +4529,32 @@ extern "C" LEX_STRING * thd_query_string (MYSQL_THD thd) > return(&thd->query_string.string); > } > > + > +/** > + Get the current query string for the thread. > + > + @param thd The MySQL internal thread pointer > + @param buf Buffer where the query string will be copied > + @param buflen Length of the buffer > + > + @return Length of the query > + > + @note This function is thread safe as the query string is > + accessed under mutex protection and the string is copied > + into the provided buffer. @see thd_query_string(). > +*/ > + > +extern "C" size_t thd_query_safe(MYSQL_THD thd, char *buf, size_t buflen) > +{ > + mysql_mutex_lock(&thd->LOCK_thd_data); > + size_t len= MY_MIN(buflen - 1, thd->query_length()); > + memcpy(buf, thd->query(), len); > + mysql_mutex_unlock(&thd->LOCK_thd_data); > + buf[len]= '\0'; > + return len; > +} Okay. But as neither thd_query_string() nor thd_query() are part of the plugin API, I'd suggest to remove them, they're inherently unsafe and should not be used. Few other engines use thd_query_string() need to be fixed too (but they bypass the plugin API and we don't promise stability for internal functions). Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

Re: [Maria-developers] [Commits] 922f970: MDEV-12882 - Assertion failed in MDL_context::upgrade_shared_lock
by Sergei Golubchik 23 Jun '17

23 Jun '17

Hi, Sergey! On Jun 22, Sergey Vojtovich wrote: > revision-id: 922f9700a7fb084e3f7d6cff56e27e24fc603ed7 (mariadb-10.2.6-58-g922f970) > parent(s): 62021f391a42c5577190aa43cb8ad91e56235b46 > committer: Sergey Vojtovich > timestamp: 2017-06-22 17:35:36 +0400 > message: > > MDEV-12882 - Assertion failed in MDL_context::upgrade_shared_lock > > Relaxed assertion (in MySQL it was removed). > For "LOCK TABLES t1 WRITE CONCURRENT, t1 READ" upgrade lock to weakest > existing suitable lock, which is MDL_SHARED_NO_READ_WRITE. > > diff --git a/mysql-test/t/mdl.test b/mysql-test/t/mdl.test > new file mode 100644 > index 0000000..5a74ae5 > --- /dev/null > +++ b/mysql-test/t/mdl.test > @@ -0,0 +1,15 @@ Hm. I'd totally expect main.mdl test to exist for years :) > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 412cd1d..7c910e8 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2787,6 +2787,7 @@ static bool lock_tables_open_and_lock_tables(THD *thd, TABLE_LIST *tables) > ! table->prelocking_placeholder && > table->table->file->lock_count() == 0) > { > + enum enum_mdl_type lock_type; > /* > In case when LOCK TABLE ... READ LOCAL was issued for table with > storage engine which doesn't support READ LOCAL option and doesn't > @@ -2799,9 +2800,12 @@ static bool lock_tables_open_and_lock_tables(THD *thd, TABLE_LIST *tables) > deadlock_handler.init(); > thd->push_internal_handler(&deadlock_handler); > > + lock_type= table->table->mdl_ticket->get_type() == MDL_SHARED_WRITE ? > + MDL_SHARED_NO_READ_WRITE : MDL_SHARED_READ_ONLY; > + > bool result= thd->mdl_context.upgrade_shared_lock( > table->table->mdl_ticket, > - MDL_SHARED_READ_ONLY, > + lock_type, > thd->variables.lock_wait_timeout); This only works if you lock WRITE, then READ. If you do it the other way around, you'll get an error. Isn't it inconsistent? Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

[Maria-developers] Condition pushdown into derived tables and outer joins.
by Sergey Petrunia 23 Jun '17

23 Jun '17

Hello Igor, While reviewing MDEV-12845, I've found this piece in sql_select.cc: while ((tbl= li++)) { /* Do not push conditions from where into materialized inner tables of outer joins: this is not valid. */ if (tbl->is_materialized_derived()) { /* Do not push conditions from where into materialized inner tables of outer joins: this is not valid. */ if (!tbl->is_inner_table_of_outer_join()) Q1: Is int meaningful to have two identical comments? Q2: It is not valid push parts of WHERE, but it is valid to push the parts of ON expression. An example: explain format=json select * from t21 LEFT JOIN (select a,b, COUNT(*) as CNT from t22 group by a,b) TBL ON (t21.a=TBL.a AND TBL.a<5); here, "TBL.a<5" can be pushed down into the subquery, but it is not. EXPLAIN EXTENDED confirms it: "query_block": { "select_id": 1, "const_condition": "1", "table": { "table_name": "t21", "access_type": "ALL", "rows": 10, "filtered": 100 }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t21.a"], "rows": 2, "filtered": 100, "attached_condition": "trigcond(trigcond(t21.a < 5 and t21.a is not null))", "materialized": { "query_block": { "select_id": 2, "filesort": { "sort_key": "t22.a, t22.b", "temporary_table": { "table": { "table_name": "t22", "access_type": "ALL", "rows": 10, "filtered": 100 } } } } } } Just in case, the commands to create the dataset were: create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t21 (a int, b int, c int); insert into t21 select a,a,a from ten; create table t22 (a int, b int, c int); insert into t22 select a,a,a from ten; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Project Progress Report [Week 3]
by ANKIT KUMAR 20 Jun '17

20 Jun '17

Hi, In this week, we worked on in-memory structures:- Which representation to choose(prefix or expression tree), How to store values and how authentication will be done. I have prepared pseudocode to implement this subtask and have made implemented some part of code. Hopefully by 25th May, I will finish off with >50% of the project. I will declare Phase-2 deliverables next Monday(26 May). Kind regards, Ankit Kumar

2 1

Re: [Maria-developers] MDEV-12620 - set lock_wait_timeout = 1; flush tables with read lock;
by Sergei Golubchik 16 Jun '17

16 Jun '17

Hi, Sergey! On Jun 16, Sergey Vojtovich wrote: > revision-id: 056bab0880544d91ea67d18fe8db65b4f6625482 (mariadb-10.1.24-22-g056bab0) > parent(s): 58f87a41bd8de7370cc05c41977fadc685826c9e > committer: Sergey Vojtovich > timestamp: 2017-06-16 15:47:46 +0400 > message: > > MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock; > lock not released after timeout > > Release GRL if FLUSH TABLES phase failed. > > diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test > index 8e80978..c479c2a 100644 > --- a/mysql-test/t/mdl_sync.test > +++ b/mysql-test/t/mdl_sync.test > @@ -4806,6 +4806,30 @@ disconnect con2; > disconnect con3; > > +--echo # > +--echo # MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock; > +--echo # lock not released after timeout > +--echo # > +CREATE TABLE t1(a INT) ENGINE=InnoDB; > +SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; > +send SELECT * FROM t1; > + > +connect (con1,localhost,root,,); > +SET debug_sync='now WAIT_FOR ready'; > +# lock_wait_timeout should be 0 in 10.3, so that we don't have to wait at all what's special in 10.3? > +SET lock_wait_timeout=1; > +--error ER_LOCK_WAIT_TIMEOUT > +FLUSH TABLES WITH READ LOCK; > +SET debug_sync='now SIGNAL go'; > + > +connection default; > +reap; > +SET debug_sync='RESET'; > +DROP TABLE t1; What should happen without a bug fix? DROP TABLE hangs? > + > +disconnect con1; > + > # Check that all connections opened by test cases in this file are really > # gone so execution of other tests won't be affected by their presence. > --source include/wait_until_count_sessions.inc > diff --git a/sql/sql_reload.cc b/sql/sql_reload.cc > index 376dfeb..d68ce96 100644 > --- a/sql/sql_reload.cc > +++ b/sql/sql_reload.cc > @@ -253,7 +253,8 @@ bool reload_acl_and_cache(THD *thd, unsigned long long options, > NOTE: my_error() has been already called by reopen_tables() within > close_cached_tables(). > */ > - result= 1; > + thd->global_read_lock.unlock_global_read_lock(thd); > + return 1; > } Sure. Looks correct. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1