fix test cases like

... prepare the test, create a table or a user
create event ... on schedule every 1 second ... do something ...
... verify that something was done
... cleanup drop event, table or user

here the event scheduler can manage to start the worker for the
second execution of the event, then cleanup happens
and the worker realizes that it cannot change the security context
(as the user is dropped) or cannot insert (as the table is dropped).

followup for 279b0db8 (that removed suppression of /Event Schedule/)

Statements affected by this bug are delete statements that have all
these conditions

1) single table delete syntax
2) and in (sub-query) predicate
3) semi-join optimization enabled
4) an order by clause.

Semijoin optimization on an innocent looking query, such as

DELETE FROM t1 WHERE c1 IN (select c2 from t2) ORDER BY c1;

turns it from a single table delete to a multi-table delete.

During multi_delete::initialize_tables for the top level join object, a
table is initialized missing a keep_current_rowid flag, needed to
position a handler for removal of the correct row after the filesort
structure has been built.

Fix provided by Monty (monty@mariadb.com)
OK'd in slack:#askmonty 2023-12-01
applicable to 11.1 on

Connector/NET does not expect collation IDs returned by "show collations"
to be NULL, runs into an exception.

The fix is to determine connector/net using its connection attributes,
then make sure "show collations" does not output NULL IDs.

The patch introduces new old_mode NO_NULL_COLLATION_IDs, that is
automatically set, once MySQL Connector/NET connection is determined.

A test was added, that uses MySql.Data from powershell - only works
if MySql.Data is installed into GAC (i.e with C/NET MSI package)

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.

Eventfds have a simplier interface and are one file
descriptor rather than two.

Reuse the patten of the accepting socket connections
by testing for abort after a poll returns. This way
the same event descriptor can be used for Quit
and debugging trigger.

Also correct the registration of mem pressure file
descriptors.

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.

The log_sys.lsn_lock that was introduced in
commit a635c406
had better be located in the same cache line with log_sys.latch
so that log_t::append_prepare() needs to modify only two first
cache lines where log_sys is stored.

log_t::lsn_lock: On Linux, change the type from pthread_mutex_t to
something that may be as small as 32 bits, to pack more data members
in the same cache line. On Microsoft Windows, CRITICAL_SECTION works
better.

log_t::check_flush_or_checkpoint_: Renamed to need_checkpoint.
There is no need to pause all writer threads in log_free_check() when
we only need to write log_sys.buf to ib_logfile0. That will be done in
mtr_t::commit().

log_t::append_prepare_wait(): Make the member function non-static
to simplify the call interface, and add a parameter for the LSN.

log_t::append_prepare(): Invoke append_prepare_wait() at most once.
Only set_check_for_checkpoint() if a log checkpoint needs to
be written. If the log buffer needs to be written, we will take care
of it ourselves later in our caller. This will reduce interference
with log_free_check() in other threads.

mtr_t::commit(): Call log_write_up_to() if needed.

log_t::get_write_target(): Return a log_write_up_to() target
to mtr_t::commit().

buf_flush_ahead(): If we are in furious flushing, call
log_sys.set_check_for_checkpoint() so that all writers will wait
in log_free_check() until the checkpoint is done. Otherwise,
the test innodb.insert_into_empty could occasionally report
an error "Crash recovery is broken".

log_check_margins(): Replaced by log_free_check().

log_flush_margin(): Removed. This is part of mtr_t::commit()
and other operations that write log.

log_t::create(), log_t::attach(): Guarantee that buf_free < max_buf_free
will always hold on PMEM, to satisfy an assumption of
log_t::get_write_target().

log_write_up_to(): Assert lsn!=0. Such calls are not incorrect, but it
is cheaper to test that single unlikely condition in mtr_t::commit()
rather than test several conditions in log_write_up_to().

innodb_drop_database(), unlock_and_close_files(): Check the LSN before
calling log_write_up_to().

ha_innobase::commit_inplace_alter_table(): Remove redundant calls to
log_write_up_to() after calling unlock_and_close_files().

Reviewed by: Vladislav Vaintroub
Stress tested by: Matthias Leich
Performance tested by: Steve Shaw

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.

- Fixing mariabackup.full_backup test case

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

Remove ORACLE from the (session) sql_mode in connections made with sql
service to run init queries

The connection is new and the global variable value takes effect
rather than the session value from the caller of spider_db_init.

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

Let us tolerate multiple "Memory pressure event freed"
in case there a real memory pressure event occurred
in addition to the one that this test simulates.
Also, clean up some SET variables.