Tuesday, October 22, 2024

A MySQL 9.1.0 branch with patches

Following-up on the previous MySQL 9.0.0 branch with patches, I have rebased them on 9.1.0: patched-mysql-9.1.0.

The set of the patches is the same as for 9.0.0: it improves AddressSanitizer support and fixes/improves clone plugin to support a second transactional storage engine:

  1. Fix for Bug 115120: Provide memory debugging macro implementations for AddressSanitizer.
  2. Fix for Bug 109922: SEs do not coordinate clone rollbacks on instance startup.
  3. Fix for Bug 109920: SEs cannot add to clone data size estimates.
  4. Fix for Bug 109919: Clone plugin does not support arbitrary length O_DIRECT files.
  5. Fix for Bug 107715: Multiple SE clone not calling clone_end after mixed clone_begin failure/success.
  6. Fix for Bug 106750: Some clone tests needlessly marked as Linux-only.
  7. Fix for Bug 106737: Wrong SE called to ack clone application if >1 SE.

Friday, October 18, 2024

Building and testing MySQL 8.0.40 / 8.4.3 / 9.1.0 on macOS

Congrats to Oracle friends with MySQL 8.0.40 / 8.4.3 / 9.1.0 releases! Let's continue my blog post series on builds and tests on macOS (the previous post for MySQL 8.0.38 / 8.4.1 / 9.0.0 is here. I skipped the emergency hotfixes 8.0.39 / 8.4.2 / 9.0.1).

Build

Mostly good news here. XCode 16 works (8.4.3 & 9.10 need -Wno-unused-const-variable), and so does LLVM 18. The very recent LLVM 19 release will need fixes for the new errors (and not only warnings which could be disabled), but that's expected. Homebrew LLVM still needs the workaround to avoid mixing system and Homebrew LLVM static linker utilities (Bug #113113 Build failure with Homebrew LLVM 14-19 on macOS). All releases build with maximum system libraries, good.

Test

Many changes here. Several existing bugs have been fixed, but there are also new regressions. As usual, I tested Release, Debug, and Debug+ASan+UBSan configurations.

New bugs

Little or no changes

Fixed and no longer reproducing bugs:

Totals

To sum up the existing build & test bugs, we have 10 new, 11 unchanged, and 8 fixed bugs. A lot of churn, and still plus two regressions total.

Newer tooling

I have also logged two bugs which seem to be new classes of errors from newer tooling instead of being MySQL regressions, i.e. they are very likely present in older versions too. These I don't count in the above, but will start doing so from the next releases:

Valgrind

I have been complaining that running Valgrind is excessively slow, and that there was an error present on every test (Bug #115229 pwrite(buf) points to uninitialised byte(s) in os_fusionio_get_sector_size()). The good news is that this bug has been fixed, making the Valgrind runs actually usable now. I have started one for 9.1.0, will see how far along it goes.

One-off issues that I couldn't handle better

Stuff I couldn't repeat reliably or at all, just dumping it here in case I have to reference it next time.

  • rpl_nogtid.rpl_semi_sync_non_group_commit_deadlock 8.0.40 Debug+San reports odr-violation on ReplSemiSyncBase::kPacketMagicNum, does not reproduce running individually
  • binlog.binlog_mysqlbinlog_raw failed once on 8.0.40 Debug+San (L77 copy_file error)
  • rpl_gtid.rpl_gtids_table_disable_binlog_on_slave failed once on 8.4.3 Debug+San build
  • main.index_merge_innodb_hypergraph failed once on 8.4.3 Debug+San build
  • innodb.trigger_function_lock_compare timed out once there
  • auth_sec.grant_as_ddl failed once 8.4.3 Release build
  • rpl_gtid.rpl_gtids_table_disable_binlog_on_slave failed once on 9.1.0 Debug+San build
  • merge_innodb_tests-t failed once on 9.1.0 Debug+San
  • main.lock_backup failed once 9.1.0 with MTR thread id masking clobbering error number
  • innodb.trigger_function_lock_compare failed once on 9.1.0 Debug

Conclusion

I am happy to see that some of the bugs are getting fixed, but the trend is still going the wrong way, and I miss the fully-clean mid-8.0 releases.

Thursday, October 10, 2024

Emacs Projectile Tweaks for MySQL Development

I am using Emacs with Projectile (besides other packages) for MySQL development. Here's a couple of little tweaks to save a few keystrokes here and there:

  • Visit (which in Emacs parlance means open) the MTR test file, by its MTR name instead of file path:
;; This is a not a MySQL-specific implementation of `projectile-find-test-file'.
;; That function is meant for jumping from a source file to its test file.
;; TODO(laurynas): this is not an exhaustive implementation for all locations
;; test files can reside. Add them as the need arises.
(defun my-visit-mtr-test ()
  "Input MySQL MTR test name and visit it."
  (interactive)
  (let* ((input (read-string "MySQL MTR test name: "))
         ;; TODO(laurynas): error checking
         (parts (split-string input "\\."))
         (suite-name (car parts))
         (test-in-suite (concat "t/" (cadr parts) ".test"))
         (test-root (concat (projectile-project-root) "mysql-test/"))
         (file-path (if (string= suite-name "main")
                        (concat test-root test-in-suite)
                      (concat test-root "suite/" suite-name "/"
                              test-in-suite))))
    (find-file file-path)))

(define-key projectile-command-map "M" #'my-visit-mtr-test)

The last line binds this command to M in the Projectile command map, so if your Projectile keybinding prefix is C-c p (C is Ctrl in case non-Emacs users are still reading), you can do C-c p M to invoke it.

  • Register .test and .result files as "other file types" for each other. This way projectile-find-other-file (at e.g. C-c p a) can be used for quick jumping between tests and their results:
(add-to-list 'projectile-other-file-alist '("test" "result"))
(add-to-list 'projectile-other-file-alist '("result" "test"))

If I think of more utilities, I'll try not to forget to update this post.

Friday, September 06, 2024

Running Perl-using MTR tests for fb-mysql under macOS

The headline is slightly misleading. Oracle MySQL has several Perl-using MTR tests, the whole MTR framework is Perl-based, and those tests run fine out of the box (with an occasional bug). However, fb-mysql has additional tests that call the Random Query Generator under the hood, and these tests don't work out of the box under macOS.

Here's what I had to do to get them working:

# mysql-client default version is 9.0 now, and you'll get the wrong auth plugin error
brew install perl mysql-client@8.0

# TODO: does not survive upgrades, do what the Perl Homebrew formula advises with
# local::lib

/opt/homebrew/opt/perl/bin/cpan DBI
/opt/homebrew/opt/perl/bin/cpan JSON
/opt/homebrew/opt/perl/bin/cpan Expect

# Adjust path only for compilation below. For now I don't need it globally
export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"

# Run the following command and expect it to fail with the error as in
# https://stackoverflow.com/questions/53277884/installation-of-dbdmysql-fails-with-symbol-not-found-on-osx-mojave
/opt/homebrew/opt/perl/bin/cpan DBD::mysql

# Now go to ~/.cpan/build/DBD-mysql-<latest> and do:
perl Makefile.PL \
     --libs="-L/opt/homebrew/opt/mysql-client/lib -L/opt/homebrew/lib -lmysqlclient -lz -lzstd -lssl -lcrypto -lresolv"
sudo make install

And now:

[  5%] rocksdb.rqg_runtime 'rocksdb_ddse'       w4  [ pass ]  41533

Success! Stay tuned for getting Python tests running too.

Friday, August 23, 2024

A README for my MySQL development tooling

Over the years I have written and borrowed various bits of scripts and configuration files to make MySQL development easier by configuring and automating things. Having written patch2testlist a few months ago, I figured that now I have at least one thing that should work not only on my machine. Thus I put together a README for MySQL-related stuff in my dotfiles, also included below.

My MySQL Development Tools

Tool configuration

  • clangd: I am so glad C++ finally got universal IDE tooling and clangd is very useful for MySQL development.
  • Emacs: my primary Emacs config is elsewhere, while this is a directory-local config that takes effect in the source trees. It prevents creating spurious .ispell.dict files, sets the C++ style to Google, and makes Magit (magit-todos actually) exclude 3rd party TODO comments.
  • cpplint: cpplint is still marginally useful with MySQL sources.

I symlink these into the parent directory of all the source trees.

Could be useful to you

  • patch2testlist: for a given patch, print a list of MTR tests to run to cover the test changes in the patch. Introduced in its own blog post. Unlike the scripts below, patch2testlist is standalone and works without the zsh utility functions.

Useful to me

  • mysql-work.sh: a shell initialization script that sets up environment variables and helper functions to configure, build, & test various MySQL versions on macOS and Linux. For example, $MY8037D will expand to CMake options for a debug build of MySQL 8.0.37. As for the functions, among others, mysql_build will configure and build a given tree, and rmtr will incrementally build and run MTR tests with libeatmydata. This script adds various warning disable flags so that older unpatched trees can be built with newer compilers within reason. It also absorbs bug workarounds and complications related to build environments, for example, different MySQL versions need both OpenSSL 1.1 and 3 installed, which then in turn results in build issues if both versions are present simultaneously. This script is not usable without its zsh utility functions.
  • fetchworksrc: fetch all remotes for Git repos in WORK_REPOS_TO_PULL environment variable; pull all the worktrees in WORK_TREES_TO_UPDATE one and do incremental builds. This script runs at midnight on my work machine. There are some quality of life embellishments such as skipping the the build if the machine is a laptop on battery and managing Homebrew OpenSSL version incompatibilities in builds. Again, this script is not usable without its zsh utility functions.

zsh function library

This directory. One file per function as zsh requires. Currently they fall into two sets of responsibilities: querying the source tree for version information and managing OpenSSL 3 build workarounds.

No longer maintained (patches welcome)

  • gca, the workhorse script from my time at Percona. Used when the same patch has to be developed for more than one major MySQL series (e.g. the patch is needed for 8.0, 8.4, & 9.0, using an example that is current at the time of the writing). There is a constraint that the lower series version of the patch has to be git merged to the next higher series, but the latest commits of these series are not fully merged to each other due to how Oracle pushes their source trees. So, a lower series feature branch has to be created not from the tip but from some older commit which had been fully merged. This script manages finding such commits and creating the worktrees. Luckily I only deal with single series these days.
  • reupmerge, used in that same multiple-series workflow. It handles the scenario where, i.e. a code review change needs to be applied to a lower branch commit in a feature branch. Changing that commit requires re-merging it to the higher series. For the case where the higher series commit needs not to change, this script automates saving its diff, and re-merging the lower series with that exact saved diff.
  • mysql-work.el: mostly outdated Emacs configuration bits, mostly copied from colleagues at Percona. They set up MySQL (and InnoDB) indentation styles that were used up to 5.7, and provide a bit of syntax support for MTR .test files.

Tuesday, July 09, 2024

A MySQL 9.0.0 branch with patches

Recently I made public a branch of MySQL 8.4.0 with patches. Now that newer upstream versions have been released, I have rebased the branch on 9.0.0: patched-mysql-9.0.0.

I did not add any new patches, and dropped the LLVM compilation fix patch. Thus the current patch set improves AddressSanitizer support and includes several fixes/improvements for the clone plugin to support a second transactional storage engine:

  1. Fix for Bug 115120: Provide memory debugging macro implementations for AddressSanitizer.
  2. Fix for Bug 109922: SEs do not coordinate clone rollbacks on instance startup.
  3. Fix for Bug 109920: SEs cannot add to clone data size estimates.
  4. Fix for Bug 109919: Clone plugin does not support arbitrary length O_DIRECT files.
  5. Fix for Bug 107715: Multiple SE clone not calling clone_end after mixed clone_begin failure/success.
  6. Fix for Bug 106750: Some clone tests needlessly marked as Linux-only.
  7. Fix for Bug 106737: Wrong SE called to ack clone application if >1 SE.

Thursday, July 04, 2024

Building and testing MySQL 8.0.38 / 8.4.1 / 9.0.0 on macOS

Oracle has just released MySQL 8.0.38/8.4.1/9.0.0, so let's see how the Valgrind testing of the previous set of releases is going:

[ 65%] innodb.bug33788578_rec_IV_set            w2  [ fail ]  Found warnings/errors in error log file!

It only managed two thirds of a run (a --big-test one) in a few weeks, which means that I either need a faster machine, or that Valgrind testing does not take even remotely reasonable amount of time. Despite the run being incomplete, it resulted in couple of bug reports because every single test gives an error: #115229: pwrite(buf) points to uninitialised byte(s) in os_fusionio_get_sector_size(). I also noticed that one test always times out: #114998: Test rpl_gtid.rpl_gtid_parallel times out under Valgrind. I need to rethink Valgrind testing going forward, maybe by trying dropping --big-test first.

Back to the new releases. A nice surprise is that the they build with XCode 15.3 even though the associated bug (#113123: Compilation fails with LLVM 17 and XCode 15.3) is still open. However, an LLVM 18 build fails in the bundled zlib, and this time I refrained from reporting a bug. GCC 11–14 also fail, but they are officially unsupported for macOS, so I won't be reporting that, unless I encounter issues on Linux.

Fixed and no longer reproducing bugs:

New bugs:

Little to no changes

Finally, I did not retest #113113: Build failure with Homebrew LLVM 14-17 on macOS, where I keep using the workaround of setting CMAKE_AR to Homebrew ar.

To sum up, 5 bugs fixed or no longer reproducing, 7 new, 11 with no changes, 1 not tested, and 2 new Valgrind ones. I don't like this trend and I miss the middle-8.0 releases that were fully clean under macOS.

Friday, June 14, 2024

Implementing a Table-Returning MySQL Function

Edit: links to Percona SEQUENCE_TABLE implementation added to the end

MySQL has many native functions and it's relatively straightforward to write new ones. While the official internals manual has a chapter on adding native functions, some aspects aren't immediately obvious, such as the possible return types for functions. The linked docs suggest that functions can return a double, an integer, or a string, but doesn't mention that they can also return JSON documents (i.e. JSON_ARRAY) and relational tables (i.e. JSON_TABLE). I have filed a bug for this omission, meanwhile this post will be my attempt at documenting the implementation of table-returning functions.

We will implement a table-returning function called LAST_INSERT_IDS, We will focus on the server infrastructure to support that, not on the function's purpose or the actual data it returns. Let's do the simplest possible thing: return a table with one column and one row.

Exploring the Codebase

Grepping the source tree for JSON_TABLE points to Table_function class declared in sql/table_function.h. Source code history then points to this commit, which in turn mentions Worklog #8867 (Add JSON table functions). Luckily for us it's a relatively old worklog, thus available publicly! From the worklog we can learn that any result tables will start as in-memory temporary tables which will spill to disk as needed. Good. The worklog also touches upon a lot of stuff elsewhere in the server so that the query optimizer will know how to deal with table-returning functions. We won't need to deal with that while implementing a new function, but it may provide debugging starters should anything go wrong.

Function class

To start writing code, we have to derive a new concrete class from Table_function and let the compiler tell us what methods must be implemented:

class Table_function_last_insert_ids final : public Table_function {
 public:
  bool init() override;
  bool fill_result_table() override;
  const char *func_name() const override;
  bool print(const THD *thd, String *str,
             enum_query_type query_type) const override;
  bool walk(Item_processor processor, enum_walk walk, uchar *arg) override;

 private:
  List<Create_field> *get_field_list() override;
  bool do_init_args() override;
};

But let's not implement them just yet (stub them out with assert(0) bodies to learn what gets called when), and let's look into instantiating the class object first. The regular functions get registered with MySQL in func_array variable in sql/item_create.cc, and the surrounding code knows how to create their objects. But JSON_TABLE is not there! Grepping shows that it is wired directly to the parser, probably because its arguments are non-trivial. Our function is much simpler in that regard, can we get away with putting it there instead of patching parser? It turns out that no, we cannot, because the simpler functions derive from Item_func class, which derives from Item, which is what this infrastructure expects. And our table-returning function derives from Table_function, which has no further ancestors.

Patching the parser

OK, off to the parser we go. To find a starting point, let's check what JSON_TABLE does. Grepping for Table_function_json in the parser (the worklog is not too specific in this area), we get a match for PT_table_factor_function class in sql/parse_tree_nodes.cc. Then we grep for that class in the parser grammar and we get a hint why the table-returning functions are harder to implement than other kinds of functions:

table_function:
        JSON_TABLE_SYM '(' expr ',' text_literal columns_clause ')'
        opt_table_alias
        {
          // Alias isn't optional, follow derived's behavior
          if ($8 == NULL_CSTR)
          {
            my_message(ER_TF_MUST_HAVE_ALIAS,
                       ER_THD(YYTHD, ER_TF_MUST_HAVE_ALIAS), MYF(0));
            MYSQL_YYABORT;
          }

          $$= NEW_PTN PT_table_factor_function($3, $5, $6, to_lex_string($8));
        }
      ;

We can see that the parser knows how to work with exactly one table-returning function that is named JSON_TABLE, thus we have to patch the parser. Let's keep everything under the non-terminal symbol table_function:

table_function:
          json_table_function
        | last_insert_ids_function
        ;

last_insert_ids_function:
          LAST_INSERT_IDS_SYM '(' ')'
          opt_table_alias
          {
            // Alias isn't optional, follow derived's behavior
            if ($4 == NULL_CSTR)
            {
                my_message(ER_TF_MUST_HAVE_ALIAS,
                           ER_THD(YYTHD, ER_TF_MUST_HAVE_ALIAS), MYF(0));
                MYSQL_YYABORT;
            }

            $$= NEW_PTN PT_last_insert_ids_function(to_lex_string($4));
          }
        ;

json_table_function:
        // As before

Unfortunately the opt_table_alias block must be duplicated. But, no increase in the parser shift/reduce conflicts! There is more not particularly interesting stuff to do in the lexer and the parser:

  • Add the function name to the lexer, {SYM("LAST_INSERT_IDS", LAST_INSERT_IDS_SYM)}, to sql/lex.h.
  • Declare this lexer symbol as a token for the parser. I did not make it a keyword because JSON_TABLE wasn't one neither: %token LAST_INSERT_IDS_SYM 10024.
  • Finally, declare last_insert_ids_function symbol to be of table_reference type, same as table_function was before.

Parse Tree Node class

That's enough for the lexer and the parser, next we need to implement PT_last_insert_ids_function class:

class PT_last_insert_ids_function : public PT_table_reference {
 public:
  PT_last_insert_ids_function(const LEX_STRING &table_alias)
      : m_table_alias{table_alias} {}

 private:
  const LEX_STRING m_table_alias;
};

Without a PT_last_insert_ids_function::contextualize method, which would actually create the Table_function_last_insert_ids object, the above implementation fails rather non-obviously:

mysqltest: At line 24: Query 'SELECT * FROM LAST_INSERT_IDS() as ids' failed.
ERROR 1096 (HY000): No tables used

Let's add that method by copying, pasting, and deleting the non-applicable bits of PT_table_factor_function::contextualize. The job of this method will be to create the function object and to assign a name and a table for the query optimizer for the result.

bool PT_last_insert_ids_function::contextualize(Parse_context *pc) {
  if (super::contextualize(pc)) return true;
  auto *const fn = new (pc->mem_root) Table_function_last_insert_ids{};
  if (unlikely(fn == nullptr)) return true;

  LEX_CSTRING alias;
  alias.length = strlen(fn->func_name());
  alias.str = sql_strmake(fn->func_name(), alias.length);
  if (unlikely(alias.str == nullptr)) return true;

  auto *const ti = new (pc->mem_root) Table_ident(alias, fn);
  if (ti == nullptr) return true;

  m_table_ref = pc->select->add_table_to_list(pc->thd, ti, m_table_alias.str, 0,
                                              TL_READ, MDL_SHARED_READ);
  if (m_table_ref == nullptr || pc->select->add_joined_table(m_table_ref))
    return true;

  return false;
}

Filling out the function class implementation

This implementation forces us to define the first method in Table_function_last_insert_ids, a very simple one:

const char *func_name() const override { return "last_insert_ids"; }

Let's run it!

SELECT * FROM LAST_INSERT_IDS() as ids;
...
Assertion failed: (0), function init, file table_function.h, line 440.

…which points to the stub Table_function_last_insert_ids::init(). Let's say we have no meaningful initialization at this point, so remove the assert from the stub:

Assertion failed: (false), function get_field_list, file table_function.h, line 464.

This is get_field_list method, and we have to implement it to describe the schema of the table we will be returning:

List<Create_field> *Table_function_last_insert_ids::get_field_list() {
  assert(fields.is_empty());

  auto *const field = new Create_field;
  field->init_for_tmp_table(MYSQL_TYPE_LONGLONG, MAX_BIGINT_WIDTH,
                            DECIMAL_NOT_SPECIFIED, /* is_nullable */ false,
                            /* is_unsigned */ true, 0, "insert_id");
  fields.push_back(field);

  return &fields;
}

This implementation assumes it is not a simple getter and thus is called exactly once. If the assumption will shown to be incorrect by the assert, I'll move the code to init and will reduce this one to a simple getter. Also, something will have to free the memory we are allocating for the field, that's for later.

Run again…

Assertion failed: (false), function do_init_args, file table_function.h, line 467.

For JSON_TABLE, this method is documented to "check whether given default values can be saved to fields." Let's assume that we have no meaningful actions here, remove the assert, return success, run again:

Assertion failed: (0), function fill_result_table, file table_function.h, line 442.

Finally we get to the second serious method that should provide the payload for the result table:

bool Table_function_last_insert_ids::fill_result_table() {
  assert(!fields.is_empty());
  assert(!table->materialized);
  assert(table->s->fields == 1);

  empty_table();

  auto *const field = get_field(0);
  assert(field->field_index() == 0);
  field->store(current_thd->first_successful_insert_id_in_prev_stmt);
  field->set_notnull();
  write_row();

  return false;
}

And…

SELECT * FROM LAST_INSERT_IDS() as ids;
insert_id
0

Success!

Implementing the remaining stubs

We still have two stub methods with asserts in them: print and walk. Let's try figuring out under what conditions they get called, again, by looking at JSON_TABLE implementation.

Let's start with print. It gets called by sql_lex.cc Table_ref::print. There are references to optimizer trace and something else around that. Let's see if that "something else" refers to the good old EXPLAIN:

EXPLAIN SELECT * FROM LAST_INSERT_IDS() as ids;
Assertion failed: (false), function print, file table_function.h, line 450.
7   mysqld                              0x000000010385ffb8 Table_function_last_insert_ids::walk(bool (Item::*)(unsigned char*), enum_walk, unsigned char*) + 0

Interesting! We tried to find what calls print, but we found what calls walk instead. Since our function is atomic with regard to the query optimizer and has no internal structure, there is nothing to walk, let's remove the assert:

EXPLAIN SELECT * FROM LAST_INSERT_IDS() as ids;
Assertion failed: (false), function print, file table_function.h, line 450.

Great, so the same EXPLAIN statement exercises both walk and print. Let's add a simple implementation for the latter:

bool print(const THD *, String *str, enum_query_type) const override {
  return !str->append(STRING_WITH_LEN("last_insert_ids()"));
}

And now the EXPLAIN!

EXPLAIN SELECT * FROM LAST_INSERT_IDS() as ids;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ids	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Table function: last_insert_ids; Using temporary
Warnings:
Note	1003	/* select#1 */ select `ids`.`insert_id` AS `insert_id` from last_insert_ids() `ids`

Memory management

One last thing is that Field object which was allocated in Table_function_last_insert_ids::get_field_list and never freed. Let's see if the standard tooling catches this error. AddressSanitizer under Linux includes LeakSanitizer, let's try it:

==31384==ERROR: LeakSanitizer: detected memory leaks

Direct leak of 1440 byte(s) in 6 object(s) allocated from:
    #0 0xab9c0d1a99ac in operator new(unsigned long) (/home/laurynas/vilniusdb/last_insert_ids/_build-debug-llvm-14-san/runtime_output_directory/mysqld+0xb0599ac) (BuildId: 611e155cd8dc43455bd5d5e0ddbdfe53ae205fa4)
    #1 0xab9c0e6bb8bc in Table_function_last_insert_ids::get_field_list() /home/laurynas/vilniusdb/last_insert_ids/sql/table_function.cc:778:23
    #2 0xab9c0e6b5514 in Table_function::create_result_table(THD*, unsigned long long, char const*) /home/laurynas/vilniusdb/last_insert_ids/sql/table_function.cc:64:46
    #3 0xab9c0e0024fc in Table_ref::setup_table_function(THD*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_derived.cc:933:23
    #4 0xab9c0e388bfc in Query_block::resolve_placeholder_tables(THD*, bool) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_resolver.cc:1310:15
    #5 0xab9c0e3854a4 in Query_block::prepare(THD*, mem_root_deque<Item*>*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_resolver.cc:247:7
    #6 0xab9c0e3e3da4 in Sql_cmd_select::prepare_inner(THD*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_select.cc:484:17
    #7 0xab9c0e3e2d74 in Sql_cmd_dml::prepare(THD*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_select.cc:399:11
    #8 0xab9c0e3e4360 in Sql_cmd_dml::execute(THD*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_select.cc:539:9
    #9 0xab9c0e263934 in mysql_execute_command(THD*, bool, unsigned long long*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_parse.cc:5489:29
    #10 0xab9c0e25c8c8 in dispatch_sql_command(THD*, Parser_state*, unsigned long long*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_parse.cc:6276:21
    #11 0xab9c0e253894 in dispatch_command(THD*, COM_DATA const*, enum_server_command) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_parse.cc:2565:7
    #12 0xab9c0e259124 in do_command(THD*) /home/laurynas/vilniusdb/last_insert_ids/sql/sql_parse.cc:1745:18
    #13 0xab9c0e7f6f10 in handle_connection(void*) /home/laurynas/vilniusdb/last_insert_ids/sql/conn_handler/connection_handler_per_thread.cc:307:13
    #14 0xab9c12076ee8 in pfs_spawn_thread(void*) /home/laurynas/vilniusdb/last_insert_ids/storage/perfschema/pfs.cc:3022:3
    #15 0xfbb124cc5978 in start_thread nptl/./nptl/pthread_create.c:447:8
    #16 0xfbb124d2ba48 in thread_start misc/../sysdeps/unix/sysv/linux/aarch64/clone3.S:76

Yep, a leak. Let's try freeing that memory in the destructor:

Table_function_last_insert_ids::~Table_function_last_insert_ids() {
  assert(fields.elements == 1);
  delete fields.head();
}

Run again, no leak or any other Sanitizer error.

Summary checklist

So, to summarize, the checklist for adding a new table-returning function to MySQL:

  • Add the function name as a lexer symbol in sql/lex.h
  • Add the function name symbol as a token in the parser (sql/sql_yacc.yy)
  • In the parser, rename table_function to json_table_function and add new table_function with json_table_function and new function alternatives. All these symbols must be typed as table_reference
  • Create a new parse tree node class, for example:
class PT_last_insert_ids_function : public PT_table_reference {
  using super = PT_table_reference;

 public:
  PT_last_insert_ids_function(const LEX_STRING &table_alias)
      : m_table_alias{table_alias} {}

  bool contextualize(Parse_context *pc) override;

 private:
  const LEX_STRING m_table_alias;
};

bool PT_last_insert_ids_function::contextualize(Parse_context *pc) {
  if (super::contextualize(pc)) return true;
  auto *const fn = new (pc->mem_root) Table_function_last_insert_ids{};
  if (unlikely(fn == nullptr)) return true;

  LEX_CSTRING alias;
  alias.length = strlen(fn->func_name());
  alias.str = sql_strmake(fn->func_name(), alias.length);
  if (unlikely(alias.str == nullptr)) return true;

  auto *const ti = new (pc->mem_root) Table_ident(alias, fn);
  if (ti == nullptr) return true;

  m_table_ref = pc->select->add_table_to_list(pc->thd, ti, m_table_alias.str, 0,
                                              TL_READ, MDL_SHARED_READ);
  if (m_table_ref == nullptr || pc->select->add_joined_table(m_table_ref))
    return true;

  return false;
}
  • Create a new table function class:
class Table_function_last_insert_ids final : public Table_function {
 public:
  ~Table_function_last_insert_ids() override;

  bool init() override { return false; }

  bool fill_result_table() override;

  const char *func_name() const override { return "last_insert_ids"; }

  bool print(const THD *, String *str, enum_query_type) const override {
    return !str->append(STRING_WITH_LEN("last_insert_ids()"));
  }

  bool walk(Item_processor, enum_walk, uchar *) override {
    return false;
  }

 private:
  List<Create_field> *get_field_list() override;

  bool do_init_args() override { return false; }

  List<Create_field> fields;
};

Table_function_last_insert_ids::~Table_function_last_insert_ids() {
  assert(fields.elements == 1);
  delete fields.head();
}

List<Create_field> *Table_function_last_insert_ids::get_field_list() {
  assert(fields.is_empty());

  auto *const field = new Create_field;
  field->init_for_tmp_table(MYSQL_TYPE_LONGLONG, MAX_BIGINT_WIDTH,
                            DECIMAL_NOT_SPECIFIED, /* is_nullable */ false,
                            /* is_unsigned */ true, 0, "insert_id");
  fields.push_back(field);

  return &fields;
}

bool Table_function_last_insert_ids::fill_result_table() {
  assert(!fields.is_empty());
  assert(!table->materialized);
  assert(table->s->fields == 1);

  empty_table();

  auto *const field = get_field(0);
  assert(field->field_index() == 0);
  field->store(current_thd->first_successful_insert_id_in_prev_stmt);
  field->set_notnull();
  write_row();

  return false;
}

Percona SEQUENCE_TABLE

After I have posted this, Percona's Yura Soroking pointed out on LinkedIn their implementation of SEQUENCE_TABLE function: blog post, the initial implementation source code. This implementation follows similar framework as the toy implementation above.

Tuesday, May 28, 2024

A MySQL 8.4.0 branch with patches

While writing the previous post, I noticed that I didn't have a central location for the patches that I submitted to Oracle. Some were in local branches, some were .patch files lying around. So now I pushed a tree that has all those patches applied in a single place: patched-mysql-8.4.0, and I even added a README. This tree hopefully will make it easier to rebase on future Oracle releases. I had maintained similar branches before while pushing some of the Percona patches to Oracle around early 8.0 times.

As for the patches themselves, the majority of them add various missing features for the clone plugin to be able to support more than one transactional storage engine. There is also a compilation fix and a slightly improved AddressSanitizer support patch.

All MySQL 8.4.0 users are advised to migrate ASAP! /s

Wednesday, May 08, 2024

Building and testing MySQL 8.0.37 & 8.4.0 on macOS

The first MySQL LTS release, 8.4.0, is out, together with 8.0.37! Which means it's time for me to build and test them in my main work environment, continuing the series (8.3.0/8.0.36, 8.2.0/8.0.35).

The first surprise is that both these releases do not build with the current XCode (15.3 at the time of writing), because the LLVM 17 compilation failure I previously reported (#113123: Compilation fails with LLVM 17 and XCode 15.3) is not fixed yet and started affected Apple toolchain too. I am using Homebrew-packaged LLVM 16 for all the builds of these versions. I didn't do any LLVM 18 nor GCC testing neither.

For the good news, I no longer get a build-breaking warning in NDB, even though its bug (#113662) is still open. Finally, for the build-related no-news, nothing has changed with regard to system vs bundled libraries: both versions continue to build with all the system libraries, except for zlib.

On to the testsuite. It seems that 8.4.0 release build testsuite takes some 20% less time than the 8.0.37 one. It must be the payoff from the deprecated feature removal removing their tests too, although I did not try to confirm that.

Fixed bugs and no longer reproducing test failures on open bugs:

New test failures:

I wanted to call the following the list of bugs with no changes, but I can't. While some of the bugs indeed have no changes, there is also a small but definite creep there: a test that only failed in one configuration before now fails in two. A second sibling test started failing with same symptoms. That's definitely not "no changes", but not new independent bugs neither. It's worrying.

I did not re-test two bugs:

Thus, the numbers are: 4 new bugs reported, 5 fixed or no longer reproducing, 10 bugs with no changes or with a bit of creep, 2 non-tested.

But wait, there's more! I have also sent some patches to Oracle by OCA! I am only counting the recent patches I developed at VilniusDB and not the ones I submitted at Percona. So how many have they applied since 8.3.0?

So, zero. Of course two out of four patches are for clone with a 2nd transactional storage engine, thus the excuse of Oracle MySQL not having such engine sounds plausible.

That's about it. I am still running Valgrind tests, and they should complete any week now, unless I have to kill them to take their machine for something else. I will not be updating this post or writing a new one unless their results are very, very unexpected. Otherwise here's to the new MySQL releases and hopefully I will continue the series in three months.

Tuesday, January 30, 2024

Introducing patch2testlist for MySQL development

I wrote a small shell utility patch2testlist that might be useful for fellow MySQL developers. It reads a diff and outputs the list of tests touched in this diff to run in a format suitable for mysql-test-run.pl consumption. Furthermore, when provided with a path to the source tree of the diff, it handles included files.

There are two ways to invoke it.

  1. Quick-and-dirty mode that does not handle included files:

    $ ./mtr `git diff | patch2testlist` ...
    
  2. Thorough mode that considers included files, if the source tree path is given:

    $ ./mtr `git diff | patch2testlist ../..` ...
    

What does it do? Let's consider an example:

$ git diff | diffstat
 mysql-test/extra/rpl_tests/rpl_replica_start_after_clone.inc                                    |    2 
 mysql-test/include/keyring_tests/binlog/rpl_encryption_master_key_rotation_at_startup.inc       |    5 -
 mysql-test/include/keyring_tests/mats/rpl_encryption.inc                                        |    2 
 mysql-test/include/keyring_tests/mats/rpl_encryption_master_key_generation_recovery.inc         |    2 
 mysql-test/suite/auth_sec/include/acl_tables_row_locking_test.inc                               |    4 
 mysql-test/suite/binlog/t/binlog_restart_server_with_exhausted_index_value.test                 |    1 
 mysql-test/suite/component_keyring_file/inc/rpl_setup_component.inc                             |    1 
 mysql-test/suite/innodb/t/log_8_0_11_case1.test                                                 |    1 
 mysql-test/suite/rocksdb/r/sys_tables.result                                                    |    2 
 mysql-test/suite/rocksdb/r/sys_tables_acl_tables_row_locking.result                             |  384 +++++++++++++++++++---------------------------------------------------------------
 mysql-test/suite/rocksdb/r/sys_tables_is_statistics_mysql.result                                |    4 
 mysql-test/suite/rocksdb/r/sys_tables_mysqlcheck.result                                         |    8 -
 mysql-test/suite/rpl/t/rpl_cloned_slave_relay_log_info.test                                     |    4 
 mysql-test/suite/rpl/t/rpl_encryption.test                                                      |    3 
 mysql-test/suite/rpl/t/rpl_encryption_master_key_generation_recovery.test                       |    3 
 mysql-test/suite/rpl/t/rpl_encryption_master_key_rotation_at_startup.test                       |    5 -
 mysql-test/suite/rpl/t/rpl_gtid_innodb_sys_header.test                                          |    2 
 mysql-test/suite/rpl_gtid/t/rpl_gtid_xa_commit_failure_before_gtid_externalization.test         |    1 
 mysql-test/suite/rpl_gtid/t/rpl_gtid_xa_commit_one_phase_failure_before_prepare_in_engines.test |    1 
 mysql-test/suite/rpl_gtid/t/rpl_gtid_xa_prepare_failure_before_prepare_in_engines.test          |    1 
 mysql-test/suite/rpl_gtid/t/rpl_gtid_xa_rollback_failure_before_gtid_externalization.test       |    1 
 mysql-test/suite/rpl_nogtid/t/rpl_assign_gtids_to_anonymous_transactions_clone.test             |    4 
 mysql-test/suite/rpl_nogtid/t/rpl_gtid_mode.test                                                |    5 -
 mysql-test/suite/rpl_nogtid/t/rpl_nogtid_encryption_read.test                                   |    3 
 mysql-test/suite/test_services/t/test_host_application_signal_plugin.test                       |    3 
 mysql-test/t/basedir.test                                                                       |    5 -
 mysql-test/t/mysqld_daemon.test                                                                 |    3 
 mysql-test/t/mysqld_safe.test                                                                   |   27 ++---
 mysql-test/t/restart_server.test                                                                |    3 
 mysql-test/t/restart_server_no_acl.test                                                         |    3 
...
$ git diff | patch2testlist
binlog.binlog_restart_server_with_exhausted_index_value innodb.log_8_0_11_case1 main.basedir
main.mysqld_daemon main.mysqld_safe main.restart_server main.restart_server_no_acl
rocksdb.sys_tables rocksdb.sys_tables_acl_tables_row_locking
rocksdb.sys_tables_is_statistics_mysql rocksdb.sys_tables_mysqlcheck
rpl.rpl_cloned_slave_relay_log_info rpl.rpl_encryption
rpl.rpl_encryption_master_key_generation_recovery
rpl.rpl_encryption_master_key_rotation_at_startup rpl.rpl_gtid_innodb_sys_header
rpl_gtid.rpl_gtid_xa_commit_failure_before_gtid_externalization
rpl_gtid.rpl_gtid_xa_commit_one_phase_failure_before_prepare_in_engines
rpl_gtid.rpl_gtid_xa_prepare_failure_before_prepare_in_engines
rpl_gtid.rpl_gtid_xa_rollback_failure_before_gtid_externalization
rpl_nogtid.rpl_assign_gtids_to_anonymous_transactions_clone rpl_nogtid.rpl_gtid_mode
rpl_nogtid.rpl_nogtid_encryption_read test_services.test_host_application_signal_plugin

The quick-and-dirty mode above does not require a hundred line script, a ten-line one will do. But notice that several of the changed files in the diffstat output are test include files (i.e. rpl_replica_start_after_clone.inc). Ideally we'd want to run any tests that include (directly and indirectly) such files, and the ten-line script does not handle this case.

That's what the other ninety lines of the script do. If the optional source tree path argument is given, then it greps for any included files under mysql-test/, then greps for newly-found files and so on until it finds no more:

$ git diff | patch2testlist ../..
auth_sec.acl_tables_row_locking binlog.binlog_restart_server_with_exhausted_index_value
component_keyring_file.rpl_binlog_cache_encryption
component_keyring_file.rpl_binlog_cache_temp_file_encryption
component_keyring_file.rpl_default_table_encryption component_keyring_file.rpl_encryption
component_keyring_file.rpl_encryption_master_key_generation_recovery
component_keyring_file.rpl_encryption_master_key_rotation_at_startup innodb.log_8_0_11_case1
main.basedir main.mysqld_daemon main.mysqld_safe main.restart_server main.restart_server_no_acl
rocksdb.sys_tables rocksdb.sys_tables_acl_tables_row_locking rocksdb.sys_tables_is_statistics_mysql
rocksdb.sys_tables_mysqlcheck rpl.rpl_cloned_slave_relay_log_info rpl.rpl_encryption
rpl.rpl_encryption_master_key_generation_recovery rpl.rpl_encryption_master_key_rotation_at_startup
rpl.rpl_gtid_innodb_sys_header rpl.rpl_slave_start_after_clone
rpl_gtid.rpl_gtid_only_start_replica_after_clone
rpl_gtid.rpl_gtid_xa_commit_failure_before_gtid_externalization rpl_gtid.rpl_gtid_xa_commit_one_phase_failure_before_prepare_in_engines
rpl_gtid.rpl_gtid_xa_prepare_failure_before_prepare_in_engines
rpl_gtid.rpl_gtid_xa_rollback_failure_before_gtid_externalization
rpl_nogtid.rpl_assign_gtids_to_anonymous_transactions_clone rpl_nogtid.rpl_gtid_mode
rpl_nogtid.rpl_nogtid_encryption_read test_services.test_host_application_signal_plugin

As you can see the list is now significantly longer, indicating a more thorough test run coverage of the diff. All this extra grepping takes about 90 seconds on my machine, if some popular include files are touched. I have no idea whether that's with hot or cold FS cache. I also don't know whether replacing grep with rg would it make it faster.

To minimize the false positives in included file search, grep considers the lines that don't start with the MTR language comment character #, and are like ...source...basename-of-included-file. This allows false positives in indented comments and inside string literals (that one should be rare) and it cannot tell apart files with the same name in different directories. In theory it also allows false negatives if an include file is referenced using a string variable to store its name. Any suggestions for better regexps are welcome.

It goes without saying that it is best applied on test-only patches. If you touch the source code, then you should be looking at whole MTR runs, or, if possible, MTR runs of selected suites. But if you are indeed working on a test-only patch, this script reduces the required test time effectively.

Should be portable but currently tested on macOS only. Feedback is welcome!

Wednesday, January 24, 2024

Building and testing MySQL 8.0.36 and 8.3.0 on macOS

The previous releases (8.0.35 and 8.2.0) resulted in me reporting fifteen bugs. Let's find out whether 8.0.36 and 8.3.0 will fare better on an M1 Mac.

Let's start with the build. Boost goes away as an external dependency in 8.3.0, removing the need to specify Boost-related CMake options, good. The server continues to build successfully with -DWITH_SYSTEM_LIBS=ON but now started requiring -DWITH_ZLIB=bundled, because 8.3.0 made the system libraries option govern zlib too, and the one in XCode is one patch level version too old. The Homebrew-installed version is ignored.

8.0.36 Release configuration builds with a single potentially-fatal warning: bug #113662 (NDB compilation error on macOS Release build). Finding this made me look, why is NDB built at all, if I did not add -DWITH_NDB=ON? This resulted in bug #113661 (NDB storage engine built ignoring -DWITH_NDB=OFF (which is OFF by default too)).

The most serious build-related issue I saw previously was incorrect query results if compiled with LLVM 15 and newer, reported as bug #113049 (MTR test json.array_index fails with a result difference) and bug #113046 (MTR tests for SELECT fail with ICP, MRR, possibly other flags). This issue has been fixed, although the bugs are still open (thus no release notes entries neither). As Tor Didriksen explained, they are open due to still remaining issues with recent MSVC compilers. But, LLVM works fine for me now and that's great.

The previous releases also required -ffp-contract=off compilation flag workaround to take care of some failures: bug #113047 (MTR test main.derived_limit fails with small cost differences), bug #113048 (MTR test gis.gis_bugs_crashes fails with a result difference). This has been mostly addressed, except that #113047 is fixed in 8.0.36 and 8.4.0 but not 8.3.0, so that failure still remains if the workaround is dropped.

The previous releases could not be compiled with LLVM 17, and no changes occurred here, bug #113123 (Compilation fails with LLVM 17) still applies.

Moving on to tests in Release, Debug, and Debug+ASan+UBsan configurations. Looking better than the last time, this is what I had to report:

So, to sum up, 10 bugs reported, 4 bugs confirmed fixed, 5 bugs (#113123, #113260, #113189, #113190, #113258) have no changes, and 1 bug (#113023) I did not test.

All in all, this looks OK. While no perfect clean testsuite results I was used to in some older releases, no miscompilation-like bugs neither, and that's fine.

Thursday, January 11, 2024

MySQL clone plugin internals and MyRocks clone design

I just realized that about MySQL and MyRocks clone I never actually published anything more serious than a single-emoji post on Facebook, a link to an Oracle umbrella bug, and this tweet.

So, let's talk about clone. MySQL has a clone plugin which can be used to copy new instances from existing ones, and it's also integrated into group replication for the same purpose. In Oracle releases this plugin copies only InnoDB tables, making the feature unsuitable for MyRocks instances.

Now MyRocks is the first storage engine, besides InnoDB, to get the clone support, and it works on mixed MyRocks/InnoDB instances while ensuring that the cloned instances are consistent across engines too. The code is in Meta's branch, I don't believe there are any user docs (but MyRocks support is so seamless that Oracle docs suffice! Only half-joking here), but there are IMHO extensive internals docs at Meta's wiki: MyRocks Clone Plugin

Their scope is broader than the title might suggest. Not only the MyRocks clone design is discussed, but there is also a clone background section, which discusses how clone works internally and fully applies to the Oracle branches too.

Like with all things 3rd party storage engines, it is rare to develop a feature without having to patch the server (or in this case server, clone, & InnoDB plugins). The details of this patch are also discussed in the Wiki, and also in the aforementioned Oracle umbrella bug.

Last but not least, with background and patches elsewhere out of the way, the Wiki has the design of MyRocks clone proper.

I hope the feature will reach MyRocks downstreams one day. Since MariaDB currently has no clone plugin, that leaves Percona Server. Maybe these docs will help with the porting, and also for advanced end-user troubleshooting. Clone away!

Thursday, December 14, 2023

MySQL 8.0.35 and 8.2.0 are out, here are my 15 compilation/test bug reports

I'm only a month and a half late to the party. That's, unfortunately, because I tried to build it and run its tests, on macOS, of all things. First the good news: it builds, and does so with the maximum set of 3rd party libraries possible.

Next I tried running the testsuite. I am used to clean test results in Oracle releases, under good conditions at least (not too heavy a load on the system, not too high a --parallel setting), with only occasional issues. This time I saw dozens of failures under debug, debug+sanitizers, release configurations, and tried to convert them to bug reports, best-effort.

First I identified a Homebrew-packaged Perl incompatibility with a test script: https://bugs.mysql.com/bug.php?id=113023.

Then I had a couple of test output differences where the difference was in floating point values: https://bugs.mysql.com/bug.php?id=113047 (MTR test main.derived_limit fails with small cost differences) and https://bugs.mysql.com/bug.php?id=113048 (MTR test gis.gis_bugs_crashes fails with a result difference). I am not a floating point programming expert, but somewhat luckily I remembered that there is a GCC option -ffp-contract=off, and that MySQL CMake script checks whether to add it. On a hunch that maybe the CMake test is incomplete (it is Linux-only and I was on macOS) I tried adding it as a workaround and it worked!

The next set of bugs was nastier. A bunch of query optimizer tests were failing with incorrect query results (https://bugs.mysql.com/bug.php?id=113046), and so did a JSON array test (https://bugs.mysql.com/bug.php?id=113049). To find the triggering conditions I tried different compilers, and, found that the tests pass if compiled with LLVM 14 and fail with LLVM 15, 16, 17, and XCode 15. I had no idea whether this is a compiler bug, MySQL undefined behavior, or something else, but Tor Didriksen posted on #113049 that "Recent versions of Clang have changed their implementation of std::sort(), and our own 'varlen_sort()' function returns wrong results.", one less mystery then.

Checking those different compiler versions was not trivial, because Homebrew-packaged LLVM 14 to 17 fail to build MySQL: https://bugs.mysql.com/bug.php?id=113113. Something about some incompatibility between system ar and LLVM ranlib utilities, with a workaround to use the ar coming from LLVM, i.e. -DCMAKE_AR=/opt/homebrew/opt/llvm@16/bin/llvm-ar. My build script is at 700 lines now, and that's already with some parts factored out.

On the top of the previous bug, LLVM 17, being new, had its regular and expected share of new warnings/errors: https://bugs.mysql.com/bug.php?id=113123.

Back from the build-with-different-compilers detour, there were still some test failures unaccounted: a debug assertion in group replication (https://bugs.mysql.com/bug.php?id=113257), all the TLS 1.3-using tests failing (https://bugs.mysql.com/bug.php?id=113258), spam in the replicating server error log (https://bugs.mysql.com/bug.php?id=113260).

At this point I stopped processing MTR tests, as I had already logged many bugs, and it became harder to avoid duplicates, so thought I could look at the unit tests. Here I'll just give a list of partial findings:

That's why it took me ~six weeks (and fifteen bug reports) to celebrate the new MySQL releases. That's halfway to the next expected release date on the quarterly schedule, and I hope I will be able to write a much shorter blog post much sooner after that release, as usual!


Monday, October 23, 2023

Strong typing: comparing Rust newtype to C++

Rust source code often makes heavy use of the newtype idiom, where a new type is created for an underlying primitive type to differentiate it from other uses of the same underlying type. The term "newtype" comes from Haskell where it's not an idiom, but a keyword, thus a built-in language feature.

I was wondering why I never heard of newtype as a C++ developer, because C++ is obviously a strongly-typed language, where the same issue exists. There are different ways to approach it and this is my attempt to get the thoughts on the topic in order, there will be no earth-shattering insights.

Rust: newtype

Suppose you are developing a database and have transaction IDs and log sequence numbers. Both are u64 but are not interoperable in any way. So in Rust, a natural implementation would be to apply newtype idiom twice:

pub struct TransactionId(u64);

impl TransactionId {
    fn new(id: u64) -> Self {
        Self(id)
    }

    fn get(&self) -> u64 {
      self.0
    }
    ...
}

impl fmt::Display for TransactionId { ... }

pub struct LogSequenceNumber(u64);

impl LogSequenceNumber { ... }
...

Let's enumerate the options in C++.

C++: do nothing

Do nothing, and use std::uint64_t for both types. No compiler protection, no documentation at the type name, thus the most bug-prone option. Obviously there is nothing to stop us from using this same option in Rust too.

C++: use type aliases

Introduce type aliases:

// Can also be done with typedef, but let's stick to modern C++:
using transaction_id = std::uint64_t;
using log_sequence_number = std::uint64_t;

This expresses the intent, documents things whenever the type name appears, and is not too verbose. The downside is that it does not introduce new types, only aliases for existing ones, meaning that transaction IDs assign to LSNs and back freely.

C++: introduce new types

Introduce new types. Like in Rust, differently-named structs with identical fields can be used.

struct transaction_id {
  std::uint64_t val;
}

struct log_sequence_id {
  std::uint64_t val;
}

Now type safety is increased and the type mix-up is prevented by the compiler. But so are most operations with type variables, requiring writing extra code to have the desired functionality, compared to the first two options. Writing this extra code will be more verbose than the same in Rust because the latter has support for traits, which can have default implementations.

struct log_sequence_id {
  ...
  // explicit is important, we don't want to make the incompatible types
  // implicitly-covertable again inadvertently
  explicit log_sequence_id(std::uint64_t v) : val{v} {}

  log_sequence_id& operator += (std::size_t log_delta) {
    val += log_delta;
    return *this;
  }
  ...
}

Naturally, limiting available operations is advantageous too, in both languages. For example, it makes no sense to add two transaction IDs together.

Since this is C++, meaning that we have the template-hammer, making all the problems look like template-nails for better or worse, we could try avoiding spelling out structs every time:

// Written this way only to show a point. The actual implementation would be more
// complex to be able to handle move-only types and wrap large objects efficiently.
template<typename T, typename Tag>
class newtype {
 public:
  explicit newtype(T v) : val{v} {}
  void set(T v) { val = v; }
  T get() const { return val; }
 private:
  T val;
};

struct log_sequence_id_tag{};
using log_sequence_id = newtype<std::uint64_t, log_sequence_id_tag>;

struct transaction_id_tag{};
using transaction_id = newtype<std::uint64_t, transaction_id_tag>;

Now introducing a newtype is reduced to two lines of code. Again, C++ developers do not usually discuss newtype but they do discuss strongly-typed using and typedefs, which is the same thing, called differently.

In most cases we are wrapping a single value of a primitive or string type. Those wrapped values are then operated using free functions or methods of some other classes. Thus, in this setting, this is a great option and we are done. But suppose we want to add some methods to the newly-introduced type instead of using free functions. The newtype template will not allow this, not unless we introduce inheritance:

using log_sequence_id_base = newtype<std::uint64_t, log_sequence_id_tag>;

class log_sequence_id : public log_sequence_id_base {
   ...
};

At which point the use of the newtype template becomes questionable and the code simplifies by folding the value into the class:

class log_sequence_id {
 public:
  ...
 private:
  std::uint64_t value;
};

Here we are back to creating a new type manually, just like before, without templates. This seems to be different from Rust, where a single-field struct will clearly show its newtype origins in the declaration, regardless of how much functionality it acquired later on.

So, there you have it. Both languages are strongly typed and have means to introduce new distinct types built on the existing ones, with Rust calling this newtype, and developers having a choice in C++ between type aliases, which don't actually increase type safety, to succinct templates and verbose types with some trade-offs.

Monday, September 25, 2023

Implementing durability in a MySQL storage engine

update 2023-09-28: edited for non-durable SE commits under group commit, and fixed the trx->flush_log_later discussion.

update 2023-09-27: Binlog group commits asks the storage engines to commit non-durably, will edit the post even more.

update 2023-09-26: trx->flush_log_later is actually used. Will edit the post.

Let's review how a MySQL storage engine should implement transaction durability by flushing / syncing WAL writes to disk. For performance reasons (group 2PC), let's also review when it specifically should not sync writes to disk. The reference durability implementation is, of course, InnoDB.

The main storage engine entry point is handlerton::commit. Since in general the storage engines participate in two-phase commit protocol with the binary log, there is also handlerton::prepare, and handlerton::flush_logs participates too. Let's ignore rollbacks, savepoints, explicit XA transactions, read only transactions, transactions on temporary tables only, crash recovery, and transaction coordinators other than the binlog.

Background: Group Commit

It was implemented (WL#5223) in its current form in MySQL 5.6, and its internals are described in this Mats Kindahl's blog post. I will not repeat everything here (and I'm sure I'd miss a lot of details), but for durability discussion, from the storage engine side, the group commit looks as follows:

  • prepare(t1) with reduced durability;
  • prepare(t2) with reduced durability;
  • prepare(tn) with reduced durability;
  • flush_logs(), making all the prepares above durable;
  • commit(t1) with reduced durability;
  • commit(t2) with reduced durability;
  • commit(tn) with reduced durability.

A surprise here is that the commits are performed with reduced durability too. How do reduced-durability commits implement full durability for the committed transactions, then? Turns out, the design of binlog group commit is only the commit of binlog itself is durable, and for the storage engines, prepares are made durable in batches and that's it. If their commits are lost, binlog crash recovery will roll forward the prepared transactions.

This design is counterintuitive if one thinks that innodb-flush-log-at-trx-commit=1, as documented, makes InnoDB commits durable in this setup, which it does not, and it is possible to see binlog crash recovery in action. Davi Arnaut reported this as bug #75519 in 2015, and IMHO few users are aware of this behavior.

Anyway, back to the implementation. Apparently the server developers did not want to change the prepare/commit handlerton interface, so the server durability request (full or reduced) is not passed in as an argument, but must be queried by thd_get_durability_property returning an enum with two possible values HA_REGULAR_DURABILITY and HA_IGNORE_DURABILITY.

Later, in 8.0, this durability property was reused to implement correct & performant commit order on multithreaded replicas, when binlog is disabled (WL#7846).

InnoDB: handlerton::commit

Implemented by innobase_commit.

Comes last in the group commit, but let's review it first. In other setups it might be the only entry point.

Wherever I say "write [to the disk] and sync|flush", the mental model is that of a buffered write with a separate flush/sync afterwards. If O_SYNC or O_DSYNC is used to write the log instead, then the write and the sync are a single operation.

Let's ignore non-default innobase_commit_concurrency setups.

First the code sets trx->flush_log_later and then goes through the call stack innobase_commit -> innobase_commit_low -> trx_commit_for_mysql -> trx_commit -> trx_commit_low. The last one calls trx_write_serialisation_history, which makes the necessary commit writes to a mini-transaction, then trx_commit_low commits the mini-transaction by creating the redo log records. Nothing is done for durability yet at this point. Finally trx_commit_low calls trx_commit_in_memory, which sees that trx->flush_log_later is set and sets trx->must_flush_log_later. (if trx_commit is called from other API than SE commit, then flush_log_later will not be set and the durability will be ensured in this function).

At this point the callstack returns all the way back to innobase_commit, which calls trx_complete_for_mysql, which now checks trx->must_flush_log_later (set), durability request (reduced), and whether this is a DDL transaction. If it is not, then nothing is done, and InnoDB reports the commit as successful. If it is a DDL transaction, then log is flushed ignoring the reduced durability request and innodb_flush_log_at_trx_commit setting..

The above mentioned that DDL transactions are flushed more than regular ones, regardless of innodb_flush_log_at_trx_commit setting. This is a deliberate design decision, which has to do with the data dictionary, I believe. To understand why, consider the relevant parts of server startup sequence:

  1. InnoDB comes up, and performs its own recovery from its redo log.
  2. Server data dictionary is initialized.
  3. Binlog crash recovery runs.
If any DD transactions are trapped in prepared state by the time of the data dictionary initialization, they will be invisible, while their disk changes (e.g. a tablespace renamed on disk) will be present on disk. This inconsistency is likely to be fatal for the DD, and binlog crash recovery runs too late to recover from that.

InnoDB: handlerton::prepare

Implemented by innobase_xa_prepare.

It calls trx_prepare_for_mysql -> trx_prepare -> trx_prepare_low, which updates the undo log state for the transaction a mini-transaction, committing which makes the top-level transaction prepared. Then trx_prepare calls trx_flush_logs, which will either do nothing or write and flush the redo log up to the mini-transaction's commit LSN, depending on the server durability request.

InnoDB: handlerton::flush_logs

Implemented by innobase_flush_logs.

It has a bool argument telling whether it was invoked as a part of binlog group commit, which is the interesting case here, ignoring the other option of it being invoked by FLUSH LOGS SQL statement. It writes the redo log buffer to disk and flushes it according it to innodb_flush_log_at_trx_commit value.

Bugs reported while writing this:

Bugs found while writting this:

Bugs that made me write this:

Friday, August 25, 2023

MySQL Build Times: Use Ninja

I had noticed Ninja as one of the possible CMake generators long time ago, but never paid attention to it, as I could not imagine it being better than Make so much that it'd be worth switching. Then, when I posted my MySQL -ftime-trace results, I got a comment on LinkedIn that Ninja visualizes build time nicely. I tried that, and it did, and I went back to Make builds.

A few months later, I am looking at Vittorio Romeo's "Improving Compilation Times" presentation slides (download them, do not read inline on GitHub; there is also the talk video itself), and the very first low-hanging fruit advice is "use Ninja".

OK, so let's actually try, say, Debug build on Facebook MySQL 8.0.28:

  • make -j13: 4m43s
  • ninja: 4m17s

A 10% improvement with roughly zero effort is nice. There are other niceties too: you don't have to figure out the right make parallelism argument for -j, as Ninja handles that automatically, and the terminal is not spammed with the build log of all the source files that have been built uneventfully. Only compiler warnings and any irregular build output is there.

To use it, add -G Ninja to CMake invocation, and then use ninja instead of make to build. I have patched my scripts.

Tuesday, May 09, 2023

Tips for making MySQL builds & tests faster

Recently, Mark discovered that a part of FB MySQL sources were recompiled three times in a single build. That has been fixed, and I also played with clang -ftime-trace to see where the build time goes. I believe there is more to this topic, so I wanted to organize my thoughts on the subject.

In software development, the shorter the change-build-test cycle is, the higher the developer productivity. Yet MySQL is not making it easy to have short "build" and "test" steps in this cycle. A reasonably powerful Intel Core i9 laptop used to take 20-30 minutes for a clean debug build without the unit tests. The MTR testsuite takes hours, and it is possible to make it run for days if you wish.

What can be done about this? Here's what's working for me, focusing on 8.0 trees. There is no silver bullet here, and some of the suggestions might be even somewhat effort-intensive to set up. Luckily, most suggestions are independent and optional.

Source trees and build artifacts

TL;DR: build incrementally as much as possible. Disk space is cheap, while your time is expensive. A common "antipattern" (quotes because there is nothing wrong with such workflow otherwise) is to have a single local git clone with a single build directory. Changing a branch with git checkout forces a clean build. Changing the build type (i.e. you built Debug previously, now need RelWithDebInfo, or Debug + AddressSanitizer enabled) forces a clean build. Don't force clean builds; keep all the previously-built artifacts around as much as possible:

  • Have one build dir for every build type (e.g. build/debug, build/release, build/debug-asan). Never delete a build dir unless forced to.
  • Never use git checkout in the local clone directory, use git worktrees for everything. Only delete a worktree (and its build dirs) once that branch is merged.
  • You are free to store the build dirs wherever you want, but in order to keep track which build dir belongs to which source tree, for me, the simplest option was to keep them below the source tree. Oracle MySQL has build/ in its .gitignore, so that's a good prefix dir for them.

One objection to incremental builds is that they might somehow result in differences in build artifacts compared to the same clean builds, and that would be bad. In practice, sometimes something does break an incremental build with a build error, forcing a clean rebuild–an occasional checkpoint if you will. I have never encountered a silent divergence that was somehow detrimental, and your CI/CD farm will build your PRs cleanly anyway.

Now if you follow the worktree advice, you are likely to have quite a few of them. Some of them will be your personal feature branches, while others will be shared feature branches, and main/master/8.0/5.7 trunk branches where others commit and push. Set up a cron job to pull the later and build overnight. Pros: ready builds for your work in the morning. Cons: you left your work last night with a working build, and someone pushed a commit that broke the build for you, which is what you find in the morning. IMHO the pros outweigh the cons.

Some MySQL source trees, like the Meta one, are incompatible with git worktrees. Luckily there is a not too-complicated workaround of adding the following CMake options: -DMYSQL_GITHASH=0 -DMYSQL_GITDATE=2100-02-29 -DROCKSDB_GITHASH=0 -DROCKSDB_GITDATE=2100-02-29. Maybe one day it will be fixed properly.

A thing that did not work well for me is ccache. While easy to set up, at least twice I had to waste a lot of time on apparent source-binary mismatch only to figure out that ccache is substituting an incorrect binary object file. That was enough for me to drop it, and I could never measure its benefit anyway.

Build options

According to the MySQL docs, there are over 160 CMake options. Some of them can affect the build times for better.

Use system libraries as much possible

You are in the business of developing MySQL, not its bundled 3rd party libraries. If you are lucky, you are also not in the business of developing MySQL integration with any of them. So, ignore the bundled libraries as much as possible and use your system ones: -DWITH_SYSTEM_LIBS=ON, after installing all the dependencies (which I won't list here). Unfortunately, that's only a theory, and in practice there's a difference between theory and practice. Let's take macOS, for example, to see which of the bundled still have to be used:

  • 8.0.33: -DWITH_RAPIDJSON=bundled
  • 8.0.32-29: system libs only, yay!
  • 8.0.28-27: -DWITH_RAPIDJSON=bundled -DWITH_LZ4=bundled -DWITH_FIDO=bundled
  • 8.0.26: -DWITH_RAPIDJSON=bundled -DWITH_LZ4=bundled

That's not too bad, and, given that we are stuck with every release for at least three months (much longer than that if using, say, the Meta tree), it's worth figuring out.

Skip the unit tests but be careful

-DWITH_UNIT_TESTS=OFF is by far the single most time-saving CMake option. Usually it is also not as bad as it may sound for development because the MTR tests are still there, and depending on what you are working on, the MTR tests might cover your testing needs completely. The biggest risk there is updating some internal API in a not particularly interesting way and forgetting to update its users in the unit tests. I am still figuring out the best way here to have my cake and eat it too.

Older versions: skip the functionality you don't need

This is quickly becoming an obsolete tip, but including it for completeness. Group replication used to be an optional build part, and the X plugin still is (-DWITH_MYSQLX=OFF). Unfortunately, disabling the X plugin breaks quite a few unrelated-to-X MTR tests, so I don't do that anymore.

Testing

Use libeatmydata

Install Stewart Smith's libeatmydata and always use it, except if building with sanitizers on Linux. It is packaged for Ubuntu, macOS Homebrew, and likely elsewhere. It cuts about 25% of MTR testsuite runtime by silently substituting all the fsync and related calls with no-ops for the tested processes. It is transparent, invisible, not getting in your way etc.–a pure win. Its invocation in the context of MTR tests is a handful to type, so I am using a shell script helper:


UNAME_OUT="$(uname -s)"
if [ "$UNAME_OUT" = "Darwin" ]; then
    if [ "$(arch)" = "arm64" ]; then
        BREW="/opt/homebrew/opt"
    else
        BREW="/usr/local/opt"
    fi
    EMD_LIBDIR="$BREW/libeatmydata/lib"
    unset BREW
    export MTR_EMD=(
        "–mysqld-env=DYLD_LIBRARY_PATH=$EMD_LIBDIR"
        "–mysqld-env=DYLD_FORCE_FLAT_NAMESPACE=1"
        "–mysqld-env=DYLD_INSERT_LIBRARIES=$EMD_LIBDIR/libeatmydata.dylib")
    unset EMD_LIBDIR
else
    export MTR_EMD=(
        "–mysqld-env=LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libeatmydata.so")
fi

mtr_emd() {
    ./mtr "${MTR_EMD[@]}" "$@"
}

MTR also provides the --mem option which tries to use a non-persistent filesystem for running tests. In theory this should speed it up even more than libeatmydata, but I could never get it work reliably.

Use --parallel, find its best value for your machine & your tests.

For instance, for single-server tests on an Apple M1 Max (8 performance and 2 efficiency cores) the fastest I found was --parallel=15. Replication tests complicate this by spawning three servers per test instead of one.

Don't test what you don't need

Now we are deep in Captain Obvious territory, but still. Developing a plugin, say clone? Great, most of the time --suite=clone is enough. Sure, even with plugins the separation is not perfect, and there are dependencies, for example group replication uses clone too, but it's a start. You are less lucky if you work on InnoDB, or something in i.e. THD or Handler that is a dependency of everything.

Hardware

Throwing hardware at the build time problem is a great option if you have the resources. IMHO, there are two main routes to consider: the offline-portable one & the connected-powerful one.

If you need a laptop and want the option of working offline, then Apple Silicon is second to none. Replacing an Intel Core i9 laptop with a M1 Max one made MySQL builds five times faster. Five actual times! The downside is that macOS is not exactly a datacenter server OS, and so if you are the only one on the team on macOS, guess who just became the macOS port maintainer? There is an option of running Linux on Apple Silicon, which I heard virtualizes at near-bare metal speed, but I haven't explored it yet. Even then you become the ARM port maintainer, which is not that bad considering Graviton.

If a desktop is OK, then there are options, although I haven't tried this myself. Apple should still be in the running, and you could get an AMD CPU with up to 64 cores, which should make a short work of even MySQL build.

If a network is OK, then Sunny recommends using icecream to distribute compilation. I haven't tried this either.

Conclusion

I wrote down everything I knew about making MySQL build and test faster. Have I missed anything? Please comment.