lp:maria

Author: Oleksandr "Sanja" Byelkin
Author Date: 2022-05-11 15:11:49 UTC

MDEV-5215 prerequisite: remove test and test_* database hacks in the test suite

Author: Julius Goryavsky
Author Date: 2022-05-19 02:53:32 UTC

MDEV-28423 continuation: Galera progress reporting + rolling updates

A new feature to support progress reporting (including progress
reporting in the JSON format) that was added in 10.9 can crash the
rolling update scenario if the old version donor node does not pass
the "total" tag in the MAGIC_FILE to the joiner node. The "grep"
utility that is used to extract this tag in this case will return
an empty string, which will then be substituted into the "if"
statement, resulting in a syntactically invalid construct.

This commit corrects this issue.

Also, when using the previous fixes (from MDEV-28423 and from
MDEV-28593), authentication errors are possible in case of mixing
versions at the opposite sides - if the donor is newer than joiner,
due to incorrect reading of the new MAGIC_FILE format by older node
versions (from the joiners side). This problem is also fixed here,
taking advantage of the fact that older versions are still able
to read GUID coordinates even if multiple strings are passed to
them at the end.

In addition, the new progress reporting exchanges the length of
the transmitted data between the donor and the joiner before the
start of the transfer, but does not use this information on the
joiner side, which can lead to incorrect calculation of progress
by the joiner (not synchronous with calculations from the donor
side) if from the joiner side reporting works in compatibility
mode with older versions. This commit moves the construction
of the command line for progress reporting to a point where
we already know the size and can use that knowledge.

Also, to facilitate SST script support in the future, the
script code now has been made unified between all versions
from 10.3 to 10.9.

Author: Julius Goryavsky
Author Date: 2022-05-19 01:45:19 UTC

MDEV-28423 continuation: Galera progress reporting + rolling updates

A new feature to support progress reporting (including progress
reporting in the JSON format) that was added in 10.9 can crash the
rolling update scenario if the old version donor node does not pass
the "total" tag in the MAGIC_FILE to the joiner node. The "grep"
utility that is used to extract this tag in this case will return
an empty string, which will then be substituted into the "if"
statement, resulting in a syntactically invalid construct.

This commit corrects this issue.

Also, when using the previous fixes (from MDEV-28423 and from
MDEV-28593), authentication errors are possible in case of mixing
versions at the opposite sides - if the donor is newer than joiner,
due to incorrect reading of the new MAGIC_FILE format by older node
versions (from the joiners side). This problem is also fixed here,
taking advantage of the fact that older versions are still able
to read GUID coordinates even if multiple strings are passed to
them at the end.

In addition, the new progress reporting exchanges the length of
the transmitted data between the donor and the joiner before the
start of the transfer, but does not use this information on the
joiner side, which can lead to incorrect calculation of progress
by the joiner (not synchronous with calculations from the donor
side) if from the joiner side reporting works in compatibility
mode with older versions. This commit moves the construction
of the command line for progress reporting to a point where
we already know the size and can use that knowledge.

Also, to facilitate SST script support in the future, the
script code now has been made unified between all versions
from 10.3 to 10.9.

Author: Oleg Smirnov
Author Date: 2022-05-18 14:36:32 UTC

MDEV-24837 HAVING clause yields wrong results

Set items references to point to the temporary table fields
when processing aggregate operations

Author: Kiyoshi Takeda
Author Date: 2022-05-18 12:49:24 UTC

MDEV-14642 Assertion 'table->s->db_create_options == part_table->s->db_create_options' failed in compare_table_with_partition

When trying to execute ALTER TABLE EXCHANGE PARTITION with different definitions, assertion

    table->s->db_create_options == part_table->s->db_create_options

failed in compare_table_with_partition().
However, this execution should not be allowed since executing 'exchange partition' requires the identical structure of the two tables.

To fix the problem, I deleted the assertion code and added code that returns an error that indicates tables have different definitions.

Author: Sergei Golubchik
Author Date: 2022-05-18 11:11:16 UTC

MDEV-28606 Server crashes in st_select_lex::add_table_to_list instead of error 1066: Not unique table/alias

10.2-only fix, 10.3+ uses LEX_STRING's and checks the length first

Author: Vlad Lesin
Author Date: 2022-05-18 11:09:40 UTC

MDEV-28607 add option to mtr to start mariabackup under rr

The new --rr-backup mtr option is added.

Author: Oleg Smirnov
Author Date: 2022-05-16 11:32:39 UTC

MDEV-27366 SIGSEGV in handler_index_cond_check on SELECT in connection with rowid_filter setting

Cause: when optimizer picks a full table scan it does not reset previously
chosen rowid filter.
Solution: reset rowid filter for full table scan in best_access_path()

Author: Julius Goryavsky
Author Date: 2022-05-17 21:49:13 UTC

MDEV-28583: Galera: binlogs disappear after rsync IST

This commit sends a flag indicating the presence of the "--bypass"
option from the donor node to the joiner nodes during rsync IST,
because without such a flag it is impossible to distinguish IST
from the SST on the joiner nodes (in IST/SST scripts, because the
"--bypass" option is still not passed to scripts from server code).
Specifically, this fixes an issue with binary logs disappearing
after IST (via rsync). There are also changes to diagnostic messages
here that will make it easier to diagnose script-related problems
in the future when debugging and when checking the logs. This commit
also adds more robust signal handlers - to handle exceptions during
script execution. These handlers won't mask some crashes and it
also unifies exit codes between different scripts. These changes
have already been helpful to debugging "bypass" flag handling.

Author: Julius Goryavsky
Author Date: 2022-05-17 21:43:32 UTC

MDEV-28583: Galera: binlogs disappear after rsync IST

This commit sends a flag indicating the presence of the "--bypass"
option from the donor node to the joiner nodes during rsync IST,
because without such a flag it is impossible to distinguish IST
from the SST on the joiner nodes (in IST/SST scripts, because the
"--bypass" option is still not passed to scripts from server code).
Specifically, this fixes an issue with binary logs disappearing
after IST (via rsync). There are also changes to diagnostic messages
here that will make it easier to diagnose script-related problems
in the future when debugging and when checking the logs. This commit
also adds more robust signal handlers - to handle exceptions during
script execution. These handlers won't mask some crashes and it
also unifies exit codes between different scripts. These changes
have already been helpful to debugging "bypass" flag handling.

Author: Julius Goryavsky
Author Date: 2022-05-16 12:17:15 UTC

rnew

Author: midenok
Author Date: 2022-05-16 13:11:20 UTC

MDEV-27328 MSAN fix

Author: Sergey Petrunia
Author Date: 2022-05-14 17:19:05 UTC

Code cleanup in/around check_interleaving_with_nj()

- In best_extension_by_limited_search(), do not check for
  "(remaining_tables & real_table_bit)", it is guaranteed to be true.
  Make it an assert.
- In (!idx || check_interleaving_with_nj())", remove the !idx part.
  This check made sense only in the original version of this function.
- "micro optimization" in check_interleaving_with_nj().

Author: Nayuta Yanagisawa
Author Date: 2022-05-14 06:03:12 UTC

MDEV-28564 Remove option value '-1' from spider_bgs_first_read, spider_bgs_mode, and spider_bgs_second_read

Remove the option value '-1', which has been deprecated by MDEV-27169,
of spider_bgs_first_read, spider_bgs_mode, and spider_bgs_second_read.

Author: Sergey Petrunia
Author Date: 2022-05-07 16:10:59 UTC

Optimizer trace: Make ref_optimizer_key_uses[*] show the index name

Author: Daniel Black
Author Date: 2022-05-11 03:28:42 UTC

MDEV-28534: clang-12 compile warnings (innodb)

Solves compile error:

/buildbot/amd64-ubuntu-2004-msan/build/storage/innobase/include/buf0types.h:149:23: error: 'constexpr' non-static member function will not be implicitly 'const' in C++14; add 'const' to avoid a change in behavior [-Werror,-Wconstexpr-not-const]
  constexpr ulonglong raw() { return m_id; }
                      ^
                            const

Author: Brandon Nesterenko
Author Date: 2022-05-10 20:25:35 UTC

MDEV-28530: Revoking privileges from a non-existing user on a master breaks replication on the slave in the presence of replication filters

Problem:
========
Replication can break while applying a query log event if its
respective command errors on the primary, but is ignored by the
replication filter within Grant_tables on the replica. The bug
reported by MDEV-28530 shows this with REVOKE ALL PRIVILEGES using a
non-existent user. The primary will binlog the REVOKE command with
an error code, and the replica will think the command executed with
success because the replication filter will ignore the command while
accessing the Grant_tables classes. When the replica performs an
error check, it sees the difference between the error codes, and
replication breaks.

Solution:
========
If the replication filter check done by Grant_tables logic ignores
the tables, reset thd->slave_expected_error to 0 so that
Query_log_event::do_apply_event() can be made aware that the
underlying query was ignored when it compares errors.

Note that this bug also effects DROP USER if not all users exist
in the provided list, and the patch fixes and tests this case.

Reviewed By:
============
<TODO>

Author: Roman Nozdrin
Author Date: 2022-05-10 14:47:04 UTC

The patch fixes openssl3-related compilation failure found using fresh U22 image

Author: Julius Goryavsky
Author Date: 2022-05-10 06:18:25 UTC

MDEV-28015: Galera: GTID value is missing when mariabackup SST is used

Author: Julius Goryavsky
Author Date: 2022-05-10 06:22:21 UTC

MDEV-28500: Hashicorp: Debian packaging is broken

This commit fixes a documentation installation
issue (for debian packaging) and generally brings
the installation control files up to date (as for
the rest of the components).

Author: Daniel Black
Author Date: 2022-05-10 02:43:28 UTC

deb: limit columnstore to tested platforms

"We only support Debian 10, but not Debian 11 and from today
not Debian 9, and only Ubuntu 18.04 and 20.04, but not 22.04"

Author: Norio Akagi
Author Date: 2022-05-07 21:48:30 UTC

MDEV-27892 Improve an error message for foreign server exists

Improve and add more languages for the ERROR 1476: Foreign Server
already exists.

Author: Monty
Author Date: 2022-05-09 08:36:44 UTC

Simple optimization: Remove JOIN::set_group_rpa as it is not needed

Author: Elena Stepanova
Author Date: 2022-05-07 14:27:39 UTC

Hashicorp debian build/installation test #2

Author: Daniel Black
Author Date: 2022-05-06 02:00:01 UTC

Deb: dh_missing --fail-missing - columnstore

Per man dh_missing, not-installed will exand wildcards
since debhelper 11.1. Since Stretch is on 10.2.5, this won't happen.

As columnstore is still only x86_64 we can use that in the file.

Author: Brandon Nesterenko
Author Date: 2022-04-27 01:51:42 UTC

MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.%

Problem:
========
When replicating SET DEFAULT ROLE, the pre-update check (i.e. that
in set_var_default_role::check()) tries to validate the existence of
the given rules/user even when the targeted tables are ignored. When
previously issued CREATE USER/ROLE commands are ignored by the
replica because of the replication filtering rules, this results in
an error because the targeted data does not exist.

Solution:
========
Before checking that the given roles/user exist of a SET DEFAULT
ROLE command, first ensure that the mysql.user and
mysql.roles_mapping tables are not excluded by replication filters.

Reviewed By:
============
Andrei Elkin <andrei.elkin@mariadb.com>
Sergei Golubchik <serg@mariadb.com>

Author: Brandon Nesterenko
Author Date: 2022-04-27 01:51:42 UTC

MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.%

Problem:
========
When replicating SET DEFAULT ROLE, the pre-update check (i.e. that
in set_var_default_role::check()) tries to validate the existence of
the given rules/user even when the targeted tables are ignored. When
previously issued CREATE USER/ROLE commands are ignored by the
replica because of the replication filtering rules, this results in
an error because the targeted data does not exist.

Solution:
========
Before checking that the given rules/user exist of a SET DEFAULT
ROLE command, first ensure that the mysql.user and
mysql.roles_mapping tables are not excluded by replication filters.

Reviewed By
===========
Andrei Elkin <andrei.elkin@mariadb.com>

Author: Brandon Nesterenko
Author Date: 2022-04-27 01:51:42 UTC

MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.%

Problem:
========
When replicating SET DEFAULT ROLE, the pre-update check (i.e. that
in set_var_default_role::check()) tries to validate the existence of
the given rules/user even when the targeted tables are ignored. When
previously issued CREATE USER/ROLE commands are ignored by the
replica because of the replication filtering rules, this results in
an error because the targeted data does not exist.

Solution:
========
Before checking that the given rules/user exist of a SET DEFAULT
ROLE command, first ensure that the mysql.user and
mysql.roles_mapping tables are not excluded by replication filters.

Reviewed By
============
Andrei Elkin <andrei.elkin@mariadb.com>

Author: Jan Lindström
Author Date: 2022-04-29 06:49:14 UTC

MDEV-24688 : galera.galera_ist_progress MTR failed: assert_grep.inc failed

Grep is not safe as it will confuse if there is more than one
matching line or if progress report is slightly different as
progress reporting is afected by how fast IST is send/received.

Fix is to use sed and we are interested only that there is at
least one progress report from IST.

Author: Marko Mäkelä
Author Date: 2022-04-28 06:01:37 UTC

Merge

Author: Nikita Malyavin
Author Date: 2022-04-27 17:29:03 UTC

fix ha_innobase plugin initialization race with purge table open

Now that purge opens a table very early, at undo node parsing stage,
it slips before plugin initialization ends.
This causes ASAN global-buffer-overflow and segfault.

The problem is that `resolve_sysvars` function that normalizes table
options (used in parse_engine_table_options during table open) is called
after purge starts (srv_start() call in innodb_init()).

To solve this, additional resolve_sysvars call is addid before srv_start
in innodb_init.

Author: Nikita Malyavin
Author Date: 2022-04-26 01:38:14 UTC

add deterministic tests

Author: Marko Mäkelä
Author Date: 2022-04-27 10:16:07 UTC

MDEV-28422 Page split breaks a gap lock

btr_insert_into_right_sibling(): Inherit any gap lock from the
left sibling to the right sibling before inserting the record
to the right sibling and updating the node pointer(s).

lock_update_node_pointer(): Update locks in case a node pointer
will move.

Based on mysql/mysql-server@c7d93c274fdc5c56e36458fa4000fa3a483ffffd

Author: Julius Goryavsky
Author Date: 2022-04-26 13:18:22 UTC

MDEV-28291: Post-review changes

Author: Sergey Petrunia
Author Date: 2022-04-26 12:18:15 UTC

Merge branch '10.2' of github.com:MariaDB/server into 10.2

Author: Sergey Petrunia
Author Date: 2022-04-26 12:18:15 UTC

Merge branch '10.2' of github.com:MariaDB/server into 10.2

Author: Julius Goryavsky
Author Date: 2022-04-25 22:47:24 UTC

MDEV-26171: Post-merge fix

Author: Igor Babaev
Author Date: 2022-04-22 14:53:16 UTC

MDEV-27212 Crash in Item_equal::sort on second execution of stored procedure

This bug could cause a crash of the server at the second call of a stored
procedure when it executed a query containing a mergeable derived table /
view whose specification used another mergeable derived_table or view and a
subquery with outer reference in the select list of the specification.
Such queries could cause the same problem when they were executed for the
second time in a prepared mode.
The problem appeared due to a typo mistake in the legacy code of the function
create_view_field() that prevented building Item_direct_view_ref wrapper
for the mentioned outer reference at the second execution of the query and
setting the depended_from field for the outer reference.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

Author: Jan Lindström
Author Date: 2022-04-25 07:18:50 UTC

MDEV-28377 : galera.galera_as_slave_nonprim bind: Address already in use

Reported error is caused by misconfiguration of node_2. Additionally,
node_4 that should have been non-galera node contained also
incorrect configuration parameters. Removed some sleeps from test
case and replaced them with wait_condition.

Author: Julius Goryavsky
Author Date: 2022-04-22 23:57:35 UTC

MDEV-26171: Post-merge fixes

Author: Julius Goryavsky
Author Date: 2022-04-20 16:35:35 UTC

MDEV-28277: post-review fix

Author: Jan Lindström
Author Date: 2022-04-19 09:02:34 UTC

MDEV-28314 : The Galera cluster primary node goes into hang mode when innodb_encryption_threads is enabled

When we enable writes after Galera SST srv_n_fil_crypt_threads needs
to be set temporally to 0 (as was done when writes were disabled)
to make sure that encryption threads will be really started based
on old value.

Fix provided by Marko Mäkelä and added a test case.

Author: Brandon Nesterenko
Author Date: 2022-04-04 13:35:48 UTC

MDEV-25768: ALTER gtid_slave_pos table storage engine should not be allowed when slave is running.

Problem:
========
The mysql.gtid_slave_pos table can have its engine modified while
the SQL thread is alive.

Solution:
========
Use MDL locks to ensure that the table can only have its
engine modified when the slave SQL thread is not running.

Reviewed By:
============
TODO

Author: Julius Goryavsky
Author Date: 2020-07-13 17:12:00 UTC

MDEV-19281: Plugin implementation for the Hashicorp Vault KMS

- Authentication is done using the Hashicorp Vault's token
  authentication method;
- If additional client authentication is required, then the
  path to the CA authentication bundle file may be passed
  as a plugin parameter;
- The creation of the keys and their management is carried
  out using the Hashicorp Vault KMS and their tools;
- Key values stored as hexadecimal strings;
- Key values caching is supported.
- Implemented a time-invalidated cache for key values and
  for key version numbers received from the Hashicorp Valult
  server;
- The plugin uses libcurl (https) as an interface to
  the HashiCorp Vault server;
- JSON parsing is performed through the JSON service
  (through the include/mysql/service_json.h);
- HashiCorp Vault 1.2.4 was used for development and testing.

Author: Oleg Smirnov
Author Date: 2022-04-17 15:30:31 UTC

MDEV-25994 Crash with union of my_decimal type in ORDER BY clause

During execution of Type_handler_decimal_result::make_sort_key_part()
the call to item->val_decimal_result() may fail and return NULL pointer.
Such case was not analyzed which led to the crash.
This commit adds necessary checks to the case described above and also
adds an indicator of successful execution for make_sort_key() functions
(return value = FALSE for success and TRUE failure)

Author: Nayuta Yanagisawa
Author Date: 2022-04-05 09:20:41 UTC

MDEV-28010 Deprecate spider_crd_type and spider_crd_weight

Deprecate the variables spider_crd_type and spider_crd_weight.
The value should be defined by the engine developers.

Author: Vlad Lesin
Author Date: 2022-04-14 11:27:23 UTC

MDEV-17843 Assertion `page_rec_is_leaf(rec)' failed in lock_rec_queue_validate upon SHOW ENGINE INNODB STATUS

lock_validate() accumulates page ids under locked LockMutexGuard, then
releases the latch, and invokes lock_rec_block_validate() for each page.
Some other thread has ability to add/remove locks and change pages
between releasing the latch in lock_validate() and acquiring it in
lock_rec_validate_page().

lock_rec_validate_page() can invoke lock_rec_queue_validate() for
non-locked supremum, what can cause ut_ad(page_rec_is_leaf(rec)) failure
in lock_rec_queue_validate().

The fix is to invoke lock_rec_queue_validate() only for locked records
in lock_rec_validate_page().

The error message in lock_rec_block_validate() is not necessary as
BUF_GET_POSSIBLY_FREED mode is used to get block from buffer pool, and
this is not error if a block was evicted.

The test case would require new debug sync point. I think it's not
necessary as the fixed code is debug-only.

Author: Marko Mäkelä
Author Date: 2022-04-14 11:30:26 UTC

Merge

Author: Marko Mäkelä
Author Date: 2022-04-14 09:59:59 UTC

MDEV-21423: Replace trx_sys.rw_trx_hash with a locked hash table

The embedded page_hash_latch in buf_pool.page_hash have served us well.
Let us attempt the same approach for trx_sys.rw_trx_hash,
instead of using the lock-free hash table that is unable to shrink.

Compared to the lock-free hash table, any iteration of the entire
hash table, as in rw_trx_hash.for_each() or rw_trx_hash.for_each_until()
may potentially modify a large number of cache lines.
Outside debug checks, trx_sys.rw_trx_hash is being iterated in
ReadView::open(), trx_sys_t::clone_oldest_view(), and
trx_sys_t::get_min_trx_id() (invoked on secondary index lock checks).

To reduce the impact of locking on processors
that support transactional memory, we introduce the predicate
rw_trx_hash_t::empty() so that unnecessary acquisition and releasing
of hash array latches can be avoided when an entire slice of the
hash array is empty.

FIXME: This is reducing throughput in many cases. How to improve this
further?

TODO: Find a reasonable value for the constexpr size_t n_cells.

Author: Oleg Smirnov
Author Date: 2022-04-13 12:56:34 UTC

MDEV-27699 ANALYZE FORMAT=JSON fields are incorrect for UNION ALL queries

UNION ALL queries are a subject of optimization introduced in MDEV-334
when creation of a temporary table is skipped.
While there is a check for this optimization in Explain_union::print_explain()
there was no such in Explain_union::print_explain_json(). This resulted in
printing irrelevant data like:
  "union_result": {
    "table_name": "<union2,3>",
    "access_type": "ALL",
    "r_loops": 0,
    "r_rows": null
in case when creation of the temporary table was actually optimized out.
This commits adds a check whether the temporary table was actually created
during the UNION ALL processing and eliminates printing of the irrelevant data.

Author: Nikita Malyavin
Author Date: 2022-04-13 19:08:06 UTC

MDEV-26281 ASAN use-after-poison when complex conversion involved in blob

The problem existed for long. Item_func_in::create_array allocates `array`
on mem_root but releases it with C++ `delete` in Item_func_in::cleanup().

Allocating on a mem root is incorrect in a case of Item, since it controls
its memory by an independent mechanism, and its lifetime differs from
mem_roots (thd, stmt, table).

It was also incorrect to allocate in_vector::base on mem_root by the same
reasons (also done in in_row).

So far, thd argument has been completely removed from constructors of the
hierarchy, and RAII memory management is favored.

Author: Nikita Malyavin
Author Date: 2021-10-28 23:04:07 UTC

MDEV-26508 heap-use-after-free in Item_default_value::walk on 2nd SP run

The bug is a regression of c47e4aab62c6.

In Item_default_value::enchant_default_with_arg_processor `arg` argument is
set with f_item from values list.

Then on the 2nd execution this item turns out to be freed.

This is because of check_fields() call where value items are subctituted
the temporary ones with thd->change_item_tree().

Solution: we should keep Item_default_value::arg clean after SP run.
Thus, register the change of the item tree. After SP run NULL will be set
back to Item_default_value::arg.

Author: Daniel Black
Author Date: 2022-04-13 05:05:19 UTC

MDEV-27791: rocksdb_log_dir test postfix

We can only remove a subdirectory in mtr on an installed instance

Example failure previously:

CURRENT_TEST: rocksdb.rocksdb_log_dir
mysqltest: At line 15: Path '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/tmp/1' is not a subdirectory of MYSQLTEST_VARDIR '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/1'or MYSQL_TMP_DIR '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/tmp/1'

Author: Daniel Black
Author Date: 2022-04-13 05:05:19 UTC

MDEV-27791: rocksdb_log_dir test postfix

We can only remove a subdirectory in mtr on an installed instance

Example failure previously:

CURRENT_TEST: rocksdb.rocksdb_log_dir
mysqltest: At line 15: Path '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/tmp/1' is not a subdirectory of MYSQLTEST_VARDIR '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/1'or MYSQL_TMP_DIR '/usr/local/mariadb-10.9.0-linux-systemd-x86_64/mysql-test/var/tmp/1'

Author: Julius Goryavsky
Author Date: 2022-04-11 23:01:26 UTC

MDEV-26171: wsrep_sst_receive_address does not parse IPv6 address correctly

This commit fixes problems with parsing ipv6 addresses given via
the wsrep_sst_receive_address and wsrep_node_address options.

Also, this commit removes extra lines in the configuration files
in the mtr test suites for Galera related to these parameters.

Author: Dmitry Shulga
Author Date: 2022-04-11 06:37:55 UTC

MDEV-16128: Server crash in Item_func::print_op on 2nd execution of PS

For some queries that involve tables with different but convertible
character sets for columns taking part in the query, repeatable
execution of such queries in PS mode or as part of a stored routine
would result in server abnormal termination.

For example,
CREATE TABLE t1 (a2 varchar(10));
CREATE TABLE t2 (u1 varchar(10) CHARACTER SET utf8);
CREATE TABLE t3 (u2 varchar(10) CHARACTER SET utf8);
PREPARE stmt FROM
  "SELECT t1.* FROM (t1 JOIN t2 ON (t2.u1 = t1.a2))
   WHERE (EXISTS (SELECT 1 FROM t3 WHERE t3.u2 = t1.a2))";

EXECUTE stmt;
EXECUTE stmt; <== Running this prepared statement the second time
                  results in server crash.

The reason of server crash is that an instance of the class
Item_func_conv_charset, that created for conversion of a column
from one character set to another, is allocated on execution
memory root but pointer to this instance is stored in an item
placed on prepared statement memory root. Below is calls trace to
the place where an instance of the class Item_func_conv_charset
is created.

setup_conds
 Item_func::fix_fields
  Item_bool_rowready_func2::fix_length_and_dec
   Item_func::setup_args_and_comparator
    Item_func_or_sum::agg_arg_charsets_for_comparison
     Item_func_or_sum::agg_arg_charsets
      Item_func_or_sum::agg_item_set_converter
       Item::safe_charset_converter

And the following trace shows the point where a pointer to
the instance of Item_func_conv_charset is passed to the class
Item_func_eq, that is created on a memory root of the prepared
statement.

Prepared_statement::execute
 mysql_execute_command
  execute_sqlcom_select
   handle_select
    mysql_select
     JOIN::optimize
      JOIN::optimize_inner
       convert_join_subqueries_to_semijoins
        convert_subq_to_sj
---
    Item_func_eq *item_eq=
      new (thd->mem_root) Item_func_eq(thd, subq_pred->left_expr_orig,
                                       subq_lex->ref_pointer_array[0]);

On completing execution of the statement EXECUTE stmt
the items placed on execution query arena are released but the instance
of Item_func_eq that was created on permanent memory root still does exist
and has one of its arguments (stored in args[0]) referencing to the released
object. Next time the prepared statement is executed access to dangling
pointer leads to server crash.

So, to fix the issue the item Item_func_conv_charset should be created
on permanent memory root in case a prepared statement or stored routine
is run.

Author: Oleg Smirnov
Author Date: 2022-04-06 12:41:50 UTC

Remove a garbage file from mysql-test

Author: Daniel Black
Author Date: 2022-04-07 04:15:08 UTC

MDEV-27955: main.func_json_notembedded postfix -make big

While attempting to reduce the iterations per Monty's suggestion in
MDEV-27955, I wasn't able to succeed in arriving at a predictable
value.

While some functions like json_merge_patch where predictably within
the limit on small values, other functions like json_array_append
where still significantly fast to generate a result and not
ER_STATEMENT_TIMEOUT.

So lets leave this as a big test and hope test machine have sufficient
ram for the lower, but still large results previously committed.

Author: Daniel Black
Author Date: 2022-03-25 06:25:11 UTC

MDEV-28153: Debian autobake- use absolute dependencies (Salsa postfix)

Autobake now includes a dependency on lsb-release.

As the BB CI images
(https://github.com/MariaDB/mariadb.org-tools/blob/master/buildbot.mariadb.org/ci_build_images/debian.Dockerfile)
have explicit dependencies, there's no point maintaining them badly in
two places. We don't want do the full autobake-deb.sh there, just enough
to have the control file containing the correct dependencies.

Author: Oleksandr "Sanja" Byelkin
Author Date: 2022-04-06 07:31:59 UTC

MDEV-25638 Assertion `!result' failed in convert_const_to_int

The test suite triggers 3 bugs:

I. Absence of STATUS_NO_RECORD on an empty record
II. Mismatch signed and unsigned becaue parameter is not
     taking from the item but was a constant.
III. Problem with rollback changes made for Items in the TABLE object:

    A cycle of live of Items (expressions) in table is following:

    1) Parsed in the query arena of the table
    2) Prepared with the permanent query arena of the table be active as a
    runtime and stmt (permanent) arena (here is difference from usual
    execution)
    3) usage (can be sorter of one query live (CREATE TABLE for example) or
    longer then several queries (SELECT and using table cache), it is not a
    problem because all fields are local to the table).
    4) cleanup and deallocate with the TABLE object

    So looking on the above all changes in the expressions belonged to
    a TABLE object should be just ignored.

Author: Elena Stepanova
Author Date: 2022-04-04 11:48:11 UTC

FreeBSD build test

Author: Elena Stepanova
Author Date: 2022-04-04 11:47:12 UTC

FreeBSD build test

Author: Elena Stepanova
Author Date: 2022-04-04 11:44:55 UTC

FreeBSD build test

Author: Elena Stepanova
Author Date: 2022-04-04 10:57:06 UTC

FreeBSD build test

Author: Marko Mäkelä
Author Date: 2022-04-04 07:37:23 UTC

Merge

Author: Jan Lindström
Author Date: 2022-04-01 10:29:31 UTC

MDEV-28210 : SIGSEGV in the test galera.galera_sst_rsync2

We should make sure that wsrep exists before calling wsrep->post_rollback

Author: Nayuta Yanagisawa
Author Date: 2022-03-31 13:38:54 UTC

MDEV-21618 CREATE UNIQUE INDEX fails with "ERROR 1286 (42000): Unknown storage engine 'partition'"

The server doesn't use the enforced storage engine in ALTER TABLE
without ENGINE clause to avoid an unwanted engine change.

However, the server tries to use the enforced engine in CREATE
INDEX. As a result, the false positive error is raised. The server
should not apply the enforced engine in CREATE INDEX too.

Author: mkaruza
Author Date: 2021-12-16 08:39:26 UTC

MDEV-27263 Cluster bootstrap node shows duplicate wsrep allowlist IP warning messages on each restart.

We should clear `wsrep_allowlist` table on bootstrap before writing to
it.

Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>

Author: Oleg Smirnov
Author Date: 2022-03-30 07:42:20 UTC

MDEV-24560 Avoid possible use of uninitialized tab->table

This patch amends the previous fix for MDEV-24560.
Issue: moving initialization of tab->table to the end of the function
can cause dereferencing NULL in add_sorting_to_table().
The solution is to initialize tab->table as before but reset it to NULL
in case of an error during JOIN::create_postjoin_aggr_table() execution.

Author: Jan Lindström
Author Date: 2022-03-03 16:51:12 UTC

MDEV-24845 : Oddities around innodb_fatal_semaphore_wait_threshold and global.innodb_disallow_writes

We will remove variable innodb_disallow_writes because it is badly
designed and implemented. Parameter will be marked as removed.

Instead we will be using
* Galera provider is paused i.e. all commits will wait
* FLUSH TABLES WITH READ LOCK (FTWRL) to avoid any DDL during SST, this
global read lock i.e MDL_BACKUP_FTWRL1 will conflict MDL_BACKUP_DML
taken by InnoDB background threads doing writes. Similarly, it will conflict
with MBL_BACKUP_START used by mariabackup.
* We force flushing all dirty pages from buffer pool and force InnoDB checkpoint
* Encryption, purge, background statistics and FTS-optimize threads will
acquire MDL_BACKUP_DML before continuing. This will conflict with lock
acquired in FTWRL. Note that we will not use waiting. If MDL-lock can't be
acquired we will skip the operation.

handler.cc
handler.h
  Add new API function ha_force_checkpoint to force checkpoint
  inside InnoDB.

xtrabackup.cc
  Remove INNODB_DISALLOW_WRITES code

mdl.cc
  New functions to trylock and release global MDL lock.

wsrep_sst.cc
  Force flushing all dirty pages from buffer pool and force InnoDB checkpoint

mysqld.cc
  Mark innodb-disallow-writes variable as removed.

dict0stats.cc
dict_stats_func
  Acquire backup lock and release it after we have done

fil0crypt.cc
fil_crypt_start_encrypting_space()
fil_crypt_thread()
  Acquire backup lock and release it after we have done

fts0opt.cc
fts_optimize_sync_table()
fts_optimize_callback()
  Acquire backup lock and release it after we have done

ha_innodb.cc
  Remove all WITH_INNODB_DISALLOW_WRITES code
wsrep_force_checkpoint()
  New API function to flush dirty pages from buffer pool and
  force full checkpoint.

trx_purge,cc
trx_purge()
  Acquire and release global MDL_BACKUP_DML lock

try

r

Author: Daniel Black
Author Date: 2022-03-18 06:36:53 UTC

Deb: enable pmem on riscv64

Author: Nayuta Yanagisawa
Author Date: 2021-10-29 10:04:53 UTC

MDEV-26127 Assertion `err != DB_DUPLICATE_KEY' failed or InnoDB: Failing assertion: id != 0 on ALTER ... REBUILD PARTITION

During rebuild of partition, the partitioning engine calls
alter_close_table(), which does not unlock and close some table
instances of the target table.
Then, the engine fails to rename partitions because there are table
instances that are still locked.

Closing all the table instance of the target table fixes the bug.

Author: Oleksandr "Sanja" Byelkin
Author Date: 2022-03-23 09:35:38 UTC

MDEV-27957 Select from view with subselect fails with lost connection

The problem is in getting select number of excluded EXPLAIN-node,
because on excluding links in exclued unit removed.

IMHO it is simplier to get the number in other way and let the EXPLAIN
proceed then fix EXPLAIN. (the other solution is to do not create nodes
for excluded units)

Author: Vicențiu Ciorbaru
Author Date: 2022-03-23 09:52:22 UTC

MEDV-14443: Show create database denies

Author: Sergey Petrunia
Author Date: 2022-03-22 07:07:39 UTC

Update test results (8)

Author: Julius Goryavsky
Author Date: 2022-03-22 03:40:04 UTC

MDEV-26971 post-fix: fixes for SST scripts

1) Fixed a bug with incorrect calculation of the payload length
   when using the --innodb-undo-directory option on the command
   line (not in the .cnf file);
2) Fixed a bug with incorrect processing of progress=none in the
   presence of *.qp-files;
3) Progress processing for *.qp-files has been made more logically
   unified with the semantics of the adjust_progress function;
4) Added forgotten option "--maxdepth 1" when searching for
   *.qp-files;
5) Eliminated "cosmetic" differences with the SST scripts in
   previous versions, which otherwise will cause problems when
   automatically migrating future changes;
6) Fixed a bug caused by automatic migration of the MDEV-27524.

Author: Vlad Lesin
Author Date: 2022-03-14 17:46:40 UTC

MDEV-27557 InnoDB unnecessarily commits mtr during secondary index search to preserve clustered index latching order

New function to release latches till savepoint was added in mtr_t. As there is
no longer need to limit MDEV-20605 fix usage for locking reads only, the
limitation is removed.

Author: Julius Goryavsky
Author Date: 2022-03-21 01:35:20 UTC

Additional fixes

Author: Monty
Author Date: 2022-03-17 17:37:51 UTC

Last sync of functions for drop table between 10.2 and 10.7

Functions checked / synced between 10.2 and 10.7:

handler.cc
ha_delete_table()
int handler::delete_table(const char *name)
static my_bool delete_table_force(THD *thd, plugin_ref plugin, void *arg);
int ha_delete_table_force(THD *thd, const char *path, const LEX_CSTRING *db,
                          const LEX_CSTRING *alias);

sql_table.cc
mysql_rm_table_no_locks()
- Things related to drop force synced

Usage of ha_delete_table()

Author: Monty
Author Date: 2022-03-17 14:58:43 UTC

Fixed warning for maria.maria-recovery2 about crashed table

The bug was a missing va_start in eprint() which caused a wrong table
name to be printed.
Patch backported from 10.3.

Author: Jan Lindström
Author Date: 2022-03-15 10:48:29 UTC

MDEV-24143 : Galera nodes "randomly" crashing in Item_func_release_lock::val_int

Fixed on MDEV-27713. Added additional test case.

Author: Daniel Black
Author Date: 2022-03-18 01:20:09 UTC

MDEV-28011: debian autobake cleanup (10.6, pmem +uring)

Fixing the version of debian/ubuntu depencies in 10.6
removes the apt-cache checking of libpmem and liburing
dependencies.

By arraging the checks earliest to latest, we unconditionally
change the dependences in earlier versions, and in later versions
we perform architecture checks to see if there is a dependency
on this architure before removing/changing.

This takes from the architecture information on Ubuntu[1,2]
and Debian[3,4].

[1] https://packages.ubuntu.com/search?suite=all&arch=any&searchon=names&keywords=liburing-dev
[2] https://packages.ubuntu.com/search?suite=all&arch=any&searchon=names&keywords=libpmem-dev
[3] https://packages.debian.org/search?suite=all&section=all&arch=any&searchon=names&keywords=liburing-dev
[4] https://packages.debian.org/search?suite=all&section=all&arch=any&searchon=names&keywords=libpmem-dev

Author: Julius Goryavsky
Author Date: 2020-07-13 17:12:00 UTC

MDEV-19281: Plugin implementation for the Hashicorp Vault KMS

- Authentication is done using the Hashicorp Vault's token
  authentication method;
- If additional client authentication is required, then the
  path to the CA authentication bundle file may be passed
  as a plugin parameter;
- The creation of the keys and their management is carried
  out using the Hashicorp Vault KMS and their tools;
- Key values stored as hexadecimal strings;
- Key values caching is supported.
- Implemented a time-invalidated cache for key values and
  for key version numbers received from the Hashicorp Valult
  server;
- The plugin uses libcurl (https) as an interface to
  the HashiCorp Vault server;
- JSON parsing is performed through the JSON service
  (through the include/mysql/service_json.h);
- HashiCorp Vault 1.2.4 was used for development and testing.

Author: Jan Lindström
Author Date: 2022-03-16 08:14:48 UTC

Fix compile error.

Author: Alexey Botchkov
Author Date: 2022-03-15 12:28:33 UTC

MDEV-27159 Re-design the upper level of handling DML commands.

Sql_cmd_insert class introduced.

Author: Julius Goryavsky
Author Date: 2022-03-10 08:58:26 UTC

MENT-1437: Failure in hashicorp_key_rotation_age test

Fixed a failure in the hashicorp key_rotation_age test -
the test made more robust and working with the latest
changes in the innodb.

Also improved error handling during plugin initialization
(memory deallocation is performed in the case of failure).

Author: Angelique Sklavounos
Author Date: 2022-03-14 16:47:29 UTC

MDEV-27849: test timeout increase

Author: Elena Stepanova
Author Date: 2022-03-13 22:59:44 UTC

MDEV-28036 gcol.gcol_supported_sql_funcs_xxx fail in FIPS mode

Author: Marko Mäkelä
Author Date: 2022-03-11 15:46:38 UTC

Merge 10.9 and revert 1766a18e06a056155031dabefb88ce7f201ad921

Author: Vladislav Vaintroub
Author Date: 2022-03-07 09:36:16 UTC

MDEV-26603 asynchronous redo log write

Split redo log write submission and completion.

Currently, AIO is not really used much, because it turns out to be 10-15%
slower than synchronous IO, in write-heavy benchmarks.

It looks like that AIO with callbacks in threadpool currently adds more
context switches, if submitting thread waits for it.

With this patch, AIO is only used where latency is not that important,
in "fire-and-forget" manner, e.g log_write_up_to() retry logic, or prior to
LRU flushing,or in srv_master periodic timer task.

Still, thanks to AIO, internal group commit logic could be improved and
sped up. Apparently, "next groupcommit lead candidate" thread waiting was
removed.

After some benchmarking, spins in group_commit_lock::acquire() were removed
as they did not have any positive effect.

So overall the effect of that patch should be positive.

Author: Oleg Smirnov
Author Date: 2022-03-07 07:38:04 UTC

MDEV-27776 Make EXPLAIN show filtered and partitions columns

Author: Sergey Petrunia
Author Date: 2021-12-17 17:29:41 UTC

MDEV-27188: Suppress optimizer output when executing prepare

- Do not write anything into Optimizer Trace at Prepare phase
- When the query gets an error at Prepare phase, make sure there
  is no trace written, either. This is important as we need to
  produce the same trace for "mtr --ps-protocol" and regular mtr run.
- For other kinds of errors, trace is still produced as it might be
  valuable.

(variant 3)

Author: Julius Goryavsky
Author Date: 2022-03-04 13:02:25 UTC

galera_3nodes.galera_2_cluster test: ignoring expected warnings

Author: Brandon Nesterenko
Author Date: 2022-03-03 16:16:21 UTC

MDEV-27850

Temporary commit for buildbot testing of DEBUG_SYNC
race detection and test fixes.

Author: Monty
Author Date: 2021-11-01 10:34:24 UTC

Update row and key fetch cost models to take into account data copy costs

Before this patch, when calculating the cost of fetching and using a
row/key from the engine, we took into account the cost of finding a
row or key from the engine, but did not consistently take into account
index only accessed, clustered key or covered keys for all access
paths.

The cost of the WHERE clause (TIME_FOR_COMPARE) was not consistently
considered in best_access_path(). TIME_FOR_COMPARE was used in
calculation in other places, like greedy_search(), but was in some
cases (like scans) done an a different number of rows than was
accessed.

The cost calculation of row and index scans didn't take into account
the number of rows that where accessed, only the number of accepted
rows.

When using a filter, the cost of index_only_reads and cost of
accessing and disregarding 'filtered rows' where not taken into
account, which made filters cost less than there actually where.

To remedy the above, the following key & row fetch related costs
has been added:

- The cost of fetching and using a row is now split into different costs:
  - key + Row fetch cost (as before) but multiplied with the variable
  'optimizer_cache_cost' (default to 0.5). This allows the user to
  tell the optimizer the likehood of finding the key and row in the
  engine cache.
- RECORD_COPY_COST, The cost copying a row from the engine to the
  sql layer or creating a row from the join_cache to the record
  buffer. Mostly affects table scan costs.
- INDEX_COPY_COST the cost of finding the next key and copying it from
  the engine to the SQL layer. This is used when we calculate the cost
  index only reads. It makes index scans more expensive than before if
  they cover a lot of rows. (main.index_merge_myisam)
- INDEX_LOOKUP_COST, the cost of finding the first key in a range.
  This replaces the old define IDX_LOOKUP_COST, but with a higher cost.
- INDEX_NEXT_FIND_COST, the cost of finding the next key (and rowid).
  when doing a index scan and comparing the rowid to the filter.
  Before this cost was assumed to be 0.
- ROW_LOOKUP_COST, the cost of fetching a row by rowid.

All of the above constants/variables are now tuned to be somewhat in
proportion of executing complexity to each other. There is tuning
need for these in the future, but that can wait until the above are
made user variables as that will make tuning much easier.

To make the usage of the above easy, there are new (not virtual)
cost calclation functions in handler:
- ha_read_time(), like read_time(), but take optimizer_cache_cost into
  account.
- ha_read_and_copy_time(), like ha_read_time() but take into account
  RECORD_COPY_TIME
- ha_read_and_compare_time(), like ha_read_and_copy_time() but take
  TIME_FOR_COMPARE into account.
- ha_read_with_rowid(). Read row with row id, taking RECORD_COPY_COST
  into account. This is used with filesort where we don't need
  to execute the WHERE clause again.
- ha_keyread_time(), like keyread_time() but take
  optimizer_cache_cost into account.
- ha_keyread_and_copy_time(), like ha_keyread_time(), but add
  INDEX_COPY_COST.
- ha_key_scan_time(), like key_scan_time() but take
  optimizer_cache_cost nto account.
- ha_key_scan_and_compare_time(), like ha_key_scan_time(), but add
  INDEX_COPY_COST & TIME_FOR_COMPARE.

I also added some setup costs for doing different types of scans and
creating temporary tables (on disk and in memory). This encourages
the optimizer to not use these for simple 'a few row' lookups if
there are adequate key lookup strategies.
- TABLE_SCAN_SETUP_COST, cost of starting a table scan.
- INDEX_SCAN_SETUP_COST, cost of starting an index scan.
- HEAP_TEMPTABLE_CREATE_COST, cost of creating in memory
  temporary table.
- DISK_TEMPTABLE_CREATE_COST, cost of creating an on disk temporary
  table.

When calculating cost of fetching ranges, we had a cost of
IDX_LOOKUP_COST (0.125) for doing a key div for a new range. This is
now replaced with 'io_cost * INDEX_LOOKUP_COST (1.0) *
optimizer_cache_cost', which matches the cost we use for 'ref' and
other key lookups. The effect is that the cost is now a bit higher
when we have many ranges for a key.

Allmost all calculation with TIME_FOR_COMPARE is now done in
best_access_path(). 'JOIN::read_time' now includes the full
cost for finding the rows in the table.

In the result files, many of the changes are now again close to what
they where before the "Update cost for hash and cached joins" commit,
as that commit didn't fix the filter cost (too complex to do
everything in one commit).

The above changes showed a lot of a lot of inconsistencies in
optimizer cost calculation. The main objective with the other changes
was to calculation as similar (and accurate) as possible to make
different plans more comparable.

Detailed list of changes:

- Calculate index_only_cost consistently and correctly for all scan
  and ref accesses. The row fetch_cost and index_only_cost now
  takes into account clustered keys, covered keys and index only accesses.
- cost_for_index_read now returns both full cost and index_only_cost
- Fixed cost calculation of get_sweep_read_cost() to match other
  similar costs. This is bases on the assumption that data is more
  often stored on SSD than a hard disk.
- Replaced constant 2.0 with new define TABLE_SCAN_SETUP_COST.
- Some scan cost estimates did not take into account
  TIME_FOR_COMPARE. Now all scan costs takes this into
  account. (main.show_explain)
- Added session variable optimizer_cache_hit_ratio (default 50%). By
  adjusting this on can reduce or increase the cost of index or direct
  record lookups. The effect of the default is that key lookups is now
  a bit cheaper than before. See usage of 'optimizer_cache_cost' in
  handler.h.
- JOIN_TAB::scan_time() did not take into account index only scans,
  which produced a wrong cost when index scan was used. Changed
  JOIN_TAB:::scan_time() to take into consideration clustered and
  covered keys. The values are now cached and we only have to call
  this function once. Other calls are changed to use the cached
  values. Function renamed to JOIN_TAB::estimate_scan_time().
- Fixed that most index cost calculations are done the same way and
  more close to 'range' calculations. The cost is now lower than
  before for small data sets and higher for large data sets as we take
  into account how many keys are read (main.opt_trace_selectivity,
  main.limit_rows_examined).
- Ensured that index_scan_cost() ==
  range(scan_of_all_rows_in_table_using_one_range) +
  MULTI_RANGE_READ_INFO_CONST. One effect of this is that if there
  is choice of doing a full index scan and a range-index scan over
  almost the whole table then index scan will be preferred (no
  range-read setup cost). (innodb.innodb, main.show_explain,
  main.range)
  - Fixed the EQ_REF and REF takes into account clustered and covered
    keys. This changes some plans to use covered or clustered indexes
    as these are much cheaper. (main.subselect_mat_cost,
    main.state_tables_innodb, main.limit_rows_examined)
  - Rowid filter setup cost and filter compare cost now takes into
    account fetching and checking the rowid (INDEX_NEXT_FIND_COST).
    (main.partition_pruning heap.heap_btree main.log_state)
  - Added INDEX_NEXT_FIND_COST to
    Range_rowid_filter_cost_info::lookup_cost to account of the time
    to find and check the next key value against the container
  - Introduced ha_keyread_time(rows) that takes into account finding
    the next row and copying the key value to 'record'
    (INDEX_COPY_COST).
  - Introduced ha_key_scan_time() for calculating an index scan over
    all rows.
  - Added IDX_LOOKUP_COST to keyread_time() as a startup cost.
  - Added index_only_fetch_cost() as a convenience function to
    OPT_RANGE.
  - keyread_time() cost is slightly reduced to prefer shorter keys.
    (main.index_merge_myisam)
  - All of the above caused some index_merge combinations to be
    rejected because of cost (main.index_intersect). In some cases
    'ref' where replaced with index_merge because of the low
    cost calculation of get_sweep_read_cost().
  - Some index usage moved from PRIMARY to a covering index.
    (main.subselect_innodb)
- Changed cost calculation of filter to take INDEX_LOOKUP_COST and
  TIME_FOR_COMPARE into account. See sql_select.cc::apply_filter().
  filter parameters and costs are now written to optimizer_trace.
- Don't use matchings_records_in_range() to try to estimate the number
  of filtered rows for ranges. The reason is that we want to ensure
  that 'range' is calculated similar to 'ref'. There is also more work
  needed to calculate the selectivity when using ranges and ranges and
  filtering. This causes filtering column in EXPLAIN EXTENDED to be
  100.00 for some cases where range cannot use filtering.
  (main.rowid_filter)
- Introduced ha_scan_time() that takes into account the CPU cost of
  finding the next row and copying the row from the engine to
  'record'. This causes costs of table scan to slightly increase and
  some test to changed their plan from ALL to RANGE or ALL to ref.
  (innodb.innodb_mysql, main.select_pkeycache)
  In a few cases where scan time of very small tables have lower cost
  than a ref or range, things changed from ref/range to ALL.
  (main.myisam, main.func_group, main.limit_rows_examined,
  main.subselect2)
- Introduced ha_scan_and_compare_time() which is like ha_scan_time()
  but also adds the cost of the where clause (TIME_FOR_COMPARE).
- Added small cost for creating temporary table for
  materialization. This causes some very small tables to use scan
  instead of materialization.
- Added checking of the WHERE clause (TIME_FOR_COMPARE) of the
  accepted rows to ROR costs in get_best_ror_intersect()
- Removed '- 0.001' from 'join->best_read' and optimize_straight_join()
  to ensure that the 'Last_query_cost' status variable contains the same
  value as the one that was calculated by the optimizer.
- Take avg_io_cost() into account in handler::keyread_time() and
  handler::read_time(). This should have no effect as it's 1.0 by
  default, except for heap that overrides these functions.
- Some 'ref_or_null' accesses changed to 'range' because of cost
  adjustments (main.order_by)
- Added scan type "scan_with_join_cache" for optimizer_trace. This is
  just to show in the trace what kind of scan was used.
- When using 'scan_with_join_cache' take into account number of
  preceding tables (as have to restore all fields for all previous
  table combination when checking the where clause)
  The new cost added is:
  (row_combinations * RECORD_COPY_COST * number_of_cached_tables).
  This increases the cost of join buffering in proportion of the
  number of tables in the join buffer. One effect is that full scans
  are now done earlier as the cost is then smaller.
  (main.join_outer_innodb, main.greedy_optimizer)
- Removed the usage of 'worst_seeks' in cost_for_index_read as it
  caused wrong plans to be created; It prefered JT_EQ_REF even if it
  would be much more expensive than a full table scan. A related issue
  was that worst_seeks only applied to full lookup, not to clustered
  or index only lookups, which is not consistent. This caused some
  plans to use index scan instead of eq_ref (main.union)
- Changed federated block size from 4096 to 1500, which is the typical
  size of an IO packet.
- Added costs for reading rows to Federated. Needed as there is no
  caching of rows in the federated engine.
- Added ha_innobase::read_with_rowid() cost function.
- A lot of extra things added to optimizer trace
  - More costs, especially for materialization and index_merge.
  - Make lables more uniform
  - Fixed a lot of minor bugs
  - Added 'trace_started()' around a lot of trace blocks.
- When calculating ORDER BY with LIMIT cost for using an index
  the cost did not take into account the number of row retrivals
  that has to be done or the cost of comparing the rows with the
  WHERE clause. The cost calculated would be just a fraction of
  the real cost. Now we calculate the cost as we do for ranges
  and 'ref'.
- 'Using index for group-by' is used a bit as we now take into account
   the WHERE clause cost when comparing with 'ref' and prefer the
   method with fewer row combinations. (main.group_min_max).

Bugs fixed:
- Fixed that we don't calculate TIME_FOR_COMPARE twice for some plans,
  like in optimize_straight_join() and greedy_search()
- Fixed bug in save_explain_data where we could test for the wrong
  index when displaying 'Using index'. This caused some old plans to
  show 'Using index'. (main.subselect_innodb, main.subselect2)
- Fixed bug in get_best_ror_intersect() where 'min_cost' was not
  updated, and the cost we compared with was not the one that was
  used.
- Fixed very wrong cost calculation for priority queues in
  check_if_pq_applicable(). (main.order_by now correctly uses priority
  queue)
- When calculating cost of EQ_REF or REF, we added the cost of
  comparing the WHERE clause with the found rows, not all row
  combinations. This made ref and eq_ref to be regarded way to cheap
  compared to other access methods.
- FORCE INDEX cost calculation didn't take into account clustered or
  covered indexes.
- JT_EQ_REF cost was estimated as avg_io_cost(), which is half the
  cost of a JT_REF key. This may be true for InnoDB primary key, but
  not for other unique keys or other engines. Now we use handler
  function to calculate the cost, which allows us to handle
  consistently clustered, covered keys and not covered keys.
- ha_start_keyread() didn't call extra_opt() if keyread was already
  enabled but still changed the 'keyread' variable (which is wrong).
  Fixed by not doing anything if keyread is already enabled.
- multi_range_read_info_cost() didn't take into account io_cost when
  calculating the cost of ranges.
- fix_semijoin_strategies_for_picked_join_order() used the wrong
  record_count when calling best_access_path() for SJ_OPT_FIRST_MATCH
  and SJ_OPT_LOOSE_SCAN.
- Hash joins didn't provide correct best_cost to the upper level, which
  means that the cost for hash_joins more expensive than calculated
  in best_access_path (a difference of 10x * TIME_OF_COMPARE).
  This is fixed in the new code thanks to that we now include
  TIME_OF_COMPARE cost in 'read_time'.

Other things:
- Added some 'if (thd->trace_started())' to speed up code
- Removed not used function Cost_estimate::is_zero()
- Simplified testing of HA_POS_ERROR in get_best_ror_intersect().
  (No cost changes)
- Moved ha_start_keyread() from join_read_const_table() to join_read_const()
  to enable keyread for all types of JT_CONST tables.
- Made a few very short functions inline in handler.h

Notes:
- In main.rowid_filter the join order of order and lineitem is swapped.
  This is because the cost of doing a range fetch of lineitem(98 rows) is
  almost as big as the whole join of order,lineitem. The filtering will
  also ensure that we only have to do very small key fetches of the rows
  in lineitem.
- main.index_merge_myisam had a few changes where we are now using
  less keys for index_merge. This is because index scans are now more
  expensive than before.
- handler->optimizer_cache_cost is updated in ha_external_lock().
  This ensures that it is up to date per statements.
  Not an optimal solution (for locked tables), but should be ok for now.
- 'DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a' does not take cost of
  filesort into consideration when table scan is chosen.
  (main.myisam_explain_non_select_all)
- perfschema.table_aggregate_global_* has changed because an update
  on a table with 1 row will now use table scan instead of key lookup.

TODO in upcomming commits:
- Fix selectivity calculation for ranges with and without filtering and
  when there is a ref access but scan is chosen.
  For this we have to store the lowest known value for
  'accepted_records' in the OPT_RANGE structure.
- Change that records_read does not include filtered rows.
- test_if_cheaper_ordering() needs to be updated to properly calculate costs.
  This will fix tests like main.order_by_innodb, main.single_delete_update
- Extend get_range_limit_read_cost() to take into considering
  cost_for_index_read() if there where no quick keys. This will reduce
  the computed cost for ORDER BY with LIMIT in some cases.
  (main.innodb_ext_key)
- Change all optimizer cost constants to user variables. This will make it
  possible for the users to tune the cost model if needed instead of having
  to ask for a new server build.
- Fix that we take into account selectivity when counting the number of rows
  we have to read when considering using a index table scan to resolve
  ORDER BY.
- Add new calculation for reaed_with_rowid() where we take into account the
  benefit of reading multiple rows from the same page.

fixup to be combined with previous commit

Another fixup, to be combined with the previous commits

Fixed a lot of inconsistencies in optimizer cost calculation. The main
objective was get cost calculation as similar (and accurate) as
possible to make different plans more comparable.

- Replaced constant 2.0 with new define TABLE_SCAN_SETUP_COST.
- Added RECORD_COPY_COST, the cost of finding the next row and copying
  it to record for table scans.
- Added INDEX_COPY_COST, the cost of finding the next key and copying it
  to record for index scans.
- Added INDEX_NEXT_FIND_COST, the cost of finding the next index entry and
  checking it against filter.
- Some scan cost estimates did not take into account
  TIME_FOR_COMPARE. Now all scan costs takes this into
  account. (main.show_explain)
- Added session variable optimizer_cache_hit_ratio (default 50%). By
  adjusting this on can reduce or increase the cost of index or direct
  record lookups. The effect of the default is that key lookups is now
  a bit cheaper than before. See usage of 'optimizer_cache_cost' in
  handler.h.
- JOIN_TAB::scan_time() did not take into account index only scans,
  which produced a wrong cost when index scan was used. Fixed by
  adding support for covering keys. Cached also the calculated values
  to avoid future calls during optimization phase.
- Fixed that most index cost calculations are done the same way and
  more close to 'range' calculations. The cost is now lower than before for
  small data sets and higher for large data sets as we take into account
  how many keys are read.
  - Ensured that index_scan_cost() ==
    range(scan_of_all_rows_in_table_using_one_range) +
    MULTI_RANGE_READ_INFO_CONST. One effect of this is that if there is
    choice of doing a full index scan and a range-index scan over almost
    the whole table then index scan will be preferred (no range-read
    setup cost).
    (innodb.innodb, main.show_explain, main.range)
  - Fixed the EQ_REF and REF takes into account clustered and covered keys.
    This changes some plans to use covered or clustered indexes as these are
    much cheaper.
    (main.subselect_mat_cost main.state_tables_innodb)
  - Rowid filter setup cost and filter compare cost now takes into account
    fetching and checking the rowid (INDEX_NEXT_FIND_COST).
   (main.partition_pruning heap.heap_btree main.log_state)
  - Introduced ha_keyread_time(rows) that takes into account finding the
    next row and copying the key value to 'record' (INDEX_COPY_COST).
  - Introduced ha_key_scan_time() for calculating an index scan over all
    rows.
  - Added IDX_LOOKUP_COST to keyread_time() as a startup cost.
  - Added index_only_fetch_cost() as a convenience function to
    OPT_RANGE.
  - keyread_time() cost is slightly reduced to prefer shorter keys.
    (main.index_merge_myisam)
  - All of the above caused some index_merge combinations to be
    rejected because of cost (main.index_intersect). In some cases
    'ref' where replaced with index_merge because of the low
    cost calculation of get_sweep_read_cost().
  - Some index usage moved from PRIMARY to a covering index.
    (main.subselect_innodb)
- Don't use matching_records_in_range() to try to estimate the number of
  filtered rows for ranges. The reason is that we want to ensure that 'range'
  is calculated similar to 'ref'. There is also more work needed to
  calculate the selectivity when using ranges and ranges and filtering.
  This causes filtering column in EXPLAIN EXTENDED to be 100.00 for
  some cases where range cannot use filtering.
  (main.rowid_filter)
- Introduced ha_scan_time() that takes into account the CPU cost of
  finding the next row and copying the row from the engine to
  'record'. This causes costs of table scan to slightly increase and
  some test to changed their plan from ALL to RANGE or ALL to ref.
  (innodb.innodb_mysql, main.select_pkeycache)
- Introduced ha_scan_and_compare_time() which is like ha_scan_time()
  but also adds the cost of checking the where clause
  (TIME_FOR_COMPARE).
- Introduced ha_read_with_rowid() that takes into account
  RECORD_COPY_COST.
- Added checking of the WHERE clause of the accepted rows to ROR costs
  in get_best_ror_intersect()
- Removed '- 0.001' from 'join->best_read' and optimize_straight_join()
  to ensure that the 'Last_query_cost' status variable contains the same
  value as the one that was calculated by the optimizer.
- Added INDEX_NEXT_FIND_COST to Range_rowid_filter_cost_info::lookup_cost
  to account of the time to find and check the next key value against the
  container
- Changed 'JOIN_TAB:::scan_time() to take into consideration clustered and
  covered keys. The values are now cached and we only have to call this
  function once. Other calls are changed to use the cached values.
  Function renamed to JOIN_TAB::estimate_scan_time().
- Take avg_io_cost() into account in handler::keyread_time() and
  handler::read_time(). This should have no effect as it's 1.0 by
  default, except for heap that overrides these functions.
- Some 'ref_or_null' accesses changed to 'range' because of cost
  adjustments (main.order_by)
- Added scan type "scan_with_join_cache" for optimizer_trace. This is just
  to show what kind of scan was used.
-I had to remove the usage of 'worst_seeks' in cost_for_index_read as it
 cases wrong plans to be created; It prefered JT_EQ_REF even if it would
 be much more expensive than a full table scan. A related issue was that
 worst_seeks only applied to full lookup, not to clustered or index only
 lookups, which is not consistent. This caused some plans to use index
 scan instead of eq_ref (main.union)

Bugs fixed:
- Fixed that we don't calculate TIME_FOR_COMPARE twice for some plans,
  like in optimize_straight_join() and greedy_search()
- Fixed bug in save_explain_data where we could test for the wrong
  index when displaying 'Using index'. This caused some old plans to
  show 'Using index'. (main.subselect_innodb, main.subselect2)
- Fixed bug in get_best_ror_intersect() where 'min_cost' was not updated,
  and the cost we compared with was not the one that was used.
- Fixed very wrong cost calculation for priority queues in
  check_if_pq_applicable().
- When calculating cost of EQ_REF or REF, we added the cost of comparing
  the WHERE clause with the found rows, not all row combinations. This
  made ref and eq_ref to be regarded way to cheap compared to other
  access methods.
- FORCE INDEX cost calculation didn't take into account clustered or
  covered indexes.
- JT_EQ_REF cost was estimated as avg_io_cost(), which is half the
  cost of a JT_REF key. This may be true for InnoDB primary key, but
  not for other unique keys or other engines. Now we use handler
  function to calculate the cost, which allows us to handle
  consistently clustered, covered keys and not covered keys.
- ha_start_keyread() didn't call extra_opt() if keyread was already
  enabled but still changed the 'keyread' variable (which is wrong).
  Fixed by not doing anything if keyread is already enabled.
- multi_range_read_info_cost() didn't take into account io_cost when
  calculating the cost of ranges.

Other things:
- Added some 'if (thd->trace_started())' to speed up code
- Removed not used function Cost_estimate::is_zero()
- Simplified testing of HA_POS_ERROR in get_best_ror_intersect().
  (No cost changes)
- Moved ha_start_keyread() from join_read_const_table() to join_read_const()
  to enable keyread for all types of JT_CONST tables.
- Made a few very short functions inline in handler.h

TODO in upcomming commit:
- Fix selectivity calculation for ranges with and without filtering and
  when there is a ref access but scan is chosen.
  For this we have to store the lowest known value for
  'accepted_records' in the OPT_RANGE structure.
- test_if_cheaper_ordering() needs to be updated to properly calculate costs.
  This will fix tests like main.order_by_innodb.
- Extend get_range_limit_read_cost() to take into considering
  cost_for_index_read() if there where no quick keys. This will reduce
  the computed cost for ORDER BY with LIMIT in some cases.
  (main.innodb_ext_key)

COMMENTS:
- In main.rowid_filter the join order of order and lineitem is swapped.
  This is because the cost of doing a range fetch of lineitem(98 rows) is
  almost as big as the whole join of order,lineitem. The filtering will
  also ensure that we only have to do very small key fetches of the rows
  in lineitem.
- handler->optimizer_cache_cost is updated in ha_external_lock().
  This ensures that it is up to date per statements.
  Not an optimal solution (for locked tables), but should be ok for now.

Another temporary commit to be combined with previous ones

Author: Andrei
Author Date: 2022-03-01 12:07:07 UTC

MDEV-27963 multisource_for_channel sometimes fails in bb with result content mismatch

The mismatch with the result file was caused by asynchronicity
START-SLAVE caused relay log rotation and the respective
SHOW-SLAVE-STATUS' field check.

Fixed with masking two possible results with a single pattern.

A more complicated alternative solution of waiting for the end of
relay-log rotation is waived because the test does not really require
the exact relay-log name.

Author: Jan Lindström
Author Date: 2022-01-28 07:00:38 UTC

MDEV-24845 : Oddities around innodb_fatal_semaphore_wait_threshold and global.innodb_disallow_writes

We will remove variable innodb_disallow_writes because it is badly
designed and implemented. Parameter will be marked as removed.

Instead we will be using
* Galera provider is paused i.e. all commits will wait
* FLUSH TABLES WITH READ LOCK (FTWRL) to avoid any DDL during SST
* We set max_dirty_pages_pct to 0.0 to flush all dirty pages from buffer pool
* We force flushing all dirty pages from buffer pool and force InnoDB checkpoint
* We set max_dirty_pagec_pct to 99.9 to avoid flushing any pages
* We set server to read-only
* Encryption, purge and FTS-optimize threads will acquire MDL_BACKUP_DML
before continuing. This will conflict with lock acquired in FTWRL.
Note that we will not use waiting. If MDL-lock can't be acquired
we will skip the operation.

xtrabackup.cc
  Remove INNODB_DISALLOW_WRITES code

handler.cc
handler.h
  Add new API function ha_force_checkpoint to force checkpoint
  inside InnoDB.

mysqld.cc
  Mark innodb-disallow-writes variable as removed.

sql_class.cc
  New functions to get and release backup lock.

wsrep_sst.cc
  Add functions to set innodb_max_dirty_pages_pct,
  innodb_max_dirty_pages_pct_lwm and read_only.
  We set them as 0.0 and then call new API function
  to force buffer pool flush and full checkpoint.
  After it has finished, we set them as 99.9.

fil0crypt.cc
fil_crypt_start_encrypting_space()
fil_crypt_thread()
  Acquire backup lock and release it afte we have done

fts0opt.cc
fts_optimize_sync_table()
fts_optimize_callback()
  Acquire backup lock and release it afte we have done

ha_innodb.cc
  Remove all WITH_INNODB_DISALLOW_WRITES code
wsrep_force_checkpoint()
  New API function to flush dirty pages from buffer pool and
  force full checkpoint.

trx_purge,cc
trx_purge()
  Acquire and release global MDL_BACKUP_DML lock

Author: Sergey Petrunia
Author Date: 2021-12-09 17:13:35 UTC

Code cleanup: don't call subquery_types_allow_materialization() on prepare

For subqueries that are processed as semi-joins.

Author: Nayuta Yanagisawa
Author Date: 2022-01-24 06:01:32 UTC

MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default tmp SE

Setting Spider as the default tmp storage engine results in the
server crash at its shutdown.

The crash is due to the lack of NULL checking in intern_plugin_lock().

Author: Nayuta Yanagisawa
Author Date: 2022-01-24 06:01:32 UTC

MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default tmp SE

Setting Spider as the default tmp storage engine results in the
server crash at its shutdown.

Spider cannot maintain any physical data, and thus setting it as
the default tmp storage engine is non-sense. We simply prohibit this
configuration by setting HTON_TEMPORARY_NOT_SUPPORTED to Spider.