Revert "MDEV-27233 Server hangs when using --init-file which loads Spider and creates a Spider table"

This reverts commit c160a115.

also call top_level_transform() recursively for

(a OR b) AND (c OR d)

it's incorrect to use change_item_tree() to replace arguments
of top-level AND/OR, because they (arguments) are stored in a List,
so a pointer to an argument is in the list_node, and individual
list_node's of top-level AND/OR can be deleted in Item_cond::build_equal_items().
In that case rollback_item_tree_changes() will modify the deleted object.

Luckily, it's not needed to use change_item_tree() for top-level
AND/OR, because the whole top-level item is copied and preserved
in prep_where and prep_on, and restored from there.

So, just don't.

Post-push fix. The flag of transaction which indicates that it's necessary
to forbid gap lock inheritance after XA PREPARE could be inverted if
lock_release_on_prepare_try() is invoked several times. The fix is to
toggle it on lock_release_on_prepare() exit.

The test fails extremely often on a Windows builder, and nobody seems
to be interested in fixing it.

ha_innobase::check(): Do not enable READ UNCOMMITTED isolation level
for temporary tables, because it would report index count mismatch
for secondary indexes.

row_check_index(): Ignore EXTENDED for temporary tables, because
the tables are private to the current connection and there will be
no purge of committed transaction history.

The test innodb.innodb-wl5522-debug would occasionally hang
(especially when run with ./mtr --rr) due to a deadlock between
btr_store_big_rec_extern_fields() and dict_stats_analyze_index().
The two threads would acquire the clustered index root page latch and
the tablespace latch in the opposite order. The deadlock was possible
because dict_stats_analyze_index() was holding the index latch in
shared mode and an index root page latch, while waiting for the
tablespace latch. If a stronger dict_index_t::lock had been held
by dict_stats_analyze_index(), any operations that free or allocate
index pages would have been blocked.

In each caller of fseg_n_reserved_pages() except ibuf_init_at_db_start()
which is a special case for ibuf.index at database startup, we must hold
an index latch that prevents concurrent allocation or freeing of index
pages.

Any operation that allocates or free pages that belong to an index tree
must first acquire an index latch in Update or Exclusive mode, and while
holding that, acquire an index root page latch in Update or Exclusive
mode.

dict_index_t::clear(): Also acquire an index latch. Otherwise,
the test innodb.insert_into_empty could hang.

btr_get_size_and_reserved(): Assert that a strong enough index latch
is being held. Only acquire a shared fil_space_t::latch; we are only
reading, not modifying any data.

dict_stats_update_transient_for_index(),
dict_stats_analyze_index(): Acquire a strong enough index latch. Only
acquire a shared fil_space_t::latch.

These operations had followed the same order of acquiring latches in
every InnoDB version since the very beginning
(commit c533308a).
The calls for acquiring tablespace latch had previously been moved in
commit 87839258 and
commit 1e9c922f.

The hang was introduced in
commit 2e814d47 which imported
mysql/mysql-server@ac74632293bea967b352d1b472abedeeaa921b98
which failed to strengthen the locking requirements of the function
btr_get_size().

1. The merge aeccbbd9 has overwritten
lock0lock.cc, and the changes of MDEV-29622 and MDEV-29635 were
partially lost, this commit restores the changes.

2. innodb.deadlock_wait_thr_race test:

The following hang was found during testing.

There is deadlock_report_before_lock_releasing sync point in
Deadlock::report(), which is waiting for sel_cont signal under lock_sys_t
lock. The signal must be issued after "UPDATE t SET b = 100" rollback,
and that rollback is executing undo record, which is blocked
on dict_sys latch request. dict_sys is locked by the thread of statistics
update(dict_stats_save()), and during that update lock_sys lock is
requested, and can't be acquired as Deadlock::report() holds it. We have
to disable statistics update to make the test stable.

But even if statistics update is disabled, and transaction with consistent
snapshot is started at the very beginning of the test to prevent purging,
the purge can still be invoked for system tables, and it tries to open
system table by id, what causes dict_sys.freeze() call and dict_sys
latching. What, in combination with lock_sys::xx_lock() causes the same
deadlock as described above. We need to disable purging globally for the
test as well.

All the above is applicable to innodb.deadlock_wait_lock_race test also.

If all elements in the list of 'IN' or 'NOT IN' clause are equal
and there are no NULLs then clause
-  "a IN (e1,..,en)" can be converted to "a = e1"
-  "a NOT IN (e1,..,en)" can be converted to "a <> e1".
This means an object of Item_func_in can be replaced with an object
of Item_func_eq for IN (e1,..,en) clause and Item_func_ne for
NOT IN (e1,...,en). Such a replacement allows the optimizer to choose
a better execution plan

Non-blocking log_write_upto (MDEV-24341) was only designed  for the
client connections. Fix, so it is not be triggered for any system THD.

Previously, an incomplete solution only excluded Innodb purge THDs, but
not  the slave for example.

The hang in MDEV still remains somewhat a mystery though, it is not
immediately clear how exactly condition variable can become corrupted.
But it is clear that it can be avoided.

To prevent ASAN heap-use-after-poison in the MDEV-16549 part of
./mtr --repeat=6 main.derived
the initialization of Name_resolution_context was cleaned up.

- InnoDB should do partial rollback when error happens during
buffered bulk insert write operation.

- Background statistics thread should keep the table in the
statistics queue itself when the table under bulk insert operation

dict_stats_analyze_index(): Set the maximum value for index_stats_t
if the table is in bulk operation

dict_stats_update(), dict_stats_update_transient_for_index(),
dict_stats_update_transient(): Returns DB_SUCCESS_LOCKED_REC
if the table under bulk insert operation

dict_stats_process_entry_from_recalc_pool(): Add the table
back to recalc pool if the table under bulk insert operation

We'd like to update our HELP contents independently of the test cases.

Like 5e270ca2, the same concept
is applied to make the MTR output of a dummy help topic is done
to prove the HELP can works as a prepared statement.

The lock is created during page splitting after moving records and
locks(lock_move_rec_list_(start|end)()) to the new page, and inheriting
the locks to the supremum of left page from the successor of the infimum
on right page.

There is no need in such inheritance for READ COMMITTED isolation level
and not-gap locks, so the fix is to add the corresponding condition in
gap lock inheritance function.

One more fix is to forbid gap lock inheritance if XA was prepared. Use the
most significant bit of trx_t::n_ref to indicate that gap lock inheritance
is forbidden. This fix is based on
mysql/mysql-server@b063e52a8367dc9d5ed418e7f6d96400867e9f43

FreeBSD 13 has libcurl in /usr/local/lib so linking failed just trying
to link curl by name. CMake finds curl's true place so let's use that.

Read the version of the view share when we read definition to prevent
simultaniouse access to a view table SHARE (and so its MEM_ROOT)
from different threads.

MDEV-16549 Server crashes in Item_field::fix_fields on query with view and subquery, Assertion `context' failed, Assertion `field' failed

Add one-table-resolve context for items created with an aim of switching
to temporary table because then it can be cloned in push-down-condition.

The problem was fixed earlier. Adding an MTR test only.

Spider waits until the server initialization has been completed
(i.e., mysqld_server_started has been set to 1) before creating the
Spider system tables. Spider also wait until the system tables have
been created before instantiating ha_spider.

These waits leads to dead lock when a non-system Spider table is
created by --in-file. This is because queries passed by --in-file
are executed during the server initialization and creating the
non-system Spider table causes an instantiation of ha_spider.

In the first place, there seems to be no need for Spider to do such
a synchronization. Thus, we simply remove the synchronization.

GRANT ROLE can update db-level privileges -> must invalidate acl_cache

* to "clear hostname cache" one needs to use hostname_cache->clear()
* no need to clear acl_cache for SET DEFAULT ROLE

This is a new version of the patch instead of the reverted:

  MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not work after upgrade

Ignore the difference in key packing flags HA_BINARY_PACK_KEY and HA_PACK_KEY
during ALTER to allow ALGORITHM=INSTANT and ALGORITHM=NOCOPY in more cases.

If for some reasons (e.g. due to a bug fix such as MDEV-20704) these
cumulative (over all segments) flags in KEY::flags are different for
the old and new table inside compare_keys_but_name(), the difference
in HA_BINARY_PACK_KEY and HA_PACK_KEY in KEY::flags is not really important:

MyISAM and Aria can handle such cases well: per-segment flags are stored in
MYI and MAI files anyway and they are read during ha_myisam::open()
ha_maria::open() time. So indexes get opened with correct per-segment
flags that were calculated during the table CREATE time, no matter
what the old (CREATE time) and new (ALTER TIME) per-index compression
flags are, and no matter if they are equal or not.

All other engine ignore key compression flags, so this change
is safe for other engines as well.