January 2020 - developers - lists.mariadb.org

Re: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification
by Aleksey Midenkov 08 Jun '21

08 Jun '21

Hello, Sergei! On Fri, May 3, 2019 at 8:43 PM Sergei Golubchik <serg(a)mariadb.com> wrote: > > Hi, Aleksey! > > On May 03, Aleksey Midenkov wrote: > > revision-id: ef2519fee4e (versioning-1.0.5-17-gef2519fee4e) > > parent(s): 56145be2951 > > author: Aleksey Midenkov <midenok(a)gmail.com> > > committer: Aleksey Midenkov <midenok(a)gmail.com> > > timestamp: 2018-06-28 13:42:09 +0300 > > message: > > > > MDEV-16546 System versioning setting to allow history modification > > > > 1. Add server variable system_versioning_modify_history which will > > allow to set values for row_start, row_end in DML operations. > > > > 2. If secure_timestamp is YES or REPLICATION, > > system_versioning_modify_history does not have effect. If > > secure_timestamp is SUPER, system_versioning_modify_history requires > > special privilege (same as for setting current timestamp). > > I thought more about this idea. We don't really want to have the history > editable, do we? Well, I'm thinking about rollback table data to specific point in time. That could be a useful feature. > But it's needed for replication, to keep the master and > slave identical. That's what secure_timestamp is for. > > The idea was that this new variable, system_versioning_modify_history, > will be just a convenience feature, it will not allow history editing > any more than one can do without it. > > But now I suspect that even with secure_timestamp=NO one cannot truly > edit history. One can only insert new rows with arbitrary timestamps. > For example, to insert a row with row_start=1000 and row_end=2000, one > needs to do (if secure_timestamp=NO): > > set timestamp=1000; > insert row; > set timestamp=2000; > delete row; > > But I don't see how one can update or delete a history row with > secure_timestamp=NO. > > Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can > use the BINLOG command and truly edit the history arbitrarily, by faking > row events. I don't really get it why this is so important: since there is some limitation by configuration and privilege, we are just fine. Everything can be changed at filesystem level after all. > > The conclusion, I believe, is that system_versioning_modify_history > should allow INSERTs when secure_timestamp=NO, and it should allow > UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER. I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER? > > The second thing I don't like at all, is when a table is created like > > CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING > > with row_start/row_end implicit. You don't have it in the test, but > anyway one should be able to load history into such a table, while the > table does not have row_start and row_end columns. From the user point > of view these columns don't exist, they're pseudo-columns, like ROWID. > They just cannot be insertable-into, conceptually. But a user will want > to restore the history, right? I don't have a solution for this yet :( > Any ideas? We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that? > > See below a couple of minor comments about the patch itself. > > ... These are going to be fixed. > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org -- All the best, Aleksey Midenkov @midenok

2 15

[Maria-developers] Lag Free ALTER Table for Slave
by Sachin Setiya 27 Feb '20

27 Feb '20

Hi Everyone! This mail will describe usage and implementation of Lag Free Alter. And this will also answer the question raised by Kristian and Simon J Mudd(in 2016) Desc:- This will split Alter into 2 different commits. START ALTER and COMMIT /ROLLBACK ALTER , Start Alter will be written in binlog as soon as we get the locks for the table, alter will proceeds as usual and at the time of writing binlog if alter is successful we will write COMMIT Alter other wise ROLLBACK Alter. Usage:- Using this feature is quite simple. 1. On master you have to turn on `BINLOG_SPLIT_ALTER` dynamic variable. 2. Slave must be using parallel replication. Advance Usage:- So alter is divided like this. 1. START identifier Actual_alter_stmt 2. COMMIT/ROLLBACK identifier Actual_alter_stmt. OR 2. COMMIT/ROLLBACK identifier ALTER identifier is thread_id. Questions by Simon Mudd. >>* this behaviour should be configurable? Yes. >> - new global variable on the master to allow injection of this changed event stream? Right , `BINLOG_SPLIT_ALTER` >> - a new session variable set in the session where the command is triggered ? Right , `BINLOG_SPLIT_ALTER` >> - on slave a setting to indicate how many INPLACE ALTER TABLEs can run at once? No setting so far , but I am thinking of maximum no of CONCURRENT ALTER = slave_parallel_threads >>* how does a DBA monitor what’s going on? >> - the progress and number of active DDL statements So as we there is 2 part, So progress will go like this 1. Executing of start alter (this will take most of time) 2. Waiting for commit/rollback Signal 3. Commit/ Rollback Alter. Number of active ALTER , these will create new threads so DBA can know using this or I am thinking of adding variable in SHOW SLAVE INFO. which will show active DDL. >> - please consider adding counters/metrics for: >> * number of “asynchronous DDLs” in progress / completed successfully / failed or rolled back Okay, We can have counter for these metrics. If i get time to implement this. >> * sizes of the DDL changes made so far Not sure if we need this. >> * number of threads busy in this state (maybe can be implied from SHOW PROCESSLIST or equivalent but nicer to have an easy to query metric) Show processlist will show the busy threads. Sample Output when concurrent alter is running on slave. slave_parallel_threads= 10 Every 1.0s: mysql -uroot -S /home/sachin/alter/build/mysql-test/var/tmp/mysqld.2.sock -e show processlist sachin-sp52: Wed Jan 22 16:56:00 2020 Id User Host db Command Time State Info Progress 1 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000 3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 2 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000 5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000 10 root localhost:44478 test Sleep 6 NULL 0.000 11 root localhost:44480 test Sleep 7 NULL 0.000 15 root localhost:44492 test Sleep 6 NULL 0.000 16 root localhost:44494 test Sleep 6 NULL 0.000 17 system user NULL Slave_IO 6 Waiting for master to send event NULL 0.000 19 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 22 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 20 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 23 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 24 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 25 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 26 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 21 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 27 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 28 system user NULL Slave_worker 6 Waiting for work from SQL thread NULL 0.000 18 system user NULL Slave_SQL 6 Slave has read all relay log; waiting for the slave I/O thread to update it NULL 0.000 29 system user test Slave_worker 6 NULL /*!100001 START 19 alter table t3 add column c int, force, algorithm=inplace */ 0.000 30 system user test Slave_worker 6 NULL /*!100001 START 17 alter table t1 add column c int, force, algorithm=inplace */ 0.000 31 system user test Slave_worker 6 NULL /*!100001 START 18 alter table t2 add column c int, force, algorithm=inplace */ 0.000 32 system user test Slave_worker 6 NULL /*!100001 START 20 alter table t4 add column c int, force, algorithm=inplace */ 0.000 33 system user test Slave_worker 6 NULL /*!100001 START 21 alter table t5 add column c int, force, algorithm=inplace */ 0.000 34 root localhost NULL Query 0 Init show processlist 0.000 >>* what happens if the commit/rollback ddl event never arrives? (the statement on the master could be skipped for one of several reasons) So here are 2 options If slave is still running DBA can manually run COMMIT/ROLLBACK identifier alter_stmt/ALTER , where identifier is same as START ALTER identifier. so this will either commit or rollback alter. If slave is not running. Since slave is not running all context is lost , so this will take as much as time a normal alter. >> - I assume that users on the slave see the old structure all the time until it completes Right, we work on copy of alter for myisam and for other case innodb will take care of it. >> - would this have a storage or performance penalty on the slave if the commit/rollback DDL event never arrives? Not performance but yes storage and ram penalty. >> - can a “commit” / “rollback” be manually triggered by the DBA in such circumstances? yes. >>* what happens if the server crashes while this process (or these processes) are ongoing when the server starts up again? So if we crash in START ALTER no issue , we can run it again , since it is transactional(we are working on copy so ..) If we crash after start alter and we receive COMMIT alter then it will be treated as normal alter. ROLLBACK ALTER will be instant. If we crash in COMMIT ALTER this will be same as crash in normal ALTER table , so DBA has to deal with it. Architecture:- Lets look at sample alter perf data for innodb and myisam Myisam + 96.72% 0.00% mysqld mysqld [.] mysql_parse + 96.72% 0.00% mysqld mysqld [.] mysql_execute_command + 96.72% 0.00% mysqld mysqld [.] Sql_cmd_alter_table::execute - 96.72% 0.00% mysqld mysqld [.] mysql_alter_table - mysql_alter_table - 96.63% copy_data_between_tables + 79.64% handler::ha_write_row + 6.24% TABLE::update_default_fields + 5.71% READ_RECORD::read_record + 1.87% do_copy_null 1.05% Field::do_field_int + 0.95% _mcount InnoDB + 41.27% 0.00% mysqld mysqld [.] mysql_parse + 41.27% 0.00% mysqld mysqld [.] mysql_execute_command + 41.27% 0.00% mysqld mysqld [.] Sql_cmd_alter_table::execute - 41.27% 0.00% mysqld mysqld [.] mysql_alter_table - mysql_alter_table - 41.27% mysql_inplace_alter_table - 41.15% handler::ha_inplace_alter_table ha_innobase::inplace_alter_table - row_merge_build_indexes - 41.00% row_merge_read_clustered_index + 22.88% row_merge_insert_index_tuples + 5.89% row_build_w_add_vcol + 5.20% BtrBulk::finish + 3.20% row_merge_buf_add + 0.87% page_cur_move_to_next 0.62% rec_get_offsets_func So as we can see most of the work is done by copy_data_between_tables/ mysql_inplace_alter_table. Master Side:- There is not much change into master side. So after locking table and before executing these functions we write START _id_ alter_stmt into binlog. And at the time of write_bin_log we write COMMIT _id_ alter_stmt into binlog. So alter statement will be divided into 2 , hence 2 gtid. START_ALTER will have special flag FL_START_ALTER_E1(4), this will be used on slave side to create new worker thread for start alter processing. Slave Side:- This require parallel replication on slave side. So in do_event when we get a gtid_event with FL_START_ALTER_E1, ::choose_thread will call rpl_parallel_add_extra_worker, which will create a new worker thread and this will do start alter processing. So the gtid_log_event and next Query_log_event will be scheduled to this new thread. And this thread will be exited as soon as alter is finished. On slave side START ALTER will binlog after successfully getting MDL lock and thd lock. Untill this point we are executed as DDL(so new GCO), but after getting locks we will call finish_event_group. So that new events can proceed in parallel. Then it will continue to execute code in mysql_alter_table until we reach a non transactional part(like renaming table/dropping table in myisam) So before executing NON-Transactional part it will wait signal from other worker thread to either abort or proceed forward. We will add a entry into mi-> start_alter_list, with thread_id(of master) as a key. COMMIT/ROLLBACK Alter is treated as a normal query_log_event so it will be assigned normal worker. This command will take SQLCOM_COMMIT_PREVIOUS path in mysql_execute_command. We will simple search for thread_id into start_alter_list and change status from ::WAITING to ::COMMIT_ALTER and signal the wait condition if we dont find the thread_id we will wait on wait condition, So simple it is just consumer producer between start alter and commit/rollback. Questions by Kristian:- >Can you explain what you mean by "true LOCK=NONE"? It is not clear from your >description. I think you mean something that will allow to run an ALTER >TABLE on the slave in parallel with the same statement on the master? Yes >There will be a number of things to consider with this. Previously, all >transactions in the binlog have been atomic; this introduces related events >that can be arbitrarily separated in the binlog. >For example, suppose the slave stops in the middle between BEGIN_DDL_EVENT >and COMMIT_DDL_EVENT/ROLLBACK_DDL_EVENT. What is then the slave's binlog >position / GTID position? Now we are using 2 gtid for ALTER , So it wont be issue. >Hopefully, the exiting pool of slave worker threads (currently used for >parallel replication) can be used for this as well? Doesn't seem right to >introduce a new kind of thread pool... We are using same(global) thread pool but I am creating new threads for START ALTER. Otherwise we will have a deadlock , Suppose if we have 5 concurrent START ALTER and just 5 slave-worker-threads , all worker will be waiting for COMMIT/ROLLBACK, but we cant execute commit rollback because we dont have any free worker. There can be more case when we have concurrent DML too , so creating a new thread was the safest option. >Won't you need some mechanism to prevent following events to access the >table before ALTER TABLE in a worker thread can acquire the metadata lock? Right, So Untill Locking START ALTER is executed as DDL so no following event will be executed before locking. And one more thing regarding locks , So there will we only one thread will be doing whole work of alter, (we can just assume that there is some sleep on slave) so execution on master and slave will be equivalent. >There are some nasty issues to be aware of with potential deadlocks related >to FLUSH TABLES WITH GLOBAL READ LOCK and such, when multiple threads are >trying to coordinate waiting and locking; there were already some quite hard >issues with this for parallel replication. I have to test for it. Code branch bb-10.5-olter Jira (Mdev-11675(https://jira.mariadb.org/browse/MDEV-11675)) Regards Sachin -- Regards Sachin Setiya Software Engineer at MariaDB

3 4

Re: [Maria-developers] bd98ef106c8: MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
by Sergei Golubchik 20 Feb '20

20 Feb '20

Hi, Aleksey! 1. It should be possible to enable/disable auto-creation. For example, CREATE TABLE ... PARTITION BY SYSTEM_TIME ... PARTITIONS AUTO; this solves few problems at once: * a user explicitly tells when auto-creation should work * you don't need to worry "if this name is already occupied" * you can define that these partitions can never overflow (for INTERVAL) * if you know that AUTO partitions never overflow, you can keep the old behavior for ALTER TABLE ADD PARTITION. 2. Separate thread is an interesting solution. Nicely avoids lots of problems. Still: * it's asynchronous, all tests have to take it into account * it's racy. "low probability" or not, with our number of users they'll hit it and rightfully will report it as a bug * if LOCK TABLE prevents it, partitions can overflow But I think these problems are easier to solve than those we'll face if auto-creation will happen in the main connection thread. So, let's keep your approach with a thread. But instead of going through the parser and mysql_execute_command, create a function that takes a TABLE or a TABLE_SHARE. And adds a partition there. It'll fix the "racy" part. This function can be called from a new thread. As for the LOCK TABLES - if you're under LOCK TABLES, you can simply call that function (to add a partition) directly at the end of the main INSERT/UPDATE/DELETE statement. It'll solve the last problem, LOCK TABLES won't prevent auto-creation. A couple of style comments: 1. create a one-liner make_partition_name (or something) with: sprintf(move_ptr, "p%u", i) and use it both in create_default_partition_names() and in your new code. Just to make sure partition names are always generated uniformly by the same function and if we'll want to change it to "pp%u" it should be done only in one place. 2. don't overload operators, use, say, bool greater_than(size_t seconds) But overall it's pretty good. A nice idea with a separate thread. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org On Nov 07, Aleksey Midenkov wrote: > revision-id: bd98ef106c8 (mariadb-10.4.7-55-gbd98ef106c8) > parent(s): 6d1fa01e8f1 > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2019-08-19 12:01:14 +0300 > message: > > MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT > > When there are E empty partitions left, auto-create N new empty > partitions. > > This scheme must not allow partition overflow. I.e. E-fill time must > not exceed N-creation time. This means that low values for INTERVAL > and LIMIT must not be allowed for auto-creation. In case when overflow > is detected there is no need to do anything special: a warning will be > issued and the user will run manual rebuild to redistribute records > correctly. This is important because automatic ADD must be done fast, > without forced rebuild, by the obvious reason. > > Initial version implements hard-coded values of 1 for E and N. As well > as auto-creation threshold MinInterval = 1 hour, MinLimit = 1000. > > The name for newly added partition will be first chosen as "pX", where > X is partition number and "p" is hard-coded name prefix. If this name > is already occupied, the X will be incremented until the resulting > name will be free to use. > > Auto-creation mechanism is applied to every table having LIMIT or > INTERVAL clause. Note that there is no much sense in specifying > explicit partition list in this case and this is covered by > MDEV-19903. The syntax to explicitly turn it on/off as well as > user-defined values for E, N and name prefix is subject for further > discussion and feature requests. > > ALTER TABLE ADD PARTITION is now always fast. If there some history > partition overflow occurs manual ALTER TABLE REBUILD PARTITION is > needed.

2 5

[Maria-developers] [DISCUSS] Don't see ARM release information in MariaDB download page
by bo zhaobo 03 Feb '20

03 Feb '20

Hi Team, I want to make clear a thing about the Mariadb download page. Hope team could give some advices or help. As I had sent out an discussion to maria-docs, but didn't get some help. So I think I may get something in dev channel. When I want to download a aarch64 mariadb package, I'm failed to search any information in the content of page [1]. But I tried to search the RPM/DEB package, such as in '*Red Hat, Fedora, and CentOS Packages*' and '*Debian and Ubuntu Packages*', and I can see there are ARM(aarch64/arm64) specific packages in these linked pages [2]. And documentation in [1] just say OS/CPU with "*RedHat/CentOS/Fedora (x86, x86_64, ppc64, ppc64le)*". It seems that there is difference between the documentation with real released packages. That's why I'm here to want to make it clear and discuss with team. *How about add 'aarch64' in the OS/CPU description?* I think that would be great if community could fix that, because it is good for users. They could get what they want from MariaDB download page(MariaDB docs related). Thanks very much. BR ZhaoBo [1] https://downloads.mariadb.org/mariadb/10.5.0/ [2] http://ftp.igh.cnrs.fr/pub/mariadb//mariadb-10.5.0/yum/ http://ftp.igh.cnrs.fr/pub/mariadb//mariadb-10.5.0/repo/ [image: Mailtrack] <https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=sig…> Sender notified by Mailtrack <https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=sig…> 20/01/06 下午07:31:54

3 7

778e96749bc: MDEV-20923:UBSAN: member access within address … which does not point to an object of type 'xid_count_per_binlog'
by sujatha 23 Jan '20

23 Jan '20

revision-id: 778e96749bcadc5528bb7f214a711272f7413b96 (mariadb-10.1.43-66-g778e96749bc) parent(s): 982294ac1680938ac9223fb64a64e21f0cbc322a author: Sujatha committer: Sujatha timestamp: 2020-01-23 16:17:55 +0530 message: MDEV-20923:UBSAN: member access within address … which does not point to an object of type 'xid_count_per_binlog' Problem: ------- Accessing a member within 'xid_count_per_binlog' structure results in following error when 'UBSAN' is enabled. member access within address 0xXXX which does not point to an object of type 'xid_count_per_binlog' Analysis: --------- The problem appears to be that no constructor for 'xid_count_per_binlog' is being called, and thus the vtable will not be initialized. Fix: --- Defined a parameterized constructor for 'xid_count_per_binlog' class. --- sql/log.cc | 27 +++++++++++---------------- sql/log.h | 13 ++++++++++++- 2 files changed, 23 insertions(+), 17 deletions(-) diff --git a/sql/log.cc b/sql/log.cc index acf1f8f8a9c..0efef6d1e29 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -3216,7 +3216,7 @@ void MYSQL_BIN_LOG::cleanup() DBUG_ASSERT(!binlog_xid_count_list.head()); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::cleanup(): Removing xid_list_entry " "for %s (%lu)", b); - my_free(b); + delete b; } mysql_mutex_destroy(&LOCK_log); @@ -3580,17 +3580,9 @@ bool MYSQL_BIN_LOG::open(const char *log_name, */ uint off= dirname_length(log_file_name); uint len= strlen(log_file_name) - off; - char *entry_mem, *name_mem; - if (!(new_xid_list_entry = (xid_count_per_binlog *) - my_multi_malloc(MYF(MY_WME), - &entry_mem, sizeof(xid_count_per_binlog), - &name_mem, len, - NULL))) + new_xid_list_entry= new xid_count_per_binlog(log_file_name+off, (int)len); + if (!new_xid_list_entry) goto err; - memcpy(name_mem, log_file_name+off, len); - new_xid_list_entry->binlog_name= name_mem; - new_xid_list_entry->binlog_name_len= len; - new_xid_list_entry->xid_count= 0; /* Find the name for the Initial binlog checkpoint. @@ -3607,7 +3599,10 @@ bool MYSQL_BIN_LOG::open(const char *log_name, mysql_mutex_unlock(&LOCK_xid_list); if (!b) b= new_xid_list_entry; - strmake(buf, b->binlog_name, b->binlog_name_len); + if (b->binlog_name) + strmake(buf, b->binlog_name, b->binlog_name_len); + else + goto err; Binlog_checkpoint_log_event ev(buf, len); DBUG_EXECUTE_IF("crash_before_write_checkpoint_event", flush_io_cache(&log_file); @@ -3711,7 +3706,7 @@ bool MYSQL_BIN_LOG::open(const char *log_name, { WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::open(): Removing xid_list_entry for " "%s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_cond_broadcast(&COND_xid_list); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::open(): Adding new xid_list_entry for " @@ -3758,7 +3753,7 @@ Turning logging off for the whole duration of the MySQL server process. \ To turn it on again: fix the cause, \ shutdown the MySQL server and restart it.", name, errno); if (new_xid_list_entry) - my_free(new_xid_list_entry); + delete new_xid_list_entry; if (file >= 0) mysql_file_close(file, MYF(0)); close(LOG_CLOSE_INDEX); @@ -4252,7 +4247,7 @@ bool MYSQL_BIN_LOG::reset_logs(THD *thd, bool create_new_log, DBUG_ASSERT(b->xid_count == 0); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::reset_logs(): Removing " "xid_list_entry for %s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_cond_broadcast(&COND_xid_list); reset_master_pending--; @@ -9736,7 +9731,7 @@ TC_LOG_BINLOG::mark_xid_done(ulong binlog_id, bool write_checkpoint) break; WSREP_XID_LIST_ENTRY("TC_LOG_BINLOG::mark_xid_done(): Removing " "xid_list_entry for %s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_mutex_unlock(&LOCK_xid_list); diff --git a/sql/log.h b/sql/log.h index b4c9b24a3a9..277e5c6f69c 100644 --- a/sql/log.h +++ b/sql/log.h @@ -587,7 +587,18 @@ class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG long xid_count; /* For linking in requests to the binlog background thread. */ xid_count_per_binlog *next_in_queue; - xid_count_per_binlog(); /* Give link error if constructor used. */ + xid_count_per_binlog(char *log_file_name, uint log_file_name_len) + :binlog_id(0), xid_count(0) + { + binlog_name_len= log_file_name_len; + binlog_name= (char *) my_malloc(binlog_name_len, MYF(MY_ZEROFILL)); + if (binlog_name) + memcpy(binlog_name, log_file_name, binlog_name_len); + } + ~xid_count_per_binlog() + { + my_free(binlog_name); + } }; I_List<xid_count_per_binlog> binlog_xid_count_list; mysql_mutex_t LOCK_binlog_background_thread;

1 0

d9716cfadb8: MDEV-20923:UBSAN: member access within address … which does not point to an object of type 'xid_count_per_binlog'
by sujatha 23 Jan '20

23 Jan '20

revision-id: d9716cfadb8cd3448f25d8b411451f87064cecaf (mariadb-10.1.43-66-gd9716cfadb8) parent(s): 982294ac1680938ac9223fb64a64e21f0cbc322a author: Sujatha committer: Sujatha timestamp: 2020-01-23 16:00:41 +0530 message: MDEV-20923:UBSAN: member access within address … which does not point to an object of type 'xid_count_per_binlog' Problem: ------- Accessing a member within 'xid_count_per_binlog' structure results in following error when 'UBSAN' is enabled. member access within address 0xXXX which does not point to an object of type 'xid_count_per_binlog' Analysis: --------- The problem appears to be that no constructor for 'xid_count_per_binlog' is being called, and thus the vtable will not be initialized. Fix: --- Defined a parameterized constructor for 'xid_count_per_binlog' class. --- sql/log.cc | 27 +++++++++++---------------- sql/log.h | 14 +++++++++++++- 2 files changed, 24 insertions(+), 17 deletions(-) diff --git a/sql/log.cc b/sql/log.cc index acf1f8f8a9c..0efef6d1e29 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -3216,7 +3216,7 @@ void MYSQL_BIN_LOG::cleanup() DBUG_ASSERT(!binlog_xid_count_list.head()); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::cleanup(): Removing xid_list_entry " "for %s (%lu)", b); - my_free(b); + delete b; } mysql_mutex_destroy(&LOCK_log); @@ -3580,17 +3580,9 @@ bool MYSQL_BIN_LOG::open(const char *log_name, */ uint off= dirname_length(log_file_name); uint len= strlen(log_file_name) - off; - char *entry_mem, *name_mem; - if (!(new_xid_list_entry = (xid_count_per_binlog *) - my_multi_malloc(MYF(MY_WME), - &entry_mem, sizeof(xid_count_per_binlog), - &name_mem, len, - NULL))) + new_xid_list_entry= new xid_count_per_binlog(log_file_name+off, (int)len); + if (!new_xid_list_entry) goto err; - memcpy(name_mem, log_file_name+off, len); - new_xid_list_entry->binlog_name= name_mem; - new_xid_list_entry->binlog_name_len= len; - new_xid_list_entry->xid_count= 0; /* Find the name for the Initial binlog checkpoint. @@ -3607,7 +3599,10 @@ bool MYSQL_BIN_LOG::open(const char *log_name, mysql_mutex_unlock(&LOCK_xid_list); if (!b) b= new_xid_list_entry; - strmake(buf, b->binlog_name, b->binlog_name_len); + if (b->binlog_name) + strmake(buf, b->binlog_name, b->binlog_name_len); + else + goto err; Binlog_checkpoint_log_event ev(buf, len); DBUG_EXECUTE_IF("crash_before_write_checkpoint_event", flush_io_cache(&log_file); @@ -3711,7 +3706,7 @@ bool MYSQL_BIN_LOG::open(const char *log_name, { WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::open(): Removing xid_list_entry for " "%s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_cond_broadcast(&COND_xid_list); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::open(): Adding new xid_list_entry for " @@ -3758,7 +3753,7 @@ Turning logging off for the whole duration of the MySQL server process. \ To turn it on again: fix the cause, \ shutdown the MySQL server and restart it.", name, errno); if (new_xid_list_entry) - my_free(new_xid_list_entry); + delete new_xid_list_entry; if (file >= 0) mysql_file_close(file, MYF(0)); close(LOG_CLOSE_INDEX); @@ -4252,7 +4247,7 @@ bool MYSQL_BIN_LOG::reset_logs(THD *thd, bool create_new_log, DBUG_ASSERT(b->xid_count == 0); WSREP_XID_LIST_ENTRY("MYSQL_BIN_LOG::reset_logs(): Removing " "xid_list_entry for %s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_cond_broadcast(&COND_xid_list); reset_master_pending--; @@ -9736,7 +9731,7 @@ TC_LOG_BINLOG::mark_xid_done(ulong binlog_id, bool write_checkpoint) break; WSREP_XID_LIST_ENTRY("TC_LOG_BINLOG::mark_xid_done(): Removing " "xid_list_entry for %s (%lu)", b); - my_free(binlog_xid_count_list.get()); + delete binlog_xid_count_list.get(); } mysql_mutex_unlock(&LOCK_xid_list); diff --git a/sql/log.h b/sql/log.h index b4c9b24a3a9..e69c9c39eaf 100644 --- a/sql/log.h +++ b/sql/log.h @@ -587,7 +587,19 @@ class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG long xid_count; /* For linking in requests to the binlog background thread. */ xid_count_per_binlog *next_in_queue; - xid_count_per_binlog(); /* Give link error if constructor used. */ + xid_count_per_binlog(char *log_file_name, uint log_file_name_len) + :binlog_name(log_file_name), binlog_name_len(log_file_name_len), + binlog_id(0), xid_count(0) + { + binlog_name_len= log_file_name_len; + binlog_name= (char *) my_malloc(binlog_name_len, MYF(MY_ZEROFILL)); + if (binlog_name) + memcpy(binlog_name, log_file_name, binlog_name_len); + } + ~xid_count_per_binlog() + { + my_free(binlog_name); + } }; I_List<xid_count_per_binlog> binlog_xid_count_list; mysql_mutex_t LOCK_binlog_background_thread;

1 0

[Maria-developers] 43f27ae4778: MDEV-21490: binlog tests fail with valgrind: Conditional jump or move depends on uninitialised value in sql_ex_info::init
by sujatha 23 Jan '20

23 Jan '20

revision-id: 43f27ae4778b3e7be86897e49c182dc05080fd88 (mariadb-10.1.43-66-g43f27ae4778) parent(s): 982294ac1680938ac9223fb64a64e21f0cbc322a author: Sujatha committer: Sujatha timestamp: 2020-01-23 11:57:46 +0530 message: MDEV-21490: binlog tests fail with valgrind: Conditional jump or move depends on uninitialised value in sql_ex_info::init Problem: ======= P1) Conditional jump or move depends on uninitialised value(s) sql_ex_info::init(char const*, char const*, bool) (log_event.cc:3083) code: All the following variables are not initialized. ---- return ((cached_new_format != -1) ? cached_new_format : (cached_new_format=(field_term_len > 1 || enclosed_len > 1 || line_term_len > 1 || line_start_len > 1 || escaped_len > 1))); P2) Conditional jump or move depends on uninitialised value(s) Rows_log_event::Rows_log_event(char const*, unsigned int, Format_description_log_event const*) (log_event.cc:9571) Code: Uninitialized values is reported for 'var_header_len' variable. ---- if (var_header_len < 2 || event_len < static_cast<unsigned int>(var_header_len + (post_start - buf))) P3) Conditional jump or move depends on uninitialised value(s) Table_map_log_event::pack_info(Protocol*) (log_event.cc:11553) code:'m_table_id' is uninitialized. ---- void Table_map_log_event::pack_info(Protocol *protocol) ... size_t bytes= my_snprintf(buf, sizeof(buf), "table_id: %lu (%s.%s)", m_table_id, m_dbnam, m_tblnam); Fix: === P1 - Fix) Initialize cached_new_format,field_term_len, enclosed_len, line_term_len, line_start_len, escaped_len members in default constructor. P2 - Fix) "var_header_len" is initialized by reading the event buffer. In case of an invalid event the buffer will contain invalid data. Hence added a check to validate the event data. If event_len is smaller than valid header length return immediately. P3 - Fix) 'm_table_id' within Table_map_log_event is initialized by reading data from the event buffer. Use 'VALIDATE_BYTES_READ' macro to validate the current state of the buffer. If it is invalid return immediately. --- sql/log_event.cc | 7 +++++++ sql/log_event.h | 9 ++++++++- 2 files changed, 15 insertions(+), 1 deletion(-) diff --git a/sql/log_event.cc b/sql/log_event.cc index e8881c77f2b..9924b9a0493 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -9535,6 +9535,12 @@ Rows_log_event::Rows_log_event(const char *buf, uint event_len, uint8 const post_header_len= description_event->post_header_len[event_type-1]; + if (event_len < (uint)(common_header_len + post_header_len)) + { + m_cols.bitmap= 0; + DBUG_VOID_RETURN; + } + DBUG_PRINT("enter",("event_len: %u common_header_len: %d " "post_header_len: %d", event_len, common_header_len, @@ -11043,6 +11049,7 @@ Table_map_log_event::Table_map_log_event(const char *buf, uint event_len, const char *post_start= buf + common_header_len; post_start+= TM_MAPID_OFFSET; + VALIDATE_BYTES_READ(post_start, buf, event_len); if (post_header_len == 6) { /* Master is of an intermediate source tree before 5.1.4. Id is 4 bytes */ diff --git a/sql/log_event.h b/sql/log_event.h index 2c8dc3d7353..04fce65faeb 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -2057,7 +2057,14 @@ class Query_log_event: public Log_event ****************************************************************************/ struct sql_ex_info { - sql_ex_info() {} /* Remove gcc warning */ + sql_ex_info(): + cached_new_format(-1), + field_term_len(0), + enclosed_len(0), + line_term_len(0), + line_start_len(0), + escaped_len(0) + {} /* Remove gcc warning */ const char* field_term; const char* enclosed; const char* line_term;

1 0

[Maria-developers] A cleanup for libmariadb
by Alexander Barkov 20 Jan '20

20 Jan '20

Hi Georg, Can you please review this cleanup patch? It's a prerequisite for: MDEV-17832 Protocol: extensions for Pluggable types and JSON, GEOMETRY The idea is that this patch introduces a new file libmariadb/mariadb_priv.h and adds prototypes of these functions: void free_rows(MYSQL_DATA *cur); int ma_multi_command(MYSQL *mysql, enum enum_multi_status status); MYSQL_FIELD * unpack_fields(MYSQL_DATA *data, MA_MEM_ROOT *alloc,uint fields, my_bool default_value); and removes these prototypes from mariadb_stmt.c. I think it should be safer this way. Later I'll add there more prototypes into libmariadb/mariadb_priv.h, e.g. functions that will be added under terms of MDEV-17832. Note, I also removed the "my_bool long_flag_protocol" parameter from unpack_fields(), it's not used anyway. Under terms of MDEV-17832 I'll have to add a "const MYSQL *mysql" parameter. So if we ever need to check CLIENT_LONG_FLAG inside unpack_fields() again, it can be done through the "mysql" parameter: (mysql->server_capabilities & CLIENT_LONG_FLAG) Thanks!

1 0

[Maria-developers] A cleanup for libmariadb
by Alexander Barkov 20 Jan '20

20 Jan '20

Hi Georg, Can you please review this cleanup patch? It's a prerequisite for: MDEV-17832 Protocol: extensions for Pluggable types and JSON, GEOMETRY The idea is that this patch introduces a new file libmariadb/mariadb_priv.h and adds prototypes of these functions: void free_rows(MYSQL_DATA *cur); int ma_multi_command(MYSQL *mysql, enum enum_multi_status status); MYSQL_FIELD * unpack_fields(MYSQL_DATA *data, MA_MEM_ROOT *alloc,uint fields, my_bool default_value); and removes these prototypes from mariadb_stmt.c. I think it should be safer this way. Later I'll add there more prototypes into libmariadb/mariadb_priv.h, e.g. functions that will be added under terms of MDEV-17832. Note, I also removed the "my_bool long_flag_protocol" parameter from unpack_fields(), it's not used anyway. Under terms of MDEV-17832 I'll have to add a "const MYSQL *mysql" parameter. So if we ever need to check CLIENT_LONG_FLAG inside unpack_fields() again, it can be done through the "mysql" parameter: (mysql->server_capabilities & CLIENT_LONG_FLAG) Thanks!

1 0

Re: [Maria-developers] 9d2c63d45e4: MDEV-20076: SHOW GRANTS does not quote role names properly
by Sergei Golubchik 19 Jan '20

19 Jan '20

Hi, Oleksandr! On Jan 19, Oleksandr Byelkin wrote: > revision-id: 9d2c63d45e4 (mariadb-10.3.20-6-g9d2c63d45e4) > parent(s): d4edb0510ec > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2019-11-14 09:32:54 +0100 > message: > > MDEV-20076: SHOW GRANTS does not quote role names properly > > Quotes added to output. > > diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test > index 649bba7d1ca..045cbf8fc86 100644 > --- a/mysql-test/main/grant5.test > +++ b/mysql-test/main/grant5.test > @@ -33,3 +33,26 @@ REVOKE EXECUTE ON PROCEDURE sp FROM u; > --error ER_TABLE_NOT_LOCKED > REVOKE PROCESS ON *.* FROM u; > DROP TABLE t1; > + > +--echo # > +--echo # MDEV-20076: SHOW GRANTS does not quote role names properly > +--echo # > + > +create role 'role-1'; > +create role 'rock\'n\'roll'; > +create user 'user-1'@'localhost'; > +create user 'O\'Brien'@'localhost'; > +grant select on mysql.user to 'role-1'; > +grant select on mysql.user to 'rock\'n\'roll'; > +GRANT 'role-1' TO 'user-1'@'localhost'; > +GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost'; > +show grants for 'role-1'; > +show grants for 'rock\'n\'roll'; > +show grants for 'user-1'@'localhost'; > +show grants for 'O\'Brien'@'localhost'; > +drop role 'role-1'; > +drop role 'rock\'n\'roll'; > +drop user 'user-1'@'localhost'; > +drop user 'O\'Brien'@'localhost'; > + > +--echo # End of 10.3 tests please add few tests when sql_quote_show_create=0. Like 1. sql_quote_show_create=0, role name is not reserved (e.g. foo) 2. sql_quote_show_create=0, role name is reserved (e.g. fetch) 3. sql_quote_show_create=0, role name is not a valid identifier (e.g. role-1) Then ok to push. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] edc9059c31b: MDEV-18027: Running out of file descriptors and eventual crash
by Sergei Golubchik 19 Jan '20

19 Jan '20

Hi, Oleksandr! Ok to push. Just one comment, see below: On Jan 19, Oleksandr Byelkin wrote: > revision-id: edc9059c31b (mariadb-10.2.28-3-gedc9059c31b) > parent(s): 0b1bc4bf76f > author: Oleksandr Byelkin <sanja(a)mariadb.com> > committer: Oleksandr Byelkin <sanja(a)mariadb.com> > timestamp: 2019-11-05 16:24:26 +0100 > message: > > MDEV-18027: Running out of file descriptors and eventual crash > > For automatic number of opened files limit take into account number of > table instances for table cache > > diff --git a/sql/mysqld.cc b/sql/mysqld.cc > index 34e5704bcfe..9907c17ee07 100644 > --- a/sql/mysqld.cc > +++ b/sql/mysqld.cc > @@ -4461,6 +4461,17 @@ static int init_common_variables() > if (files < wanted_files && global_system_variables.log_warnings) > sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > > + /* > + If we required too much tc_instances than we reduce it but not more > + then in half The comment is wrong, you don't do "not more than in half", you can reduce it all the way to 1. Which, I think, it correct. So the code is ok, but the comment is misleading, please remove the "but not more than in half" part. > + */ > + SYSVAR_AUTOSIZE_IF_CHANGED(tc_instances, > + (uint32) MY_MIN(MY_MAX((files - extra_files - > + max_connections)/ > + 2/tc_size, > + 1), > + tc_instances), > + uint32); > /* > If we have requested too much file handles than we bring > max_connections in supported bounds. Still leave at least Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

[Maria-developers] 回复: [Maria-discuss] [MariaDB 10.3.7] Hello, everyone. My process core dumped while calling mysql_ping().
by Ma Allen 15 Jan '20

15 Jan '20

Hi Markus, Only MySqlWaitConnected() in the two functions doesn't use lock, reading database uses mutex. What's more, the two functions in initialization relates to ONLY the main thread. No other threads call them. Previously, the program is written with mysqllib, which is not MariaDB lib currently used. Mysqllib supports multiple threads to concurrently use one connection. Its tutorials were also referred to: https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html MySQL :: MySQL 5.7 Reference Manual :: 27.7.3.3 Writing C API Threaded Client Programs<https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html> This section provides guidance for writing client programs that use the thread-related functions in the MySQL C API. For further information about these functions, see Section 27.7.11, “C API Threaded Function Descriptions”.For examples of source code that uses them, look in the client directory of a MySQL source distribution: dev.mysql.com After replacing mysqllib with MariaDB lib, everything works well on the test environment except one of the clients'. Allen ________________________________ 发件人: Maria-discuss <maria-discuss-bounces+mazhh=outlook.com(a)lists.launchpad.net> 代表 Markus Mäkelä <markus.makela(a)mariadb.com> 发送时间: 2020年1月14日 15:40 收件人: maria-discuss(a)lists.launchpad.net <maria-discuss(a)lists.launchpad.net> 主题: Re: [Maria-discuss] [Maria-developers] [MariaDB 10.3.7] Hello, everyone. My process core dumped while calling mysql_ping(). Hi, Is the connection protected by a lock of some sorts? To my knowledge the library does not support concurrent use of a single connection. I'd recommend trying to first wrap all access to the connection with a mutex and see if that solves the problem. Markus On 1/14/20 08:33, Ma Allen wrote: Environment: MariaDB 10.3.7 database server & related headers and libraries CentOS 7.3 or 7.4 ./configure --prefix=/home/spiderflow --localstatedir=/home/spiderflow --enable-unix-socket --with-libnss-libraries=/usr/lib64 --with-libnss-includes=/usr/include/nss3 --with-libnspr-libraries=/usr/lib64 --with-libnspr-includes=/usr/include/nspr4 --enable-netmap --enable-rust --disable-gccmarch-native --enable-mysql --with-libmysql-includes=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/include/mysql --with-libmysql-libraries=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/lib Phenomenon: (gdb) #0 ma_pvio_write (pvio=0x10101010101b2e0, buffer=buffer@entry=0x2510101 "\001", length=length@entry=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_pvio.c:350 #1 0x00007f7100249dea in ma_net_real_write (net=net@entry=0xee9de0 <g_mysql_info>, packet=0x2510101 "\001", len=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:335 #2 0x00007f7100249f60 in ma_net_flush (net=net@entry=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:166 #3 0x00007f710024a71a in ma_net_write_command (net=net@entry=0xee9de0 <g_mysql_info>, command=command@entry=14 '\016', packet=packet@entry=0x7f71003d6c67 "", len=0, disable_flush=disable_flush@entry=0 '\000') at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:244 #4 0x00007f7100252ca4 in mthd_my_send_cmd (mysql=0xee9de0 <g_mysql_info>, command=<optimized out>, arg=0x7f71003d6c67 "", length=0, skipp_check=<optimized out>, opt_arg=<optimized out>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:394 #5 0x00007f71002518d0 in mysql_ping (mysql=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:2552 #6 0x00000000006651b2 in MySqlWaitConnected (p_mysql_data=p_mysql_data@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:676 #7 0x0000000000668819 in LoadProtoConfInfos (pInfo=pInfo@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:835 #8 0x000000000066c53a in InitBaseLine () at ../srcSF/spiderFlow-proto-baseline.c:1069 #9 0x00000000005f308b in PostConfLoadedSetup (suri=0x10dbb80 <suricata>) at suricata.c:2900 #10 0x0000000000425ce8 in main (argc=5, argv=0x7fff1cdca258) at suricata.c:3072 (gdb) Analyzation: The core dump always exists after running for a while or maybe after the sever restarts. It occurs at the initialization and involves with ONLY ONE thread, which is the main thread of the whole process. The related code is two functions, which both checks the connection and read data from MariaDB. The first function works fine, however, the second one's checking connection leads to core dump. The code to check connection is as follows: void MySqlWaitConnected(MYSQL * p_mysql_data) { while (mysql_ping(p_mysql_data) != 0) { SCLogInfo("Mysql ping error:%s", mysql_error(p_mysql_data)); #ifdef WIN32 Sleep(3000); #else sleep(3); #endif } } Previously, I solved a likely similar problem as follows: Multiple threads shared ONE MySQL handler, which is the MYSQL data structure. Before inserting data into MariaDB without mutex, MySqlWaitConnected() is called. So while multiple threads do the same operation, inserting data is interrupted by mysql_ping() that leads to core dump. Afterwards, I comment MySqlWaitConnected() and add mutex for mysql_real_query() and mysql_commit(). Does anyone meet the same problem before? Any suggestion is appreciated. Thanks in advance. Best Regards, Allen Ma _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers(a)lists.launchpad.net<mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp -- Markus Mäkelä, Senior Software Engineer MariaDB Corporation t: +358 40 7740484 | IRC: markusjm@freenode

1 2

Re: [Maria-developers] 10670133744: MDEV-16978 Application-time periods: WITHOUT OVERLAPS
by Sergei Golubchik 14 Jan '20

14 Jan '20

Hi, Nikita! On Dec 16, Nikita Malyavin wrote: > revision-id: 10670133744 (mariadb-10.4.4-492-g10670133744) > parent(s): 251c6e17269 > author: Nikita Malyavin <nikitamalyavin(a)gmail.com> > committer: Nikita Malyavin <nikitamalyavin(a)gmail.com> > timestamp: 2019-11-26 19:22:04 +1000 > message: > > MDEV-16978 Application-time periods: WITHOUT OVERLAPS > > * The overlaps check is implemented on a handler level per row command. It creates a separate cursor (actually, another handler instance) and caches it inside the original handler, when ha_update_row or ha_insert_row is issued. Cursor closes on unlocking the handler. > > * Containing the same key in index means unique constraint violation even in usual terms. So we fetch left and right neighbours and check that they have same key prefix, excluding from the key only the period part. If it doesnt match, then there's no such neighbour, and the check passes. Otherwise, we check if this neighbour intersects with the considered key. > > * the check does introduce new error and fails with ER_DUPP_KEY error. This might break REPLACE workflow and should be fixed separately wrap long lines, pease. > diff --git a/sql/handler.cc b/sql/handler.cc > index 46a0c313c80..ed36f3c5bd3 100644 > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -6402,6 +6407,14 @@ int handler::ha_external_lock(THD *thd, int lock_type) > mysql_audit_external_lock(thd, table_share, lock_type); > } > > + if (lock_type == F_UNLCK && check_overlaps_handler) > + { > + check_overlaps_handler->ha_external_lock(table->in_use, F_UNLCK); > + check_overlaps_handler->close(); > + check_overlaps_handler= NULL; > + overlaps_error_key= -1; > + } I'm still thinking about how avoid this overhead or at least to simplify the code. One option is to use HA_EXTRA_REMEMBER_POS It doesn't nest, you're right, but it can be fixed. Another, simpler, option is to use TABLE::update_handler. This is a second auxillary handler, a clone, exactly as yours, created for to check long uniques. So I don't see a need to create a yet another clone when you can simply use TABLE::update_handler. It's never used for scanning, only for point lookups, so there is no position that you can disrupt. upd: you seem to have got the same idea. and you're right, it should be in the handler class, not in the TABLE as I originally wanted. > + > if (MYSQL_HANDLER_RDLOCK_DONE_ENABLED() || > MYSQL_HANDLER_WRLOCK_DONE_ENABLED() || > MYSQL_HANDLER_UNLOCK_DONE_ENABLED()) > @@ -6935,6 +6956,134 @@ void handler::set_lock_type(enum thr_lock_type lock) > table->reginfo.lock_type= lock; > } > > +int handler::ha_check_overlaps(const uchar *old_data, const uchar* new_data) > +{ > + DBUG_ASSERT(new_data); > + if (!table_share->period.unique_keys) > + return 0; > + if (table->versioned() && !table->vers_end_field()->is_max()) > + return 0; > + > + bool is_update= old_data != NULL; > + if (!check_overlaps_buffer) > + check_overlaps_buffer= (uchar*)alloc_root(&table_share->mem_root, > + table_share->max_unique_length > + + table_share->reclength); > + auto *record_buffer= check_overlaps_buffer + table_share->max_unique_length; > + auto *handler= this; Please, add a comment that on INSERT handler->inited can be NONE > + if (handler->inited != NONE) > + { > + if (!check_overlaps_handler) > + { > + check_overlaps_handler= clone(table_share->normalized_path.str, > + &table_share->mem_root); > + int error= -1; > + if (check_overlaps_handler != NULL) > + error= check_overlaps_handler->ha_external_lock(table->in_use, F_RDLCK); > + if (error) > + return error; > + } > + handler= check_overlaps_handler; > + > + // Needs to compare record refs later is old_row_found() > + if (is_update) > + position(old_data); > + } > + > + // Save and later restore this handler's keyread > + int old_this_keyread= this->keyread; what's that for? you aren't using `this` anywhere below. Unless handler == this, but then this->keyread cannot be enabled. > + DBUG_ASSERT(this->ha_end_keyread() == 0); Eh. Never put any code with side effects into an assert. Assert are conditionally compiled. > + > + int error= 0; > + > + for (uint key_nr= 0; key_nr < table_share->keys && !error; key_nr++) > + { > + const KEY &key_info= table->key_info[key_nr]; > + const uint key_parts= key_info.user_defined_key_parts; > + if (!key_info.without_overlaps) > + continue; > + > + if (is_update) > + { > + bool key_used= false; > + for (uint k= 0; k < key_parts && !key_used; k++) > + key_used= bitmap_is_set(table->write_set, > + key_info.key_part[k].fieldnr - 1); > + if (!key_used) > + continue; > + } > + > + error= handler->ha_index_init(key_nr, 0); > + if (error) > + return error; > + > + auto old_row_found= [is_update, old_data, record_buffer, this, handler](){ There is no reason to use a lambda here. could you rewrite that, please? In this particular case old_row_found() is only used once, so you can inline your lambda there. > + if (!is_update) > + return false; > + /* In case of update it could appear that the nearest neighbour is > + * a record we are updating. It means, that there are no overlaps > + * from this side. > + * > + * An assumption is made that during update we always have the last > + * fetched row in old_data. Therefore, comparing ref's is enough > + * */ > + DBUG_ASSERT(handler != this && inited != NONE); as a general rule please use DBUG_ASSERT(x); DBUG_ASSERT(y); and not DBUG_ASSERT(x && y); > + DBUG_ASSERT(ref_length == handler->ref_length); > + > + handler->position(record_buffer); > + return memcmp(ref, handler->ref, ref_length) == 0; > + }; > + > + error= handler->ha_start_keyread(key_nr); > + DBUG_ASSERT(!error); > + > + const uint period_field_length= key_info.key_part[key_parts - 1].length; > + const uint key_base_length= key_info.key_length - 2 * period_field_length; > + > + key_copy(check_overlaps_buffer, new_data, &key_info, 0); > + > + /* Copy period_end to period_start. > + * the value in period_end field is not significant, but anyway let's leave > + * it defined to avoid uninitialized memory access > + */ please format your multi-line comments to follow the existing server code conventions > + memcpy(check_overlaps_buffer + key_base_length, > + check_overlaps_buffer + key_base_length + period_field_length, > + period_field_length); > + > + /* Find row with period_start < (period_end of new_data) */ > + error = handler->ha_index_read_map(record_buffer, > + check_overlaps_buffer, > + key_part_map((1 << key_parts) - 1), > + HA_READ_BEFORE_KEY); > + > + if (!error && old_row_found()) > + error= handler->ha_index_prev(record_buffer); > + > + if (!error && table->check_period_overlaps(key_info, key_info, > + new_data, record_buffer) == 0) > + error= HA_ERR_FOUND_DUPP_KEY; > + > + if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE) > + error= 0; > + > + if (error == HA_ERR_FOUND_DUPP_KEY) > + overlaps_error_key= key_nr; > + > + int end_error= handler->ha_end_keyread(); > + DBUG_ASSERT(!end_error); > + > + end_error= handler->ha_index_end(); > + if (!error && end_error) > + error= end_error; > + } > + > + // Restore keyread of this handler, if it was enabled > + if (old_this_keyread < MAX_KEY) > + DBUG_ASSERT(this->ha_start_keyread(old_this_keyread) == 0); > + > + return error; > +} > + > #ifdef WITH_WSREP > /** > @details > diff --git a/sql/key.cc b/sql/key.cc > index bf50094a9e4..d4f33467e2b 100644 > --- a/sql/key.cc > +++ b/sql/key.cc > @@ -899,3 +899,45 @@ bool key_buf_cmp(KEY *key_info, uint used_key_parts, > } > return FALSE; > } > + > + a comment please. What does the function return? -1/0/1 ? > +int key_period_compare_bases(const KEY &lhs_key, const KEY &rhs_key, > + const uchar *lhs, const uchar *rhs) > +{ > + uint base_part_nr= lhs_key.user_defined_key_parts - 2; > + int cmp_res= 0; > + for (uint part_nr= 0; !cmp_res && part_nr < base_part_nr; part_nr++) > + { > + Field *f= lhs_key.key_part[part_nr].field; > + cmp_res= f->cmp(f->ptr_in_record(lhs), > + rhs_key.key_part[part_nr].field->ptr_in_record(rhs)); > + } > + > + return cmp_res; > +} > + a comment please. What does the function return? > +int key_period_compare_periods(const KEY &lhs_key, const KEY &rhs_key, > + const uchar *lhs, const uchar *rhs) > +{ > + uint base_part_nr= lhs_key.user_defined_key_parts - 2; > + > + Field *lhs_fields[]= {lhs_key.key_part[base_part_nr].field, > + lhs_key.key_part[base_part_nr + 1].field}; > + > + Field *rhs_fields[]= {rhs_key.key_part[base_part_nr].field, > + rhs_key.key_part[base_part_nr + 1].field}; > + > + int cmp[2][2]; /* l1 > l2, l1 > r2, r1 > l2, r1 > r2 */ > + for (int i= 0; i < 2; i++) > + { > + for (int j= 0; j < 2; j++) > + { > + cmp[i][j]= lhs_fields[0]->cmp(lhs_fields[i]->ptr_in_record(lhs), > + rhs_fields[j]->ptr_in_record(rhs)); > + } > + } > + > + bool overlaps = (cmp[0][0] <= 0 && cmp[1][0] > 0) > + || (cmp[0][0] >= 0 && cmp[0][1] < 0); > + return overlaps ? 0 : cmp[0][0]; Couldn't this be simplifed? Like if (cmp[1][0] <= 0) // r1 <= l2 return -1; if (cmp[0][1] >= 0) // l1 >= r2 return 1; return 0; and I think it'd be clearer to remove this cmp[][] array and write the condition directly. May be with shortcuts like const Field const * f=lhs_fields[0]; const uchar const * l1=lhs_fields[0]->ptr_in_record(lhs), ... if (f->cmp(r1, l2) <= 0) return -1; if (f->cmp(l1, r2) >= 0) return 1; return 0; > +} > \ No newline at end of file > diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt > index 36188e58624..bb2f0fc5296 100644 > --- a/sql/share/errmsg-utf8.txt > +++ b/sql/share/errmsg-utf8.txt > @@ -7943,3 +7943,9 @@ ER_WARN_HISTORY_ROW_START_TIME > eng "Table `%s.%s` history row start '%s' is later than row end '%s'" > ER_PART_STARTS_BEYOND_INTERVAL > eng "%`s: STARTS is later than query time, first history partition may exceed INTERVAL value" > +ER_KEY_CONTAINS_PERIOD_FIELDS > + eng "Key %`s should not contain period fields in order to make it WITHOUT OVERLAPS" I'd say "Key %`s cannot explicitly include column %`s" or "WITHOUT OVERLAPS key %`s cannot explicitly include column %`s" but I think the latter looks weird, I like the first variant more > +ER_PERIOD_WITHOUT_OVERLAPS_PARTITIONED > + eng "Period WITHOUT OVERLAPS is not implemented for partitioned tables" I don't think we should create a separate error message for every feature that doesn't work with partitioning. There's already ER_FOREIGN_KEY_ON_PARTITIONED eng "Foreign key clause is not yet supported in conjunction with partitioning" ER_PARTITION_NO_TEMPORARY eng "Cannot create temporary table with partitions" ER_FULLTEXT_NOT_SUPPORTED_WITH_PARTITIONING eng "FULLTEXT index is not supported for partitioned tables" which is two error messages too many. So I'd just say ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING end "Partitioned tables do not support %s" where %s could be CREATE TEMPORARY TABLE, FOREIGN KEY, FULLTEXT, WITHOUT OVERLAPS, and whatever else partitioned tables don't or won't support. Note that you cannot remove old error messages, so just rename the first one to ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING, and keep the rest unused but still present in the errmsg-utf8.txt file. > +ER_PERIOD_WITHOUT_OVERLAPS_NON_UNIQUE > + eng "Period WITHOUT OVERLAPS is only allowed for unique keys" "Only UNIQUE or PRIMARY keys can be WITHOUT OVERLAPS" > diff --git a/sql/table.cc b/sql/table.cc > index 7ed5121a9c6..8b84fb3035d 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -1519,6 +1520,15 @@ bool read_extra2(const uchar *frm_image, size_t len, extra2_fields *fields) > size_t length= extra2_read_len(&extra2, e2end); > if (!length) > DBUG_RETURN(true); > + > + auto fill_extra2= [extra2, length](LEX_CUSTRING *section){ > + if (section->str) > + return true; > + *section= {extra2, length}; > + return false; > + }; don't use a lambda here either, make it a proper function > + > + bool fail= false; > switch (type) { > case EXTRA2_TABLEDEF_VERSION: > if (fields->version.str) // see init_from_sql_statement_string() > @@ -1726,11 +1725,26 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, > keyinfo= &first_keyinfo; > thd->mem_root= &share->mem_root; > > + auto err= [thd, share, &handler_file, &se_plugin, old_root](){ > + share->db_plugin= NULL; > + share->error= OPEN_FRM_CORRUPTED; > + share->open_errno= my_errno; > + delete handler_file; > + plugin_unlock(0, se_plugin); > + my_hash_free(&share->name_hash); > + > + if (!thd->is_error()) > + open_table_error(share, OPEN_FRM_CORRUPTED, share->open_errno); > + > + thd->mem_root= old_root; > + return HA_ERR_NOT_A_TABLE; > + }; Revert that too > + > if (write && write_frm_image(frm_image, frm_length)) > - goto err; > + DBUG_RETURN(err()); > > if (frm_length < FRM_HEADER_SIZE + FRM_FORMINFO_SIZE) > - goto err; > + DBUG_RETURN(err()); > > share->frm_version= frm_image[2]; > /* > @@ -2251,14 +2265,30 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, > pos+= period.constr_name.length; > > if (init_period_from_extra2(&period, pos, end)) > - goto err; > + DBUG_RETURN(err()); > status_var_increment(thd->status_var.feature_application_time_periods); > } > > + if (extra2.without_overlaps.str) > + { > + const uchar *key_pos= extra2.without_overlaps.str; > + period.unique_keys= read_frm_keyno(key_pos); > + for (uint k= 0; k < period.unique_keys; k++) > + { > + key_pos+= frm_keyno_size; > + uint key_nr= read_frm_keyno(key_pos); > + key_info[key_nr].without_overlaps= true; > + } > + > + if ((period.unique_keys + 1) * frm_keyno_size > + != extra2.without_overlaps.length) > + DBUG_RETURN(err()); you can also check here that extra2.application_period.str != NULL otherwise it's OPEN_FRM_CORRUPTED too > + } > + > if (extra2.field_data_type_info.length && > field_data_type_info_array.parse(old_root, share->fields, > extra2.field_data_type_info)) > - goto err; > + DBUG_RETURN(err()); > > for (i=0 ; i < share->fields; i++, strpos+=field_pack_length, field_ptr++) > { > @@ -8600,6 +8616,15 @@ void TABLE::evaluate_update_default_function() > DBUG_VOID_RETURN; > } > a comment please. What does the function return? > +int TABLE::check_period_overlaps(const KEY &lhs_key, const KEY &rhs_key, > + const uchar *lhs, const uchar *rhs) > +{ > + int cmp_res= key_period_compare_bases(lhs_key, rhs_key, lhs, rhs); > + if (cmp_res) > + return cmp_res; > + > + return key_period_compare_periods(lhs_key, rhs_key, lhs, rhs); > +} > > void TABLE::vers_update_fields() > { > diff --git a/sql/table.h b/sql/table.h > index 043341db608..e871471101f 100644 > --- a/sql/table.h > +++ b/sql/table.h > @@ -1620,6 +1621,13 @@ struct TABLE > int period_make_insert(Item *src, Field *dst); > int insert_portion_of_time(THD *thd, const vers_select_conds_t &period_conds, > ha_rows *rows_inserted); > + /* > + @return -1, lhs precedes rhs > + 0, lhs overlaps rhs > + 1, lhs succeeds rhs > + */ ah, a comment, good :) but move it to the function definition, please > + static int check_period_overlaps(const KEY &lhs_key, const KEY &rhs_key, > + const uchar *lhs, const uchar *rhs); > int delete_row(); > void vers_update_fields(); > void vers_update_end(); > @@ -1759,10 +1767,18 @@ class IS_table_read_plan; > > /** number of bytes used by field positional indexes in frm */ > constexpr uint frm_fieldno_size= 2; > +/** number of bytes used by key position number in frm */ > +constexpr uint frm_keyno_size= 2; > static inline uint16 read_frm_fieldno(const uchar *data) > { return uint2korr(data); } > -static inline void store_frm_fieldno(const uchar *data, uint16 fieldno) > +static inline void store_frm_fieldno(uchar *data, uint16 fieldno) > +{ int2store(data, fieldno); } > +static inline uint16 read_frm_keyno(const uchar *data) > +{ return uint2korr(data); } > +static inline void store_frm_keyno(uchar *data, uint16 fieldno) > { int2store(data, fieldno); } > +static inline size_t extra2_str_size(size_t len) > +{ return (len > 255 ? 3 : 1) + len; } why did you move that? it's still not used anywhere outside of unireg.cc > > class select_unit; > class TMP_TABLE_PARAM; > diff --git a/sql/unireg.cc b/sql/unireg.cc > index 7130b3e5d8a..ea5d739a97e 100644 > --- a/sql/unireg.cc > +++ b/sql/unireg.cc > @@ -390,7 +385,9 @@ LEX_CUSTRING build_frm_image(THD *thd, const LEX_CSTRING &table, > > if (create_info->period_info.name) > { > - extra2_size+= 1 + extra2_str_size(period_info_len); > + // two extra2 sections are taken after 10.5 This is a confusing comment, it suggests that both extra2 sections were added in 10.5. Remove the comment, please, it's not worth it. > + extra2_size+= 2 + extra2_str_size(period_info_len) > + + extra2_str_size(without_overlaps_len); > } > > bool has_extra2_field_flags_= has_extra2_field_flags(create_fields); > diff --git a/sql/unireg.h b/sql/unireg.h > index 419fbc4bd80..873d6f681fc 100644 > --- a/sql/unireg.h > +++ b/sql/unireg.h > @@ -177,7 +177,8 @@ enum extra2_frm_value_type { > > EXTRA2_ENGINE_TABLEOPTS=128, > EXTRA2_FIELD_FLAGS=129, > - EXTRA2_FIELD_DATA_TYPE_INFO=130 > + EXTRA2_FIELD_DATA_TYPE_INFO=130, > + EXTRA2_PERIOD_WITHOUT_OVERLAPS=131, Please, try to create a table that uses WITHOUT OVERLAPS and open it in 10.4. Just as a test, to make sure it works as expected. > }; > > enum extra2_field_flags { Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 2

[Maria-developers] [MariaDB 10.3.7] Hello, everyone. My process core dumped while calling mysql_ping().
by Ma Allen 14 Jan '20

14 Jan '20

Environment: MariaDB 10.3.7 database server & related headers and libraries CentOS 7.3 or 7.4 ./configure --prefix=/home/spiderflow --localstatedir=/home/spiderflow --enable-unix-socket --with-libnss-libraries=/usr/lib64 --with-libnss-includes=/usr/include/nss3 --with-libnspr-libraries=/usr/lib64 --with-libnspr-includes=/usr/include/nspr4 --enable-netmap --enable-rust --disable-gccmarch-native --enable-mysql --with-libmysql-includes=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/include/mysql --with-libmysql-libraries=/usr/local/mariadb-10.3.7-linux-systemd-x86_64/lib Phenomenon: (gdb) #0 ma_pvio_write (pvio=0x10101010101b2e0, buffer=buffer@entry=0x2510101 "\001", length=length@entry=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_pvio.c:350 #1 0x00007f7100249dea in ma_net_real_write (net=net@entry=0xee9de0 <g_mysql_info>, packet=0x2510101 "\001", len=5) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:335 #2 0x00007f7100249f60 in ma_net_flush (net=net@entry=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:166 #3 0x00007f710024a71a in ma_net_write_command (net=net@entry=0xee9de0 <g_mysql_info>, command=command@entry=14 '\016', packet=packet@entry=0x7f71003d6c67 "", len=0, disable_flush=disable_flush@entry=0 '\000') at /home/buildbot/buildbot/build/libmariadb/libmariadb/ma_net.c:244 #4 0x00007f7100252ca4 in mthd_my_send_cmd (mysql=0xee9de0 <g_mysql_info>, command=<optimized out>, arg=0x7f71003d6c67 "", length=0, skipp_check=<optimized out>, opt_arg=<optimized out>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:394 #5 0x00007f71002518d0 in mysql_ping (mysql=0xee9de0 <g_mysql_info>) at /home/buildbot/buildbot/build/libmariadb/libmariadb/mariadb_lib.c:2552 #6 0x00000000006651b2 in MySqlWaitConnected (p_mysql_data=p_mysql_data@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:676 #7 0x0000000000668819 in LoadProtoConfInfos (pInfo=pInfo@entry=0xee9de0 <g_mysql_info>) at ../srcSF/spiderFlow-proto-baseline.c:835 #8 0x000000000066c53a in InitBaseLine () at ../srcSF/spiderFlow-proto-baseline.c:1069 #9 0x00000000005f308b in PostConfLoadedSetup (suri=0x10dbb80 <suricata>) at suricata.c:2900 #10 0x0000000000425ce8 in main (argc=5, argv=0x7fff1cdca258) at suricata.c:3072 (gdb) Analyzation: The core dump always exists after running for a while or maybe after the sever restarts. It occurs at the initialization and involves with ONLY ONE thread, which is the main thread of the whole process. The related code is two functions, which both checks the connection and read data from MariaDB. The first function works fine, however, the second one's checking connection leads to core dump. The code to check connection is as follows: void MySqlWaitConnected(MYSQL * p_mysql_data) { while (mysql_ping(p_mysql_data) != 0) { SCLogInfo("Mysql ping error:%s", mysql_error(p_mysql_data)); #ifdef WIN32 Sleep(3000); #else sleep(3); #endif } } Previously, I solved a likely similar problem as follows: Multiple threads shared ONE MySQL handler, which is the MYSQL data structure. Before inserting data into MariaDB without mutex, MySqlWaitConnected() is called. So while multiple threads do the same operation, inserting data is interrupted by mysql_ping() that leads to core dump. Afterwards, I comment MySqlWaitConnected() and add mutex for mysql_real_query() and mysql_commit(). Does anyone meet the same problem before? Any suggestion is appreciated. Thanks in advance. Best Regards, Allen Ma

1 0

Re: [Maria-developers] [Commits] afdd6191d5d: Big Test added for sorting
by Sergey Petrunia 13 Jan '20

13 Jan '20

Hi Varun, As disucssed: - Please fix function names - Please fix the comments to explain what the function is generating - Please don't use session variables. Ok to push after this is addressed On Fri, Jan 03, 2020 at 02:28:25AM +0530, Varun wrote: > revision-id: afdd6191d5dcb004ec9ac0b908871ad8a370da34 (mariadb-10.4.11-18-gafdd6191d5d) > parent(s): 59d4f2a373a7960a533e653877ab69a97e91444a > author: Varun Gupta > committer: Varun Gupta > timestamp: 2020-01-03 02:26:58 +0530 > message: > > Big Test added for sorting > > --- > mysql-test/main/order_by_pack_big.result | 194 +++++++++++++++++++++++++++++++ > mysql-test/main/order_by_pack_big.test | 107 +++++++++++++++++ > 2 files changed, 301 insertions(+) > > diff --git a/mysql-test/main/order_by_pack_big.result b/mysql-test/main/order_by_pack_big.result > new file mode 100644 > index 00000000000..66aad449c38 > --- /dev/null > +++ b/mysql-test/main/order_by_pack_big.result > @@ -0,0 +1,194 @@ > +set @save_rand_seed1= @@RAND_SEED1; > +set @save_rand_seed2= @@RAND_SEED2; > +set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772; > +create table t1(a int); > +insert into t1 select seq from seq_1_to_10000 order by rand(); > +# > +# parameters: > +# mean mean for the column to be considered > +# max_val max_value for the column to be considered > +# > +# This function also calculates the standard deviation > +# which is required to convert standard normal distribution > +# to normal distribution I cannot make any sense of this. The intent of this function is to generate random numbers with the mean of `mean` and standard deviation of ... (max_val - mean) /6 ? > +# > +CREATE FUNCTION f1(mean DOUBLE, max_val DOUBLE) RETURNS DOUBLE > +BEGIN > +DECLARE std_dev DOUBLE DEFAULT 0; > +SET @z= (rand() + rand() + rand() + rand() + rand() + rand() + > +rand() + rand() + rand() + rand() + rand() + rand() - 6); Here we get mean=1, stddev=1. > +SET std_dev= (max_val - mean)/6; > +SET @z= std_dev*@z + mean; ok so we have generated a random number with the mean 'mean' and std_dev as shown above. > +return @z; Please do not use session variables for function-local computations. The variables are in user-session scope, that is, this function will eventually cause a surprise by overwriting user's @z. > +END| > +# > +# parameters: > +# len length of the random string to be generated > +# > +# This function generates a random string for the length passed > +# as an argument with characters in the range of [A,Z] > +# > +CREATE function f2(len INT) RETURNS varchar(128) > +BEGIN > +DECLARE str VARCHAR(256) DEFAULT ''; > +DECLARE x INT DEFAULT 0; > +WHILE (len > 0) DO > +SET x =round(rand()*25); > +SET str= CONCAT(str, CHAR(65 + x)); > +SET len= len-1; > +END WHILE; > +RETURN str; > +END| > +# > +# parameters: > +# mean mean for the column to be considered > +# min_val min_value for the column to be considered > +# max_val max_value for the column to be considered > +# > +CREATE function f3(mean DOUBLE, min_val DOUBLE, max_val DOUBLE) RETURNS INT > +BEGIN > +DECLARE r DOUBLE DEFAULT 0; > +WHILE 1=1 DO > +set r= f1(mean, max_val); > +IF (r >= min_val) THEN > +RETURN round(r); > +end if; > +END WHILE; > +RETURN 0; > +END| > +create table t2 (id INT NOT NULL, a INT, b int); > +insert into t2 select a, f3(12, 0, 64), f3(32, 0, 128) from t1; > +CREATE TABLE t3( > +id INT NOT NULL, > +names VARCHAR(64), > +address VARCHAR(128), > +PRIMARY KEY (id) > +); > +# > +# table t3 stores string calculated from the length stored in > +# table t2 > +# > +insert into t3 select id, f2(a), f2(b) from t2; > +set sort_buffer_size=262144*10; > +flush status; > +select id, > +MD5(group_concat(substring(names,1,3), substring(address,1,3))) > +FROM t3 > +GROUP BY id DIV 100 > +ORDER BY id; > +id MD5(group_concat(substring(names,1,3), substring(address,1,3))) > +10 351239227a41de08388ea422f928cc29 > +149 67299eb34e363edabe31576890087e97 > +232 7ac931ef07a24ebe1293093ec6fa8f3d > +311 8625cade62c8b45c63d8978f8968ebb5 > +430 362761f4180d40372667c8dd7cdcc436 > +502 5380af74db071a35fb1d2491368e641b > +665 d3e3e2a2cb4e0de17c4f12e5b7745802 > +719 5d93632d4c30ec99802f7be7582f4f2d > +883 27747ef400898c7eeeba3ebea8c42fb1 > +942 d1e4ae80ca57b99ee49201b658a7b040 > +1007 fceb25160237c8a3c262735b81d027ac > +1134 cfa9c86c901aaace0e9e94dc6a837468 > +1226 4fb8e9ab9acdd251e7bc51db9e4d2f3b > +1367 e17fa4948562b3411f0b64084de0c605 > +1486 85dd0f507e660600820f106dc8887edf > +1502 5bf6015f936908eed31f5769ad4b0d72 > +1674 01f6c54ea21c4acd26f6c1df6abd793c > +1781 6d38cd061db1f30e2e37cd7d9ac600ad > +1803 2ac17a3853677ffde105735c92a9f2ea > +1969 e1e2e39e9d26baebe23232a429783feb > +2087 af67a443d21665bbb425a783f4e434fa > +2111 1906e379e9ae0b3b580fa134d2a5a146 > +2268 2afaf9091f92fb8e409142552724a85e > +2328 5a8fd5d24c9f7c7bcfbcde84a5b0cfe2 > +2416 d9a69c46523f71fce606c6d6c92ca516 > +2599 55a436a6fb744eefd6878473c34fa41e > +2602 98317430fe15bcc9bb5968b5052c9106 > +2777 8b5c30ae940ff7f31839309b535e3a15 > +2858 0db2f3bcb138c2f91445c4205374a3b4 > +2922 fed051b9185591bc0aaebd1e1471944d > +3027 f0cff102210e7fa32db222ac3444e4cf > +3131 c2f3f5a92d4c2b45cadd9c8cbf04d1be > +3220 8db6dfcca0461654dcb963fe2e1d8f41 > +3331 42031ed42643c755dfd936eb96b28ed5 > +3452 09f418c82012ff6789a6429be0c10f98 > +3519 7d26aac1dbbcff68b528b8c1d80a2c7b > +3680 0ff5b4295168db71b997f6001bba7015 > +3799 3460724c5fc7271a0a3189bf275b9b89 > +3876 13f21a3dfc2bad54c12fffae7cdf8326 > +3937 a240132ca8905b8165bf6e16fa6e7b3a > +4029 5fabf8408215c5bf896eda4e173a8a98 > +4158 c7829b1eeda97ff8c9b2a24ead3f6df6 > +4291 0d24e7e9da38dc44ffb43976560c4730 > +4355 bc804d019300149cb891b8fe8afbe445 > +4461 bb5a658677030b64ca3fd095f8a054fd > +4544 e04f6bfc8dcb8d8014ce39e1b707ed0b > +4646 06af0dd12faee32a07e785c4d56856b8 > +4714 d0c99cc1aead7d06e5323867867d4b00 > +4848 208d1ca5ade34053d92f96937f76380b > +4935 3b62eb6129970e714bdc74565183e183 > +5014 9e19c021b79e32ea6fceb7ced26a3a68 > +5184 41fa16423738302b2fdd6cda8e52f2c9 > +5219 3ab8090c30c0206c1e30ce6cd76cb617 > +5349 bd3e73dd60fbd1819aa468d3d0e6999c > +5400 80dc0e71fcbd2abfec9b585cc04a7545 > +5507 96ed16d40a9e6a1231bc88bd6b3f9c3e > +5672 764347fc7e265a1478c890fa38d8c892 > +5725 6767ae39fec9b789b8b542080162af46 > +5849 41df99caa43ee3f3b162c66c3eb61a44 > +5941 0725e779ca53da50461ef0d3758d819d > +6064 06d28bf28138d5726ab61e51a2e87edc > +6135 b2567b682dd449e358e11c4fb7f7bb72 > +6289 8aa8131d32436add670fed1e7628b297 > +6329 127b1600d2a9f857501f0263536d200b > +6404 266b87348831b9cc5b570e2b16c3006a > +6580 f70b98a00f6adb163c0f89bb6bb6d1ad > +6653 a13a591ba0c88985040c51fda2af7a72 > +6773 ee4306ceb6a3266617707a1ca637c328 > +6822 a8c368cc486b650f6254614535b5b051 > +6938 a7c160cec86018b78942b60b62b5b7fd > +7085 eb360d65bc8080cd5879fb8ddee830cd > +7180 c54bebbb560d9e9196a6f986022d4253 > +7290 4d1820f520483d785ba4e1c89b938f20 > +7390 0d3cd69b8e02fde232df802f3e9fc7a2 > +7449 7328ee3fe9383f891b9af5244c63a0e0 > +7589 467169481de385077ebcad083dd36b0b > +7686 ae22b711e21ba0e0fe20ba713408263a > +7713 e20cd84a1ee8bd1d743947c9c381731d > +7844 bc3f0534e283616d6a4dbb0902c03fa6 > +7935 146ea350d8f1cfef44aa7470cf9e02f8 > +8059 3a88201a77ccbd8ce651eeb555c29fe5 > +8153 9db1e67ef602768b7182401905bacc26 > +8245 c5e6c51763b0bbc1a7e72fe1615f9440 > +8310 ee37ab957141c733350e21a6ed2176f5 > +8432 34ae43ecbfa6c96e12a8c315937d511f > +8596 710f7c0bc4fadbdd859352b584b19d66 > +8647 df6f807e47599027749e1b09b04f6083 > +8742 5efcaddfa993721074a1691947ca611e > +8856 40ad2459d26129770ac6ac2da757ad7e > +8967 344f6b2c8242b9b3bbd09898a80ba4ee > +9057 3084c365110820be5bbfc721f4b2f37d > +9148 13b2a5aa09a1f107f656e848a963e8ea > +9275 908187dba9416102a566b955b29f709e > +9311 d6c8096f5763c6ebdaccb3e2cc3ae686 > +9488 62deb4d1a8900ea7cd7daa1909917490 > +9518 730ecae84924d86922c82152c191d0f6 > +9696 0a15d3446ba3d4b7ca8224633fbab666 > +9752 a74f840a4e599466799d4e0879533da0 > +9887 a7c29b0e5edfcd20572e0fda12a9e9aa > +9903 e89c3ab708646a5d73683ea68c4e366a > +10000 9cc0d2b033602eaea73fa9b2201b01b6 > +show status like '%sort%'; > +Variable_name Value > +Sort_merge_passes 0 > +Sort_priority_queue_sorts 0 > +Sort_range 0 > +Sort_rows 10101 > +Sort_scan 2 > +set sort_buffer_size=default; > +set @@RAND_SEED1= @save_rand_seed1; > +set @@RAND_SEED2= @save_rand_seed2; > +drop function f1; > +drop function f2; > +drop function f3; > +drop table t1, t2, t3; > diff --git a/mysql-test/main/order_by_pack_big.test b/mysql-test/main/order_by_pack_big.test > new file mode 100644 > index 00000000000..021edfee13f > --- /dev/null > +++ b/mysql-test/main/order_by_pack_big.test > @@ -0,0 +1,107 @@ > +--source include/big_test.inc > +--source include/have_sequence.inc > +--source include/have_64bit.inc > + > +set @save_rand_seed1= @@RAND_SEED1; > +set @save_rand_seed2= @@RAND_SEED2; > +set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772; > + > +create table t1(a int); > +insert into t1 select seq from seq_1_to_10000 order by rand(); > +delimiter |; > + > +--echo # > +--echo # parameters: > +--echo # mean mean for the column to be considered > +--echo # max_val max_value for the column to be considered > +--echo # > +--echo # This function also calculates the standard deviation > +--echo # which is required to convert standard normal distribution > +--echo # to normal distribution > +--echo # > + > +CREATE FUNCTION f1(mean DOUBLE, max_val DOUBLE) RETURNS DOUBLE > +BEGIN > + DECLARE std_dev DOUBLE DEFAULT 0; > + SET @z= (rand() + rand() + rand() + rand() + rand() + rand() + > + rand() + rand() + rand() + rand() + rand() + rand() - 6); > + SET std_dev= (max_val - mean)/6; > + SET @z= std_dev*@z + mean; > + return @z; > +END| > + > +--echo # > +--echo # parameters: > +--echo # len length of the random string to be generated > +--echo # > +--echo # This function generates a random string for the length passed > +--echo # as an argument with characters in the range of [A,Z] > +--echo # > + > +CREATE function f2(len INT) RETURNS varchar(128) > +BEGIN > + DECLARE str VARCHAR(256) DEFAULT ''; > + DECLARE x INT DEFAULT 0; > + WHILE (len > 0) DO > + SET x =round(rand()*25); > + SET str= CONCAT(str, CHAR(65 + x)); > + SET len= len-1; > + END WHILE; > +RETURN str; > +END| > + > +--echo # > +--echo # parameters: > +--echo # mean mean for the column to be considered > +--echo # min_val min_value for the column to be considered > +--echo # max_val max_value for the column to be considered > +--echo # > + > +CREATE function f3(mean DOUBLE, min_val DOUBLE, max_val DOUBLE) RETURNS INT > +BEGIN > + DECLARE r DOUBLE DEFAULT 0; > + WHILE 1=1 DO > + set r= f1(mean, max_val); > + IF (r >= min_val) THEN > + RETURN round(r); > + end if; > + END WHILE; > + RETURN 0; > +END| > + > +delimiter ;| > + > +create table t2 (id INT NOT NULL, a INT, b int); > +insert into t2 select a, f3(12, 0, 64), f3(32, 0, 128) from t1; > + > +CREATE TABLE t3( > + id INT NOT NULL, > + names VARCHAR(64), > + address VARCHAR(128), > + PRIMARY KEY (id) > +); > + > +--echo # > +--echo # table t3 stores string calculated from the length stored in > +--echo # table t2 > +--echo # > + > +insert into t3 select id, f2(a), f2(b) from t2; > + > +set sort_buffer_size=262144*10; > +flush status; > +select id, > + MD5(group_concat(substring(names,1,3), substring(address,1,3))) > +FROM t3 > +GROUP BY id DIV 100 > +ORDER BY id; > +show status like '%sort%'; > +set sort_buffer_size=default; > + > +set @@RAND_SEED1= @save_rand_seed1; > +set @@RAND_SEED2= @save_rand_seed2; > + > +drop function f1; > +drop function f2; > +drop function f3; > +drop table t1, t2, t3; > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB and reproducible builds: Mroonga, RocksDB and TokuDB
by Otto Kekäläinen 09 Jan '20

09 Jan '20

Reproducible builds are important for software supply chain security. See https://reproducible-builds.org/ All packages in Debian are tested for reproducibility. Currently the latest MariaDB 10.3 build in Debian fails due to the build of the Mroonga, RocksDB and TokuDB plugins. See https://tests.reproducible-builds.org/debian/rb-pkg/unstable/amd64/mariadb-… I've filed issues about this at Mroonga and RocksDB now: https://github.com/mroonga/mroonga/issues/298 https://github.com/facebook/rocksdb/issues/6276 TokuDB does not seem to accept any issues anymore and there are no commits since 2015. The project is dead, right? https://tokutek.atlassian.net/projects/DB/issues https://github.com/percona/tokudb-engine

1 0

Re: [Maria-developers] c91ec05e01b: MDEV-20865 Store foreign key info in TABLE_SHARE
by Sergei Golubchik 07 Jan '20

07 Jan '20

Hi, Aleksey! Just a question for now (and a couple of style comments, I didn't look at the logic yet) On Dec 04, Aleksey Midenkov wrote: > revision-id: c91ec05e01b (mariadb-10.4.4-427-gc91ec05e01b) > parent(s): e6d653b448d > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2019-11-26 13:04:07 +0300 > message: > > MDEV-20865 Store foreign key info in TABLE_SHARE > > 1. Access foreign keys via TABLE_SHARE::foreign_keys and > TABLE_SHARE::referenced_keys; > > 2. Remove handler FK interface: > > - get_foreign_key_list() > - get_parent_foreign_key_list() > - referenced_by_foreign_key() Good, that was the goal > 3. Invalidate referenced shares on: > > - RENAME TABLE > - DROP TABLE > - RENAME COLUMN > - CREATE TABLE > - ADD FOREIGN KEY > > When foreign table is created or altered by the above operations all > referenced shares are closed. This blocks the operation while any > referenced shares are used (when at least one its TABLE instance is > locked). And this is the main question of this email: Why do you close referenced tables? Minor comments below > diff --git a/sql/handler.h b/sql/handler.h > index e913af1d15d..10984505f70 100644 > --- a/sql/handler.h > +++ b/sql/handler.h > @@ -1030,6 +1031,15 @@ struct TABLE_SHARE; > struct HA_CREATE_INFO; > struct st_foreign_key_info; > typedef struct st_foreign_key_info FOREIGN_KEY_INFO; > +class Table_ident; > +class FK_list : public List<FOREIGN_KEY_INFO> > +{ > +public: > + /* Get all referenced tables for foreign key fk_name. */ > + bool get(THD *thd, std::set<Table_ident> &result, LEX_CSTRING &fk_name, bool foreign); > + /* Get all referenced or foreign tables. */ > + bool get(THD *thd, std::set<Table_ident> &result, bool foreign); Seems unnecessary. Copying a list into a std::set _could_ be justified, if you'd later used it for quick checks "if this table belongs to a set" - something you cannot quickly do with a List. But as far as I can see you copy the List into std::set and then iterate this set. This doesn't make much sense, you can iterate the original list just fine. > +}; > typedef bool (stat_print_fn)(THD *thd, const char *type, size_t type_len, > const char *file, size_t file_len, > const char *status, size_t status_len); > diff --git a/sql/lex_string.h b/sql/lex_string.h > index a62609c6b60..769f4dcbf5e 100644 > --- a/sql/lex_string.h > +++ b/sql/lex_string.h > @@ -41,11 +41,47 @@ class Lex_cstring : public LEX_CSTRING > str= start; > length= end - start; > } > + Lex_cstring(const LEX_CSTRING &src) > + { > + str= src.str; > + length= src.length; > + } > void set(const char *_str, size_t _len) > { > str= _str; > length= _len; > } > + Lex_cstring *strdup_root(MEM_ROOT &mem_root) The way you use it, it looks like you really need a constructor, not a strdup. > + { > + Lex_cstring *dst= > + (Lex_cstring *) alloc_root(&mem_root, sizeof(Lex_cstring)); > + if (!dst) > + return NULL; > + if (!str) > + { > + dst->str= NULL; > + dst->length= 0; > + return dst; > + } > + dst->str= (const char *) memdup_root(&mem_root, str, length + 1); > + if (!dst->str) > + return NULL; > + dst->length= length; > + return dst; > + } > + bool operator< (const Lex_cstring& rhs) const > + { > + return length < rhs.length || (length == rhs.length && memcmp(str, rhs.str, length) < 0); > + } > + bool operator== (const Lex_cstring& rhs) const > + { > + return length == rhs.length && 0 == memcmp(str, rhs.str, length); > + } > + bool operator> (const Lex_cstring& rhs) const > + { > + return length > rhs.length || (length == rhs.length && memcmp(str, rhs.str, length) > 0); > + } Nope. We've been here before, haven't we? Don't overload operators. If you want a method to compare, call it cmp(), or greater_than(), or something btw your comparison has quite weird and unconventional semantics > + > }; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 15