- developers - lists.mariadb.org

[Maria-developers] Progress Report GSoC 2020 (10-16 August)
by Anton Mikhaylenko 17 Aug '20

17 Aug '20

Good day, everybody. In this post, I want to describe what I did this week, August 10-16. This week I've running benchmarks for threadpool and compare results. Regards, Anton Mikhailenko

1 0

[Maria-developers] 5dc2ebae474: MDEV-22330: mysqlbinlog stops with an error Don't know how to handle column type: 255 meta: 4 (0004)
by sujatha 17 Aug '20

17 Aug '20

revision-id: 5dc2ebae47481afe5b0769f0e0463d64b77143c0 (mariadb-10.1.43-260-g5dc2ebae474) parent(s): 101ce10d0ddda1e38806b8d2c491298482e7ab78 author: Sujatha committer: Sujatha timestamp: 2020-08-13 09:58:25 +0530 message: MDEV-22330: mysqlbinlog stops with an error Don't know how to handle column type: 255 meta: 4 (0004) Analysis: ======== "mysqlbinlog -v" option will reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata. `log_event_print_value` is the function responsible for printing values and their types. This function doesn't handle GEOMETRY type. Hence the above error gets printed. Fix: === Add support for GEOMETRY datatype. --- .../suite/binlog/r/binlog_mysqlbinlog_row.result | 56 +++++++++++++++++++++- .../suite/binlog/t/binlog_mysqlbinlog_row.test | 14 +++++- sql/log_event.cc | 17 +++++-- 3 files changed, 80 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result index 9a3f4751165..0b05c4f449f 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row.result @@ -339,7 +339,11 @@ a 123.47 999.99 DROP TABLE t1dec102; -flush logs; +CREATE TABLE t1 (a GEOMETRY DEFAULT NULL); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (POINT(10,10)); +DROP TABLE t1; +FLUSH LOGS; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; @@ -4623,6 +4627,56 @@ SET TIMESTAMP=1000000000/*!*/; DROP TABLE `t1dec102` /* generated by server */ /*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # GTID 0-1-321 ddl +/*!100001 SET @@session.gtid_seq_no=321*//*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +CREATE TABLE t1 (a GEOMETRY DEFAULT NULL) +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # GTID 0-1-322 +/*!100001 SET @@session.gtid_seq_no=322*//*!*/; +BEGIN +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number # +# at # +#010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F +### INSERT INTO `test`.`t1` +### SET +### @1=NULL /* GEOMETRY meta=4 nullable=1 is_null=1 */ +# at # +#010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +COMMIT +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # GTID 0-1-323 +/*!100001 SET @@session.gtid_seq_no=323*//*!*/; +BEGIN +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # Table_map: `test`.`t1` mapped to number # +# at # +#010909 4:46:40 server id 1 end_log_pos # Write_rows: table id # flags: STMT_END_F +### INSERT INTO `test`.`t1` +### SET +### @1='\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00$@\x00\x00\x00\x00\x00\x00$@' /* GEOMETRY meta=4 nullable=1 is_null=0 */ +# at # +#010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +COMMIT +/*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # GTID 0-1-324 ddl +/*!100001 SET @@session.gtid_seq_no=324*//*!*/; +# at # +#010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 +SET TIMESTAMP=1000000000/*!*/; +DROP TABLE `t1` /* generated by server */ +/*!*/; +# at # #010909 4:46:40 server id 1 end_log_pos # Rotate to master-bin.000002 pos: 4 DELIMITER ; # End of log file diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test index 0c94d968338..93c26cc57a2 100644 --- a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test @@ -450,7 +450,19 @@ INSERT INTO t1dec102 VALUES (999.99); SELECT * FROM t1dec102 ORDER BY a; DROP TABLE t1dec102; -flush logs; + +# +# MDEV-22330: mysqlbinlog stops with an error Don't know how to handle column +# type: 255 meta: 4 (0004) +# Check support for GEOMETRY type with verbose mode. +# +CREATE TABLE t1 (a GEOMETRY DEFAULT NULL); + +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (POINT(10,10)); +DROP TABLE t1; + +FLUSH LOGS; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /((a)[0-9]*=[0-9]*[.][0-9]{1,3})[0-9e+-]*[^ ]*(.*(FLOAT|DOUBLE).*[*].)/\1...\2/ diff --git a/sql/log_event.cc b/sql/log_event.cc index a1a442df43f..e8121ba35ff 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -2575,13 +2575,20 @@ log_event_print_value(IO_CACHE *file, const uchar *ptr, "Not enough metadata to display the value. "); break; + case MYSQL_TYPE_GEOMETRY: + strmake(typestr, "GEOMETRY", typestr_length); + if (!ptr) + goto return_null; + + length= uint4korr(ptr); + my_b_write_quoted(file, ptr + meta, length); + return length + meta; + default: { - char tmp[5]; - my_snprintf(tmp, sizeof(tmp), "%04x", meta); - my_b_printf(file, - "!! Don't know how to handle column type=%d meta=%d (%s)", - type, meta, tmp); + fprintf(stderr, + "\nError: Don't know how to handle column type: %d meta: %d (%04x)\n", + type, meta, meta); } break; }

1 0

[Maria-developers] Weekly Report
by Kartik Soneji 10 Aug '20

10 Aug '20

Hi, This week - Had a meeting with Robert about further actions regarding GSoC and discussed expectations for the end of the month. - Started extending the tests to include all combinations of engine + library. let %TEST_MATRIX = { "connect" => ("bzip2", ), "innodb" => ("bzip2", "lz4", "lzma", "lzo", "snappy" ), "mroonga" => ( "lz4", "zstd"), "rocksdb" => ("bzip2", "lz4", "lz4hc", "snappy", "zstd") }; - Started writing a HOWTO about adding a new compression library in sql/compression/HOWTO. - Finished the first draft of the ZStd service. Since ZStd and RocksDB are both written by Facebook, RocksDB makes extensive use of the ZStd functions. RocksDB also compiles with different sets of functions depending on the compile time version of ZStd, so we need to decide on a minimum version number and enforce it while loading the library. Next week - Finish the implementation for ZStd. - Continue writing the HOWTO file. - Update the old service implementations. - Continue replicating the implementation for other libraries. Thanks, Kartik

1 0

[Maria-developers] Progress Report GSoC 2020 (3-9 August)
by Anton Mikhaylenko 10 Aug '20

10 Aug '20

Good day, everybody. In this post, I want to describe what I did this week, August 3-9. This week, I wrote a function, that gets current CPU Utilization for Windows and Linux systems. Also, I merged commits with squash into one. Regards, Anton Mikhailenko

1 0

[Maria-developers] 0664e1e766d: MDEV-22338: Assertion `!current_stmt_is_commit || !rgi->tables_to_lock' failed in Query_log_event::do_apply_event
by sujatha 10 Aug '20

10 Aug '20

revision-id: 0664e1e766d303187b381ace1c53547d1821e35c (mariadb-10.1.43-252-g0664e1e766d) parent(s): 85bd5314c56c150d756066806d4a6cb5b682383f author: Sujatha committer: Sujatha timestamp: 2020-08-10 12:29:48 +0530 message: MDEV-22338: Assertion `!current_stmt_is_commit || !rgi->tables_to_lock' failed in Query_log_event::do_apply_event Analysis: ========= With row based replication, master can generate event groups like shown below. BEGIN TABLE_MAP COMMIT i.e A binlog event group, without any rows event in it. Such groups are possible when a query operating on transactional table, doesn't modify any data in the table. For example a transaction is trying to modify both trans and non-trans tables and a trigger exists on non-transtable which modifies a trans table. CREATE TABLE t1 (id int) engine=innodb; CREATE TABLE t2 (id int) engine=myisam; CREATE TRIGGER t2 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM t1 LIMIT 1; INSERT INTO t2 VALUES (1); Above insert will write a record in 't2' table and its corresponding binlog entry is generated. First table map events are added to appropriate caches. t2's Table_Map is written into statement_cache. t1's Table_Map is written into transaction_cache. 'Write_rows' event is added to t2's statement_cache. Since there are no rows to remove from table 't1' there will not be any 'Delete_rows' event in the transaction cache. There won't be any 'Xid' event as well. During flush operation 'COMMIT' will be added to the transaction cache to complete the group. "t2's statement_cache" "t1's transaction_cache" BEGIN BEGIN TABLE_MAP TABLE_MAP WRITE_ROWS COMMIT COMMIT On the slave side it has strict rules, that a 'Table_Map' event should be followed by a rows event. This rule gets violated for 't1' transaction and results in an assert. Fix: ==== Replace the assert with if-else block. In a case where TABLE_MAP event is not followed by rows event invoke 'rows_event_stmt_cleanup' to initiate appropriate cleanup. The fix is based on upstream patch: commit da01f3846b62a2e41111256f9d6f0a2bb2de23c4 Author: Andrei Elkin <aelkin(a)mysql.com> --- .../suite/rpl/r/rpl_no_rows_event_assert.result | 26 ++++++++++++ .../suite/rpl/t/rpl_no_rows_event_assert.test | 46 ++++++++++++++++++++++ sql/log_event.cc | 22 ++++++++++- 3 files changed, 92 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_no_rows_event_assert.result b/mysql-test/suite/rpl/r/rpl_no_rows_event_assert.result new file mode 100644 index 00000000000..7661bb62305 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_no_rows_event_assert.result @@ -0,0 +1,26 @@ +include/master-slave.inc +[connection master] +######################################################################### +# Test case1: Transaction cache has 'Table_Map' event followed by # +# 'COMMIT'. # +######################################################################### +CREATE TABLE t1 (id int) ENGINE=INNODB; +CREATE TABLE t2 (id int) ENGINE=MYISAM; +CREATE TRIGGER t2 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM t1 LIMIT 1; +INSERT INTO t2 VALUES (1); +DROP TABLE t1,t2; +######################################################################### +# Test case2: Transaction cache has 'Query', 'Table_Map' followed by # +# 'COMMIT'. # +######################################################################### +CREATE TABLE t1(f INT) ENGINE=INNODB; +CREATE TABLE t2(f INT) ENGINE=MYISAM; +CREATE TABLE t3(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE t4(f INT) ENGINE=MYISAM; +CREATE TRIGGER trig1 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3(i) SELECT * FROM t4 LIMIT 0; +BEGIN; +DELETE FROM t1 WHERE f=0; +INSERT INTO t2 VALUES (2); +COMMIT; +DROP TABLE t1,t2,t3,t4; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_no_rows_event_assert.test b/mysql-test/suite/rpl/t/rpl_no_rows_event_assert.test new file mode 100644 index 00000000000..c5cc7634615 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_no_rows_event_assert.test @@ -0,0 +1,46 @@ +--source include/have_innodb.inc +--source include/have_binlog_format_mixed_or_row.inc +--source include/master-slave.inc +# ==== References ==== +# +# MDEV-22338: Assertion `!current_stmt_is_commit || !rgi->tables_to_lock' +# failed in Query_log_event::do_apply_event +# + +--echo ######################################################################### +--echo # Test case1: Transaction cache has 'Table_Map' event followed by # +--echo # 'COMMIT'. # +--echo ######################################################################### +--connection master +CREATE TABLE t1 (id int) ENGINE=INNODB; +CREATE TABLE t2 (id int) ENGINE=MYISAM; +CREATE TRIGGER t2 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM t1 LIMIT 1; +INSERT INTO t2 VALUES (1); +--sync_slave_with_master + +# Cleanup +--connection master +DROP TABLE t1,t2; + +--echo ######################################################################### +--echo # Test case2: Transaction cache has 'Query', 'Table_Map' followed by # +--echo # 'COMMIT'. # +--echo ######################################################################### + +CREATE TABLE t1(f INT) ENGINE=INNODB; +CREATE TABLE t2(f INT) ENGINE=MYISAM; +CREATE TABLE t3(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE t4(f INT) ENGINE=MYISAM; +CREATE TRIGGER trig1 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3(i) SELECT * FROM t4 LIMIT 0; + +BEGIN; +DELETE FROM t1 WHERE f=0; +INSERT INTO t2 VALUES (2); +COMMIT; +--sync_slave_with_master + +# Cleanup +--connection master +DROP TABLE t1,t2,t3,t4; + +--source include/rpl_end.inc diff --git a/sql/log_event.cc b/sql/log_event.cc index a1a442df43f..c37512aa6c1 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -4329,8 +4329,26 @@ int Query_log_event::do_apply_event(rpl_group_info *rgi, thd->clear_error(1); current_stmt_is_commit= is_commit(); - DBUG_ASSERT(!current_stmt_is_commit || !rgi->tables_to_lock); - rgi->slave_close_thread_tables(thd); + if (strcmp("COMMIT", query) == 0 && rgi->tables_to_lock != NULL) + { + /* + Cleaning-up the last statement context: + Found a row based event group which didn't modfiy any rows. i.e. + BEGIN + TABLE_MAP + COMMIT + */ + int error; + if ((error = rows_event_stmt_cleanup(rgi, thd))) + { + rli->report(ERROR_LEVEL, error, "Error in cleaning up after an event " + "preceding the commit; the group log file/position: %s %llu", + rli->group_master_log_name, + (ulong) rli->group_master_log_pos); + } + } + else + rgi->slave_close_thread_tables(thd); /* Note: We do not need to execute reset_one_shot_variables() if this

1 0

Re: [Maria-developers] [Commits] c776cad5f8e: MDEV-22702: Assertion `!field->is_null()' failed in my_decimal::my_decimal
by Sergey Petrunia 06 Aug '20

06 Aug '20

Hi Varun, First: I can still get the assertion failure if I run this: set @@sql_mode='only_full_group_by'; select min(1 mod a1), bit_or(a2) over () from t1; As far as I understand, the query is not valid for only_full_group_by mode. (Window functions as such are ok, but here window function is using a2, which cannot be accessed in the post-grouping context). More input below. On Wed, Jul 08, 2020 at 02:58:31PM +0530, Varun wrote: > revision-id: c776cad5f8ecf2675510deeb55724d7255a52503 (mariadb-10.4.11-267-gc776cad5f8e) > parent(s): cc0dca366357651ddb549e31a12b1ecd39c7380e > author: Varun Gupta > committer: Varun Gupta > timestamp: 2020-07-08 14:58:17 +0530 > message: > > MDEV-22702: Assertion `!field->is_null()' failed in my_decimal::my_decimal > > With implicit grouping with window functions, we need to make sure that all the > fields inside the window functions are nullable as any non-aggregated field can > produce a NULL value. ... > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 0983cea44d0..16a0b3b08a6 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -1510,6 +1510,8 @@ class st_select_lex: public st_select_lex_node > } > > bool have_window_funcs() const { return (window_funcs.elements !=0); } > + uint32 get_number_of_window_funcs() const > + { return (uint32)window_funcs.elements; } Why use uint32 when n_sum_items is uint, and elements is also uint? I suggest just keep using uint. > ORDER *find_common_window_func_partition_fields(THD *thd); > > bool cond_pushdown_is_allowed() const > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 0ca5ab23288..05463efe9a5 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -1201,6 +1201,18 @@ JOIN::prepare(TABLE_LIST *tables_init, > break; > } > } > + /* > + If the query has a window function with an aggregate function, > + then also we have a mix of elements with and without grouping. > + Window function can be in the ORDER BY clause too so the check > + is made separately. Wording could be better. I don't understand what did you mean to say about ORDER BY. can you elaborate? > + Window function is inherited from Item_sum so each window function is > + also registered as a sum item, so need to check that we have an > + explicit aggregate function also in the query. what's "explicit aggregate" ? A regular aggregate function (i.e. not a window function)? > + */ > + if (select_lex->have_window_funcs() && > + select_lex->get_number_of_window_funcs() < select_lex->n_sum_items) > + mixed_implicit_grouping= true; > } > > table_count= select_lex->leaf_tables.elements; BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Optimizer call followup: for the parser, JSON_TABLE is not a "table function"
by Sergei Petrunia 05 Aug '20

05 Aug '20

Hello, A followup to the optimizer call discussion: Here is the production from the SQL Standard describing JSON_TABLE: section 7.6 <table reference>: <table primary> ::= <table or query name> [ <query system time period specification> ] [ <correlation or recognition> ] | <derived table> <correlation or recognition> | <lateral derived table> <correlation or recognition> | <collection derived table> <correlation or recognition> | <table function derived table> <correlation or recognition> | <only spec> [ <correlation or recognition> ] | <data change delta table> [ <correlation or recognition> ] | <JSON table> <correlation or recognition> | <JSON table primitive> <correlation name> | <parenthesized joined table> <table function derived table> ::= TABLE <left paren> <collection value expression> <right parent> Section 7.11 <JSON table>: <JSON table> ::= JSON_TABLE <left paren> ....... <right parent> That is, JSON_TABLE and TABLE (...) are two different productions that have nothing in common. So, as far as syntax is concerned, JSON_TABLE is not a "table function". BR -- Sergei P.

1 0

[Maria-developers] Weekly Report
by Kartik Soneji 03 Aug '20

03 Aug '20

Hi, This week - Finished writing common testing foundation that allows other services to be tested easily. For example: --let $test_library = lzma --source test_library.inc --let $test_library = lzo --source test_library.inc ... - Finished the first draft of the Snappy service. Snappy has a C++ api that is used by RocksDB, so we have two options: - Use the mangled C++ function names directly, and load them normally with dlopen. - Re-implement the C++ function from Snappy’s C exports. The mangled function names may vary across platforms, so we are going with the second option for now. - Implemented global status variables for the services. This lays the foundation for integration with the storage engines. - Finished a first draft of the auto logic for the --use-compression= switch. Next week - Need to review my refactoring and decide on next steps. Details of refactor: - Removed the service struct. - Removed the struct from sql/sql_plugin_services.ic. - Define an anonymous struct in sql/compression/<library>.cc. - Initialize the struct with dummy function pointers. - If the library is successfully loaded, replace them with pointers to the real functions. - Define wrapper functions that forward the call to the function pointers in the struct. - Continue replicating the implementation for other libraries. - Decide what to do when dynamic libraries have the versions suffixed to the .so file. For example, liblzma.so.5.2.4 is included with Ubuntu 20, but liblzma.so is not. The liblzma.so -> liblzma.so.5.2.4 symlink is only added after installing the liblzma-dev package. - Sync up with Robert to discuss further actions. Thanks, Kartik

1 0

[Maria-developers] Review for: MDEV-17399 Add support for JSON_TABLE, part #9
by Sergey Petrunia 03 Aug '20

03 Aug '20

Hi Alexey, More input: == Circular dependencies are allowed == This query will cause an assert in the optimizer, for obvious reasons: select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; == Character set inference is wrong == create table t20 (json varchar(100) character set utf8); insert into t20 values ('{"value":"АБВ"}'); create table tj20 as select T.value from t20, json_table(t20.json, '$' columns (value varchar(32) PATH '$.value')) T; mysql> show create table tj20\G *************************** 1. row *************************** Table: tj20 Create Table: CREATE TABLE `tj20` ( `value` varchar(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The source document uses UTF-8 but the values from it are in latin1? == Comments and code cleanup == There are a lot of comments missing, some duplicate code, and code using out-of-date ways of doing things. Please find attached a patch with obvious fixes for most of it. == Explain == Let's follow MySQL here: let the tabular EXPLAIN show "Table function: json_table" in the Extra column, let EXPLAIN FORMAT=JSON show "table_function": "json_table" in the "table" element. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Obtaining usernames in MariaDB UDFs
by Denis Scherbakov 02 Aug '20

02 Aug '20

Hello guys, I am building a set of MariaDB UDFs that require to know a name of a user who called them in each and every call. Users should not be able to pretend as someone else, hence usernames as an argument cannot be used. I compile UDFs against MariaDB headers and I was able to obtain the current_thd, but the security_ctx pointer inside is NULL. Could somebody give me a hint what I am doing wrong? Thank you for your answers! Best regards, Denis Scherbakov

2 2