January 2025 - developers - lists.mariadb.org

Next step on MDEV-34705, implement binlog in InnoDB
by Kristian Nielsen 29 Jan '25

29 Jan '25

Hi Marko, Time flies, somehow it's already more than a year since our first discussions on implementing the binlog in InnoDB and avoiding the extra fsync() and complexity of the two-phase commit between InnoDB and binlog. But things have progressed, and I have now reached the point where most of the basic groundwork is implemented. Event groups are binlogged to InnoDB tablespaces. Binlog dump thread can read the binlog and send to slave, and replication is working. Large event groups are split into pieces, bounding the amount of data that needs to be atomically written in mini-transactions and at commit time. There are still many details left, but mostly in the server-layer replication code which should be manageable, just will take some time to get completed. I think now is a good time for you to take a first real look at the InnoDB part of the changes, I would really value your input. The main part of the InnoDB code is in two files: 1. handler/handler0binlog.cc for the high-level part that deals mostly with the new binlog file format and interfacing to the server layer. 2. fsp/fsp0binlog.cc for the low-level part that most tightly interacts with the InnoDB mini-transactions and buffer pool. The most interesting part for you to look at is fsp/fsp0binlog.cc (~1k lines), though I'm happy to hear comments on any part of the patch, of course. The code is pushed to GitHub in the branch knielsen_binlog_in_engine: https://github.com/MariaDB/server/commits/knielsen_binlog_in_engine and I've also attached the complete patch. This is my first major patch for InnoDB, so there will undoubtedly be a number of style changes required. But the overall structure of the code should now be close to what I imagine would be the final result, with some pending ToDo steps marked in comments in the code, and detailed in the below list, some of which we discussed a bit already. I hope you will take a look at the patch and let me know of any questions or other things you need from me. Maybe we can also find a chance to discuss further if you will come to FOSDEM start of February, or I could visit sometimes in Finland. - Kristian. ---- Some known outstanding issues in the InnoDB part: - We previously discussed removing some of the page header overhead for binlog tablespaces. Currently the code just leaves alone the first FIL_PAGE_DATA bytes (38) and the last FIL_PAGE_DATA_END (8 IIRC). - We discussed previously to write the current LSN at the start of the tablespace, and use this in recovery to handle that we have only two tablespace IDs that are reused. So we need code in recovery that checks the LSN at the start of the tablespace, and skips redo records with LSN smaller than this. - We want to avoid the double-write buffer for binlog pages, at least for the first page write (most pages will only be written as full pages). You mentioned an idea to completely avoid the double-write buffer and instead do some specific code for recovery in the uncommon case where a partial binlog page is written to disk due to low commit activity. - The flushing of binlog pages to disk currently happens in a dedicated thread in the background. I'd welcome ideas on how to do this differently. It is good to flush binlog pages quickly and re-use their buffer pool entries for something better. Also writing the pages to disk quickly (not necessarily fsync()'ing) makes the data readable by mysqlbinlog. - Checksum and encryption should use the standard InnoDB mechanism. I assume checksum is already handled in the code through using the buffer pool and mini-transactions to read/write pages. Not sure about encryption. I need to implement that the code handles checksum and decryption when reading the pages manually from the file (not through buffer pool).

2 10

2025 Q1 release - near final
by Daniel Black 28 Jan '25

28 Jan '25

The issue where this is tracked is: https://jira.mariadb.org/browse/MDEV-35789 Current blockers: https://jira.mariadb.org/issues/?filter=31019&jql=project%20in%20(mdev)%20A… The "fix version" in the above url list all are 11.4+ so all LTS versions should be stable. Branches bb-10.5-release (LTS) bb-10.6-release (LTS) bb-10.11-release (LTS) bb-11.4-release (LTS) bb-11.7-release (stable for first time) bb-11.8-release (gamma - no LTS announcement made) If there's regression particularly impacting please ensure the issue has a blocker priority, with a good business case reason why it should block, and make as blocks MDEV-35789. 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: * pushes to *-release branch also gains the same tag as the main 10.X, 11.X tags. Check commit at the beginning of the server startup to determine actual commit. Repos for testing (packages may not last more than a few weeks at urls): 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}

1 0

Parameterized queries
by E.S. Rosenberg 27 Jan '25

27 Jan '25

Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries. So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page? Refs: https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.h… https://mariadb.com/kb/en/com_query/ Thanks! Eli

4 9

Re: 6a4b85ce1cc: MDEV-20281 "[ERROR] Failed to write to mysql.slow_log:" without error reason
by Sergei Golubchik 25 Jan '25

25 Jan '25

Hi, Oleksandr, Ok to push after fixing spagetty (either by fixing a typo or by changing the code pattern as suggested below). On Jan 25, Oleksandr Byelkin wrote: > revision-id: 6a4b85ce1cc (mariadb-10.5.27-202-g6a4b85ce1cc) > parent(s): d261fa5c703 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2025-01-24 17:24:17 +0100 > message: > > MDEV-20281 "[ERROR] Failed to write to mysql.slow_log:" without error reason > > Add "backup" (in case of absence issued by error) reasons for failed logging. > > @@ -998,6 +1047,9 @@ bool Log_to_csv_event_handler:: > close_log_table(thd, &open_tables_backup); > thd->time_zone_used= save_time_zone_used; > DBUG_RETURN(result); > +store_err: // spagetty, but reduce size of the code > + cause= "Can't write data (possible incorrect log table structure)"; > + goto err; > } typo. it's "spaghetti" but may be better to avoid it altogether by rewriting as cause= "..."; if (field[0]->store(...)) goto err; if (field[1]->store(...)) goto err; if (field[2]->store(...)) goto err; ... cause= NULL; Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

1 0

Re: 0b403e03939: MDEV-35661 Assertion `!cache_list ||...' fails in online_alter_close_connection
by Sergei Golubchik 22 Jan '25

22 Jan '25

Hi, Nikita, On Jan 19, Nikita Malyavin wrote: > revision-id: 0b403e03939 (mariadb-11.7.1-16-g0b403e03939) > parent(s): 40e502eca4c > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2025-01-03 00:54:52 +0100 > message: > > MDEV-35661 Assertion `!cache_list ||...' fails in > online_alter_close_connection > > After MDEV-35182 (88adcbf3) fix, online_alter still wasn't fully ready > for `XA PREPARE` to be skipped. > > In fact, according to XA standard, even xa_commit() call may happen > without preceding xa_prepare(), so this stage is completely optional. > > This commit removes any relying on xa_prepare() call. The only thing > that will be done there is an equivalent of "statement-level commit", > which basically just flushes a pending rows event to an online alter > cache. > > This is done by always storing an online alter cache in the XA_data > object whenever XA is active, rather than in THD::ha_data. Why? I thought it'd be simpler to keep the cache in the THD and take it off it in commit or rollback. That is, almost as now, but commit/rollback won't rely on prepare being called before. Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

2 1

Re: 33e36944232: MDEV-30111 InnoDB: Failing assertion: update->n_fields == 0 in row_ins_sec_index_entry_by_modify
by Sergei Golubchik 22 Jan '25

22 Jan '25

Hi, Alexander, On Jan 22, Alexander Barkov wrote: > revision-id: 33e36944232 (mariadb-10.6.20-19-g33e36944232) > parent(s): 25be7da2024 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2024-11-14 10:19:24 +0400 > message: > > MDEV-30111 InnoDB: Failing assertion: update->n_fields == 0 in row_ins_sec_index_entry_by_modify > > Also fixes: > > MDEV-32190 Index corruption with unique key and nopad collation (without DESC or HASH keys) > > The code in strings/strcoll.inl when comparing an empty string > to a string like 0x0001050001 did not take into account > that the leftmost weight in the latter can be zero, while > there are some more weights can follow the zero weight. > > Rewriting the code to treat the shorter string as smaller than > a longer string. > > --- a/strings/strcoll.inl > +++ b/strings/strcoll.inl > @@ -192,10 +192,10 @@ MY_FUNCTION_NAME(strnncoll)(CHARSET_INFO *cs __attribute__((unused)), > >0 >0 Two weights were scanned, check weight difference. > */ > if (!a_wlen) > - return b_wlen ? -b_weight : 0; > + return b_wlen ? -1 : 0; > > if (!b_wlen) > - return b_is_prefix ? 0 : a_weight; > + return b_is_prefix ? 0 : +1; > > if ((res= (a_weight - b_weight))) > return res; Sorry, I don't understand it. The function returns >0, 0, or <0. It shouldn't matter whether it returns -b_weight or -1, a_weight or +1. Except if the weight can be <= 0 itself. But in that case, if ((res= (a_weight - b_weight))) return res; is rather suspicious too. Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

1 0

Has anyone implemented InnoDB redo log-based physical replication?
by Otto Kekäläinen 21 Jan '25

21 Jan '25

Hi, Has anyone attempted or seen any existing implementations "physical" replication between two MariaDB instances, where a secondary read-only instance would follow the primary by directly reading and applying page changes from the primary's redo logs? The secondary instance would start (and restart if necessary) using a full copy of the data from the primary instance, but while running keep itself current by reading redo logs from the primary instance, and apply pages based on the increasing LSN number. I call this "physical" replication to distinguish it from normal logical replication based on e.g. binlogs, and this would obviously only work for InnoDB. Thanks, Otto

2 1

On the question on producing bintars going forward
by Kristian Nielsen 21 Jan '25

21 Jan '25

Hi Vicentiu, I read through your proposal for how to handle MariaDB bintars. I know a lot of the old history on this (like from working in the MySQL build team back in 2005), I also know first-hand of the tough spot between what people think is "easily possible" and the practicalities of packaging for real linux systems. So I hope my input can be useful in some way: 1. To me, providing bintars is not important. Nor is providing any binaries for that matter. MariaDB is open source, the most important thing is it is easy to compile. Others can provide binaries as needed, and in fact distros are already doing so very well. So I personally am fine with not releasing bintars going forward. I know binaries are considered important by others. 2. I agree a clear definition is lacking of exactly what binaries should be/provide. My opinion for bintars is that they are a way to use MariaDB easily similar to compiling the source, but without the need for having installed a compiler toolchain. So bintars should be the result of a mostly default compile of the source, on a stable/oldstable linux distro like Debian or some redhat with minimal optional dependencies installed. Most work should go into making default compile do the right thing, as opposed to endless tweaking in build scripts or Buildbot for how bintars are compiled. 3. In your proposal, I think there is too much focus (out of good intentions, I'm sure) about solving all possible problems. No install method is perfect for all use cases. Bintars provide very simple install or even easy-of-use without installing, but they have other limitations. Let us not try to make bintars "perfect". Rather make them simple to produce, and simple to use in the limited scenarious where they can work well. 4. Let us firmly give up on building static binaries. Static binaries do not work well, and have not done so in many years. Here is a reference for a problem with linking libc statically: https://sourceware.org/glibc/wiki/FAQ#Even_statically_linked_programs_need_… And mixing static and dynamic linking creates some very serious problems, see MDEV-32251 and MDEV-25633. We should just build a default `cmake` binary with dynamic linking against the required library dependencies, do the build on a minimal linux where most optional dependencies are not installed. This will then work well on user's systems that have compatible libraries. On systems that have not, there are other options for running MariaDB. 5. I think we should _definitely_ not maintain an infrastructure for building our own static versions of various system libraries / 3rd-party libraries, see also (4). 6. I agree with concerns about too much effort needed to maintain bintars for many years. Let us be pragmatic, make things simple, and only do what can be done with small amount of effort. Build the bintars on stable linux systems for compatibility, and use default installs as much as possible for simplicity. When those systems become too old to easily continue building bintars, just stop building them. Existing users can easily transition to compiling themselves if they really need, thanks to (2); or use another of the different options available for running MariaDB. And finally Vicentiu, let me commend you for taking responsibility of the task of producing bintars and working so constructively on getting to a working result. It is a task the difficulty of which is easily underappreciated. So these are my opinions and suggestions, hope this helps. - Kristian.

2 1

Re: d47b1ebc06d: MDEV-35326: Memory Leak in init_io_cache_ext upon SHUTDOWN
by Sergei Golubchik 10 Jan '25

10 Jan '25

Hi, Oleksandr, On Jan 09, Oleksandr Byelkin wrote: > revision-id: d47b1ebc06d (mariadb-10.5.27-143-gd47b1ebc06d) > parent(s): 3bbbeae7924 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2025-01-08 14:14:10 +0100 > message: > > MDEV-35326: Memory Leak in init_io_cache_ext upon SHUTDOWN > > The problems were that: > 1) resources was freed "asimetric" normal execution in send_eof, > in case of error in destructor. > 2) destructor was not called in case of SP for result objects. > (so if the last SP execution ended with error resorces was not > freeded on reinit before execution (cleanup() called before next > execution) and destructor also was not called due to lack of > delete call for the object) > > All result method revised and freeing resources made "symetric". > > Destructor of result object called for SP. > > Added skipped invalidation in case of error in insert. > > Removed misleading naming of reset(thd) (could be mixed with > with reset()) > > diff --git a/sql/sp_head.cc b/sql/sp_head.cc > --- a/sql/sp_head.cc > +++ b/sql/sp_head.cc > @@ -3665,6 +3665,18 @@ int sp_lex_keeper::cursor_reset_lex_and_exec_core(THD *thd, uint *nextp, > return res; > } > > +sp_lex_keeper::~sp_lex_keeper() > +{ > + if (m_lex_resp) > + { > + /* Prevent endless recursion. */ > + m_lex->sphead= NULL; > + if (m_lex->result) you don't need this if(), the standard explicitly says that delete of a NULL pointer is valid. > + delete m_lex->result; > + lex_end(m_lex); > + delete m_lex; > + } > +} > > /* > sp_instr class functions > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -3192,10 +3193,22 @@ bool select_send::send_eof() > Handling writing to file > ************************************************************************/ > > +bool select_to_file::free_recources() > +{ > + if (file >= 0) > + { > + (void) end_io_cache(&cache); > + bool error= mysql_file_close(file, MYF(MY_WME)); > + file= -1; > + return error; > + } > + return FALSE; > +} > + > bool select_to_file::send_eof() > { > int error= MY_TEST(end_io_cache(&cache)); > - if (unlikely(mysql_file_close(file, MYF(MY_WME))) || > + if (unlikely(free_recources()) || free_recources() incluses end_io_cache(), you don't need to call it separately above. > unlikely(thd->is_error())) > error= true; > > @@ -5934,7 +5935,15 @@ class select_to_file :public select_result_interceptor { > { path[0]=0; } > ~select_to_file(); > bool send_eof() override; > + void abort_result_set() override; > void cleanup() override; > + /* > + It is separated from cleanup() becaouse: 1. typo 2. if you rename cleanup() to reset_for_next_ps_execution() then you won't need that comment, because, obviously, "freeing resources" is distinct from "resetting for next ps execution" but it's not obvious that "freeing resources" is distinct from "cleanup" > + 1. cleanup can not be called (conventional execution) > + 2. cleanup not only free recources but reset counters (some sends > + counter updates in destructor) > + */ > + bool free_recources(); > }; > > > diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc > --- a/sql/sql_delete.cc > +++ b/sql/sql_delete.cc > @@ -1422,6 +1422,13 @@ void multi_delete::abort_result_set() > { > DBUG_ENTER("multi_delete::abort_result_set"); > > + /**************************************************************************** > + > + NOTE: if you change here be aware that almost the same code is in > + multi_delete::send_eof(). > + > + ***************************************************************************/ Let's talk about it separately. > + > /* the error was handled or nothing deleted and no side effects return */ > if (error_handled || > (!thd->transaction->stmt.modified_non_trans_table && !deleted)) > diff --git a/sql/sql_update.cc b/sql/sql_update.cc > --- a/sql/sql_update.cc > +++ b/sql/sql_update.cc > @@ -2743,6 +2743,13 @@ void multi_update::abort_result_set() > (!thd->transaction->stmt.modified_non_trans_table && !updated))) > return; > > + /**************************************************************************** > + > + NOTE: if you change here be aware that almost the same code is in > + multi_delete::send_eof(). multi_update::send_eof() > + > + ***************************************************************************/ > + > /* Something already updated so we have to invalidate cache */ > if (updated) > query_cache_invalidate3(thd, update_tables, 1); Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

1 0

Re: 331430a6a73: MDEV-33658 2/2 Cannot add a foreign key on a table with a matching long UNIQUE
by Sergei Golubchik 09 Jan '25

09 Jan '25

Hi, Nikita, On Jan 01, Nikita Malyavin wrote: > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index de688b4cb31..2ffceb3932b 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -217,6 +218,16 @@ 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; > + > + // The real algorithm will be BTREE is none was given by user. Technically, this is not correct, the algorithm will be whatever the engine wants. For MEMORY engine it'll be HASH. But only InnoDB can do foreign keys now, and for InnoDB th3 default key algorithm is BTREE. Let's reflect it in the comment, like, for example (also fixed a typo "is" -> "if") // The real algorithm in InnoDB will be BTREE if none was given by user > + a_alg= a_alg == HA_KEY_ALG_UNDEF ? HA_KEY_ALG_BTREE : a_alg; > + b_alg= b_alg == HA_KEY_ALG_UNDEF ? HA_KEY_ALG_BTREE : b_alg; > + > + if (a_alg != b_alg) > + return false; > + > /* Ensure that 'a' is the generated key */ > if (a->generated) > { > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 3f035defbd3..d7aeb5d7cbf 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -3556,6 +3556,303 @@ key_add_part_check_null(const handler *file, KEY *key_info, > } > > > +static > +my_bool key_check_without_overlaps(THD *thd, HA_CREATE_INFO *create_info, > + Alter_info *alter_info, > + Key &key) > +{ > + DBUG_ENTER("key_check_without_overlaps"); > + if (!key.without_overlaps) > + DBUG_RETURN(FALSE); > + // append_system_key_parts is already called, so we should check all the > + // columns except the last two. > + const auto &period_start= create_info->period_info.period.start; > + const auto &period_end= create_info->period_info.period.end; > + List_iterator<Key_part_spec> part_it_forwarded(key.columns); > + List_iterator<Key_part_spec> part_it(key.columns); > + part_it_forwarded++; > + part_it_forwarded++; > + while (part_it_forwarded++) > + { > + Key_part_spec *key_part= part_it++; > + > + if (period_start.streq(key_part->field_name) > + || period_end.streq(key_part->field_name)) > + { > + my_error(ER_KEY_CONTAINS_PERIOD_FIELDS, MYF(0), key.name.str, > + key_part->field_name.str); > + DBUG_RETURN(TRUE); > + } > + } It was simpler to have this check inside append_system_key_parts(), why did you move it? > + > + if (key.key_create_info.algorithm == HA_KEY_ALG_HASH || > + key.key_create_info.algorithm == HA_KEY_ALG_LONG_HASH) > + > + { > + my_error(ER_KEY_CANT_HAVE_WITHOUT_OVERLAPS, MYF(0), key.name.str); > + DBUG_RETURN(TRUE); > + } <...> > + KEY_CREATE_INFO *key_cinfo= &key.key_create_info; > + > + if (is_hash_field_needed) > + { > + if (key_cinfo->algorithm == HA_KEY_ALG_UNDEF) > + key_cinfo->algorithm= HA_KEY_ALG_LONG_HASH; Perhaps you shouldn't set key algorithm in add_hash_field() then? or, rather replace the assignment with an assert? > + > + if (key_cinfo->algorithm != HA_KEY_ALG_HASH && > + key_cinfo->algorithm != HA_KEY_ALG_LONG_HASH) > + { > + my_error(ER_TOO_LONG_KEY, MYF(0), max_key_length); > + DBUG_RETURN(TRUE); > + } > + } > + } > + } > + DBUG_RETURN(FALSE); > +} > + > + > /* > Preparation for table creation Regards, Sergei Chief Architect, MariaDB Server and security(a)mariadb.org

2 2