row_ins_clust_index_entry_low(): Invoke btr_set_instant() in the same
mini-transaction that has successfully inserted the metadata record.
In this way, if inserting the metadata record fails before any
undo log record was written for it, the index root page will remain
consistent.

innobase_instant_try(): Remove the btr_set_instant() call.

This is a 10.6 version of c17aca2f.

Tested by: Matthias Leich
Reviewed by: Thirunarayanan Balathandayuthapani

During mariadb-backup --backup, a table could be renamed, created and
dropped. We could have both oldname.ibd and oldname.new, and one of
the files would be deleted before the InnoDB recovery starts. The desired
end result would be that we will recover both oldname.ibd and newname.ibd.

During normal crash recovery, at most one file operation (create, rename,
delete) may require to be replayed from the write-ahead log before the
DDL recovery starts.

deferred_spaces.create(): In mariadb-backup --prepare, try to create the
file in case it does not exist.

fil_name_process(): Display a message about not found files not only
if innodb_force_recovery is set, but also in mariadb-backup --prepare.
If we are processing a FILE_RENAME for a tablespace whose recovery is
deferred, suppress the message and adjust the file name in case
fil_ibd_load() returns FIL_LOAD_NOT_FOUND or FIL_LOAD_DEFER.

fil_ibd_load(): Remove a redundant file name comparison.
The caller already compared that the file names are different.
We used to wrongly return FIL_LOAD_OK instead of FIL_LOAD_ID_CHANGED
if only the schema name differed, such as a/t1.ibd and b/t1.ibd.

Tested by: Matthias Leich
Reviewed by: Thirunarayanan Balathandayuthapani

fixes the failure of

./mtr --ps sys_vars.gtid_slave_pos_grant sysschema.v_session_ssl_status

safe_mutex: Found wrong usage of mutex 'LOCK_thd_data' and 'LOCK_active_mi'

* treat FUNC/ARRAY variables as SESSION (otherwise they won't be shown)
* allow SHOW_SIMPLE_FUNC everywhere where SHOW_FUNC is
* increase row buffer size to avoid "too short" assert

test uses +d,getnameinfo_fake_long_host to return a fake long
hostname in ip_to_hostname(). But this dbug keyword is only checked
after the lookup in the hostname cache.

the test has to flush the hostname cache in case previous tests
had it populated with fake ip addresses (perfschema tests do that)

also, remove redundant `connection` directives

* restore old ENUM values order, in case someone used
  SET @@s3_protocol_version=1

* restore old behavior for Original and Amazon protocol values,
  they behave as "Auto" and do not force a specific protocol version

Approved by Andrew Hutchings <andrew@mariadb.org>

MDEV-31296: Crash in Item_func::fix_fields when prepared statement with subqueries and window function is executed with sql_mode = ONLY_FULL_GROUP_BY

Crash was caused by referencing a null pointer on getting
the number of the nesting levels of the set function for the current
select_lex at the method Item_field::fix_fields.

The current select for processing is taken from Name_resolution_context
that filled in at the function set_new_item_local_context() and
where initialization of the data member Name_resolution_context
was mistakenly removed by the commit
  d6ee351b
   (Revert "MDEV-24454 Crash at change_item_tree")

To fix the issue, correct initialization of data member
  Name_resolution_context::select_lex
that was removed by the commit d6ee351b
is restored.

Enable unusable key notes for non-equality predicates:
   <, <=, =>, >, BETWEEN, IN, LIKE

Note, in some scenarios it displays duplicate notes, e.g.
for queries with ORDER BY:

  SELECT * FROM t1
  WHERE    indexed_string_column >= 10
  ORDER BY indexed_string_column
  LIMIT 5;

This should be tolarable. Getting rid of the diplicate note
completely would need a much more complex patch, which is
not desiable in 10.6.

Details:

- Changing RANGE_OPT_PARAM::note_unusable_keys from bool
  to a new data type Item_func::Bitmap, so the caller can
  choose with a better granuality which predicates
  should raise unusable key notes inside the range optimizer:
    a. all predicates (=, <=>, <, <=, =>, >, BETWEEN, IN, LIKE)
    b. all predicates except equality (=, <=>)
    c. none of the predicates

  "b." is needed because in some scenarios equality predicates (=, <=>)
  send unusable key notes at an earlier stage, before the range optimizer,
  during update_ref_and_keys(). Calling the range optimizer with
  "all predicates" would produce duplicate notes for = and <=> in such cases.

- Fixing get_quick_record_count() to call the range optimizer
  with "all predicates except equality" instead of "none of the predicates".
  Before this change the range optimizer suppressed all notes for
  non-equality predicates: <, <=, =>, >, BETWEEN, IN, LIKE.
  This actually fixes the reported problem.

- Fixing JOIN::make_range_rowid_filters() to call the range optimizer
  with "all predicates except equality" instead of "all predicates".
  Before this change the range optimizer produced duplicate notes
  for = and <=> during a rowid_filter optimization.

- Cleanup:
  Adding the op_collation argument to Field::raise_note_cannot_use_key_part()
  and displaying the operation collation rather than the argument collation
  in the unusable key note. This is important for operations with more than
  two arguments: BETWEEN and IN, e.g.:

    SELECT * FROM t1
    WHERE column_utf8mb3_general_ci
          BETWEEN 'a' AND 'b' COLLATE utf8mb3_unicode_ci;

    SELECT * FROM t1
    WHERE column_utf8mb3_general_ci
          IN ('a', 'b' COLLATE utf8mb3_unicode_ci);

    The note for 'a' now prints utf8mb3_unicode_ci as the collation.
    which is the collation of the entire operation:

      Cannot use key key1 part[0] for lookup:
      "`column_utf8mb3_general_ci`" of collation `utf8mb3_general_ci` >=
      "'a'" of collation `utf8mb3_unicode_ci`

    Before this change it printed the collation of 'a',
    so the note was confusing:

      Cannot use key key1 part[0] for lookup:
      "`column_utf8mb3_general_ci`" of collation `utf8mb3_general_ci` >=
      "'a'" of collation `utf8mb3_general_ci`"

The previous commit for MDEV-32884 fixed the s3_protocol_version option,
which was previous only using "Auto", no matter what it was set to. This
patch does several things to keep the old behaviour whilst correcting
for new behaviour and laying the groundwork for the future. This
includes:

* `Original` now means v2 protocol, which it would have been due to the
  option not working, so upgrades will stil work.
* A new `Legacy` option has been added to mean v1 protocol.
* Options `Path` and `Domain` have been added, these will be the only
  two options apart from `Auto` in a future release, and are more
  aligned with what this variable means.
* Fixed the s3.debug test so that it works with v2 protocol.
* Fixed the s3.amazon test so that it works with region subdomains.
* Added additional modes to the s3.amazon test.
* Added s3.not_amazon test for the remaining modes.

This replaces PR #2902.

The function scalar_comparison_op_to_lex_cstring() returned
wrong operator names for SCALAR_CMP_GE and SCALAR_CMP_GT.

The linear read-ahead (enabled by nonzero innodb_read_ahead_threshold)
works best if index leaf pages or undo log pages have been allocated
on adjacent page numbers. The read-ahead is assumed not to be helpful
in other types of page accesses, such as non-leaf index pages.

buf_page_get_low(): Do not invoke buf_page_t::set_accessed(),
buf_page_make_young_if_needed(), or buf_read_ahead_linear().
We will invoke them in those callers of buf_page_get_gen() or
buf_page_get() where it makes sense: the access is not
one-time-on-startup and the page and not going to be freed soon.

btr_copy_blob_prefix(), btr_pcur_move_to_next_page(),
trx_undo_get_prev_rec_from_prev_page(),
trx_undo_get_first_rec(), btr_cur_t::search_leaf(),
btr_cur_t::open_leaf(): Invoke buf_read_ahead_linear().

We will not invoke linear read-ahead in functions that would
essentially allocate or free pages, because pages that are
freshly allocated are expected to be initialized by buf_page_create()
and not read from the data file. Likewise, freeing pages should
not involve accessing any sibling pages, except for freeing
singly-linked lists of BLOB pages.

We will not invoke read-ahead in btr_cur_t::pessimistic_search_leaf()
or in a pessimistic operation of btr_cur_t::open_leaf(), because
it is assumed that pessimistic operations should be preceded by
optimistic operations, which should already have invoked read-ahead.

buf_page_make_young_if_needed(): Invoke also buf_page_t::set_accessed()
and return the result.

btr_cur_nonleaf_make_young(): Like buf_page_make_young_if_needed(),
but do not invoke buf_page_t::set_accessed().

Reviewed by: Vladislav Lesin
Tested by: Matthias Leich

dict_sys_t::lock(), dict_sys_t::freeze(): Assert that the current thread
is not holding dict_sys.latch in any mode. The SUX lock is reentrant,
but the simpler RW lock that we use in non-debug builds is not.

The reason for the bug was that spider plugin does some ALTER TABLE
commands before recovery is done, which was not expected.

Fixed by resetting the ddl_log lists in create_ddl_log()

What is shown in this case is the result from the following query:
select * from information_schema.processlist where id != connection_id()

This allows easy monitoring of for example MAX_MEMORY_USED

buf_page_get_zip(): Do not wait for the page latch while holding hash_lock.
If the latch is not available, ensure that any concurrent
buf_pool_t::corrupted_evict() will be able to acquire the hash_lock,
and then retry the lookup. If the page was corrupted, we will finally
"goto must_read_page", retry the read once more, and then report an error.

Reviewed by: Thirunarayanan Balathandayuthapani

btr_attach_half_pages(), btr_lift_page_up(), btr_compress():
Return DB_CORRUPTION if btr_page_get_father_block() returns nullptr

Reviewed by: Thirunarayanan Balathandayuthapani

mtr_t::commit(): If IMPORT TABLESPACE is first-time-dirtying blocks,
acquire both log_sys.mutex and log_sys.flush_order_mutex to assign
a valid m_commit_lsn so that the block will be inserted into the
correct position of buf_pool.flush_list.

This fixes occasional debug assertion failures when running the
regression test suite.

Reviewed by: Vladislav Lesin

ha_innobase::extra() invokes check_trx_exists() unconditionally even for
not supported operations. check_trx_exists() creates and registers trx_t
object if THD does not contain pointer to it. If ha_innobase::extra() does
not support some operation, it just invokes check_trx_exists() and quites.
If check_trx_exists() creates and registers new trx_t object for such
operation, it will never be freed and deregistered.

For example, if ha_innobase::extra() is invoked from purge thread with
operation = HA_EXTRA_IS_ATTACHED_CHILDREN, like it goes in
gcol.gcol_purge test, trx_t object will be registered, but not
deregisreted, and this causes innodb.gap_lock failure, as "SHOW ENGINE
INNODB STATUS" shows information about unexpected transaction at the end
of trx_sys.trx_list.

The fix is not to invoke check_trx_exists() for unsupported operations
in ha_innobase::extra().

Reviewed by: Marko Mäkelä

In debug builds, let us declare dict_sys.latch as index_lock instead of
srw_lock, so that we will benefit from the full tracking of lock ownership.

lock_table_for_trx(): Assert that the current thread is not holding
dict_sys.latch. If the dict_sys.unfreeze() call were moved to the end of
lock_table_children(), this assertion would fail in the test innodb.innodb
and many other tests that use FOREIGN KEY.

--debug is supported by allmost all our other binaries and we should keep
it also in the server to keep option names similar.

MDEV-32899 InnoDB is holding shared dict_sys.latch while waiting for FOREIGN KEY child table lock on DDL

lock_table_children(): A new function to lock all child tables of a table.
We will only hold dict_sys.latch while traversing
dict_table_t::referenced_set. To prevent a race condition with
std::set::erase() we will copy the pointers to the child tables to a
local vector. Once we have acquired references to all child tables,
we can safely release dict_sys.latch, wait for the locks, and finally
release the references.

This fixes up commit 2ca11234 (MDEV-26217)
and commit c3c53926 (MDEV-26554).

MDEV-32879 Server crash in my_decimal::operator= or unexpected ER_DUP_ENTRY upon comparison with INET6 and similar types

During the 10.5->10.6 merge please use the 10.6 code on conflicts.

This is the 10.5 version of the patch (a backport of the 10.6 version).
Unlike 10.6 version, it makes changes in plugin/type_inet/sql_type_inet.*
rather than in sql/sql_type_fixedbin.h

Item_bool_rowready_func2, Item_func_between, Item_func_in
did not check if a not-NULL argument of an arbitrary data type
can produce a NULL value on conversion to INET6.

This caused a crash on DBUG_ASSERT() in conversion failures,
because the function returned SQL NULL for something that
has Item::maybe_null() equal to false.

Adding setting NULL-ability in such cases.

Details:

- Removing the code in Item_func::setup_args_and_comparator()
  performing character set aggregation with optional narrowing.
  This aggregation is done inside Arg_comparator::set_cmp_func_string().
  So this code was redundant

- Removing Item_func::setup_args_and_comparator() as it git simplified to
  just to two lines:
    convert_const_compared_to_int_field(thd);
    return cmp->set_cmp_func(thd, this, &args[0], &args[1], true);
  Using these lines directly in:
    - Item_bool_rowready_func2::fix_length_and_dec()
    - Item_func_nullif::fix_length_and_dec()

- Adding a new virtual method:
  - Type_handler::Item_bool_rowready_func2_fix_length_and_dec().

- Adding tests detecting if the data type conversion can return SQL NULL into
  the following methods of Type_handler_inet6:
  - Item_bool_rowready_func2_fix_length_and_dec
  - Item_func_between_fix_length_and_dec
  - Item_func_in_fix_comparator_compatible_types

MDEV-32879 Server crash in my_decimal::operator= or unexpected ER_DUP_ENTRY upon comparison with INET6 and similar types

This is the 10.6 version of the patch.

Item_bool_rowready_func2, Item_func_between, Item_func_in
did not check if a not-NULL argument of an arbitrary data type
can produce a NULL value on conversion to INET6.

This caused a crash on DBUG_ASSERT() in conversion failures,
because the function returned SQL NULL for something that
has Item::maybe_null() equal to false.

Adding setting NULL-ability in such cases.

Details:

- Removing the code in Item_func::setup_args_and_comparator()
  performing character set aggregation with optional narrowing.
  This aggregation is done inside Arg_comparator::set_cmp_func_string().
  So this code was redundant

- Removing Item_func::setup_args_and_comparator() as it git simplified to
  just to two lines:
    convert_const_compared_to_int_field(thd);
    return cmp->set_cmp_func(thd, this, &args[0], &args[1], true);
  Using these lines directly in:
    - Item_bool_rowready_func2::fix_length_and_dec()
    - Item_func_nullif::fix_length_and_dec()

- Adding a new virtual method:
  - Type_handler::Item_bool_rowready_func2_fix_length_and_dec().

- Adding tests detecting if the data type conversion can return SQL NULL into
  the following methods of Type_handler_fbt:
  - Item_bool_rowready_func2_fix_length_and_dec
  - Item_func_between_fix_length_and_dec
  - Item_func_in_fix_comparator_compatible_types

Let us wait for the completion of purge before testing the KILL of
CREATE INDEX c2d ON t1(c2), so that there will be no table handle
acquisition by a purge task before the operation is rolled back.

Also, let us make the test compatible with ./mtr --repeat,
and convert variable_value from string to integer so that any
comparisons will be performed correctly.

Reviewer: <>

When the constant OS_AIO_N_PENDING_IOS_PER_THREAD is changed from 256 to 1
and the server is run with the minimum parameters
innodb_read_io_threads=1 and innodb_write_io_threads=2, two hangs
were observed.

tpool::cache<T>::put(T*): Ensure that get() in io_slots::acquire()
will be woken up when the cache previously was empty.

buf_pool_t::io_buf_t::reserve(): Schedule a possibly partial doublewrite
batch so that os_aio_wait_until_no_pending_writes() has a chance of
returning. Add a Boolean parameter and pass wait_for_reads=false inside
buf_page_decrypt_after_read(), because those calls will be executed
inside a read completion callback, and therefore
os_aio_wait_until_no_pending_reads() would block indefinitely.

In any test that uses wait_all_purged.inc, ensure that InnoDB tables
will be created without persistent statistics.

This is a follow-up to commit cd04673a
after a similar failure was observed in the innodb_zip.blob test.

trx_purge_free_segment(): If fseg_free_step_not_header() needs to be
called multiple times, acquire an exclusive latch on the
rollback segment header page after restarting the mini-transaction
so that the rest of this function cannot execute concurrently
with trx_undo_create() on the same rollback segment.

This fixes a regression that was introduced in
commit c14a3943 (MDEV-30753).

Note: The buffer-fixes that we are holding across the mini-transaction
restart will prevent the pages from being evicted from the buffer pool.
They may be accessed by other threads or written back to data files
while we are not holding exclusive latches.

Reviewed by: Vladislav Lesin

We must disable persistent statistics, because a transaction commit
from dict_stats_save() would occasionally interfere with this test.

1. fix useless correction of dec_arg *after it was used*
2. use DECIMAL_NOT_SPECIFIED instead of NOT_FIXED_DEC, because the latter
   is defined conditionally and has a wrong value in plugins