MDEV-34167 We fail to terminate transaction early with ER_LOCK_TABLE_FULL when lock memory is growing

This regression is introduced in 10.6 by following commit.
commit b6a24724
MDEV-27891: SIGSEGV in InnoDB buffer pool resize

During DML, we check if buffer pool is running out of data pages in
buf_pool_t::running_out. Here is 75% of the buffer pool is occupied by
non-data pages we rollback the current transaction and exit with
ER_LOCK_TABLE_FULL.

The integer division (n_chunks_new / 4) becomes zero whenever the total
number of chunks are < 4 making the check completely ineffective for
such cases. Also the check is inaccurate for larger chunks.

Fix-1: Correct the check in buf_pool_t::running_out.

Fix-2: While waiting for free page, check for
buf_LRU_check_size_of_non_data_objects.

This regression is introduced in 10.6 by following commit.
commit 898dcf93
(Cleanup the lock creation)

It removed one important optimization for lock bitmap pre-allocation.
We pre-allocate about 8 byte extra space along with every lock object to
adjust for similar locks on newly created records on the same page by
same transaction. When it is exhausted, a new lock object is created
with similar 8 byte pre-allocation. With this optimization removed we
are left with only 1 byte pre-allocation. When large number of records
are inserted and locked in a single page, we end up creating too many
new locks almost in n^2 order.

Fix-1: Bring back LOCK_PAGE_BITMAP_MARGIN for pre-allocation.

Fix-2: Use the extra space (40 bytes) for bitmap in trx->lock.rec_pool.

MDEV-33769: Memory leak found in the test main.rownum run with --ps-protocol against a server built with the option -DWITH_PROTECT_STATEMENT_MEMROOT

A memory leak happens on the second execution of a query that run in PS mode
and uses the function ROWNUM().

A memory leak took place on allocation of an instance of the class Item_int
for storing a limit value that is performed at the function set_limit_for_unit
indirectly called from JOIN::optimize_inner. Typical trace to the place where
the memory leak occurred is below:
 JOIN::optimize_inner
  optimize_rownum
   process_direct_rownum_comparison
    set_limit_for_unit
     new (thd->mem_root) Item_int(thd, lim, MAX_BIGINT_WIDTH);

To fix this memory leak, calling of the function optimize_rownum()
has to be performed only once on first execution and never called
after that. To control it, the new data member
  first_rownum_optimization
added into the structure st_select_lex.

fsp_alloc_free_extent(): When applicable, set *err = DB_OUT_OF_FILE_SPACE.

FreeBSD doesn't like it and hangs.

As we don't wait for signal handler, let's disable SIGHUP in bootstrap
too

.opt files, unlike combinations, accumulate, let's not overuse them

followup for 13663cb5

Makes easier to add new policy to the list, and merge to newer versions-

Workaround patch: Do not remove GROUP BY clause when it has
subquer(ies) in it.

remove_redundant_subquery_clauses() removes redundant GROUP BY clause
from queries in form:
  expr IN (SELECT no_aggregates GROUP BY ...)
  expr {CMP} {ALL|ANY|SOME} (SELECT no_aggregates GROUP BY ...)
This hits problems when the GROUP BY clause itself has subquer(y/ies).

This patch is just a workaround: it disables removal of GROUP BY clause
if the clause has one or more subqueries in it.

Tests:
- subselect_elimination.test has all known crashing cases.
- subselect4.result, insert_select.result are updated.
Note that in some cases results of SELECT are changed too (not just
EXPLAINs). These are caused by non-deterministic SQL: when running a
query like:

  x > ANY( SELECT col1 FROM t1 GROUP BY constant_expression)

without removing the GROUP BY, the executor is free to pick the value
of t1.col1 from any row in the GROUP BY group (denote it $COL1_VAL).
Then, it computes x > ANY(SELECT $COL1_VAL).

When running the same query and removing the GROUP BY:

   x > ANY( SELECT col1 FROM t1)

the executor will actually check all rows of t1.

The problem was two fold:
- REPAIR TABLE t1 USE_FRM did not work for transactional
  Aria tables (Table was thought to be repaired, which it was not) which
  caused issues in later usage of the table.
- When swapping tmp_data file to data file, sort_info files where not
  updated. This caused problems if there was several unique keys and
  there was a duplicate for the second key.

Due to this bug a wrong result might be expected from queries with
an IN subquery predicate in the WHERE clause and a derived table in the
FROM clause to which split optimization could be applied.

The function JOIN::fix_all_splittings_in_plan() used the value of the
bitmap JOIN::sjm_lookup_tables() such as it had been left after the
search for the best plan for the select containing the splittable
derived table. That value could not be guaranteed to be correct. So the
recalculation of this bitmap is needed to exclude the plans with key
accesses from SJM lookup tables.

Approved by Igor Babaev <igor@maridb.com>

Otherwise spider_direct_sql may still think the spider plugin is
available even after spider_db_done() was called.

Revert "MDEV-19949 mariabackup option of '--password' or '-p' without specifying password in commandline"

This reverts commit 91fb8b7f.

Incompatible change, see tests in the next commit

Additional changes for the galera_vote_rejoin_ddl test (for 10.5+).
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

The spider suite should --source include/start_slave.inc to make sure
the slave is up before proceeding.

When running on macOS, MTR will ask the operating system for the core count when --parallel=auto

cnt counter was incremented one extra time per line

safety first - tell mariadb client not to execute dangerous
cli commands, they cannot be present in the dump anyway.

wrapping the command in /*!999999 ..... */ guarantees that
if a non-mariadb-cli client loads the dump and sends it to the
server - the server will ignore the command it doesn't understand

mysql --sandbox

disables system (\!), tee (\T), pager with an argument(\P foo), source (\.)

does *not* disable edit (\e). Use EDITOR=/bin/false to disable
or, for example, EDITOR=rnano for something more useful

does *not* disable pager (\P) without an argument. Use
PAGER=cat or, for example PAGER=less LESSSECURE=1 for something
more useful

using a disabled command is an error, which can be ignored with --force

Also, a "sandbox" command (\-) - enables the sandbox mode until EOF
(current file or the session, if interactive)

the client is C++, use a much more concise C++ syntax

as a bonus, arguments that are used, are no longer marked "unused"

on disable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE) the engine does
not know that the long unique is logically unique, because on the
engine level it is not. And the engine disables it,

Change the disable_indexes/enable_indexes API. Instead of the enum
mode, send a key_map of indexes that should be enabled. This way the
server will decide what is unique, not the engine.

- This is a merge of commit f378e764
from 10.4 to 10.5.

This commit fixes sporadic failures in galera_3nodes_sr.GCF-336
test. The following changes have been made here:

1) A small addition to the test itself which should make
   it more deterministic by waiting for non-primary state
   before COMMIT;
2) More careful handling of the wsrep_ready variable in
   the server code (it should always be protected with mutex).

No additional tests are required.

followup for c5896384