MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0 in ha_innobase::estimate_rows_upper_bound

- Fixing the compilation issue.

This fixes a valgrind failure where the bulk_size is used before
initialised in ha_spider::end_bulk_insert().

MDEV-34542  Assertion `lock_trx_has_sys_table_locks(trx) == __null' failed in void row_mysql_unfreeze_data_dictionary(trx_t*)

- During XA PREPARE, InnoDB releases the non-exclusive locks.
But it fails to remove the non-exclusive table lock from the
transaction table locks. In the mean time, main thread evicts
the table from the LRU cache. While rollbacking the XA transaction,
InnoDB iterates through the table locks to check whether it
holds lock on any system tables and wrongly assumes the
evicted table as system table since the table id is 0

Fix:
===
During XA PREPARE, remove the table locks of the transaction while
releasing the non-exclusive locks.

Problem:
========
- During shutdown, InnoDB tries to free the asynchronous
I/O slots and hangs. The reason is that InnoDB disables
asynchronous I/O before waiting for pending
asynchronous I/O to finish.

buf_load(): InnoDB aborts the buffer pool load due to
user requested shutdown and doesn't wait for the asynchronous
read to get completed. This could lead to debug assertion
in buf_flush_buffer_pool() during shutdown

Fix:
===
os_aio_free(): Should wait all read_slots and write_slots
to finish before disabling the aio.

buf_load(): Should wait for pending read request to complete
even though it was aborted.

Simplify in an attempt to avoid:

mysqltest: At line 275: File already exist: on the write_file
lines.

Using write_line as that's what a lot of other tests
do for writing small bits to a expect file.

Review thanks Valdislav Vaintroub

MDEV-34041 Display additional information for materialized subqueries in EXPLAIN/ANALYZE FORMAT=JSON

This commits adds the "materialization" block to the output of
EXPLAIN/ANALYZE FORMAT=JSON when materialized subqueries are involved
into processing. In the case of ANALYZE additional runtime information
is displayed, such as:
  - chosen strategy of materialization
  - number of partial match/index lookup loops
  - sizes of partial match buffers

from HAVING

The bug is caused by refixing of the constant subquery in pushdown from
HAVING into WHERE optimization.

Similarly to MDEV-29363 in the problematic query two references of the
constant subquery are used. After the pushdown one of the references of the
subquery is pushed into WHERE-clause and the second one remains as the part
of the HAVING-clause.
Before the represented fix, the constant subquery reference that was going to
be pushed into WHERE was cleaned up and fixed. That caused the changes of
the subquery itself and, therefore, changes for the second reference that
remained in HAVING. These changes caused a crash.

To fix this problem all constant objects that are going to be pushed into
WHERE should be marked with an IMMUTABLE_FL flag. Objects marked with this
flag are not cleaned up or fixed in the pushdown optimization.

Approved by Igor Babaev <igor@mariadb.com>

The version test on not_valgrind_build.inc was
broken as in BB the sp-no-valgrind.test was
executed.

The implication that it wouldn't work on ASAN
was also incorrect as ASAN tests show it running
fine there.

Correct sp-no-valgrind.test for not_valgrind.inc.

There are two problems.

First, replication fails when XA transactions are used where the
slave has replicate_do_db set and the client has touched a different
database when running DML such as inserts. This is because XA
commands are not treated as keywords, and are thereby not exempt
from the replication filter. The effect of this is that during an XA
transaction, if its logged “use db” from the master is filtered out
by the replication filter, then XA END will be ignored, yet its
corresponding XA PREPARE will be executed in an invalid state,
thereby breaking replication.

Second, if the slave replicates an XA transaction which results in
an empty transaction, the XA START through XA PREPARE first phase of
the transaction won’t be binlogged, yet the XA COMMIT will be
binlogged. This will break replication in chain configurations.

The first problem is fixed by treating XA commands in
Query_log_event as keywords, thus allowing them to bypass the
replication filter. Note that Query_log_event::is_trans_keyword() is
changed to accept a new parameter to define its mode, to either
check for XA commands or regular transaction commands, but not both.
In addition, mysqlbinlog is adapted to use this mode so its
--database filter does not remove XA commands from its output.

The second problem fixed by overwriting the XA state in the XID
cache to be XA_ROLLBACK_ONLY, so at commit time, the server knows to
rollback the transaction and skip its binlogging. If the xid cache
is cleared before an XA transaction receives its completion command
(e.g. on server shutdown), then before reporting ER_XAER_NOTA when
the completion command is executed, the filter is first checked if
the database is ignored, and if so, the error is ignored.

Reviewed By:
============
Kristian Nielsen <knielsen@knielsen-hq.org>
Andrei Elkin <andrei.elkin@mariadb.com>

The server does not log errors after startup when it is started without the
--console parameter and not as a service. This issue arises due to an
undocumented behavior of FreeConsole() in Windows when only a single
process (mariadbd/mysqld) is attached to it, causing the window to close.

In this case stderr is redirected to a file before FreeConsole()
is called. Procmon shows FreeConsole closing file handle
subsequent writes to stderr fail with ERROR_INVALID_HANDLE because
WriteFile() cannot operate on the closed handle. This results in losing
all messages after startup, including warnings, errors, notes, and
crash reports.

Additionally, some users reported stderr being redirected to
multi-master.info and failing at startup, but this could not be reproduced
here.

The workaround involves calling FreeConsole() right before the redirection of
stdout/stderr. This fix has been tested with XAMPP and via cmd.exe using
"start mysqld". Automated testing using MTR is challenging for this case.

The fix is only applicable to version 10.5. In later versions, the
FreeConsole() call has been removed.

MDEV-34474 InnoDB: Failing assertion: stat_n_leaf_pages > 0 in ha_innobase::estimate_rows_upper_bound

- Column stat_value and sample_size in mysql.innodb_index_stats
table is declared as BIGINT UNSIGNED without any check constraint.
user manually updates the value of stat_value and sample_size
to zero. InnoDB aborts the server while reading the statistics
information because InnoDB expects at least one leaf
page to exist for the index.

- To fix this issue, InnoDB should interpret the value of
stat_n_leaf_pages, stat_index_size in innodb_index_stats
stat_clustered_index_size, stat_sum_of_other_index_sizes
in innodb_table_stats as valid one even though user
mentioned it as 0.

The test has been made more stable according to
the recommendations of the Codership team.

The problem was in error message suppression, which did not match
the actual warning messages, due to bad quotations.
Changed warnings message suppressions to more simple format.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

DML transactions on FK-child tables also get table locks
on FK-parent tables. If there is a DML transaction holding
such a lock, and a TOI transaction starts, the latter
BF-aborts the former and puts itself into a waiting state.
If at this moment another DML transaction on FK-child table
starts, it doesn't check that the transaction waiting on
a parent table lock is TOI, and it erroneously BF-aborts
the waiting TOI transaction.

The fix: don't roll back high-priority transaction waiting
on a lock in InnoDB, instead roll back an incoming DML
transaction.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

The IO thread can report error code 2013 into the error log when it
is stopped during the initial connection process to the primary, as
well as when trying to read an event. However, because the IO thread
is being stopped, its connection to the primary is force-killed by
the signaling thread (see THD::awake_no_mutex()), and thereby these
connection errors should be ignored.

Reviewed By:
============
Kristian Nielsen <knielsen@knielsen-hq.org>

my_like_range*() can create longer keys than Field::char_length().
This caused warnings during print_range().

Fix:

Suppressing warnings in print_range().

Line numbers had to be removed from the ignorelists in order to be
diffed against since locations of the same findings can differ
across runs. Therefore preprocessing has to be done on the CI findings
so that it can be compared to the ignorelist and new findings can be
outputted. However, since line numbers have to be removed, a situation
occurs where it is difficult to reference the location of findings
in code given the output of the CI job.

To lessen this pain, change the cppcheck template to include
code snippets which make it easier to reference where in the code
the finding is referring to, even in the absence of line numbers.
Ignorelisting works as before since locations of the finding may
change but not the code it is referring to.

Furthermore, due to the innate difficulty in maintaining ignorelists
across branches and triaging new findings, allow failure as to not
have constantly failing pipelines as a result of a new findings that
have not been addressed yet.

Lastly, update SAST ignorelists to match the newly refactored cppcheck
job and the current state of the codebase.

All new code of the whole pull request, including one or several
files that are either new files or modified ones, are contributed
under the BSD-new license. I am contributing on behalf of my
employer Amazon Web Services, Inc.

Rectify cases of mismatched brackets and address
possible cases of division by zero by checking if
the denominator is zero before dividing.

No functional changes were made.

All new code of the whole pull request, including one or several
files that are either new files or modified ones, are contributed
under the BSD-new license. I am contributing on behalf of my
employer Amazon Web Services, Inc.

crc32_avx512(): Explicitly cast ssize_t(size) to make it clear that
we are indeed applying a negative offset to a pointer.

The issue was that when repairing an Aria table of row format PAGE and
the data file was bigger the 4G, the data file length was cut short
because of wrong parameters to MY_ALIGN().

The effect was that ALTER TABLE, OPTIMIZE TABLE or REPAIR TABLE would fail
on these tables, possibly corrupting them.
The MDEV also exposed a bug where error state was not propagated properly
to the upper level if the number of rows in the table changed.

The special logic used by the memory storage engine
to keep slaves in sync with the master on a restart can
break replication. In particular, after a restart, the
master writes DELETE statements in the binlog for
each MEMORY-based table so the slave can empty its
data. If the DELETE is not executable, e.g. due to
invalid triggers, the slave will error and fail, whereas
the master will never see the problem.

Instead of DELETE statements, use TRUNCATE to
keep slaves in-sync with the master, thereby bypassing
triggers.

Reviewed By:
===========
Kristian Nielsen <knielsen@knielsen-hq.org>
Andrei Elkin <andrei.elkin@mariadb.com>

During read only mode, InnoDB doesn't allow checkpoint to happen.
So InnoDB should throw the warning when InnoDB tries to
force the checkpoint when innodb_read_only = 1 or
innodb_force_recovery = 6.

MariaDB supports a "wait-free concurrent allocator based on pinning addresses".
In `lf_pinbox_real_free()` it tries to sort the pinned addresses for better
performance to use binary search during "real free". `alloca()` was used to
allocate stack memory and copy addresses.

To prevent a stack overflow when allocating the stack memory the function checks
if there's enough stack space. However, the available stack size was calculated
inaccurately which eventually caused database crash due to stack overflow.

The crash was seen on MariaDB 10.6.11 but the same code defect exists on all
MariaDB versions.

A similar issue happened previously and the fix in fc2c1e43 was to add a
`ALLOCA_SAFETY_MARGIN` which is 8192 bytes. However, that safety margin is not
enough during high connection workloads.

MySQL also had a similar issue and the fix
https://github.com/mysql/mysql-server/commit/b086fda was to remove the use of
`alloca` and replace qsort approach by a linear scan through all pointers (pins)
owned by each thread.

This commit is mostly the same as it is the only way to solve this issue as:
1. Frame sizes in different architecture can be different.
2. Number of active (non-null) pinned addresses varies, so the frame
   size for the recursive sorting function `msort_with_tmp` is also hard
   to predict.
3. Allocating big memory blocks in stack doesn't seem to be a very good
   practice.

For further details see the mentioned commit in MySQL and the inline comments.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.

The crash is caused by the attempt to refix the constant subquery during
pushdown from HAVING into WHERE optimization.

Every condition that is going to be pushed into WHERE clause is first
cleaned up, then refixed. Constant subqueries are not cleaned or refixed
because they will remain the same after refixing, so this complicated
procedure can be omitted for them (introduced in MDEV-21184).
Constant subqueries are marked with flag IMMUTABLE_FL, that helps to miss
the cleanup stage for them. Also they are marked as fixed, so refixing is
also not done for them.
Because of the multiple equality propagation several references to the same
constant subquery can exist in the condition that is going to be pushed
into WHERE. Before this patch, the problem appeared in the following way.
After the first reference to the constant subquery is processed, the flag
IMMUTABLE_FL for the constant subquery is disabled.
So, when the second reference to this constant subquery is processed, the
flag is already disabled and the subquery goes through the procedure of
cleaning and refixing. That causes a crash.

To solve this problem, IMMUTABLE_FL should be disabled only after all
references to the constant subquery are processed, so after the whole
condition that is going to be pushed is cleaned up and refixed.

Approved by Igor Babaev <igor@maridb.com>

Valgrind looks as the assertions as examining uninitalized values.

As the assertions are tested in other Debug builds we know
it isn't all invalid.

Account for Valgrind by removing the assertion under
the WITH_VALGRIND=1 compulation.

The feedback plugin server_uid variable and the calculate_server_uid()
function is moved from feedback/utils.cc to sql/mysqld.cc

server_uid is added as a global variable (shown in 'show variables') and
is written to the error log on server startup together with server version
and server commit id.

We have an issue if a user have the following in a configuration file:
log_slow_filter=""                  # Log everything to slow query log
log_queries_not_using_indexes=ON

This set log_slow_filter to 'not_using_index' which disables
slow_query_logging of most queries.
In effect, on should never use log_slow_filter="" in config files but
instead use log_slow_filter=ALL.

Fixed by changing log_slow_filter="" that comes either from a
configuration file or from the command line, when starting to the server,
to log_slow_filter=ALL.
A warning will be printed when this happens.

Other things:
- One can now use =ALL for any 'set' variable to set all options at once.
  (backported from 10.6)

Fix for v. 10.5

Item_func_hex::fix_length_and_dec() evaluated a too short data type
for signed numeric arguments, which resulted in a 'Data too long for column'
error on CREATE..SELECT.

Fixing the code to take into account that a short negative
numer can produce a long HEX value: -1  -> 'FFFFFFFFFFFFFFFF'

Also fixing Item_func_hex::val_str_ascii_from_val_real().
Without this change, MTR test with HEX with negative float point arguments
failed on some platforms (aarch64, ppc64le, s390-x).

extra_port and port are 16 bit numbers and not 32 bit as they are
tcp ports.

Restrict their value.

Mismatched IF/ENDIF statements in cmake
caused a warning.

PFS_atomic class contains wrappers around my_atomic_* operations, which
are macros to GNU atomic operations (__atomic_*). Due to different
implementations of compilers, clang may encounter errors when compiling
on x86_32 architecture.

The following functions are replaced with C++ std::atomic type in
performance schema code base:
  - PFS_atomic::store_*()
      -> my_atomic_store*
        -> __atomic_store_n()
    => std::atomic<T>::store()

  - PFS_atomic::load_*()
      -> my_atomic_load*
        -> __atomic_load_n()
    => std::atomic<T>::load()

  - PFS_atomic::add_*()
      -> my_atomic_add*
        -> __atomic_fetch_add()
    => std::atomic<T>::fetch_add()

  - PFS_atomic::cas_*()
    -> my_atomic_cas*
      -> __atomic_compare_exchange_n()
    => std::atomic<T>::compare_exchange_strong()

and PFS_atomic class could be dropped completely.

Note that in the wrapper memory order passed to original GNU atomic
extensions are hard-coded as `__ATOMIC_SEQ_CST`, which is equivalent to
`std::memory_order_seq_cst` in C++, and is the default parameter for
std::atomic_* functions.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services.

CMake WSREP=ON has some implications for client
executables so still present this as an option
when compiling WITHOUT_SERVER. In this case
default to ON for maximium compatibility of
the build client executables and libraries.

The log_event_old.cc is included by mysqlbinlog.cc.

With -DWITHOUT_SERVER the include path for the wsrep
include headers isn't there.

As these aren't needed by the mariadb-binlog, move
these to under a ifndef MYSQL_CLIENT preprocessor.

Caused by MDEV-18590

Just like the spider/bugfix suite.

One caveat is that my_2_3.cnf needs something under mysqld.2.3 group,
otherwise mtr will fail with something like:

There is no group named 'mysqld.2.3' that can be used to resolve
'port' for ...

This will allow new tests under the spider suite to use what is
needed. It also somehow fixes issues of running a test followed by
spider.slave_trx_isolation.