Laurynas Biveinis

Building and testing MySQL 9.7.0 early access build on macOS

2026-03-24T16:25:00.000+02:00

Oracle released 9.7.0 community early access build, which is a part of their work towards fulfilling their new promises to the community. This is great news for community testing and I did my part in my usual way (previously: 8.0.45 / 8.4.8 / 9.6.0).

Build

No changes from 9.6.0: the current Xcode (26.3) builds the 9.7.0 early access build fine, LLVM 21 still fails (bug #119246).

Tests

Bad news

Bug #120088 Hypergraph MTR tests failing, although I'm happy to see hypergraph in the community builds.

No news

Good news

Bug #119252 Test router.response_cache failure no longer reproduces with 9.7.0

One-off issues

component_keyring_file.tablespace_encrypt_10 failed once under debug build
Likewise main.func_in_mrr
Likewise perfschema.idx_compare_metadata_locks
main.mysqltest failed testcase check one under debug build
rpl_gtid.rpl_semisync_gtid_tag crashed once under debug + sanitizers build
perfschema.system_events_component failed once under debug + sanitizers build
Likewise perfschema.stage_mdl_global
Likewise innodb_undo.truncate

Boring but useful: making large-scale changes on MySQL codebase with LLMs

2026-02-10T14:13:00.000+02:00

I used Claude Code to do a simple mass refactoring on MySQL 9.6 over two weeks, resulting in a 25K line diff that passes the testsuite. That was something I wanted to experiment with for some time now: making boring but useful large-scale changes to MySQL while spending as little human effort as possible. Claude Code with Opus 4.5 (and 4.6 for the last few days) was used for everything.

Why boring but useful large-scale?

Pushing LLMs to the limit–like building a browser or a compiler, I guess I'd be building a database from scratch for a comparable project–is fun, it shows us what's possible and what needs one more frontier model release, but the result artifact itself is throwaway code, thus not usable in the general sense, nor do I have time (or money for tokens) for this.
Making algorithmically challenging changes is still firmly in the domain of humans. No "Add LSM tree to InnoDB. Be thorough. Do not make any mistakes." prompting here. Although, as I'm writing this, I just saw Zongzhi Chen doing exactly that and patching PostgreSQL with an InnoDB-like doublewrite buffer. Things move quickly.
Making too simple changes is a waste of LLM capabilities where sed would do the same faster and cheaper.

For a boring but useful large-scale change I picked replacing THD * with const THD & or THD & in method parameters and class fields. This might require some background. MySQL started in mid-90s, and its current source code, after 30 years, has features of three strata: modern C++ (C++11 to C++20), early standard C++ (C++98, C++03), and even earlier "C with classes" style. One thing still surviving from the earliest days is that pointers are used instead of references, including the contexts when the pointer can never be nullptr. I'll try not to digress on why it's not the right thing, C++ Core Guidelines has that (F.16, F.17, F.18, F.19, and finally F.60 for when the pointers are the right choice).

I settled on this after a few false starts, which made promising progress but eventually imploded. That was because of my mistake of not letting the agent make intermediate commits, resulting in unmanageable working directory diff every time. Now I believe I could have completed most of them too:

"Convert THD * to THD &": yep I tried it twice, the first time it broke the server and then couldn't debug its way out of the hole.
"Replace LEX_CSTRING with std::string_view" - the partial conversion was successful, but my mistake was not considering that lex strings are both owning and non-owning whereas string views are always strictly non-owning. The project failed in trying to replace the owning lex strings with something different altogether.
"Replace printf-style formatting with std::format" - this one imploded because of no intermediate commit rule, but not before scoring some strong wins, such as converting the MySQL error log and client error machinery and messages more or less correctly.

It was also my goal to spend as little of my time as possible on this, consciously ignoring some of the current best practices of this type of work, such as not setting up CLAUDE.md, not doing it in a dedicated container where the agent has full permissions, and not implementing any kind of Ralph Wiggum loop. All of these saved me some advance planning time, but then I had to go to a terminal and tell "continue" when it stopped mid-task or inexplicably started doing edits with some shell trickery instead of its standard tools. Thus by trying to save time I wasted some time, but definitely saved some thinking and could rubber-stamp agent questions while doing other, more useful, work.

I launched Claude Code and prompted

Convert pointers to THD to (optionally const) references to THD throughout the
codebase. The safe instances to convert are the ones where the pointer is dereferenced
unconditionally, there is an assert(thd != nullptr) check, or any thd == nullptr
branches are dead code (because e.g. the function is never called with pointer ==
nullptr). Not safe instances are the ones where null pointers are passed and C API.
In the touched code (and only in the touched code!) follow Almost Always Auto while
keeping references and pointers pulled out and const as much as possible (unless it
forces other code changes), except for trivial function parameters. Examples of AAA
and const: "const auto bar = ... ", "const auto *bar = ... ", "auto &baz = ...",
"const auto &x = ...", "const auto *const y = ...", "auto *const z = ..."

and letting it plan until we both were happy with the plan, and go. As I said above, every few tens of minutes to few hours it just stopped asking permission to continue, directly against original instructions. At those points I cleared its context and pointed to the plan file again.

Finally, some two weeks later (I did not orchestrate any parallelization, it sometimes parallelized itself a bit with subagents, also I needed room in the usage limits for other work on my account), it declared it's done. Then I ran debug + sanitizers testsuite, pasted any failures to the agent asking to fix, but, very importantly, always git bisect to the failing commit first. There were over a thousand commits. It debugged everything rather easily once it always had a relatively small regressing commit identified. Nice.

I succeeded in staying below usage limits of my Claude Max plan all the time. In the failed attempts above the agent parallelized much more aggressively and burned through the week's allowance in three days. I tried to preserve my sanity and did not go the route of stacked Max accounts.

With a clean testsuite run I asked it to create a second branch with all commits squashed. This produced a +25304, -25402 line diff. I looked at random points in the diff and everywhere I was satisfied with the result. The straightforward conversion bits went fine, but it also removed some provably-redundant nullptr checks, declared non-moveable classes as such, simplified constructors. The resulting release build binary is some 12K smaller on macOS, BTW. If this were real work, I'd still have to read the whole diff for any obvious issues, but in any case this is significantly faster than doing the work myself.

"If this were real work" - or is it? The nature of this patch is such that no tracking fork could take it, only Oracle MySQL. I have signed the contributor's agreement with them, then sent them some patches and have been waiting for years but they are still not merged, thus I will not submit this one unless asked to.

I pushed the code to GitHub:

To conclude, with LLMs, everyone has an opinion. A vocal part of the community does things like OpenClaw and Gas Town. I am way, way more conservative than that: so far I successfully avoided buying a Mac Mini for OpenClaw and giving my email and bank login details to it, and I also get uncomfortable quickly due to cognitive load if running too many coding agents in parallel. But, we live in exciting times, software engineering is changing and I am very optimistic.

Building and testing MySQL 8.0.45, 8.4.8, and 9.6.0 on macOS

2026-01-23T14:39:00.000+02:00

Oracle has just released MySQL 8.0.45, 8.4.8, and 9.6.0 and here are the results of my usual testing of building them and running the testsuite on macOS, Apple Silicon hardware.

Build

All three versions compile with the current XCode (26.2) OK. With Homebrew-packaged LLVM, versions 14 to 17 inclusive are getting a strange CMake error that might have more to do with macOS than MySQL. LLVM 19 still fails to build 8.0.45 (bug #119238), but not 8.4.8, and so does LLVM 20 (bug #119239). Both these issues are pre-existing. But, 8.4.8 regressed in that it started being affected by bug #119246 too. LLVM 21 still fails to build 9.6.0 (preexisting bug #119246).

Test

Bad news:

No news aka old bad news:

Good news:

none! Every single bug I am tracking is present

Conclusion

With 4 new, 22 unchanged bugs, and not a single fix, the testsuite quality is continuing to slowly decay. I hope this will change for the better.

Addendum

A note for my future self. Issues I couldn't reliably reproduce:

main.mysqltest failing testcase check on 8.0.45, 8.0.45 debug build
innodb_zip.16k, main.mysqlpump_bugs failing with a result difference, 8.0.45 debug+sanitizers build
main.subquery_sj_firstmatch failing with a result difference, 8.0.45 release build
main.lowercase_table4 timeout, 8.0.45 debug+sanitizers build
x.mysqlxtest_mode_ssl test command failure, 8.4.8 release build
connection_control.performance_schema_processlist test result difference, 8.4.8 release build
merge_innodb_tests-t failed once, 8.4.8 debug+sanitizers build
routertest_harness_net_ts_timer failed once, 8.4.8 debug build
rpl_nogtid.rpl_semi_sync_optimize_for_static_plugin_config failing with global-buffer-overflow under 9.6.0 debug+sanitizers build, serious, but I couldn't reproduce.
routertest_integration_routing_direct, routertest_integration_routing_router_require, routertest_integration_routing_sharing, and routertest_integration_routing_sharing_constrained_pools failed once under 9.6.0 debug+sanitizers build and once under 9.6.0 debug build
perfschema.system_events_plugin, test_service_sql_api.test_sql_stmt, main.group_by, main.index_merge_innodb, perfschema.idx_compare_metadata_locks failed once, 9.6.0 debug build
router.authentication_mysql_accounts, router.app_specific_metadata_v_latest, and main.ps failed once, 9.6.0 release build

Look at me, I'm an (LLM-assisted) Emacs Lisp developer now

2025-12-01T17:12:00.000+02:00

Claude Code came out in February and I was hooked immediately. Luckily for me Anthropic introduced flat rate pricing plans very soon afterwards, or I'd have been bankrupted by pay-per-token prices by summer. I tried to channel all that newly-developed addiction to something productive, and decided to write, greenfield, some Emacs Lisp packages. I think such projects are perfectly-sized for working with Claude Code: for smaller tasks the regular chat interface worked fine (although now Claude Code handles those for me too), and for larger tasks one needs to start paying more attention to context management.

So I wrote three Emacs Lisp packages, all related to LLMs (yeah there is some infinite recursion going on here. I guess it all stops at the singularity):

mcp-server-lib.el: an Emacs Lisp library for writing Model Context Protocol (MCP) servers, running in Emacs. MCP is a protocol allowing LLMs to call tools and read things in a structured way.
elisp-dev-mcp: an MCP server for developing Emacs Lisp.
org-mcp: an MCP server for reading and updating Emacs Org tasks.

All three packages have been published on MELPA (which is like npm registry, but for Elisp), and the first package (the library one) is surprisingly popular, with about 1,700 installs as of today.

Obviously I learned a lot about LLM-assisted development too but so far I am not planning to blog about that specifically, as things move faster than my ability to draft and publish and there are some very good guides out there.

Building and testing MySQL 8.0.44 / 8.4.7 / 9.5.0 releases on macOS

2025-10-28T16:21:00.001+02:00

Oracle MySQL 8.0.44 / 8.4.7 / 9.5.0 are out and it's time for me to build and run their tests on macOS, Apple Silicon hardware. I have skipped the review of the previous set of releases this summer due to personal reasons, so this covers the delta from 8.0.42 / 8.4.5 / 9.3.0, out in spring.

Build

No need to use any of the bundled 3rd party libraries, and the current XCode (26.0.1) compiles all the releases. As usual, things get more interesting with Homebrew-packaged LLVM. All releases fail to build with LLVM 17 and lower versions with a bizarre CMake error, which may have to do more with the newer macOS version 26 than MySQL. LLVM 18 and 19 build fine.

8.0.44: does not build with 20 & 21:

8.4.7: builds with LLVM 20, and 21 has a different error than 8.0.44: Bug #119242 8.4.7 fails to build with clang 21.

9.5.0: builds with LLVM 20, and 21 has a different error from both 8.0.44 and 8.4.7: Bug #119246 9.5.0 compilation error with LLVM 21

The newest compiler errors are, unfortunately, expected. So, all in all, things are looking good here.

Tests

A lot of churn here over the past two releases.

Bad news:

No news, aka previously reported bugs with no to little changes:

Bug #113189 ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails
Bug #113190 Several tests in routertest_integration_routing_sharing_constrained_pools fail
Bug #113260 Client error in error log: MY-004031 - The client was disconnected …
Bug #113665 perfschema.relaylog fails with a result difference
Bug #113709 StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error
Bug #113722 Test main.index_merge_innodb failing with a result difference, fixed in 9.2.0, still happens on 8.0.44.
Bug #115480 Test innodb.log_first_rec_group failing
Bug #116369 rpl.rpl_semi_sync_alias crashes under AddressSanitizer, but only on 8.0.44, does not fail on 8.4.7, and the test looks gone in 9.5.0.
Bug #116373 auth_sec.acl_tables_row_locking failing with result diff
Bug #116378 routertest_integration_routing_splitting crashing under AddressSanitizer - not sure whether it's fixed or became an intermittent issue. Filing under "no news" for now
Bug #116394 Test binlog_gtid.binlog_gtid_binlog_recovery_errors crashes with an assert
Bug #118169 Test innodb.partition_upgrade_8019_mac_lctn_2_debug failing - might have been downgraded from a reliable failure to an intermittent one
Bug #118171 Test main.mysqldump-tablespace-escape failing
Bug #118185 Non-specific ASan error on TestLoaderGood/LoaderReadTest.load_wrong_version/0
Bug #118213 Test perfschema.idx_compare_mutex_instances fails under debug + sanitizers

Good news:

Bug #103338 Starting mysqld on same data files sometimes errors out due to doublewrite buf - could not reproduce it anymore, but now there is bug 119247 on the same testcase
Bug #116372 ODR violation reported by AddressSanitizer for plugin variables
Bug #116375 UBSan error on Rpl_commit_order_queue_test.Simulate_mts
Bug #117297 Test rpl.rpl_json failing
Bug #117314 Test innodb.tablespace_encrypt_9 failing - but see bug 119253
Bug #118170 Test innodb.partition_upgrade_8019_win_lctn_2_debug failing instead of skipping on macos - the test actually passes (!) on 8.0.44 and 8.4.7, and is disabled on 9.5.0.
Bug #118175 UBSan error at x/protocol/stream/decompression_input_stream.h
Bug #118185 Non-specific ASan error on TestLoaderGood/LoaderReadTest.load_wrong_version/0
Bug #118193 Test innodb.partition_upgrade_5727_mac_lctn_2 failing with a result difference
Bug #118211 Spec/ShareConnectionReconnectTest.change_user/ssl_modes_REQUIRED__PREFERRED_socket test fails
Bug #118212 Some router tests crash under AddressSanitizer
Bug #118214 LifecycleTest.Simple_AllFunctions crashes under AddressSanitizer

One-off issues:

merge_innodb-t on 8.0.44 and 8.4.7, debug + sanitizers build
gcs_xcom_control_interface on 8.0.44 and 9.5.0 release builds
routertest_integration_routing_direct on 8.0.44 release build
binlog_gtid.binlog_check_gtid_persistor_compatibility crashed on 8.4.7 debug build
rpl_gtid.rpl_gtids_table_disable_binlog_on_slave result difference on 8.4.7 debug build
innodb.trigger_function_lock_compare timeout on 8.4.7 debug build
routertest_integration_routing_splitting on 8.4.7 debug w/ sanitizers builds, maybe Bug #116378 routertest_integration_routing_splitting crashing under AddressSanitizer, but was not able to confirm
perfschema.keyring_keys result difference on 8.4.7 release build
json.array_index result difference on 9.5.0 debug w/ sanitizers build
perfschema.error_log result difference on 9.5.0 debug w/ sanitizers build
main.slow_log result difference on 9.5.0 debug w/ sanitizers build
router.app_specific_metadata_v_latest result difference on 9.5.0 release build
innodb_undo.truncate test assertion failure on 9.5.0 debug build

Conclusion

Without the one-offs I have 9 new, 15 unchanged, and 12 fixed bugs. At last the fixed ones outnumber the new ones, hopefully this trend will continue.

Building and testing MySQL 8.0.42 / 8.4.5 / 9.3.0 on macOS

2025-05-16T15:46:00.000+03:00

Previously: 8.0.41 / 8.4.4 / 9.2.0

MySQL 8.0.42 / 8.4.5 / 9.3.0 are out, and as usual, I have built and tested the new releases on macOS, due to its popularity as the server OS, of course.

Build

The 8.0 series don't build with the current XCode clang because of bug 118026 (Build failed with apple clang 17). That's inconvenient, but Homebrew-packaged LLVM clang versions 14 to 18 build it fine.

Another pre-existing build bug 117299 (ddl0bulk.h:236:15: error: parameter 'prebuilt' not found in the function declaration [-Werror,-Wdocumentation]) previously only affected 8.0, but now hits 8.4. Of course, the workarounds are simple enough.

I have also reported bug 118161 (Having Homebrew-installed googletest breaks build). I suspect this is not a 8.0.42 regression, thus will not be counting towards the running bug totals.

So, for build, we have one bug with no change, and one slightly worsened. Meh, but not too bad either.

Test

Here the things are not looking as good.

Bad news first. New bugs and existing bugs with substantial regressions. The number is substantial:

https://bugs.mysql.com/bug.php?id=116369 (rpl.rpl_semi_sync_alias crashes under AddressSanitizer) - was 8.0-only, now 9.3.0 crashes too at a different point.
https://bugs.mysql.com/bug.php?id=118169 (Test innodb.partition_upgrade_8019_mac_lctn_2_debug failing)
https://bugs.mysql.com/bug.php?id=118170 (Test innodb.partition_upgrade_8019_win_lctn_2_debug failing instead of skipping on macos)
https://bugs.mysql.com/bug.php?id=118171 (Test main.mysqldump-tablespace-escape failing)
https://bugs.mysql.com/bug.php?id=118175 (UBSan error at x/protocol/stream/decompression_input_stream.h)
https://bugs.mysql.com/bug.php?id=118184 (UBSan errors on router tests)
https://bugs.mysql.com/bug.php?id=118185 (Non-specific ASan error on TestLoaderGood/LoaderReadTest.load_wrong_version/0)
https://bugs.mysql.com/bug.php?id=118193 (Test innodb.partition_upgrade_5727_mac_lctn_2 failing with a result difference)
https://bugs.mysql.com/bug.php?id=118211 (Spec/ShareConnectionReconnectTest.change_user/ssl_modes_REQUIRED__PREFERRED_socket test fails)
https://bugs.mysql.com/bug.php?id=118212 (Some router tests crash under AddressSanitizer)
https://bugs.mysql.com/bug.php?id=118213 (Test perfschema.idx_compare_mutex_instances fails under debug + sanitizers)
https://bugs.mysql.com/bug.php?id=118214 (LifecycleTest.Simple_AllFunctions crashes under AddressSanitizer)

Bugs with no news:

https://bugs.mysql.com/bug.php?id=103338 (Starting mysqld on same data files sometimes errors out due to doublewrite buf)
https://bugs.mysql.com/bug.php?id=113189 (ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails)
https://bugs.mysql.com/bug.php?id=113190 (Several tests in routertest_integration_routing_sharing_constrained_pools fail)
https://bugs.mysql.com/bug.php?id=113260 (Client error in error log: MY-004031 - The client was disconnected …)
https://bugs.mysql.com/bug.php?id=113665 (perfschema.relaylog fails with a result difference)
https://bugs.mysql.com/bug.php?id=113709 (StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error)
https://bugs.mysql.com/bug.php?id=113722 (Test main.index_merge_innodb failing with a result difference) - was closed for 9.2.0, still happens on 8.4.5.
https://bugs.mysql.com/bug.php?id=115480 (Test innodb.log_first_rec_group failing)
https://bugs.mysql.com/bug.php?id=116373 (auth_sec.acl_tables_row_locking failing with result diff)
https://bugs.mysql.com/bug.php?id=116375 (UBSan error on Rpl_commit_order_queue_test.Simulate_mts) - no change in the current release but already closed for the next ones, looking forward to confirming.
https://bugs.mysql.com/bug.php?id=116378 (routertest_integration_routing_splitting crashing under AddressSanitizer)
https://bugs.mysql.com/bug.php?id=116394 (Test binlog_gtid.binlog_gtid_binlog_recovery_errors crashes with an assert)
https://bugs.mysql.com/bug.php?id=117297 (Test rpl.rpl_json failing)
https://bugs.mysql.com/bug.php?id=117314 (Test innodb.tablespace_encrypt_9 failing)

Closed bugs or bugs that have improved in some ways:

https://bugs.mysql.com/bug.php?id=114892 (Test XComControlTest.SuspectMemberFailedRemovalDueToMajorityLoss fails) - this one is intermittent with low enough frequency that I cannot tell whether it was fixed.
https://bugs.mysql.com/bug.php?id=116385 (Test main.mysql_upgrade_grant timing out on debug build)

Did not test:

https://bugs.mysql.com/bug.php?id=116372 (ODR violation reported by AddressSanitizer for plugin variables)

One-off issues that I wasn't able to repeat properly for bug reporting.

rpl_gtid.rpl_gtids_table_disable_binlog_on_slave on 8.0.42
rpl_gtid.rpl_gtid_create_select on 8.0.42
test_services.test_table_access on 8.0.42
main.system_variable_source on 8.0.42
innodb.trigger_function_lock_compare on 8.0.42
gcs_xcom_control_interface-t on 8.0.42 and 9.3.0
router.authentication_mysql_accounts on 9.3.0
routertest_integration_routing_sharing on 9.3.0
routertest_integration_routing_sharing_constrained_pools on 9.3.0
rpl_nogtid.rpl_semi_sync_uninstall_plugin on 9.3.0
perfschema.idx_compare_events_waits_history on 9.3.0
sys_vars.log_slow_admin_statements_func on 9.3.0
main.subquery_mat_all on 9.3.0

Conclusion

Without the one-off issues I have 13 (!) new, 15 unchanged, 2 fixed, and 1 untested bug. The positive trend of the previous set of releases is no more, and the set of open issues almost doubles in size. I hope that the next quarterly release will look better.

Building and testing MySQL 8.0.41 / 8.4.4 / 9.2.0 on macOS

2025-01-29T15:16:00.000+02:00

Previously: 8.0.40 / 8.4.3 / 9.1.0

Oracle released MySQL 8.0.41 / 8.4.4 / 9.2.0, and it's time for me to build and test it under macOS, of all things!

But first, a detour to the last set of releases Valgrind results, because its run took a few weeks on 9.1.0, and that's without --big-test. A big part of tests timed out, or failed randomly. There appear no errors that are diagnosed during server operation, and the shutdown logs are way too verbose for me to try to find any memory leaks. Perhaps 9.2.0 will fare better, and I will reduce --parallel further. Will it take two months to run? We will see!

Build

Mostly good news here, again. -DWITH_ZLIB=bundled is no longer required, that's probably due to newer XCode toolchain finally updating its zlib.

A new build bug:

https://bugs.mysql.com/bug.php?id=117299 (ddl0bulk.h:236:15: error: parameter 'prebuilt' not found in the function declaration [-Werror,-Wdocumentation]) - the workaround is trivial.

A fixed build bug:

https://bugs.mysql.com/bug.php?id=113113 (Build failure with Homebrew LLVM 14-19 on macOS) no longer happens with 8.4.4 and 9.2.0, but 8.0.41 is still affected.

MySQL 8.0.41 does not build with LLVM 19. It could probably be fixed by backporting a fix for MySQL 8.4.4 which is documented as "Implemented the standards-compliant my_char_traits<unsigned char> for use as a drop-in replacement for std::char_traits<unsigned char>, which was deprecated in Clang 18 and removed in Clang 19. (Bug #37273525)"

Test

New bugs:

https://bugs.mysql.com/bug.php?id=117297 (Test rpl.rpl_json failing)
https://bugs.mysql.com/bug.php?id=117314 (Test innodb.tablespace_encrypt_9 failing)

No changes:

https://bugs.mysql.com/bug.php?id=113260 (Client error in error log: MY-004031 - The client was disconnected …)
https://bugs.mysql.com/bug.php?id=116373 (auth_sec.acl_tables_row_locking failing with result diff)
https://bugs.mysql.com/bug.php?id=115480 (Test innodb.log_first_rec_group failing)
https://bugs.mysql.com/bug.php?id=113189 (ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails)
https://bugs.mysql.com/bug.php?id=113190 (Several tests in routertest_integration_routing_sharing_constrained_pools fail)
https://bugs.mysql.com/bug.php?id=113709 (StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error)
https://bugs.mysql.com/bug.php?id=116375 (UBSan error on Rpl_commit_order_queue_test.Simulate_mts)
https://bugs.mysql.com/bug.php?id=113665 (perfschema.relaylog fails with a result difference)
https://bugs.mysql.com/bug.php?id=116385 (Test main.mysql_upgrade_grant timing out on debug build)
https://bugs.mysql.com/bug.php?id=116378 (routertest_integration_routing_splitting crashing under AddressSanitizer)
https://bugs.mysql.com/bug.php?id=113722 (Test main.index_merge_innodb failing with a result difference)
https://bugs.mysql.com/bug.php?id=114892 (Test XComControlTest.SuspectMemberFailedRemovalDueToMajorityLoss fails) - this one I haven't seen in a while, but it's back
https://bugs.mysql.com/bug.php?id=116394 (Test binlog_gtid.binlog_gtid_binlog_recovery_errors crashes with an assert)

Fixed bugs:

https://bugs.mysql.com/bug.php?id=115495 ("Unable to read page" crash on innodb.dblwr_encrypt_recover)
https://bugs.mysql.com/bug.php?id=116390 (Test main.opt_hint_resource_group_hypergraph failing testcase check)
https://bugs.mysql.com/bug.php?id=116391 (Test main.opt_hints_join_order_hypergraph failing with a result diff)
https://bugs.mysql.com/bug.php?id=115498 (Test main.opt_hints_hypergraph always failing)

Did not test:

https://bugs.mysql.com/bug.php?id=116372 (ODR violation reported by AddressSanitizer for plugin variables)

One-off issues:

main.join_cache_nojb failed once with query execution plan difference on 8.0.41 debug build.
rpl_gtid.rpl_gtids_table_disable_binlog_on_slave failed intermittently on 8.0.41 Debug with sanitizers, 8.4.4 Debug, & 8.4.4 Debug with sanitizers builds. The same happened in the previous set of releases too.
binlog_gtid.binlog_gtid_bgc_ticket_manager failed once on 8.4.4 Debug build.
routertest_integration_routing_sharing_constrained_pools failed once in a way that is not bug #113190 above.
rpl_gtid.rpl_heartbeat_log_pos_4gb timed out once on 8.4.4 Debug with sanitizers.
binlog_gtid.binlog_check_gtid_persistor_compatibility failed once on 9.2.0 Debug with sanitizers.
innodb.trigger_function_lock_compare timed out once on 9.2.0 Debug with sanitizers.

Conclusion

Without the one-off issues I have 2 new, 13 unchanged, 4 fixed, 1 untested bug. Much less churn than the last time, and finally the trend is positive.

Storing the MySQL Data Dictionary in a 3rd-Party Storage Engine

2024-11-08T16:12:00.000+02:00

MySQL 8.0 replaced legacy FRM files with a transactional data dictionary, stored in InnoDB. Here I'll describe how any other storage engine could become the DDSE, that is, serve as the backend for the data dictionary, replacing InnoDB. This is purely an internals post, and will not look into user-facing motivations and implications of the change (which can be found here), now will it discuss storing any internal SE-specific data dictionary-like information in the server DD, nor should the DD tables be confused with system tables, a distinct category.

This is based on the experience of implementation this feature in MyRocks.

The needed changes broadly fall into three areas:

Changes to the SE to make it able work as the DDSE
Changes to the core server layer which no longer can assume InnoDB being the DDSE
Changes to InnoDB so it can keep on working while not being the DDSE

Changes to the SE to make it DDSE

The DDSE must:

be transactional
be linked statically into the server
know how to assign any internal IDs to DD tables
implement the handlerton API for DDSEs
make DD transactions durable
be able to do non-locking reads
support attachable transactions

The first two items are straightforward, in the motivation if not in the implementation. The storage engine must be transactional to maintain the transactional data dictionary. It also must be linked statically into the server, as dynamic plugins are registered in a mysql.plugin table, which is hard to access without data dictionary. This can be done by adding MANDATORY as an argument to MYSQL_ADD_PLUGIN in the CMakeLists.txt for the SE.

Internal ID assignment

The main task for the DDSE is to know how to access the DD tables without needing to refer to the DD. That is achieved by the DDSE hardcoding its internal IDs and any other needed data for the DD schema metadata. To get an ID, handler::get_se_private_id is called for each DD table, allowing the DDSE to update the passed dd::Table object to set the private IDs. For example:

dd_table->set_se_private_id(next_dd_index_id++);
for (auto *index : *dd_table->indexes()) {
  auto &properties = index->se_private_data();
  properties.set("index_id", next_dd_index_id++);
}

The create table implementation can then access this metadata to use the allocated IDs.

One question may arise, why bother going through get_se_private_id at all? Can't the CREATE TABLE code recognize that it's being called for a DD table, allocate the ID itself, and store it in the passed dd::Table object there? In principle, yes, it could, but: 1) there are asserts in the server layer that the DD tables have their SE private data set; 2) modifying passed dd::Table objects for handler CREATE TABLE is only allowed for HTON_SUPPORTS_ATOMIC_DDL engines, while there is no requirement for a DDSE to be one.

Handlerton API for DDSEs

There are several handlerton APIs that a DDSE must implement:

dict_get_server_version, dict_set_server_version: read and write the server of the DD schema in a SE-specific way. Called by the server layer during upgrades.
ddse_dict_init: the name suggests that it performs any DD initialization as needed in the DDSE. But it is also tasked with returning any extra SE-specific DD tables and tablespaces (in the InnoDB sense) back to the server DD layer. It also has a mode argument, with four possible values. While it may appear daunting needing to implement DDSE initialization in four different ways, in MyRocks this function does nothing. Not having SE-specific tables and tablespaces helps.
dict_recover: also perform any DD initialization in the DD, governed by the mode argument. The name and docs suggest that these are recovery-related actions. In MyRocks this function does nothing.
dict_register_dd_table_id: allows the DDSE to note which DD object IDs belong to DD tables. It is up to the engine to decide how to use this information. For instance, MyRocks checks whether a DROP TABLE and similar operations. are not executed against a DD table, for defense in depth.
is_dict_read_only: if the SE has a read only mode (InnoDB does), this allows telling the server layer when the DD can be read but not updated. Otherwise, return false; is a great implementation.
dict_cache_reset_tables_and_tablespaces: tells the DDSE to invalidate any cached metadata about tables and tablespaces, because it is going to be reloaded anew. This call happens in the server startup sequence right after binlog crash recovery.
dict_cache_reset: remove metadata for a single table from any internal SE caches. Called in upgrades. No-op in MyRocks, because there is no need to do any DDSE upgrades in MyRocks yet.

Durability for DD transactions

I have previously discussed SE durability implementation, and it touches upon DD transactions: if a transaction involves DD, it should be persisted regardless of any reduced durability settings (MyRocks implementation). While reduced durability for user transactions may result in lost data for the last transactions before a crash, in the case of DD it may result in desynced DD, making the whole instance nonviable, which I strongly suspect was the cause for this requirement.

Non-locking reads for user transactions for the DD tables

INFORMATION_SCHEMA views provide read-only access to the underlying DD tables for users. Now this access must be non-blocking, both ways: a DDL statement in progress such as ALTER TABLE must not block any INFORMATION_SCHEMA queries and conversely an INFORMATION_SCHEMA query, if done in a session with a high transaction isolation level, must not block any DDL statements from proceeding.

That is implemented by a new parameter for handler::extra method: HA_EXTRA_NO_READ_LOCKING. Having received this, the SE should skipping acquiring read locks, if any, for the handler's table. MyRocks implementation: setting, reading. For InnoDB implementation, you can take a look at WL#7446 (InnoDB: provide a way to do non-locking reads), note that the parameter was named differently there.

Attachable transactions

All the DD accesses are wrapped by DD transactions. These DD transactions differ from the regular user transactions in that they can and will happen in the middle of those regular transactions. The server layer takes care of starting and stopping them, and of swapping the transaction context in the thread handle. The storage engine must be prepared to handle this swapped context correctly, that is, if it observes THD::ha_data == nullptr in the middle of a transaction, it should allocate a new object to this field, and invalidate any cached assumptions about its contents. Once that is ensured, add HA_ATTACHABLE_TRX_COMPATIBLE to handler table_flags return value bitset. The server implementation design is WL#8003 (Server support for attachable transactions) and the InnoDB implementation is WL#7828.

Changes to the core server

The server assumes several things about the DDSE, and also that there is exactly one engine involved.

What the DDSE is
Early initialization of static plugins
READ COMMITTED and READ UNCOMMITTED
Foreign keys
Implicit primary keys
TABLESPACE support
handler::delete_row is bug-for-bug compatible with InnoDB implementation
Duplicate entry or lock wait timeout?
Supporting DD tables in multiple SEs
Changing the DDSE of an existing instance
Clone plugin compatibility

What the DDSE is

The DD layer is full of ha_resolve_by_legacy_type(thd, DB_TYPE_INNODB) calls for every time it needs to get the DDSE handlerton. Naturally, you'll have to grep all of sql for DB_TYPE_INNODB and refactor out some helper function to return the right handlerton for you. I wish grepping only sql/dd were enough, but there is sql/resourcegroups too.

That's not all. SQL definitions assume the engine too, the default constructor of Object_table_impl adds a hardcoded "ENGINE=InnoDB" string, which will need patching.

Early initialization of static plugins

Linking the DDSE statically into the server binary is not enough to ensure it is initialized early enough. The initialization of early plugins is handled by plugin_register_builtin_and_init_core_se in sql/plugin.cc. It has a loop where it iterates over all known at this stage plugins and initializes some of them. You'll have to add logic to initialize your SE as well.

Transaction isolation level requirements

The main level used in DD operations is READ COMMITTED. Luckily, this is sort of a "default" isolation level for transactional engines, and even if the SE implements something else, like snapshot isolation, it still has to map that to the levels MySQL understands, likely to READ COMMITTED again.

But, the server data dictionary also expects the DDSE to support READ UNCOMMITTED. Even though I spent better part of the year working on this, I still don't fully understand the reason behind it (any transaction can read its own uncommitted changes in any isolation level in every storage engine I know about, and reading uncommitted changes from other transactions for correctness looks suspicious). I have tried an experiment where I patched the server to always use READ COMMITTED with InnoDB still being the DDSE, to see what breaks. And what broke was the foreign key operations but only when there was more than one of them in a single ALTER TABLE. You have to evaluate how much of a showstopper that is for you, and whether go back to the SE and implement READ UNCOMMITTED, or patch the server to always use READ COMMITTED with DDSE.

A further complication here is that a SE may or may not support atomic DDL (handlerton flag HTON_SUPPORTS_ATOMIC_DDL), and the DDSE could go either way. If it does not, then there are interim DD transaction commits in the middle of a DDL statement, complicating this picture. I only worked on the case where the DDSE does not support atomic DDL.

Last but not least, if the unsupported isolation levels are strictly rejected, users may find it surprising that their DDL statements fail if their session uses one of the unsupported levels, for instance, if there is another engine for user tables that does support them. Consider accepting an unsupported level for the scope of any DDL statements. I am not fully certain why issue this happens at all – the attachable transactions should run in one of the accepted levels in the DDSE – but maybe the DDSE gets to see the DDL statement in an outer transaction first.

Foreign keys

The DD table definitions in .cc files under sql/dd/impl/tables feature many foreign keys. If your SE does not support them, you'll glad to learn that the DD schema has no referential actions, so the keys effectively serve as defense-in-depth against incorrect operations, but their presence is optional, and omitting their definitions works just fine. This commit also handles the hardcoded "ENGINE=InnoDB" discussed above and the next two items.

Implicit primary keys

Some of the DD tables, for example mysql.index_column_usage, have no explicit primary keys declared. But, they do have several unique non-NULL indexes, one of which is promoted to a primary key. As an implementation detail, it might be hard (and brittle) to track this promotion logic to repeat this decision correctly in the SE. If that's the case, patch the SQL definitions to make one of those unique keys a primary key explicitly.

`TABLESPACE` support

All the DD tables are created in the mysql tablespace. If your SE has no notion of tablespaces, patch the SQL definitions to remove this.

`delete_row` bug-for-bug compatibility with the DD-InnoDB interface

This was a fun one. MySQL deletes rows through handler::delete_row(const uchar *buf), where buf points to either record[0] or record[1]. But, the reference implementation InnoDB does not even look at what the buf is and relies on a pre-positioned cursor to delete the row. And the DD layer added a new call to delete_row that always passes buf == record[1], pointing to a memory block of zeros. Benign with InnoDB, anything can happen with another engine. Check sql/dd/impl/raw/raw_record.cc, so that your Raw_record::update passes m_table->record[0] instead of m_table->record[1].

Duplicate entry or lock wait timeout

The data dictionary holds the statistics tables, which usually have higher volume of writes than the rest of DD tables. They are not typical DD tables also in that it is possible, and legal, to write statistics data for the same object from two different threads, concurrently. One of them will win the race and the other may get ER_DUP_ENTRY error, which is then silently discarded.

The above is true if InnoDB is the DDSE. Other engines may handle duplicate index entries by returning a different error, for example, MyRocks returns ER_LOCK_WAIT_TIMEOUT instead and cannot be easily made to return ER_DUP_ENTRY. Thus the server has to accommodate by silently discarding either error.

Supporting DD tables in multiple SEs

While only one storage engine can host the data dictionary, it still makes sense for other storage engines to be able to have their own additional private tables in the DD, as discussed below in the context of InnoDB. This requires some changes in the server layer:

Handlerton method dict_register_dd_table_id needs to be called for the correct SE. The DD layer can no longer assume the engine of a DD table, and must get the handlerton for the dict_register_dd_table_id call from the table object.
dict_init, dict_recover, and dict_cache_reset_tables_and_tablespaces, need to be called for all participating SEs. Their implementations, especially for the first two, must handle both cases of the SE being the DDSE and not being one. But note that this code will have to be further adjusted if the DDSE change needs to be supported on existing instances.

Changing the DDSE of an existing instance

If you want to change the DDSE on existing instances, you'll have to design and write the code to perform this change. I was not the main author of this part of the project, thus I'll skip discussing it.

Clone

Technically not the core server, but a plugin. Clone is an InnoDB-centric plugin, but if your storage engine already supports clone, then almost everything will work. One thing that will not, without a larger patch for clone than I thought was worth developing, is clone rollbacks on startup. This does not happen on successful clones, and a failed clone instance with a non-InnoDB DDSE will just fail in a different way.

Changes to InnoDB

It is hard to blame InnoDB for not working straight out of the box when it does not store the data dictionary. Several changes need to be made there:

Rewrite InnoDB-specific INFORMATION_SCHEMA DD view implementations
Allow InnoDB to register its DD tables even when it's not the DDSE
Mark DDL transactions as read-write
Register DDL transactions with server 2PC coordinator
Allowing UNDO tablespace truncation to run without RW workload

Reimplementing InnoDB-specific `INFORMATION_SCHEMA` DD views.

There are several INFORMATION_SCHEMA views that are specific to InnoDB, that is, they are not server DD-layer views over DD tables: INNODB_TABLES, INNODB_TABLESPACES, INNODB_DATAFILES, INNODB_COLUMNS, INNODB_VIRTUAL. InnoDB implements those views by opening the underlying DD tables using low-level InnoDB APIs. Since those tables are in a different engine now, the views have to be reimplemented (main commit, fix 1, fix 2) using server layer APIs, luckily they are rather straightforward to use:

template <typename O, typename F>
int for_all_innodb_objects_in_dd(THD *thd, F f) {
  auto *const dc = dd::get_dd_client(thd);
  const dd::cache::Dictionary_client::Auto_releaser releaser(dc);
  std::vector<const O *> dd_objs;
  if (dc->fetch_global_components(&dd_objs)) {
    return 1;
  }

  for (const auto *obj : dd_objs) {
    if (obj->engine() != innobase_hton_name) continue;

    const auto ret = f(thd, *obj);
    if (ret) return ret;
  }

  return 0;
}

where the first template argument is dd::Table or dd::Tablespace and f reads the needed information and fill out the result row, possibly also looking into partitions, if needed:

[&tables](THD *thd, const dd::Table &table) {
  const auto table_id = table.se_private_id();
  if (dict_sys_t::is_dd_table_id(table_id)) return 0;

  if (table_id != dd::INVALID_OBJECT_ID) {
    fill_i_s_innodb_tables_row(thd, *tables, table_id);
  } else {
    assert(!table.leaf_partitions().empty());
    for (const auto *partition : table.leaf_partitions()) {
      assert(partition->engine() == innobase_hton_name);
      const auto table_id = partition->se_private_id();
      if (table_id != dd::INVALID_OBJECT_ID)
        fill_i_s_innodb_tables_row(thd, *tables, table_id);
    }
  }
  return 0;
}

InnoDB-specific DD tables

A storage engine should be allowed to have its private DD tables even if it is not the DDSE of the instance. InnoDB in particular needs its own tables in mysql schema: innodb_dynamic_metadata, innodb_table_stats, innodb_index_stats, and innodb_ddl_log. They get created and registered in ddse_dict_init handlerton method implementation and the handler method get_se_private_data will be called for them too.

There's more to it if DDSE changes need to be considered. The private data for DD tables in InnoDB is table ID, index ID, B-tree root page ID, etc. The first two are assigned sequentially. If InnoDB is not the DDSE, a naive implementation would give out the IDs that belong to different tables when InnoDB is the DDSE. To avoid this can of worms, it is prudent to play it safe and make sure that in both cases the same IDs are assigned to the private InnoDB DD tables. Last but not least, one of the InnoDB private data pieces assigned in advance is the B-tree root page ID, which must be in sync to what the underlying code actually will do at a later point. And that varies with innodb_page_size, so make sure to test and handle all possible settings for this system variable. Everything discussed in this paragraphs is handled here and also in dict_table_build_def with a btr_create debugging counterpart.

Mark DDL transactions as read-write

For some DDL operations, InnoDB did not bother marking their transactions as read-write in the DDL implementation proper, because the operation involved a write to a DD table in InnoDB anyway, taking care of that RW bit. With the DD table being in another SE, the DDL transaction incorrectly becomes RO in InnoDB, which had the effect of skipping the prepare call in the 2PC, breaking crash recovery. The existing handler::mark_trx_read_write fails to work here, being a private method. I ended up a copy-paste-shortening it to a new helper ha_innodb::mark_trx_rw, which is called from the various InnoDB DDL entry points (commit):

/** Mark transaction as read-write in the server layer.
Normally this is handled by handler::mark_trx_read_write, but when InnoDB is not
the DDSE (and transactions do not call that method many times), the few
remaining calls happen too early, when the transaction is not started yet, or
with an incompatible table_share value.
@param  thd     thread handle */
void mark_trx_rw(THD &thd) {
  auto &ha_info = thd.get_ha_data(innodb_hton_ptr->slot)->ha_info[0];
  if (ha_info.is_started())
    ha_info.set_trx_read_write();
}
// ...
if (!innobase_is_ddse()) {
  mark_trx_rw(*thd);
}

Register DDL transactions with server 2PC coordinator

With binlog disabled, when InnoDB is the DDSE, and it is, for example, creating an InnoDB table, it correctly assumes that it is the sole engine in this transaction and does not bother to register itself with the server 2PC coordinator. This assumption does not hold when InnoDB is asked to create its own table when it's not InnoDB.

Fixing this consists of adding numerous instances of

if (!innobase_is_ddse()) {
  innobase_register_trx(hton, thd, trx);
}

to the InnoDB DDL entry points (same commit as for the previous item).

GTID persistence with binlog disabled

If binlog is disabled, InnoDB persists the transaction GTID info itself. This logic needed some tweaking.

Allowing UNDO tablespace truncation to run without RW workload

With DDSE being another engine, InnoDB purge may get stuck and leave undo tablespaces untruncated. After I recovered from the initial "huh? Why is there a relationship between purge and DD?", I found that undo tablespace truncation runs only if there is something to purge (makes sense, doesn't it?). In the case of executing DROP UNDO TABLESPACE, InnoDB the DDSE would execute some read-write DD transactions, potentially creating something to purge. If it's not the DDSE, no actual writes to InnoDB happen, it has nothing to purge, consequently never getting to the undo tablespace truncation and the final drop.

One way to address this is to call trx_purge_truncate_undo_spaces from srv_do_purge. Doing that unconditionally if InnoDB is not the DDSE works, but might be less than optimal.

Testing

If your branch of MySQL needs to support both InnoDB and your SE as DDSEs, one way to do MTR testing for DDSE regressions is to have a server variable to choose the DDSE (which is likely what you'll already have if you need two support both DDSEs), and some MTR include files to skip some tests with one or the other choice. Then adding --mysqld=--ddse-var=non-default-one to MTR invocations will do the trick. Any failing tests then can be saved separately with --initialize --mysqld=--ddse-var in their option files and added as regression tests to the main testsuite runs.

Conclusion

A checklist for everything discussed above:

Changes to the SE to make it able work as the DDSE
- be transactional
- be linked statically into the server
- know how to assign any internal IDs to DD tables
- implement the handlerton API for DDSEs
- make DD transactions durable
- be able to do non-locking reads
- support attachable transactions
Changes to the core server layer which no longer can assume InnoDB being the DDSE
- What the DDSE is
- Early initialization of static plugins
- READ COMMITTED and READ UNCOMMITTED
- Foreign keys
- Implicit primary keys
- TABLESPACE support
- handler::delete_row is bug-for-bug compatible with InnoDB implementation
- Duplicate entry or lock wait timeout?
- Supporting DD tables in multiple SEs
- Changing the DDSE of an existing instance
- Clone plugin compatibility
Changes to InnoDB so it can keep on working while not being the DDSE
- Rewrite InnoDB-specific INFORMATION_SCHEMA DD view implementations
- Allow InnoDB to register its DD tables even when it's not the DDSE
- Mark DDL transactions as read-write
- Register DDL transactions with server 2PC coordinator
- Allowing UNDO tablespace truncation to run without RW workload
Testing

Reference

Oracle design documents:

WL#6380 (Formulate framework for API for DD)
WL#6394 (Bootstrap code for new DD)
WL#6599 (New Data Dictionary and I_S integration)
WL#7464 (InnoDB: provide a way to do non-locking reads)
WL#7828 (InnoDB: attachable transaction support)
WL#8003 (Server support for attachable transactions)

Blog posts:

https://dev.mysql.com/blog-archive/bootstrapping-the-transactional-data-dictionary/

A MySQL 9.1.0 branch with patches

2024-10-22T16:18:00.000+03:00

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:

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

Building and testing MySQL 8.0.40 / 8.4.3 / 9.1.0 on macOS

2024-10-18T17:03:00.000+03:00

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:

XCode 16 AddressSanitizer started catching ODR violations, resulting in Bug #116372 ODR violation reported by AddressSanitizer for plugin variables
LLVM 18 UndefinedBehaviorSanitizer also started diagnosing something that was not caught by earlier versions: Bug #116396 LLVM 18 UBSan errors on server bootstrap

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.

Emacs Projectile Tweaks for MySQL Development

2024-10-10T16:06:00.000+03:00

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.

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

2024-09-06T17:09:00.001+03:00

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.

A README for my MySQL development tooling

2024-08-23T16:01:00.000+03:00

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.

A MySQL 9.0.0 branch with patches

2024-07-09T16:11:00.000+03:00

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:

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

Building and testing MySQL 8.0.38 / 8.4.1 / 9.0.0 on macOS

2024-07-04T16:52:00.000+03:00

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

#113260: Client error in error log: MY-004031 - The client was disconnected …
#113023: mysql-test/include/wait_for_pattern_in_file.inc incompatible with Homebrew Perl
#113189: ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails
#113190: Several tests in routertest_integration_routing_sharing_constrained_pools fail
#114888: Test innodb.parallel_read_kill always fails
#113709: StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error, which is a duplicate of #109540, fixed in 8.1.0 only.
#113722: Test main.index_merge_innodb failing with a result difference
#114892: Test XComControlTest.SuspectMemberFailedRemovalDueToMajorityLoss fails
#113665: perfschema.relaylog fails with a result difference - reproduced only on 8.4.1. I guess the test is still flaky.
#113703: Test innodb.ddl_kill fails under Sanitizer build - reproduced on 8.4.1 and 9.0.0 but not 8.0.38.
#113710: Spec/HttpClientSecureTest.ensure/default_client_cipher_succeeds ASan error

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.

Implementing a Table-Returning MySQL Function

2024-06-14T16:20:00.001+03:00

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.

A MySQL 8.4.0 branch with patches

2024-05-28T18:16:00.003+03:00

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

Building and testing MySQL 8.0.37 & 8.4.0 on macOS

2024-05-08T16:52:00.001+03:00

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:

https://bugs.mysql.com/bug.php?id=113257 (Assertion on rpl.rpl_replication_observers_example_plugin_ongoing_transaction)
https://bugs.mysql.com/bug.php?id=113258 (TLS 1.3 tests fail)
https://bugs.mysql.com/bug.php?id=113420 (testNdbProcess fails under ASan+UBSan)
https://bugs.mysql.com/bug.php?id=113736 (Test main.qualify_hypergraph failing with a result difference)

New test failures:

https://bugs.mysql.com/bug.php?id=114888 (Test innodb.parallel_read_kill always fails)
https://bugs.mysql.com/bug.php?id=114892 (Test XComControlTest.SuspectMemberFailedRemovalDueToMajorityLoss fails)
https://bugs.mysql.com/bug.php?id=114894 (Test rpl.rpl_log_info_repository_persistence_require_row failing)
https://bugs.mysql.com/bug.php?id=114896 (Several tablespace encryption tests failing)

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.

https://bugs.mysql.com/bug.php?id=113189 (ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails)
https://bugs.mysql.com/bug.php?id=113190 (Several tests in routertest_integration_routing_sharing_constrained_pools fail)
https://bugs.mysql.com/bug.php?id=113260 (Client error in error log: MY-004031 - The client was disconnected …)
https://bugs.mysql.com/bug.php?id=113665 (perfschema.relaylog fails with a result difference)
https://bugs.mysql.com/bug.php?id=113702 (Test binlog.binlog_error_action fails under Sanitizer build)
https://bugs.mysql.com/bug.php?id=113703 (Test innodb.ddl_kill fails under Sanitizer build) - the test now fails under debug build without sanitizer too.
https://bugs.mysql.com/bug.php?id=113704 (Test main.log_backtrace fails under Sanitizer build), but now main.log_backtrace_debug fails too, added it under the same bug.
https://bugs.mysql.com/bug.php?id=113709 (StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error), which is a duplicate of #109540, which is fixed in 8.1.0 only. There is a comment that unit tests ASan errors are not a high priority error, it's just a tiny harmless heap-buffer-overflow /s.
https://bugs.mysql.com/bug.php?id=113710 ( Spec/HttpClientSecureTest.ensure/default_client_cipher_succeeds ASan error) - still fails on 8.0.37 but not 8.4.0. The latter shows an UBSan error though.
https://bugs.mysql.com/bug.php?id=113722 (Test main.index_merge_innodb failing with a result difference)

I did not re-test two bugs:

https://bugs.mysql.com/bug.php?id=113023 (mysql-test/include/wait_for_pattern_in_file.inc incompatible with Homebrew Perl)
https://bugs.mysql.com/bug.php?id=113113 (Build failure with Homebrew LLVM 14-17 on macOS), where I keep using the workaround of setting CMAKE_AR to Homebrew ar.

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?

https://bugs.mysql.com/bug.php?id=106737 (Wrong SE called to ack clone application if >1 SE) - nope.
https://bugs.mysql.com/bug.php?id=106750 (Some clone tests needlessly marked as Linux-only) - nope.
https://bugs.mysql.com/bug.php?id=107715 (Multiple SE clone not calling clone_end after mixed clone_begin failure/success) - nope.
https://bugs.mysql.com/bug.php?id=113123 (Compilation fails with LLVM 17 and XCode 15.3) - nope.

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.

Introducing patch2testlist for MySQL development

2024-01-30T16:51:00.001+02:00

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.

Quick-and-dirty mode that does not handle included files:
```
$ ./mtr `git diff | patch2testlist` ...
```
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!

Building and testing MySQL 8.0.36 and 8.3.0 on macOS

2024-01-24T16:04:00.001+02:00

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:

https://bugs.mysql.com/bug.php?id=113665 (perfschema.relaylog fails with a result difference)
https://bugs.mysql.com/bug.php?id=113702 (Test binlog.binlog_error_action fails under Sanitizer build)
https://bugs.mysql.com/bug.php?id=113703 (Test innodb.ddl_kill fails under Sanitizer build)
https://bugs.mysql.com/bug.php?id=113704 (Test main.log_backtrace fails under Sanitizer build)
https://bugs.mysql.com/bug.php?id=113709 (StrXfrmTest.ChineseUTF8MB4 failing with an AddressSanitizer error)
https://bugs.mysql.com/bug.php?id=113710 (Spec/HttpClientSecureTest.ensure/default_client_cipher_succeeds ASan error)
https://bugs.mysql.com/bug.php?id=113722 (Test main.index_merge_innodb failing with a result difference)
https://bugs.mysql.com/bug.php?id=113736 (Test main.qualify_hypergraph failing with a result difference)

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.

MySQL clone plugin internals and MyRocks clone design

2024-01-11T17:16:00.003+02:00

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!

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

2023-12-14T17:45:00.003+02:00

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:

https://bugs.mysql.com/bug.php?id=113189 (ColumnStatisticsTest.StoreAndRestoreAttributesEquiHeight unit test fails)
https://bugs.mysql.com/bug.php?id=113190 (Several tests in routertest_integration_routing_sharing_constrained_pools fail)
https://bugs.mysql.com/bug.php?id=113420 (testNdbProcess fails under ASan+UBSan)
https://bugs.mysql.com/bug.php?id=113421 (HistogramsTest.DecimalSingletonToJSON fails under sanitizer build)
https://bugs.mysql.com/bug.php?id=113422 (Handler_test.TableCreateReturnsRecordFileFullWhenTempTableAllocatorThrowsRecordF) - the test name is longer than the bug title length limit.

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!

Strong typing: comparing Rust newtype to C++

2023-10-23T18:14:00.000+03:00

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.

Implementing durability in a MySQL storage engine

2023-09-25T15:31:00.005+03:00

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:

InnoDB comes up, and performs its own recovery from its redo log.
Server data dictionary is initialized.
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:

https://bugs.mysql.com/bug.php?id=112456

Bugs found while writting this:

https://bugs.mysql.com/bug.php?id=75519

Bugs that made me write this:

https://github.com/facebook/mysql-5.6/issues/1370

MySQL Build Times: Use Ninja

2023-08-25T16:02:00.000+03:00

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.

Laurynas Biveinis

Building and testing MySQL 9.7.0 early access build on macOS

Build

Tests

Bad news

No news

Good news

One-off issues

Boring but useful: making large-scale changes on MySQL codebase with LLMs

Building and testing MySQL 8.0.45, 8.4.8, and 9.6.0 on macOS

Build

Test

Conclusion

Addendum

Look at me, I'm an (LLM-assisted) Emacs Lisp developer now

Building and testing MySQL 8.0.44 / 8.4.7 / 9.5.0 releases on macOS

Build

Tests

Conclusion

Building and testing MySQL 8.0.42 / 8.4.5 / 9.3.0 on macOS

Build

Test

Conclusion

Building and testing MySQL 8.0.41 / 8.4.4 / 9.2.0 on macOS

Build

Test

Conclusion

Storing the MySQL Data Dictionary in a 3rd-Party Storage Engine

Changes to the SE to make it DDSE

Internal ID assignment

Handlerton API for DDSEs

Durability for DD transactions

Non-locking reads for user transactions for the DD tables

Attachable transactions

Changes to the core server

What the DDSE is

Early initialization of static plugins

Transaction isolation level requirements

Foreign keys

Implicit primary keys

TABLESPACE support

delete_row bug-for-bug compatibility with the DD-InnoDB interface

Duplicate entry or lock wait timeout

Supporting DD tables in multiple SEs

Changing the DDSE of an existing instance

Clone

Changes to InnoDB

Reimplementing InnoDB-specific INFORMATION_SCHEMA DD views.

InnoDB-specific DD tables

Mark DDL transactions as read-write

Register DDL transactions with server 2PC coordinator

GTID persistence with binlog disabled

Allowing UNDO tablespace truncation to run without RW workload

Testing

Conclusion

Reference

A MySQL 9.1.0 branch with patches

Building and testing MySQL 8.0.40 / 8.4.3 / 9.1.0 on macOS

Build

Test

Totals

Newer tooling

Valgrind

One-off issues that I couldn't handle better

Conclusion

Emacs Projectile Tweaks for MySQL Development

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

A README for my MySQL development tooling

My MySQL Development Tools

Tool configuration

Could be useful to you

Useful to me

zsh function library

No longer maintained (patches welcome)

A MySQL 9.0.0 branch with patches

Building and testing MySQL 8.0.38 / 8.4.1 / 9.0.0 on macOS

Implementing a Table-Returning MySQL Function

Exploring the Codebase

Function class

Patching the parser

`TABLESPACE` support

`delete_row` bug-for-bug compatibility with the DD-InnoDB interface

Reimplementing InnoDB-specific `INFORMATION_SCHEMA` DD views.

InnoDB: `handlerton::commit`

InnoDB: `handlerton::prepare`

InnoDB: `handlerton::flush_logs`