October 2024 - developers - lists.mariadb.org

MDEV-34705: Storing binlog in InnoDB
by Kristian Nielsen 06 Dec '24

06 Dec '24

I am in the early stages of a project to hopefully implement a new binlog format that is managed transactionally by InnoDB. This is a complex change, and I want to solicit comments early, so I encourage feedback. The TLDR; of this: - The current binlog format stores events separately from InnoDB. This requires a complex and _very_ constly 2-phase commit protocol between the binlog and InnoDB for every commit. - The binlog format is rather naive, and not well suited as a high-performance transactional log. It exposes internal implementation detail to the slaves, and its flat-file nature makes it inflexible to read (for example finding the last event sequential start from the start). Replacing it with something new has the potential to reap many benefits. - This project proposes to extend the storage engine API to allow an engine to implement binlog storage. It will be implemented in InnoDB so that binlog events are stored in InnoDB tablespaces, and the standard InnoDB transactional and recovery mechanisms are used to provide crash safety and optionally durability. - This mechanism will allow full crash-recovery and consistency without any fsync per commit (--innodb-flush-log-at-trx-commit=0), providing huge improvement in throughput for applications that do not require strict durability requirements. And it will reduce fsync requirement to one per commit for durable config, with greatly improved opportunities for group commit. Below a long and somewhat raw write-up of my current state of design. There was some interest expressed in learning more about this work, an interest that I'm very pleased with ;-). The current state of my prototype implementation is available in the branch knielsen_binlog_in_engine: https://github.com/MariaDB/server/commits/knielsen_binlog_in_engine Architectural considerations. The fundamental problem with the binlog is that it functions as a transactional log, but it is separate from the InnoDB transactional log. This is very unoptimal. To ensure consistency in case of crash, a two-phase commit protocol is needed that requires two fsync per commit, which is very costly in terms of performance. And it causes a lot of complexity in the code as well. We should make it so that there is only a single transactional log ("single source of truth") used, at least for the case where only a single storage engine is used (eg. InnoDB) for all tables. Some different options for doing this can be considered (explained in detail below): 1. Implement a new binlog format that is stored in a new InnoDB tablespace type (or more generally in any storage engine that implements the appropriate new API for binlog). 2. Keep the current binlog format, extend the InnoDB redo log format with write-ahead records for binlog writes. Integrate binlog writes with InnoDB checkpointing and buffer pool flush. 3. Store the binlog events directly inside the InnoDB redo log. Implement log archiving of the InnoDB log to preserve old binlog data as long as desired. 4. (Mentioned only for completeness): Implement a general redo log facility on the server level, and change both InnoDB and binlog to use that facility. I am currently working on option (1), inspired by talks I had with Marko Mäkelä at the MariaDB fest last Autumn. He suggested that a new type of tablespace be added to InnoDB which would hold the binlog, and explained that this would require relative small changes in the InnoDB code. I think this is the best option. The InnoDB log and tablespace implementation is very mature, it is very good to be able to re-use it for the binlog. The integration into InnoDB looks to be very clean, and all the existing infrastructure for log write, recovery, checkpointing, buffer pool can be re-used directly. All the low-level detail of how the binlog is stored on disk can be handled efficiently inside the InnoDB code. This option will require a lot of changes in the binlog and replication code, which is the main cost of choosing this option. I have a _lot_ of experience with the MariaDB replication code, and I think this is feasible. And I think there are another set of huge benefits possible from implementing a new binlog format. The existing binlog format is very naive and neither very flexible, nor well suited as a high-performance transactional log. So changing the binlog format is something that should be done eventually anyway. Option (2) would also be feasible I think, and has the advantage of needing less changes to the replication code. Monty mentioned seeing a patch that sounded something like this. The main disadvantage is that I think this will require complex interaction between low-level InnoDB checkpointing code and low-level binlog writing code, which I fear will eventually lead to complex code that will be hard to maintain and will stiffle further innovation on either side. It also doesn't address the other problems with the current binlog format. To me, letting the storage engine control how it stores data transactionally - including the binlog events - is the "right" solution, and worth persuing over a quicker hack that tries to avoid changing code. Option (3) is also interesting. The idea would be that the binlog becomes identical to the InnoDB redo log, storing the binlog data interleaved with the InnoDB log records. This is conceptually simpler than options (1) and (2), in the sense that we don't have to first write-ahead-log a record for modifying binlog data, and then later write the actual data modification to a separate tablespace/file. But the InnoDB log is cyclic, and old data is overwritten as the log LSN wraps over the end. Thus, this option (3) will require implementing log archiving in InnoDB, which I am not sure how much work will be required. Either the binlog data could be archived by copying out asynchroneously to persistent binlog files, which becomes somewhat similar to option (1) or (2) then perhaps. Or InnoDB could be changed to create a new log file at wrap-over, renaming the old one to archive it, though this will leave the binlog data somewhat "dilluted", mixed up with InnoDB internal redo records. One pragmatic reason I've chosen option (1) over this option (3) for now is that I am more comfortable with doing large changes to replication code than to InnoDB code. Also, option (3) would more drastically change the way users can work with the binlog files, as a large amount of the most recent data would be sitting inside the InnoDB redo log and not in dedicated binlog files, unlike options (1) and (2). Still, option (3) is an interesting idea and has the potential to reduce the write amplification that will occur from (1) and (2). So I'm eager to hear any suggestions around this. Option (4) I mention only for completeness. Somehow, the "clean" way to handle transactional logging would be if the server layer provides a general logging service, and all the components would then use this for logging. InnoDB and the binlog, but also all the other logs or log-like things we have, eg. Aria, DDL log, partitioning, etc. However, I don't think it makes sense to try to discard the whole InnoDB write-ahead logging code and replace it with some new implementation done on the server level. And I'm not even sure it makes sense from a theoretical point of view - somehow, the logging method used is intrinsically a property of the storage engine; it seems dubious if a general server-level log facility could be designed that would be optimal for both InnoDB and RocksDB, for example. High-level design. Introduce a new option --binlog-storage-engine=innodb. Extend the storage engine API with the option for an engine to provide a binlog implementation, supported by InnoDB initially. Initially the option cannot be changed dynamically. When --binlog-storage-engine=innodb, the binlog files will use a different format, stored as a new type of InnoDB tablespace. It should support that the old part of the binlog is the legacy format and the new is the InnoDB format, to facilitate migrations. Maybe also support going back again, though this is less critical initially. A goal is to clean up some of the unnecessary complexity of the legacy binlog format. The old format will be supported for the foreseeable future, so the new format can break backwards compatibility. For example, the name of binlog files will be fixed, binlog-<NNNNNN>.ibb, so each file can be identified solely by its number NNNNNN. The option to decide the directory in which to store the binlog files should be supported though. I think we can require the slaves to use GTID when --binlog-storage-engine=innodb is enabled on the master. This way we can avoid exposing slaves to internal implementation details of the binlog, and the slaves no longer need to update the file relay-log.info at every commit. We should also be able to get rid of the Format_description_log_event and the Rotate_log_event at the start of every binlog file, so that the slave can view the events from the master as one linear stream and not care about how the data is split into separate binlog files on the master. The binlog format will be page based. This will allow pre-allocating the binlog files and efficiently writing them page by page. And it will be possible to access the files without scanning them sequentially from the start; eg. we can find the last event by reading the last page of the file, binary-searching for the last used page if the binlog file is partially written. The GTID indexes can be stored inside the same tablespace as the binlog data (eg. at the end of the tablespace), avoiding the need for a separate index file. GTID index design is still mostly TBD, but I think it can be implemented so that indexes are updated transactionally as part of the InnoDB commit and no separate crash recovery of GTID indexes is needed. With GTID indexes being guaranteed available, we can use them to obtain the GTID state at the start of each binlog file, and avoid the need for the Gtid_list_log_event at the start of the binlog. With a page-based log with extensible format, metadata can be added to the binlog that is only used on the master without having to introduce new replication events that are not relevant to the replication on the slave. This can be used eg. to eliminate the Binlog_checkpoint_log_event, for example. Possibly the binlog checkpoint mechanism can be completely avoided for the --binlog-storage-engine=innodb case, since there is no more 2-phase commit needed in the common case of an InnoDB-only transaction. The existing binlog checksum and encryption will no longer be used, instead the standard InnoDB checksums and encryption will be reused. Implementation plan. The storage engine API must be extended to provide a facilities for writing to the binlog and for reading from the binlog. The design of the API is TBD, should be discussed to try to make it generic and suitable for other engines than InnoDB. When writing to binlog in InnoDB, the central idea is to use the same mini-transaction (mtr) that marks the transaction as committed in InnoDB. This is what makes the binlog data guaranteed consistent with the InnoDB table data without the need for two-phase commit. The initial version I think can use the existing binlog group commit framework; this will simplify the implementation. This will thus keep the LOCK_commit_ordered and the queue_for_group_commit() mechanisms. Later work can then be to see if this can be even further improved in terms of scalability. I want to implement that large event groups can be split into multiple chunks in the binlog that no longer need to be consecutive. The final chunk will be the one that contains the GTID and marks the event group binlogged; this final chunk will then refer back to the other parts. This way, a large transactions can be binlogged without stalling the writing of other (smaller) transactions in parallel, which is a bottleneck in the legacy binlog. And it avoids problems with exceeding the maximum size of an mtr. In the first version, I think the binlog reader in the dump thread will collect and concatenate the different chunks before sending them to the slave, so simplify the initial implementation. But a later change could allow the slave to receive the different chunks interleaved between different event groups. This can even eventually allow the slave to speculatively execute events even before the transaction has committed on the master, to potentially reduce slave lag to less than the time of even a single transaction; this would be a generalisation of the --binlog-alter-two-phase feature. The binlog will be stored in separate tablespace files, each of size --max-binlog-size. The binlog files will be pre-allocated by a background thread. Since event groups can spill over to the next file, each file can be a fixed size, and hopefully also the rotate events will no longer be necessary. A busy server will quickly cycle through tablespace files, and we want to avoid "using up" a new tablespace ID for each file (InnoDB tablespace IDs are limited to 2**32 and are not reused). Two system tablespace IDs will be reserved for the binlog, and new binlog files will alternate between them. This way, the currently written binlog can be active while the previous one is being flushed to disk and the remaining writes checkpointed. Once the previous log has been flushed completely, its tablespace ID can be re-used for the next, pre-allocated binlog file and be ready for becoming active. This way, the switching to the next binlog file should be able to occur seamlessly, without any stalls, as long as page flushing can keep up. The flushing of binlog pages will be prioritised, to avoid stalling binlog writes, to free up buffer pool pages that can be used more efficiently than holding binlog data, and to quickly make the binlog files readable from outside the server (eg. with mysqlbinlog). The binlog dump thread that reads from the binlog and sends the data to slaves will use a binlog reading API implemented in InnoDB. I will prefer to read directly from the binlog files, in order to reduce pressure on the InnoDB buffer pool etc. A slave that connects from an old position back in time may need to read a lot of old data from the binlogs; there is little value in loading this data into the buffer pool, evicting other more useful pages. The reader can lookup in the InnoDB buffer pool with the BUF_GET_IF_IN_POOL flag. This way, the data can be accessed from the buffer pool if it is present. If not present, we can be sure that the data will be in the data file, and can read it from the file directly. If the data is known to be already flushed to disk before the specific binlog position, then the buffer pool lookup can be skipped altogether. The mysqlbinlog program will need to be extended somehow to be able to read from an InnoDB tablespace (or in general other storage engine). I think this means mysqlbinlog needs some kind of engine plugin facility for reading binlog. Note that the -read-from-remote-server option will also be available to read data from a mysqlbinlog that doesn't the new format, or to read the very newest data before it gets written to disk. Final words. This is still an early draft of the feature, as it is being worked on and refined; things are subject to change. And this also means that things are *open* to change, according to any suggestions with a good rationale.

2 7

Contribute to MariaDB compatibility in open source projects?
by Robert Silén 06 Nov '24

06 Nov '24

All, We have been working on community outreach as described in an earlier blog Improving MariaDB support in open source projects <https://mariadb.org/improving-mariadb-support-in-open-source-projects/>. Many projects have made efforts to improve their MariaDB compatibility with or without our help, and also documented compatibility more visibly in READMEs and documentation. However, we at the Foundation don't have the knowhow or resources to help out everybody, so I thought I'd share a list of some issues. Would somebody like to contribute to any of them? Any comment on the issues, or code contributions to them would be much appreciated. Thank you. Do share any thoughts or actions on this. Robert Silén, community advocacy at MariaDB Foundation robert.silen(a)mariadb.org - https://github.com/SOCI/soci/issues/1150: add MariaDB to README - https://github.com/tinode/chat/pull/924: Help set up CI for MariaDB (and MySQL) - https://github.com/inspircd/inspircd-docs/issues/239: tls and srv not available in MariaDB - https://github.com/umami-software/umami/discussions/2943: BIN_TO_UUID() not available in MariaDB (MDEV-15854 <https://jira.mariadb.org/browse/MDEV-15854>) - https://github.com/diesel-rs/diesel/issues/1882: Create a distinct backend for MariaDB - https://github.com/grafana/grafana: Create a dedicated DataStore for MariaDB - https://github.com/drizzle-team/drizzle-orm/pull/1692: WIP: Add support for Maria DB (core API implementation) - https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1079753: Create mariadb specific dbconfig-mariadb - https://core.trac.wordpress.org/ticket/60037: Fix version checking in next release - https://gitlab.com/zoph-project/zoph/-/issues/267: give a thumbs up to their own addition of MariaDB to documentation (preferably recommend MariaDB)

2 1

Re: e8021aaf28e: auth: refactor MYSQL_AUTHENTICATION_INTERFACE_VERSION naming
by Sergei Golubchik 29 Oct '24

29 Oct '24

Hi, Nikita, This looks very confusing, this is not how versions work. What are you trying to achieve by that? On Oct 07, Nikita Malyavin wrote: > revision-id: e8021aaf28e (mariadb-11.6.1-9-ge8021aaf28e) > parent(s): a5b80531fbb > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2024-10-04 00:16:17 +0200 > message: > > auth: refactor MYSQL_AUTHENTICATION_INTERFACE_VERSION naming > > Rename MYSQL_AUTHENTICATION_INTERFACE_VERSION to > MYSQL_AUTH_INTERFACE_VERSION_2_02. > This is the current latest version, and it will be fixed to all auth plugins > for current implementation. > MYSQL_AUTHENTICATION_INTERFACE_VERSION will stay for compatibility with 3-rd > party auth plugins and will be fixed to V. 2.02. > Add MYSQL_AUTHENTICATION_INTERFACE_VERSION_LAST that will be updated as needed. > Add MYSQL_AUTH_INTERFACE_MAJOR_MASK to identify major version. > Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

2 5

Re: b17308e254a: MDEV-34854 Parsec sends garbage when using an empty password
by Sergei Golubchik 29 Oct '24

29 Oct '24

Hi, Nikita, On Oct 07, Nikita Malyavin wrote: > revision-id: b17308e254a (mariadb-11.6.1-10-gb17308e254a) > parent(s): e8021aaf28e > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2024-10-04 01:33:47 +0200 > message: > > MDEV-34854 Parsec sends garbage when using an empty password > > When an empty password is set, the server doesn't call > st_mysql_auth::hash_password and leaves > MYSQL_SERVER_AUTH_INFO::auth_string empty. > > Fix: > generate hashes for empty passwords as well. This breaks some auth > plugins, so we increment interface version and do it only from Auth V. > 2.03. > > Some empty passwords could be already stored with no though. The user "with no though" ? > will have to call SET PASSWORD once again, anyway the authentication > wouldn't have worked for such password. ok, I presume you mean ed25519 only, because mysql_native_password worked with an empty password and it generates an empty hash for it. > diff --git a/mysql-test/suite/plugins/r/parsec.result b/mysql-test/suite/plugins/r/parsec.result > index 512c066e2d7..b7e3537af29 100644 > --- a/mysql-test/suite/plugins/r/parsec.result > +++ b/mysql-test/suite/plugins/r/parsec.result ... let's add ed25519 test too, for completeness. > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 2722ea2ea19..ba05a5656c5 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -2402,7 +2402,10 @@ static int set_user_auth(THD *thd, const LEX_CSTRING &user, > res= ER_NOT_VALID_PASSWORD; > goto end; > } > - if (pwtext.length) > + > + // Starting from version 2.03 we also generate hash for empty passwords. > + if ((info->interface_version >= MYSQL_AUTH_INTERFACE_VERSION_2_03 I don't understand this MYSQL_AUTH_INTERFACE_VERSION_2_03 thing. First, again, that's not how a version is supposed to work. Second, this empty-password change isn't a change in the API. You can simply start calling hash_password() for empty passwords and it'll just work. I've tried :) > + && pwtext.str) || pwtext.length) > { > if (info->hash_password) > { Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

2 3

2024 Q4 release
by Daniel Black 28 Oct '24

28 Oct '24

All, Just letting you know that we're in the progress of getting the releases ready. The issue where this is tracked is: * https://jira.mariadb.org/browse/MDEV-35030 Branches being released are at the tips of these branches: bb-10.5-release (LTS) bb-10.6-release (LTS) bb-10.11-release (LTS) bb-11.2-release bb-11.4-release (LTS) bb-11.6-release bb-11.7-release So far the code is merged up to bb-11.4-release. Later branches will be updated, check history before using. There are two blocking bugs on bb-11.2-release onwards affecting compilation of the columnstore and connect storage engine. In addition to compiling the source yourselves; Containers: quay.ion/mariadb-foundation/mariadb-devel:{release} list: https://quay.io/repository/mariadb-foundation/mariadb-devel?tab=tags note: * 11.4 is from the 11.4 branch rather than bb-11.4-release, * 11.2 didn't build due to blocker bugs). Repos for testing: https://ci.mariadb.org/ contains for deb and rpm distros a repo file in the form: bb-{version}-release-latest-{arch}-{distro}-{version}-{deb|rpm}-autobake.{sources|repo} Please report any serious regressions with a Blocker priority bug, and mark this issue blocking MDEV-35030.

1 0

Re: 692ca003f8c: MDEV-33658 2/2 Cannot add a foreign key on a table with a matching long UNIQUE
by Sergei Golubchik 23 Oct '24

23 Oct '24

Hi, Nikita, On Oct 18, Nikita Malyavin wrote: > revision-id: 692ca003f8c (mariadb-10.5.26-169-g692ca003f8c) > parent(s): ba988513ec7 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2024-10-13 19:33:14 +0200 > message: > > MDEV-33658 2/2 Cannot add a foreign key on a table with a matching long UNIQUE > > Cannot add a foreign key on a table with a long UNIQUE multi-column index, that > contains a foreign key as a prefix. > > Check that index algorithms match during the "generated" keys duplicate removal. > > diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test > index 2f85328bda3..08b49b6c5da 100644 > --- a/mysql-test/main/long_unique_bugs.test > +++ b/mysql-test/main/long_unique_bugs.test > @@ -706,4 +706,19 @@ alter table t1 enable keys; > insert into t1 values (2); > drop table t1; > > +--echo # > +--echo # Cannot add a foreign key on a table with a long UNIQUE multi-column > +--echo # index, that contains a foreign key as a prefix. > +--echo # > +create table a (id int(19) primary key) engine = innodb; > +create table b (id int primary key, > + long_text varchar(1000), > + constraint unique_b unique key (id, long_text) > + ) engine = innodb default charset utf8mb4; > + > +alter table b add constraint b_fk_ida foreign key (id) references a (id); > +show create table b; > +drop table b; > +drop table a; Two problems with this test case. A minor one - it doesn't start with the --echo # MDEV-33658 Cannot add a foreign key on a table with a matching long UNIQUE line. Please add it. A more serious problem - it doesn't fail on a vanilla 10.5. The original set of SQL commands from MDEV-33658 fails with "Foreign key constraint is incorrectly formed", but your test doesn't. You might've oversimplified it. Please, fix the test case to fail on vanilla 10.5 with the above error. > + > --echo # End of 10.5 tests > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index 99ba9e3587b..3d01b9d5584 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -203,7 +203,7 @@ Foreign_key::Foreign_key(const Foreign_key &rhs, MEM_ROOT *mem_root) > > /* > Test if a foreign key (= generated key) is a prefix of the given key > - (ignoring key name, key type and order of columns) > + (ignoring key name, key type and order of columns, but minding the algorithm) it doesn't ignore the order of columns and "key type" is basically algorithm, so the above can simply be "(ignoring key name)" > > NOTES: > This is only used to test if an index for a FOREIGN KEY exists > @@ -218,6 +218,17 @@ Foreign_key::Foreign_key(const Foreign_key &rhs, MEM_ROOT *mem_root) > > bool is_foreign_key_prefix(Key *a, Key *b) > { > + ha_key_alg a_alg= a->key_create_info.algorithm; > + ha_key_alg b_alg= b->key_create_info.algorithm; > + > + bool is_a_btree= a_alg == HA_KEY_ALG_BTREE || a_alg == HA_KEY_ALG_UNDEF; > + bool is_b_btree= b_alg == HA_KEY_ALG_BTREE || b_alg == HA_KEY_ALG_UNDEF; > + > + // Either the algorithms are total match, or they both are btree > + // in order to be able to prefix > + if (a_alg != b_alg && !(is_a_btree && is_b_btree)) > + return false; I don't see why algorithms can be not b-tree, if they match. What use case did you have in mind? > + > /* Ensure that 'a' is the generated key */ > if (a->generated) > { > Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

2 1

Update documentation for non-blocking client library
by Kristian Nielsen 22 Oct '24

22 Oct '24

Hi Georg, Following MDEV-34859, I wrote a small update for the documentation on the new cmake option -DWITH_BOOST_CONTEXT=ON cmake option, patch included below. However, I am not sure how I can include it in the documentation? Feel free to just take it yourself, or let me know a preferred way to submit it. - Kristian. diff --git a/configuration_options.md b/configuration_options.md index 6e8da57..726b4f8 100644 --- a/configuration_options.md +++ b/configuration_options.md @@ -27,6 +27,11 @@ If you want to use a different generator, e.g. for nmake on Windows, you need to |WITH_OPENSSL|ON| Possible values are ON or OFF. Not supported anymore since Connector/C 3.0| |WITH_SSL|SCHANNEL (windows), otherwise OPENSSL|Specifies type of TLS/SSL library. E.g. GNUTLS, OPENSSL or SCHANNEL (Windows only). OFF disables TLS/SSL functionality| +### Non-blocking client library options +| Option | Default | Description | +|-|-|- +|WITH_BOOST_CONTEXT|OFF| Use `boost::context` instead of `ucontext` for the non-blocking client API. Can be used to build the non-blocking API on platforms that do not have `ucontext`. Note that on x86_64 (aka amd64), i386, and aarch64 (aka arm64), a native implementation is always used over `ucontext` or `boost::context`. (Added in 3.3.12)| + ### Client plugins Client plugins can be configured as dynamic plugins (DYNAMIC) or built-in plugins (STATIC) by specifying the plugin name followed by suffix `_PLUGIN_TYPE` as key, and `DYNAMIC` or `STATIC` as value.

2 1

Community request: audit the PARSEC plugin
by Andrew Hutchings 16 Oct '24

16 Oct '24

Hi all, There is a new authentication plugin called PARSEC that is set to become the default authentication plugin in MariaDB soon (MDEV-32618 and MDEV-12320). The current documentation for it can be found here: https://mariadb.com/kb/en/authentication-plugin-parsec/ I personally think that using a single salt and sending it to the client could be a problem. I do not work in the security industry and the documentation has a few errors in it and pieces missing, so I'm unclear how certain things work. I think the ext-salt requirements will likely add complications to load balancing scenarios and replication. This is a request for anyone in the community to please audit this plugin before it becomes the default. Once it is the default, it will be difficult to change it. Kind Regards -- Andrew (LinuxJedi) Hutchings Chief Contributions Officer MariaDB Foundation

2 2

Re: 778c67f7af0: MDEV-18151 Skipped error returning for GRANT/SET PASSWORD
by Sergei Golubchik 09 Oct '24

09 Oct '24

Hi, Oleksandr, On Oct 09, Oleksandr Byelkin wrote: > revision-id: 778c67f7af0 (mariadb-10.5.26-154-g778c67f7af0) > parent(s): e9c999caf48 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2024-10-04 09:28:46 +0200 > message: > > MDEV-18151 Skipped error returning for GRANT/SET PASSWORD > > Make error issueing for GRANT and SET PASSWORD the same. typo. "issuing" > Report errors wich were skipped before. > > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -2370,6 +2369,21 @@ static int set_user_auth(THD *thd, const LEX_CSTRING &user, > > res= 0; > end: > + switch(res) > + { > + case ER_OUTOFMEMORY: // should be reported by my_malloc > + case ER_NOT_VALID_PASSWORD: // should be reported by plugin > + case 0: > + break; > + case ER_SET_PASSWORD_AUTH_PLUGIN: > + my_error(res, MYF(0), plugin_name); > + break; > + case ER_PASSWD_LENGTH: > + my_error(ER_PASSWD_LENGTH, MYF(0), SCRAMBLED_PASSWORD_CHAR_LENGTH); this is also reported by the plugin. The caller doesn't know what the correct length is, it's not SCRAMBLED_PASSWORD_CHAR_LENGTH, this value is different for different plugins. > + break; > + default: > + DBUG_ASSERT(0); > + } > if (unlock_plugin) > plugin_unlock(thd, plugin); > return res; > Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

1 0

PRs with one approval pending the second
by Otto Kekäläinen 04 Oct '24

04 Oct '24

Hi *all* developers, Hopefully someone else than Andrew or Sergei replies as a project of the size of MariaDB should have multiple developers who are capable of reviewing PRs, and not overload a few. These PRs already have one approval, but would need a second to be merged. If you feel you are knowledgeable in the domain of the PRs, please help out by reviewing them. Thanks! https://github.com/MariaDB/server/pull/3225: MDEV-29955: add ${PCRE_LIBRARY_PATH} to link directories https://github.com/MariaDB/server/pull/3540: Add MTRs to further test coverage around TLS functionality

1 0

PRs with two approvals but not merged
by Otto Kekäläinen 04 Oct '24

04 Oct '24

Hi, Here are two PRs that have already two approvals, yet they have not been merged for many weeks. Can you please state explicitly on the PR as a comment what the submitter needs to do, or if there is nothing to do, just merge it? * https://github.com/MariaDB/server/pull/3208 * https://github.com/MariaDB/server/pull/2556 Thanks

3 3

More extensive major version upgrade testing
by Otto Kekäläinen 04 Oct '24

04 Oct '24

Hi, If any dev here is interested in the topic of ensuring the major version upgrades (and essentially running mariadb-upgrade) continues to work flawlessly and regressions are less likely to surface unnoticed, please review and consider adding your approvals to https://github.com/MariaDB/server/pull/3503 and share feedback on WIP https://github.com/MariaDB/server/pull/3563. Thanks!

2 1

Re: [PATCH] MDEV-28413 System versioning is incorrect in Spider
by Yuchen Pei 03 Oct '24

03 Oct '24

Hi Aleksey, Thanks for the patch. Please see below for some initial review comments. There are some todos for myself noted below which I will look into tomorrow. > From b4465fc6b51dfc4e645569201e6026ec6405367b Mon Sep 17 00:00:00 2001 > From: Aleksey Midenkov <midenok(a)gmail.com> > Date: Mon, 30 Sep 2024 22:26:51 +0300 > Subject: [PATCH] MDEV-28413 System versioning is incorrect in Spider > Basic functioning of system versioning in Spider: UPDATE, DELETE, > DELETE HISTORY, AS OF, FROM .. TO, BETWEEN .. AND. > More testing should be done for: > LOAD DATA, joins, derived tables, multi-update/delete and what else? > Note that we are still using HA_CAN_DIRECT_UPDATE_AND_DELETE, but not > for periods in DELETE (fwiw there is condition > !table_list->has_period(), but it is missing in UDPATE). > --- > .../mysql-test/spider/r/versioning.result | 142 ++++++++++++++++ > .../mysql-test/spider/t/versioning.test | 151 ++++++++++++++++++ > storage/spider/spd_db_conn.cc | 19 ++- > storage/spider/spd_db_include.h | 1 + > storage/spider/spd_db_mysql.cc | 25 ++- > 5 files changed, 335 insertions(+), 3 deletions(-) > create mode 100644 storage/spider/mysql-test/spider/r/versioning.result > create mode 100644 storage/spider/mysql-test/spider/t/versioning.test > [... 149 lines elided] > diff --git a/storage/spider/mysql-test/spider/t/versioning.test b/storage/spider/mysql-test/spider/t/versioning.test > new file mode 100644 > index 00000000000..6093169bff4 > --- /dev/null > +++ b/storage/spider/mysql-test/spider/t/versioning.test > @@ -0,0 +1,151 @@ > +--source include/have_innodb.inc > +--source suite/versioning/common.inc > + > +install plugin spider soname 'ha_spider'; For init the spider plugin in spider tests we commonly do --disable_query_log --disable_result_log --source ../t/test_init.inc --enable_result_log --enable_query_log > +set spider_same_server_link= on; > + > +--replace_result $MASTER_1_MYPORT MASTER_1_MYPORT > +eval create server s foreign data wrapper mysql options > +(host '127.0.0.1', database 'test', user 'root', port $MASTER_1_MYPORT); Use evalp instead of eval and you can save the --replace_result line > + > + > +create table t1 ( > + x int, y int > +) with system versioning; > + > +create table t2 ( > + x int, y int, > + row_start timestamp(6) as row start invisible, > + row_end timestamp(6) as row end invisible, > + period for system_time (row_start, row_end) > +) with system versioning; > + > +create or replace table t1_sp ( > + x int, y int) > +engine=spider comment='wrapper "mysql", srv "s", table "t1"' > +with system versioning; > + > +create or replace table t2_sp ( > + x int, y int, > + row_start timestamp(6) as row start invisible, > + row_end timestamp(6) as row end invisible, > + period for system_time (row_start, row_end)) > +engine=spider comment='wrapper "mysql", srv "s", table "t2"' > +with system versioning; > + > +--echo # Timestamps are not propagated (subject of MDEV-16546, but not for implicit system fields) > +set timestamp= unix_timestamp('2000-01-01 00:00:00'); I tried executing directly using select spider_direct_sql('set timestamp= unix_timestamp("2000-01-01 00:00:00");', '', 'srv "s"'); But then I get extra rows in subsequent select queries and I think it is caused by the timezones. It could be a separate task to fix this, but we should warn users about it. > +insert into t1_sp values (1, 1), (2, 2); It may be obvious to you, but I find the timestamps at times hard to keep track of, so I suggest adding a comment to each DML statement indicating the time, as well as the select row_start / row_end statements. For example, change the line above to insert into t1_sp values (1, 1), (2, 2); # T1 > +insert into t2_sp values (1, 1), (2, 2); and change this line to insert into t2_sp values (1, 1), (2, 2); # T2 > +--sleep 0.01 > + > +set timestamp= unix_timestamp('2000-01-01 00:11:11'); > +--echo # check dml generating history > +delete from t1_sp where x = 1; add # T3 > +--sleep 0.01 > +delete from t2_sp where x = 1; # T4 > +--sleep 0.01 > + > +select row_end into @t1 from t2 for system_time all where x = 1; And so we can annotate this line with the timestamp above, like so: select row_end into @t1 from t2 for system_time all where x = 1; # @t1 = T4 > + > +update t1_sp set y= y + 1; # T5 > +select row_start into @t2 from t1 where x = 2; # @t2 = T5 > +--sleep 0.01 > +update t2_sp set y= y + 1; # T6 With the annotations I suggested, results from the following block of selects becomes easier to understand. > + > +select * from t1_sp; > +select * from t2_sp; > +select * from t1_sp for system_time as of timestamp @t1; > +select * from t2_sp for system_time as of timestamp @t1; > +select * from t1_sp for system_time as of timestamp @t2; > +select * from t2_sp for system_time as of timestamp @t2; > +select * from t1_sp for system_time from timestamp @t1 to timestamp @t2; > +select * from t2_sp for system_time from timestamp @t1 to timestamp @t2; > +select * from t1_sp for system_time between timestamp @t1 and timestamp @t2; > +select * from t2_sp for system_time between timestamp @t1 and timestamp @t2; > + > +--echo # spider cannot call functions, it fails in spider_create_group_by_handler() > + # at: > + # > + # 1555 if (spider_db_print_item_type(item, null, spider, null, null, 0, > + # 1556 roop_count, true, fields_arg)) > + # 1557 { > + # 1558 dbug_print("info",("spider dbton_id=%d can't create select", roop_count)); > + # 1559 spider_clear_bit(dbton_bitmap, roop_count); > + # 1560 keep_going = false; > + # 1561 break; > + # 1562 } > + # This is fine. The Spider group-by-handler (GBH) is not meant to handle every situation. Either remove this block starting from the --echo, or make it more succinct by replacing it with something like (Note that GBH supports certain functions like trim): # The spider group-by-handler does not support the check_row function > +--echo # but somehow it doesn't fail the query and continues without FOR SYSTEM_TIME ALL clause > +select *, check_row(row_start, row_end) from t1_sp for system_time all order by row_end, y, x; > +select *, check_row(row_start, row_end) from t2_sp for system_time all order by row_end, y, x; I will check why the execution without the group-by-handler results in wrong results. > +--echo # SELECT from base table works as usual: > +select *, check_row(row_start, row_end) from t1 for system_time all order by row_end, y, x; > +select *, check_row(row_start, row_end) from t2 for system_time all order by row_end, y, x; > + > +--echo # here it works nice (append_table_list() has the chance to add FOR SYSTEM_TIME ALL) > + > +# Usage: SHOW_TIMESTAMPS=1 mtr versioning.spider Shouldn't the test name be spider.versioning? > +if ($SHOW_TIMESTAMPS) > +{ > + select @t1, @t2; > +} > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t1_sp for system_time all order by row_end, y, x; > + > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t2_sp for system_time all order by row_end, y, x; > + > +delete history from t1_sp before system_time @t1; > +delete history from t2_sp before system_time @t1; > + > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t1_sp for system_time all order by row_end, y, x; > + > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t2_sp for system_time all order by row_end, y, x; > + > +select * from t1 for system_time all; > +select * from t2 for system_time all; > + > +delete history from t1_sp; > +delete history from t2_sp; > + > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t1_sp for system_time all order by row_end, y, x; > + > +if (!$SHOW_TIMESTAMPS) > +{ > +--replace_regex /\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d.\d\d\d\d\d\d/TIMESTAMP/ > +} > +select *, row_start, row_end from t2_sp for system_time all order by row_end, y, x; > + > +select * from t1 for system_time all; > +select * from t2 for system_time all; > + > +drop tables t1, t2, t1_sp, t2_sp; > +set timestamp= default; > +--source suite/versioning/common_finish.inc > + > +uninstall plugin spider; > +drop table mysql.spider_link_failed_log, mysql.spider_link_mon_servers, mysql.spider_tables, mysql.spider_table_crd, mysql.spider_table_position_for_recovery, mysql.spider_table_sts, mysql.spider_xa, mysql.spider_xa_failed_log, mysql.spider_xa_member; > +drop function spider_direct_sql; > +drop function spider_bg_direct_sql; > +drop function spider_ping_table; > +drop function spider_copy_tables; > +drop function spider_flush_table_mon_cache; For deinit the spider plugin in spider tests we commonly do --disable_query_log --disable_result_log --source ../t/test_deinit.inc --enable_result_log --enable_query_log Please replace the lines from "uninstall plugin spider;" to the end of the test with this block. > diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc > index 8750e87237e..896fce8adf5 100644 > --- a/storage/spider/spd_db_conn.cc > +++ b/storage/spider/spd_db_conn.cc > @@ -1515,9 +1515,8 @@ int spider_db_append_key_where_internal( > ) { > SPIDER_RESULT_LIST *result_list = &spider->result_list; > SPIDER_SHARE *share = spider->share; > -#ifndef DBUG_OFF > TABLE *table = spider->get_table(); > -#endif Why remove the #ifndef and #endif? > [... 99 lines elided] Best, Yuchen

1 1

[Maria-developers] MariaDB // Temporal tables
by Robert Palm 02 Oct '24

02 Oct '24

2 2