trx_purge_free_segment(), trx_purge_truncate_rseg_history():
Replace some unreachable code with debug assertions.
A buffer-fix does prevent pages from being evicted
from the buffer pool; see buf_page_t::can_relocate().

Tested by: Matthias Leich

The code in choose_best_splitting() assumed that the join prefix is
in join->positions[].

This is not necessarily the case. This function might be called when
the join prefix is in join->best_positions[], too.
Follow the approach from best_access_path(), which calls this function:
pass the current join prefix as an argument,
"const POSITION *join_positions" and use that.

In commit f99a8918 this line was changed to not use awk, and new version
copied both to init file and preinst file but overlooking that they use
different variable names.

Also fix minor syntax issues to make Shellcheck happy.

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.

buf_flush_page_cleaner(): Whenever buf_pool.ran_out(), invoke
buf_pool.get_oldest_modification(0) so that all clean blocks
will be removed from buf_pool.flush_list and buf_flush_LRU_list_batch()
will be able to evict some pages.

This fixes a regression that was likely caused by
commit a55b951e (MDEV-26827).

srv_export_innodb_status(): Update
export_vars.innodb_buffer_pool_read_requests as it was done
before commit a55b951e (MDEV-26827).
If innodb_status_variables[] pointed to a sharded variable, it would
only access the first shard.

innodb_counter_info[]: Revert a change that was accidentally made in
commit 204e7225

btr_cur_need_opposite_intention(): Check also page_zip_available()
so that we will escalate to exclusive index latch when a non-leaf
page may have to be split further due to ROW_FORMAT=COMPRESSED page
overflow.

Tested by: Matthias Leich

trx_purge_truncate_rseg_history(): Avoid a leak similar to the one
that was fixed in MDEV-31324, in case a supposedly cached undo log
page is not found in the rseg.undo_cached list.

trx_purge_truncate_history(): While waiting for a write-fixed block
to become available, simply wait for an exclusive latch on it.
Also, simplify the iteration: first check for oldest_modification>2
(to ignore clean pages or pages belonging to the temporary tablespace)
and then compare the tablespace identifier.

Before releasing buf_pool.flush_list_mutex we will buffer-fix the block
of interest. In that way, buf_page_t::can_relocate() will not hold on
the block and it must remain in the buffer pool until we have acquired
an exclusive latch on it. If the block is still dirty, we will register
it with the tablespace truncation mini-transaction; else, we will simply
release the latch and buffer-fix and move to the next block.

This also reverts commit c4d79399
because that fix should no longer be necessary; the wait for an
exclusive block latch should allow buf_pool_t::release_freed_page()
on the same block to proceed.

Tested by: Axel Schwenke, Matthias Leich

trx_purge_truncate_history(): Relax a condition that would prevent
undo log truncation if the undo log tablespaces were "contaminated"
by the bug that commit e0084b9d fixed.
That is, trx_purge_truncate_rseg_history() would have invoked
flst_remove() on TRX_RSEG_HISTORY but not reduced TRX_RSEG_HISTORY_SIZE.

To avoid any regression with normal operation, we implement this
fixup during slow shutdown only. The condition on the history list
being empty is necessary: without it, in the test
innodb.undo_truncate_recover there may be much fewer than the
expected 90,000 calls to row_purge() before the truncation.
That is, we would truncate the undo tablespace before actually having
processed all undo log records in it.

To truncate such "contaminated" or "bloated" undo log tablespaces
(when using innodb_undo_tablespaces=2 or more)
you can execute the following SQL:

BEGIN;INSERT mysql.innodb_table_stats VALUES('','',DEFAULT,0,0,0);ROLLBACK;
SET GLOBAL innodb_undo_log_truncate=ON, innodb_fast_shutdown=0;
SHUTDOWN;

The first line creates a dummy InnoDB transaction, to ensure that there
will be some history to be purged during shutdown and that the undo
tablespaces will be truncated.

trx_purge_truncate_rseg_history(): Add a parameter to specify if
the entire rollback segment is safe to be freed. If not, we may
still be able to invoke trx_undo_truncate_start() and free some pages.

trx_purge_truncate_history(): Only call trx_purge_truncate_rseg_history()
if the rollback segment is safe to process. This will avoid leaking undo
log pages that are not yet ready to be processed. This fixes a regression
that was introduced in
commit 0de3be8c (MDEV-30671).

trx_sys_t::any_active_transactions(): Separately count XA PREPARE
transactions.

srv_purge_should_exit(): Terminate slow shutdown if the history size
does not change and XA PREPARE transactions exist in the system.
This will avoid a hang of the test innodb.recovery_shutdown.

Tested by: Matthias Leich

This reverts commit b05218e0.

This reverts commit 844ddb11.

This fixes MDEV-30967, MDEV-31325, MDEV-31388

This bug could affect queries containing a subquery over splittable derived
tables and having an outer references in its WHERE clause. If such subquery
contained an equality condition whose left part was a reference to a column
of the derived table and the right part referred only to outer columns
then the server crashed in the function st_join_table::choose_best_splitting()
The crashing code was added in the commit ce7ffe61
that made the code of the function sensitive to presence of the flag
OUTER_REF_TABLE_BIT in the KEYUSE_EXT::needed_in_prefix fields.

The field needed_in_prefix of the KEYUSE_EXT structure should not contain
table maps with OUTER_REF_TABLE_BIT or RAND_TABLE_BIT.

Note that this fix is quite conservative: for affected queries it just
returns the query plans that were used before the above mentioned commit.
In fact the equalities causing crashes should be pushed into derived tables
without any usage of split optimization.

Approved by Sergei Petrunia <sergey@mariadb.com>

EXPLAIN EXTENDED should always print the field item used in the left part
of an equality expression from the SET clause of an update statement as a
reference to table column.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

buf_page_free(): If buffered changes existed for the page, drop them.

Co-developed with Thirunarayanan Balathandayuthapani

A 3-thread deadlock has been frequently observed when using
innodb_change_buffering!=none and innodb_file_per_table=0:

(1) ibuf_merge_or_delete_for_page() holding an exclusive latch on the block
and waiting for an exclusive tablespace latch in fseg_page_is_allocated()
(2) btr_free_but_not_root() in fseg_free_step() waiting for an
exclusive tablespace latch
(3) fsp_alloc_free_page() holding the exclusive tablespace latch and waiting
for a latch on the block, which it is reallocating for something else

While this was reproduced using innodb_file_per_table=0, this hang should
be theoretically possible in .ibd files as well, when the recovery or
cleanup of a failed DROP INDEX or ADD INDEX is executing concurrently
with something that involves page allocation.

ibuf_merge_or_delete_for_page(): Avoid invoking fseg_page_is_allocated()
when block==nullptr. The call was redundant in this case, and it could
cause deadlocks due to latching order violation.

ibuf_read_merge_pages(): Acquire an exclusive tablespace latch
before invoking buf_page_get_gen(), which may cause
fseg_page_is_allocated() to be invoked in ibuf_merge_or_delete_for_page().

Note: This will not fix all latching order violations in this area!
Deadlocks involving ibuf_merge_or_delete_for_page(block!=nullptr) are
still possible if the caller is not acquiring an exclusive tablespace latch
upfront. This would be the case in any read operation that involves a
change buffer merge, such as SELECT, CHECK TABLE, or any DML operation that
cannot be buffered in the change buffer.

recv_sys_t::apply(): When applying an undo log truncation operation,
invoke os_file_truncate() on space->recv_size, which must not be
less than the original truncated file size.

Alternatively, as pointed out by Thirunarayanan Balathandayuthapani,
we could assign space->size = t.pages, so that
fil_system_t::extend_to_recv_size() would extend the file back
to space->recv_size.

fil_space_t::add(): If a file handle was passed, invoke
fil_node_t::find_metadata() before releasing fil_system.mutex.
The call was moved from fil_ibd_create().

This is a 10.5 version of commit e3b06156
from 10.6.

- InnoDB purge tries to access aborted secondary index and access
the freed secondary index root page.

fil_ibd_create(): Hold fil_system.mutex until fil_node_t::find_metadata()
has completed, so that node->handle cannot be closed by a concurrent
thread. This race condition was introduced
in commit 10dd290b (MDEV-17380).

Tested by: Matthias Leich

recv_recover_page(): Remove some code which was added in
commit 0b47c126 with
no good reason and which would cause a hang after a corrupted
page was reported during crash recovery.

Tested by: Matthias Leich

There is unwanted cut'n'paste variable name in Debian pre-inst
script which causes:

df: '': No such file or directory
/var/lib/dpkg/tmp.ci/preinst: line 215: [: : integer expression expected

Rename variable to correct one and make check that that directory
or symlink really exists. If it does not then fail with error
and message.

recv_sys_t::apply(): Remove a bogus debug assertion that had been
added in commit f2c17cc9 (MDEV-29911).

It is perfectly normal that when the server was killed in the middle of
writing multiple redo log blocks, the recovery would end such that
recv_sys.scanned_lsn will point to the end of the last complete 512-byte
log block, but recv_sys.recovered_lsn will be less than that.

Also, correct the function comment of recv_sys_t::parse().

In commit f99a8918 this line was changed to not use awk, and new version
copied both to init file and preinst file but overlooking that they use
different variable names.

Also fix minor syntax issues to make Shellcheck happy.

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.

buf_flush_page_cleaner(): Whenever buf_pool.ran_out(), invoke
buf_pool.get_oldest_modification(0) so that all clean blocks
will be removed from buf_pool.flush_list and buf_flush_LRU_list_batch()
will be able to evict some pages.

This fixes a regression that was likely caused by
commit a55b951e (MDEV-26827).

row_ins_sec_index_entry_by_modify(): When noticing a corrupted secondary
index on which CREATE INDEX is not in progress, return DB_CORRUPTION
instead of intentionally crashing the server.

Tested by: Matthias Leich

trx_undo_set_state_at_finish(): Merge to its only caller,
trx_purge_add_undo_to_history().

trx_purge_add_undo_to_history(): Evaluate the condition related to
TRX_UNDO_STATE only once.

Tested by: Matthias Leich

trx_purge_truncate_history(): While waiting for a write-fixed block
to become available, simply wait for an exclusive latch on it.
Also, simplify the iteration: first check for oldest_modification>2
(to ignore clean pages or pages belonging to the temporary tablespace)
and then compare the tablespace identifier.

Before releasing buf_pool.flush_list_mutex we will buffer-fix the block
of interest. In that way, buf_page_t::can_relocate() will not hold on
the block and it must remain in the buffer pool until we have acquired
an exclusive latch on it. If the block is still dirty, we will register
it with the tablespace truncation mini-transaction; else, we will simply
release the latch and buffer-fix and move to the next block.

This also reverts commit c4d79399
because that fix should no longer be necessary; the wait for an
exclusive block latch should allow buf_pool_t::release_freed_page()
on the same block to proceed.

Tested by: Axel Schwenke, Matthias Leich

Extra tests for MDEV-30483 After upgrade to 10.6 from Mysql 5.7 seeing "InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL"

Adding tests demonstrating that columns:
- mysql.innodb_table_stats.last_update
- mysql.innodb_index_stats.last_update

contain sane values close to NOW() rathar than a garbage.

Tests cover these three underlying TIMESTAMP data formats:

- MariaDB Field_timestamp0 - UINT4 based
  Like in a MariaDB native installation running with mysql56_temporal_format=0

- MariaDB Field_timestampf - BINARY(4) based, with UNSIGNED_FLAG
  Like in a MariaDB native installation running with mysql56_temporal_format=1

- MySQL-alike Field_timestampf - BINARY(4) based, without UNSIGNED_FLAG
  Like with a MariaDB server running over a MySQL-5.6 directory
  (e.g. during a migragion).

MDEV-30483 After upgrade to 10.6 from Mysql 5.7 seeing "InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL"

Problem:

Field_timestampf implementations differ in MySQL and MariaDB:
- MariaDB sets the UNSIGNED_FLAG in Field::flags
- MySQL does not

The reference table structures
(defined in table_stats_schema and index_stats_schema)
expected the last_update column to have the DATA_UNSIGNED flag,
because MariaDB's Field_timestampf has the UNSIGNED_FLAG.
It worked fine on pure MariaDB installations.

However, if a MariaDB server starts over a MySQL-5.7 data directory during
a migration, the last_update column does not have DATA_UNSIGNED flag,
because MySQL's Field_timestampf does not have the UNSIGNED_FLAG.

This made InnoDB (after the migration from MySQL) complain into the server
error log about the unexpected data type.

The actual fix is done in storage/innobase/dict/dict0stats.cc:
It removes DATA_UNSIGNED from the prtype_mask member of the reference columns,
so now it does not require the underlying columns to have this flag.

The rest of the fix is needed for MTR tests.
The new data type plugin TYPE_MYSQL_TIMESTAMP implements a slightly modified
version of Field_timestampf, which removes the unsigned flag, so
it works like MySQL's Field_timestampf.

The MTR test ALTERs the data type of the columns
table_stats_schema.last_update and index_stats_schema.last_update
from TIMESTAMP to TYPE_MYSQL_TIMESTAMP, then makes InnoDB
verify the structure of the two statistics tables by creating
and populating an InnoDB table t1.

Without the fix made storage/innobase/dict/dict0stats.cc,
MTR complains about unexpected warnings in the server error log:

[ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is ...
[ERROR] InnoDB: Column last_update in table mysql.innodb_index_stats is ...

With the fix made storage/innobase/dict/dict0stats.cc these warnings
go away.

The crash happened in filename_to_spacename() when using it on a
filename that is not in the format of "./database/table.ibd".
According to Marko, it is possible the function is called with
the path to an undo file, which would cause a crash.

This patch fixes this by,  instead of crashing with unexpected filenames,
returning them 'as such', except for changing all '\' to '/'.