MDEV-24453 Added support for a 5th --verbose parameter in mariadb-upgrade to show mysql results for mysql_fix_privilege_tables

EXPLAIN EXTENDED for an UPDATE/DELETE/INSERT/REPLACE statement did not
produce the warning containing the text representation of the query
obtained after the optimization phase. Such warning was produced for
SELECT statements, but not for DML statements.
The patch fixes this defect of EXPLAIN EXTENDED for DML statements.

This was failing to compile with AppleClang 14.0.0.14000029.

Thanks to Arunesh Choudhary for noticing.

All the .inc files that included from binlog_encryption are refactored.

Moved rpl_parallel.inc to rpl_parallel.test

- Description:
  - Before 10.3.8 semisync was a plugin that is built into the server with
    MDEV-13073,starting with commit cbc71485.
    There are still some usage of `rpl_semi_sync_master` in mtr.
Note:
  - To recognize the replica in the `dump_thread`, replica is creating
    local variable `rpl_semi_sync_slave` (the keyword of plugin) in
    function `request_transmit`, that is catched by primary in
    `is_semi_sync_slave()`. This is the user variable and as such not
    related to the obsolete plugin.

 - Found in `sys_vars.all_vars` and `rpl_semi_sync_wait_point` tests,
   usage of plugins `rpl_semi_sync_master`, `rpl_semi_sync_slave`.
   The former test is disabled by default (`sys_vars/disabled.def`)
   and marked as `obsolete`, however this patch will remove the queries.

- Add cosmetic fixes to semisync codebase

Reviewer: <brandon.nesterenko@mariadb.com>
Closes PR #2528, PR #2380

row_upd_rec_in_place(): Avoid calling page_zip_write_rec() if we
are not modifying any fields that are stored in compressed format.

btr_cur_update_in_place_zip_check(): New function to check if a
ROW_FORMAT=COMPRESSED record can actually be updated in place.

btr_cur_pessimistic_update(): If the BTR_KEEP_POS_FLAG is not set
(we are in a ROLLBACK and cannot write any BLOBs), ignore the potential
overflow and let page_zip_reorganize() or page_zip_compress() handle it.
This avoids a failure when an attempted UPDATE of an NULL column to 0 is
rolled back. During the ROLLBACK, we would try to move a non-updated
long column to off-page storage in order to avoid a compression failure
of the ROW_FORMAT=COMPRESSED page.

page_zip_write_trx_id_and_roll_ptr(): Remove an assertion that would fail
in row_upd_rec_in_place() because the uncompressed page would already
have been modified there.

Thanks to Jean-François Gagné for providing a copy of a page that
triggered these bugs on the ROLLBACK of UPDATE and DELETE.

A 10.6 version of this was tested by Matthias Leich using
cmake -DWITH_INNODB_EXTRA_DEBUG=ON a.k.a. UNIV_ZIP_DEBUG.

mtr uses group suffix, but some existing inc and test files use
server_id for expect files. This patch aims to fix that.

For spider:

With this change we will not have to maintain a separate version of
restart_mysqld.inc for spider, that duplicates code, just because
spider tests use different names for expect files, and shutdown_mysqld
requires magical names for them.

With this change spider tests will also be able to use other features
provided by restart_mysqld.inc without code duplication, like the
parameter $restart_parameters (see e.g. the testcase mdev_29904.test
in commit ef1161e5d4f).

Tests run after this change: default, spider, rocksdb, galera, using
the following command

mtr --parallel=auto --force --max-test-fail=0 --skip-core-file
mtr --suite spider,spider/*,spider/*/* \
    --skip-test="spider/oracle.*|.*/t\..*" --parallel=auto --big-test \
    --force --max-test-fail=0 --skip-core-file
mtr --suite galera --parallel=auto
mtr --suite rocksdb --parallel=auto

buf_LRU_block_remove_hashed(): Ever since
commit 2e814d47
we could get page_zip_validate() failures after an ALTER TABLE
operation was aborted and BtrBulk::pageCommit() had never been
executed on some blocks.

fil_iterate(): Allocation bitmap pages are never encrypted.

Reviewed by: Thirunarayanan Balathandayuthapani

MDEV-28187 mariadb-backup doesn't utilise innodb-undo-log-directory (if specified as a relative path) during copy-back operation

Make absolute destination path from relative one, basing on mysql data
directory.

Reviewed by Alexander Barkov.

The hang could be seen as show slave status displaying an error like
    Last_Error: Could not execute Write_rows_v1
along with
    Slave_SQL_Running: Yes

accompanied with one of the replication threads in show-processlist
characteristically having status like

   2394 | system user  |    | NULL | Slave_worker | 50852| closing tables

It turns out that closing tables worker got entrapped in endless looping
in mark_start_commit_inner() across already garbage-collected gco items.

The reclaimed gco links are explained with actually possible
out-of-order groups of events termination due to the Last_Error.
This patch reinforces the correct ordering to perform
finish_event_group's cleanup actions, incl unlinking gco:s
from the active list.

		redundant table rebuild

- InnoDB alter fails to apply the online log during redundant table
rebuild. Problem is that InnoDB wrongly reads the length flags of the
record while applying the temporary log record.

rec_init_offsets_comp_ordinary(): For finding the n_core_null_bytes,
InnoDB should use the same logic as rec_convert_dtuple_to_rec_comp().

Included config file for proper compilation without <my_global.h>

convert empty host.db to "%", just as it's done for host.hostname
(in update_hostname())

and my_getwd(). The cause is my_errno define which
depends on my_thread_var being a not null pointer
otherwise it will be de-referenced and cause
a SEGV already in the signal handler.

Replace uses of these functions in the output_core_info
using posix read/getcwd functions instead.

The getwd fallback in my_getcwd isn't needed as
its been obsolute for a very long time.

Thanks Vladislav Vaintroub for diagnosis and posix
recommendation.

- Adding test case for --raw without -R
- Adding unsuported combination of --raw and --flashback parameters and
covered with test case

- Update per Monty's review for commit 5328dbdaa237d97e443120f2e08437fb274198c2

Replace calls to `sprintf` and `strcpy` by the safer options `snprintf`
and `safe_strcpy` in the following directories:

- libmysqld
- mysys
- sql-common
- strings

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.

rec_init_offsets_comp_ordinary(), rec_init_offsets(),
rec_get_offsets_reverse(), rec_get_nth_field_offs_old():
Simplify some bitwise arithmetics to avoid conditional jumps,
and add branch prediction hints with the assumption that most
variable-length columns are short.

Tested by: Matthias Leich

MDEV-30668 Set function aggregated in outer select used in view definition

This patch fixes two bugs concerning views whose specifications contain
subqueries with set functions aggregated in outer selects.
Due to the first bug those such views that have implicit grouping were
considered as mergeable. This led to wrong result sets for selects from
these views.
Due to the second bug the aggregation select was determined incorrectly and
this led to bogus error messages.
The patch added several test cases for these two bugs and for four other
duplicate bugs.
The patch also enables view-protocol for many other test cases.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

- Avoid passing real field cache as a parameter when we check for duplicates.

- Correct cache cleanup (cached field number also have to be reset).

- Name resolution cache simple test added.

While performing SAST scanning using Cppcheck against source code of
commit 81196469, several code vulnerabilities were found.

Fix following issues:

1. Parameters of `snprintf` function are incorrect.

   Cppcheck error:

       client/mysql_plugin.c:1228: error: snprintf format string requires 6 parameters but only 5 are given.

   It is due to commit 630d7229 introduced option `--lc-messages-dir`
   in the bootstrap command. However the parameter was not even given
   in the `snprintf` after changing the format string.

   Fix:
   Restructure the code logic and correct the function parameters for
   `snprintf`.

2. Null pointer is used in a `snprintf` which could cause a crash.

   Cppcheck error:

       extra/mariabackup/xbcloud.cc:2534: error: Null pointer dereference

   The code intended to print the swift_project name, if the
   opt_swift_project_id is NULL but opt_swift_project is not NULL.
   However the parameter of `snprintf` was mistakenly using
   `opt_swift_project_id`.

   Fix:
   Change to use the correct string from `opt_swift_project`.

3. Potential double release of a memory

   Cppcheck error:

       plugin/auth_pam/testing/pam_mariadb_mtr.c:69: error: Memory pointed to by 'resp' is freed twice.

   A pointer `resp` is reused and allocated new memory after it has been
   freed. However, `resp` was not set to NULL after freed.
   Potential double release of the same pointer if the call back
   function doesn't allocate new memory for `resp` pointer.

   Fix:
   Set the `resp` pointer to NULL after the first free() to make sure
   the same address is not freed twice.

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.

Running mysql_upgrade should end up with the exact same system tables as fresh
installations have after running mysql_install_db. To ensure the upgrade is
correct and complete:

- Remove the redundant modification of thread_id`. On 5.5 version, the
  `general_log` table was created as `CREATE TABLE IF NOT EXISTS general_log
  (..., thread_id INTEGER NOT NULL, ...)`, and starting from 10.0+, the table is
  created as `CREATE TABLE IF NOT EXISTS general_log (..., thread_id BIGINT(21)
  UNSIGNED NOT NULL, ...)`, but mysql_upgrade is not properly upgrading the
  table. It modifies the `thread_id` twice in one query, which could leave the
  table not modified and lead to other potential error when upgrading from
  MariaDB 5.5 or older.

- Update `servers` to ensure `Host` and `User` has correct data type if
  upgrading from 10.1 or older. On versions 10.0 and 10.1, the `servers` table
  was created as `CREATE TABLE IF NOT EXISTS servers (..., Host char(64) NOT
  NULL DEFAULT , ..., Owner char(64) NOT NULL DEFAULT , ...)`, and starting
  from 10.2, the table is created as `CREATE TABLE IF NOT EXISTS servers (...,
  Host varchar(2048) NOT NULL DEFAULT , ..., Owner varchar(512) NOT NULL
  DEFAULT , ...)`.

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.

1. Adding a separate MY_COLLATION_HANDLER
   my_collation_ucs2_general_mysql500_ci_handler
   implementing a proper order for ucs2_general_mysql500_ci
   The problem happened because ucs2_general_mysql500_ci
   erroneously used my_collation_ucs2_general_ci_handler.

2. Cosmetic changes: Renaming:
   - plane00_mysql500 to my_unicase_mysql500_page00
   - my_unicase_pages_mysql500 to my_unicase_mysql500_pages
   to use the same naming style with:
   - my_unicase_default_page00
   - my_unicase_defaul_pages

3. Moving code fragments from
   - handler::check_collation_compatibility() in handler.cc
   - upgrade_collation() in table.cc
   into new methods in class Charset, to reuse the code easier.

Subselect_single_value_engine cannot handle table value constructor used as
subquery. That's why any table value constructor TVC used as subquery is
converted into a select over derived table whose specification is TVC.
Currently the names  of the columns of the derived table DT are taken from
the first element of TVC and if the k-th component of the element happens
to be a subquery the text representation of this subquery serves as the
name of the k-th column of the derived table. References of all columns of
the derived table DT compose the select list of the result of the conversion.
If a definition of a view contained a table value constructor used as a
subquery and the view was registered after this conversion had been
applied we could register an invalid view definition if the first element
of TVC contained a subquery as its component: the name of this component
was taken from the original subquery, while the name of the corresponding
column of the derived table was taken from the text representation of the
subquery produced by the function SELECT_LEX::print() and these names were
usually differ from each other.
To avoid registration of such invalid views the function SELECT_LEX::print()
now prints the original TVC instead of the select in which this TVC has
been wrapped. Now the specification of registered view looks like as if no
conversions from TVC to selects were done.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

- dtuple_vcol_data_missing() should check the DATA_MISSING only for
indexed virtual column.

note that `KILL USER foo` should *not* fail with ER_KILL_DENIED_ERROR
when SHOW PROCESSLIST doesn't show connections of that user.
Because no connections exist or because the caller has no PROCESS -
doesn't matter.

also, fix the error message to make sense
("You are not owner of thread <current connection id>" is ridiculous)

here global_index_stats is expected to be big, so we don't
restart the search, but use a two-pass approach

another case of the antipattern "iterate the HASH and delete
elements as we go"

This reverts commit d78ac04e.

The benefits of `ignore=all` are that submodules are omitted from
`git status` and `git citool`. The drawbacks are - submodules
are not omitted from `git commit -a` *and* they are omitted
from `git diff` and `git show` output.

As a result one can unintentionally commit changes to submodules and not
see it in the history. Thus drawbacks outweigh benefits here.

When using the MySQL table type the CONNECT engine converted the YEAR
datatype to DATETIME for INSERT queries. This is incorrect, causing an
error on the INSERT. It should be SHORT instead.