Synopsis: If SELECT returned answer from Query Cache it is not really executed.

The reason for firing of assertion
  DBUG_ASSERT((mem_root->flags & ROOT_FLAG_READ_ONLY) == 0);
is that in case the query_cache is on and the same query run by different
stored routines the following use case can take place:
First, lets say that bodies of routines used by the test case are the same
and contains the only query 'SELECT * FROM t1';
  call p1() -- a result set is stored in query cache for further use.
  call p2() -- the same query is run against the table t1, that result in
               not running the actual query but using its cached result.
               On finishing execution of this routine, its memory root is
               marked for read only since every SP instruction that this
               routine contains has been executed.
  INSERT INT t1 VALUE (1); -- force following invalidation of query cache
  call p2() -- query the table t1 will result in assertion failure since its
               execution would require allocation on the memory root that
               has been already marked as read only memory root

The root cause of firing the assertion is that memory root of the stored
routine 'p2' was marked as read only although actual execution of the query
contained inside hadn't been performed.

To fix the issue, mark a SP instruction as not yet run in case its execution
doesn't result in real query processing and a result set got from query cache
instead.

Note that, this issue relates server built in debug mode AND with the protect
statement memory root feature turned on. It doesn't affect server built
in release mode.

It was updated for 10.6+ in MDEV-7317. Because a lower version spider
node may connect to a higher version data node, we need to change this
for 10.4 and 10.5 as well.

It was done in MDEV-29447.

In the case if some unique key fields are nullable, there can be
several records with the same key fields in unique index with at least
one key field equal to NULL, as NULL != NULL.

When transaction is resumed after waiting on the record with at least one
key field equal to NULL, and stored in persistent cursor record is
deleted, persistent cursor can be restored to the record with all key
fields equal to the stored ones, but with at least one field equal to
NULL. And such record is wrongly treated as a record with the same unique
key as stored in persistent cursor record one, what is wrong as
NULL != NULL.

The fix is to check if at least one unique field is NULL in restored
persistent cursor position, and, if so, then don't treat the record as
one with the same unique key as in the stored record key.

dict_index_t::nulls_equal was removed, as it was initially developed for
never existed in MariaDB "intrinsic tables", and there is no code, which
would set it to "true".

Reviewed by Marko Mäkelä.

In commit d74d9596 (MDEV-18543)
there was an error that would cause the hidden metadata record
to be deleted, and therefore cause the table to appear corrupted
when it is reloaded into the data dictionary cache.

PageConverter::update_records(): Do not delete the metadata record,
but do validate it.

RecIterator::open(): Make the API more similar to 10.6, to simplify
merges.

Same as MDEV-29579. For some reason, libodbc does not clean up
properly if unloaded too early with the dlclose() of spider. So we add
UNIQUE symbols to spider so the spider does not reload in dlclose().

This change, however, uncovers some hidden problems in the spider
codebase, for which we move the initialisation of some spider global
variables into the initialisation of spider itself.

Spider has some global variables. Their initialisation should be done
in the initialisation of spider itself, otherwise, if spider were
re-initialised without these symbol being unloaded, the values could
be inconsistent and causing issues.

One such issue is caused by the variables
spider_mon_table_cache_version and spider_mon_table_cache_version_req.
They are used for resetting the spider monitoring table cache and have
initial values of 0 and 1 respectively. We have that always
spider_mon_table_cache_version_req >= spider_mon_table_cache_version,
and when the relation is strict, the cache is reset,
spider_mon_table_cache_version is brought to be equal to
spider_mon_table_cache_version_req, and the cache is searched for
matching table_name, db_name and link_idx. If the relation is equal,
no reset would happen and the cache would be searched directly.

When spider is re-inited without resetting the values of
spider_mon_table_cache_version and spider_mon_table_cache_version_req
that were set to be equal in the previous cache reset action, the
cache was emptied in the previous spider deinit, which would result in
HA_ERR_KEY_NOT_FOUND unexpectedly.

An alternative way to fix this issue would be to call the spider udf
spider_flush_mon_cache_table(), which increments
spider_mon_table_cache_version_req thus making sure the inequality is
strict. However, there's no reason for spider to initialise these
global variables on dlopen(), rather than on spider init, which is
cleaner and "purer".

To reproduce this issue, simply revert the changes involving the two
variables and then run:

mtr --no-reorder spider.ha{,_part}

Commit 6dce6aec breaks out of a loop
in ha_partition::info when some partitions aren't opened, in which
case auto_increment_value assertion will fail. This commit patches
that hole.

Similar to MDEV-27658.

Also fixing the positioning of #ifdef WITH_PARTITION_STORAGE_ENGINE
blocks and add missing ones.

The spider group by handler is created in
JOIN::make_aggr_tables_info(), by which time calls to
substitute_for_best_equal_field() should have already removed all the
multiple equalities (i.e. Item_equal, with MULT_EQUAL_FUNC func_type).
Therefore, if there is still such items, it is deemed as an optimizer
bug and should be skipped.

change created by:

unifdef -DMYSQL_VERSION_ID=100400 -DMARIADB_BASE_VERSION -m storage/spider/spd_* storage/spider/ha_spider.* storage/spider/hs_client/*

basically MDEV-27637, MDEV-27641, MDEV-27655

Also removed ITEM_FUNC_TIMESTAMPDIFF_ARE_PUBLIC.

Similar to pr#2225, with the testcase adapted from that patch:

--8<---------------cut here---------------start------------->8---
From 884f7c6df16236748ca975339e0b1c267e195309 Mon Sep 17 00:00:00 2001
From: "Norio Akagi (norakagi)" <norakagi@amazon.com>
Date: Wed, 3 Aug 2022 23:30:34 -0700
Subject: [PATCH] [MDEV-28992] Push down TIMESTAMP_DIFF in spider

This changes so that TIMESTAMP_DIFF function in a query is pushed down and works natively in Spider.
Instead of directly accessing item's member, now we can rely on a public accessor method to make it work.
Unit tests are added under spider.pushdown_timestamp_diff.

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.
--8<---------------cut here---------------end--------------->8---

similar to MDEV-30981

- InnoDB reserves the free extents unnecessarily during blob
page allocation even though btr_page_alloc() can handle
reserving the extent when the existing ran out of pages to be used.

JOIN_CACHE has a light-weight initialization mode that's targeted at
EXPLAINs. In that mode, JOIN_CACHE objects are not able to execute.

Light-weight mode was used whenever the statement was an EXPLAIN. However
the EXPLAIN can execute subqueries, provided they enumerate less than
@@expensive_subquery_limit rows.

Make sure we use light-weight initialization mode only when the select is
more expensive @@expensive_subquery_limit.

Also add an assert into JOIN_CACHE::put_record() which prevents its use
if it was initialized for EXPLAIN only.

Fix a case of stack-use-after-return reported by ASAN in
Wsrep_schema_impl::open_table(). This function has a stack allocated
TABLE_LIST object and return TABLE_LIST::table to the caller.
Changed the function to take a TABLE_LIST pointer as argument.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

The failure should be table not found, rather than no spider same
server link

Partial documentation due to time constraints. Will improve over time.

Also removed a redundant parameter link_idx from
spider_get_sys_tables_connect_info().

And deleted some commented out code.

- Fix to avoid mysqltest client getting killed abruptly during
  mysql_shutdown(). When Galera replication is shutdown, wait for
  THDs with `thd->stmt_da()->is_eof()` to disconnect (these are about
  to disconnect anyway).
- Extract duplicate code from `wsrep_stop_replication()` and
  `wsrep_shutdown_replication()` in a new function.
- No need to use a custom `shutdown_mysqld.inc` in galera
  suite. Delete it, so that the one in `mysql-test/include/` is used.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

This should fix all future problems caused by a non-default global
sql_mode from the server where spider is to be installed.

Like the fix for MDEV-32753 and MDEV-33242, spider init queries
creates new connections that use the global sql_mode of the existing
connection.

- Background encryption threads wait for stop flag
to exit early from the tablespace. Alter operation
fails to set the stop flag before waiting for the
encryption thread to stop using the tablespace.

Add "real ip:<ip_or_localhost>" part to the aborted message
Only for proxy-protocoled connection, so it does not  not to cause
confusion to normal users.

Make IN->EXISTS rewrite not to compute constant left expression if it
has a subquery in it.

Fix function `remove_fragment()` in wsrep_schema so that no error is
raised if the fragment to be removed is not found in the
wsrep_streaming_log table. This is necessary to handle the case where
streaming transaction in idle state is BF aborted. This may result in
the case where the rollbacker thread successfully removes the
transaction's fragments, followed by the applier's attempt to remove
the same fragments. Causing the node to leave the cluster after
reporting a "Failed to apply write set" error.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

This fixes up 83a87da4

This had become unused in commit 2e814d47
or mysql/mysql-server@eca5b0fc17a5bd6d4833d35a0d08c8549dd3b5ec.
This cleanup affects mtr_buf_t::used().

MTR buildbot output suggest that buildbot can lose some stdout information
by overwriting it with stderr, which is captured separately

This is bad, since stdout contains information about failing test.
So, this is an attempt to minimize the damage by excluding most frequent
stderr messages - those about restart.

An attempt to fix lost output sometimes seen on buildbot.