differently react to SQL_MODE => unusable SHOW CREATE

Use abort_on_warning dependent on strict mode over create new table
like it is done for copy data and inplace alter.

This patch adds for "--ps-protocol" second execution
of queries "SELECT".
Also in this patch it is added ability to disable/enable
(--disable_ps2_protocol/--enable_ps2_protocol) second
execution for "--ps-prototocol" in testcases.

MDEV-31407: Add aliases in opt_trace.test for long column name for removing "--disable-view-protocol"

Change tests:
	opt_trace.test
	opt_trace_index_merge.test
	opt_trace_ucs2.test

MDEV-31749 sporadic assert in MDEV-30619 new test

If the workers of a parallel replica are busy (potentially with long
queues), but the SQL thread has no events left to distribute (so it
goes idle), then the next event that comes from the primary will
update mi->last_master_timestamp with its timestamp, even if the
workers have not yet finished.

This patch changes the parallel replica logic which updates
last_master_timestamp after idling from using solely sql_thread_caught_up
(added in MDEV-29639) to using the latter with rli queued/dequeued
event counters.
That is, if  the queued count is equal to the dequeued count, it
means all events have been processed and the replica is considered
idle when the driver thread has also distributed all events.

Low level details of the commit include
- to make a more generalized test for Seconds_Behind_Master on
  the parallel replica, rpl_delayed_parallel_slave_sbm.test
  is renamed to rpl_parallel_sbm.test for this purpose.
- pause_sql_thread_on_next_event usage was removed
  with the MDEV-30619 fixes. Rather than remove it, we adapt it
  to the needs of this test case
- added test case to cover SBM spike of relay log read and LMT
  update that was fixed by MDEV-29639
- rpl_seconds_behind_master_spike.test is made to use
  the negate_clock_diff_with_master debug eval.

Reviewed By:
============
Andrei Elkin <andrei.elkin@mariadb.com>

We introduce simple plugin dependency. A plugin init function may
return HA_ERR_RETRY_INIT. If this happens during server startup when
the server is trying to initialise all plugins, the failed plugins
will be retried, until no more plugins succeed in initialisation or
want to be retried.

This will fix spider init bugs which is caused in part by its
dependency on Aria for initialisation.

The reason we need a new return code, instead of treating every
failure as a request for retry, is that it may be impossible to clean
up after a failed plugin initialisation. Take InnoDB for example, it
has a global variable `buf_page_cleaner_is_active`, which may not
satisfy an assertion during a second initialisation try, probably
because InnoDB does not expect the initialisation to be called
twice.

Since TLS server certificate verification is a client
only option, this flag is removed in both client (C/C)
and MariaDB server capability flags.

This patch reverts commit 89d759b9
(MySQL Bug #21543) and stores the server certificate validation
option in mysql->options.extensions.

Since TLS server certificate verification is a client
only option, this flag is removed in both client (C/C)
and MariaDB server capability flags.

This patch reverts commit 89d759b9
(MySQL Bug #21543) and stores the server certificate validation
option in mysql->options.extensions.

noinline attribute was being ignored by clang-16 and reporting
32 stack size on Gentoo, 16 locally on Fedora 38.

Based on https://stackoverflow.com/questions/54481855/clang-ignoring-attribute-noinline
appended noopt in addition to the gcc recognised attributes.

After that the -pcre_exec(NULL, NULL, NULL, -999, -999, 0, NULL, 0)
returned 1056, simlar to gcc.

From https://bugs.gentoo.org/910188.

Thanks Zhixu Liu for the great bug report.

Restrict vcol_cleanup_expr() in close_thread_tables() to only simple
locked tables mode. Prelocked is cleaned up like normal statement: in
close_thread_table().

First UPDATE under START TRANSACTION does nothing (nstate= nstate),
but anyway generates history. Since update vector is empty we get into
(!uvect->n_fields) branch which only adds history row, but does not do
update. After that we get current row with wrong (old) row_start value
and because of that second UPDATE tries to insert history row again
because it sees trx->id != row_start which is the guard to avoid
inserting multiple trx_id-based history rows under same transaction
(because we have same trx_id and we get duplicate error and this bug
demostrates that). But this try anyway fails because PK is based on
row_end which is constant under same transaction, so PK didn't change.

The fix moves vers_make_update() to an earlier stage of
calc_row_difference(). Therefore it prepares update vector before
(!uvect->n_fields) check and never gets into that branch, hence no
need to handle versioning inside that condition anymore.

Now trx->id and row_start are equal after first UPDATE and we don't
try to insert second history row.

== Cleanups and improvements ==

ha_innobase::update_row():

vers_set_fields and vers_ins_row are cleaned up into direct condition
check. SQLCOM_ALTER_TABLE check now is not used as this is dead code,
assertion is done instead.

upd_node->is_delete is set in calc_row_difference() just to keep
versioning code as much in one place as possible. vers_make_delete()
is still located in row_update_for_mysql() as this is required for
ha_innodbase::delete_row() as well.

row_ins_duplicate_error_in_clust():

Restrict DB_FOREIGN_DUPLICATE_KEY to the better conditions.
VERSIONED_DELETE is used specifically to help lower stack to
understand what caused current insert. Related to MDEV-29813.

On create table tmp as select ... we exited Item_func::fix_fields()
with error. fix_fields_if_needed('foo' or 'bar') failed and we
returned true, but already changed const_item_cache. So the item is in
inconsistent state: fixed == false and const_item_cache == false.

Now we cleanup the item before the return if Item_func::fix_fields()
fails to process.

Use vers_check_update() to avoid inserting history row for ODKU if now
versioned fields specified in update_fields.

Constraints processing row_ins_check_foreign_constraint() was not
called because row_upd_check_references_constraints() didn't see
update as delete: node->is_delete was false.

Since MDEV-30378 we check for TRG_EVENT_DELETE to detect versioned
delete in ha_innobase::update_row().

Now we can use TRG_EVENT_DELETE to set upd_node->is_delete, so
constraints processing is triggered correctly.

1. Exclude merging history rows into fts index.

The check !history_fts && (index->type & DICT_FTS) was just incorrect
attempt to avoid history in fts index.

2. Don't check for duplicates for history rows.

The `safe_strcpy()` function was added in
https://github.com/mariadb/server/commit/567b68129943#diff-23f88d0b52735bf79b7eb76e2ddbbebc96f3b1ca16e784a347525a9c43134d77

Unfortunately, its current implementation triggers many GCC 8+ string
truncation and array bounds warnings, particularly due to the potential
for a false positive `-Warray-bounds`.

For example, the line `safe_strcpy(delimiter, sizeof(delimiter), ";")` in
`client/mysqldump.c` causes the following warning:

    [1669/1914] Building C object client/CMakeFiles/mariadb-dump.dir/mysqldump.c.o
    In file included from /PATH/include/my_sys.h:20,
                     from /PATH/mysqldump.c:51:
    In function ?safe_strcpy?,
        inlined from ?dump_events_for_db.isra? at /PATH/client/mysqldump.c:2595:3:
    /PATH/include/m_string.h:258:39: warning: array subscript 1535 is outside array bounds of ?const char[2]? [-Warray-bounds=]
      258 |   if (dst[dst_size - 2] != '\0' && src[dst_size - 1] != '\0')
          |                                    ~~~^~~~~~~~~~~~~~

GCC is reporting that the `safe_strcpy` function *could* cause an
out-of-bounds read from the constant *source* string `";"`, however this
warning is unhelpful and confusing because it can only happen if the size of
the *destination* buffer is incorrectly specified, which is not the case
here.

In https://github.com/MariaDB/server/pull/2640, Andrew Hutchings proposed
fixing this by disabling the `-Warray-bounds` check in this function
(specifically in
https://github.com/MariaDB/server/pull/2640/commits/be382d01d08739d081f6cf40f350f7414f29b49d#diff-23f88d0b52735bf79b7eb76e2ddbbebc96f3b1ca16e784a347525a9c43134d77R255-R262).

However, this was rejected because it also disables the *helpful*
`-Warray-bounds` check on the destination buffer.

Cherry-picking the commit
https://github.com/MariaDB/server/commit/a7adfd4c52307876d68ad3386cefd3757ee66e92
from 11.2 by Monty Widenius solves the first two problems:

1. It reimplements `safe_strcpy` a bit more efficiently, skipping the
   `memset(dst, 0, dst_size)`. This is unnecessary since `strncpy` already
   pads `dst` with 0 bytes.
2. It will not trigger the `-Warray-bounds` warning, because `src` is
   not read based on an offset determined from `dst_size`.

There is a third problem, however.  Using `strncpy` triggers the
`-Wstringop-truncation` warning
(https://gcc.gnu.org/onlinedocs/gcc/Warning-Options.html#index-Wstringop-truncation),
so we need to disable that.  However, that is a much less broadly and
generally-useful warning so there is no loss of static analysis value caused
by disabling it.

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.

Note: We should replace most case of safe_strcpy() with strmake() to avoid
the not needed zerofill.

Do not get value of expensive constants.

Add the end of file marker x1A to the DBF file and handle it
correctly to preserve interoperability with Libreoffice, and others
that have followed the DBF spec.

The file open mode of "a+" was problematic because Linux and the OSX,
the previous main development mode are inconsistent (see man fopen).
The main problem per the bug report was the inability to fseek back to the
beginning to update the records in the header.

As such the "a+" mode is remove and "w+b" is used inserting to a new file
and "r+b" is used for appending to the file.

In DBFFAM::CloseTableFile move PlugCloseFile down to close the file in
all modes.

The year unlike the comments is always since 1900. Use the
YYYY-MM-DD as an unabigious form during tracing.

Thanks for Mr. Zoltan Duna for the descriptive bug report.

Fix of typo in checking variable list corectness.

Fix of error handling in case of variable list parse error

MDEV-28384 UBSAN: null pointer passed as argument 1, which is declared to never be null in my_strnncoll_binary on SELECT ... COUNT or GROUP_CONCAT

Also fixes:
  MDEV-30982 UBSAN: runtime error: null pointer passed as argument 2, which is declared to never be null in my_strnncoll_binary on DELETE

Calling memcmp() with a NULL pointer is undefined behaviour
according to the C standard, even if the length argument is 0.

Adding tests for length==0 before calling memcmp() into:
- my_strnncoll_binary()
- my_strnncoll_8bit_bin

MDEV-22856 Assertion `!str || str != Ptr' and Assertion `!str || str != Ptr || !is_alloced()' failed in String::copy

The problem was earlier fixed by MDEV-26953.
Adding MTR tests only.

Concatenation error leads to typo in the warning message

Problem:

Item_func_conv::val_str() copied the ASCII string with the numeric base
conversion result directly to the function result string. In case of a
tricky character set (e.g. utf32) it produced an illformed string.

Fix:

Copy the base conversion result to the function result as is only if
the function character set is ASCII compatible, go through a
character set conversion otherwise.

Change history in the affected code:

- Since 10.4.8 (MDEV-20397 and MDEV-23311), functions ROUND(), CEILING(),
  FLOOR() return a TIME value for a TIME input.
- Since 10.4.14 (MDEV-23525), MIN() and MAX() calculate a result for a TIME
  input using val_native() rather than val_str().

Problem:

The patch for MDEV-23525 did not take into account combinations like
MIN(ROUND(time)), MAX(FLOOR(time)), etc.

MIN() and MAX() with ROUND(time), CEILING(time), FLOOR(time) as an argument
call the method val_native() of the undelying classes Item_func_round and
Item_func_int_val. However these classes implemented the method val_native()
as DBUG_ASSERT(0).

Fix:

This patch adds a TIME-specific code inside:
- Item_func_round::val_native()
- Item_func_int_val::val_native()
still with DBUG_ASSERT(0) for all other data types,
as other data types do not call val_native() of these classes.

We'll need a more generic solition eventualy, e.g.
turn Item_func_round and Item_func_int_val into Item_handled_func.
However, this change would be too risky for 10.4 at this point.

When constructing a SEL_TREE, an unsigned integer greater than
its signed equivalent caused an incorrect comparison operator to
be chosen.

The pointer was used deep in the call path.

Resolve this by setting the pointer to NULL at the end of
the function.

Tested with gcc-13.3.1 (fc38)

The warning disable 38fe266e can be reverted in 10.6+ on merge.

MDEV-26186 280 Bytes lost in mysys/array.c, mysys/hash.c, sql/sp.cc, sql/sp.cc, sql/item_create.cc, sql/item_create.cc, sql/sql_yacc.yy:10748 when using oracle sql_mode

There was a memory leak under these conditions:
- YYABORT was called in the end-of-rule action of a rule containing expr_lex
- This expr_lex was not bound to any sp_lex_keeper

Bison did not call %destructor <expr_lex> in this case, because its stack
already contained a reduced upper-level rule.

Fixing rules starting with RETURN, CONTINUE, EXIT keywords:

Turning end-of-rule actions with YYABORT into mid-rule actions
by adding an empty trailing { } block. This prevents the upper level
rule from being reduced without calling %destructor <expr_lex>.

In other rules expr_lex is used not immediately before the last
end-of-rule { } block, so they don't need changes.

MDEV-27207 Assertion `!m_null_value' failed in int FixedBinTypeBundle<FbtImpl>::cmp_item_fbt::compare or in cmp_item_inet6::compare

Also fixing:  MDEV-31719 Wrong result of: WHERE inet6_column IN ('','::1')

Problem:

When converting an Item value from string to INET6 it's possible
that the Item value itself is a not-NULL string value,
while the following result of the string-to-INET6 conversion returns NULL.

Methods cmp_item_xxx::set(), cmp_item_xxx::store_value_by_template(),
in_inet6::set() did not take this scenario into account and
tested source_item->null_value, which does not indicate if the conversion
failed.

Changing the return data type of the mentioned methods from "void" to "bool".

"true" means that:
- either the source Item was NULL
- or the source Item was not NULL, but the data type coversion to
  the destination data type (INET6 in this issue) returned NULL.

"false" means that the Item was not NULL and the data type conversion
to the destination data type worked without error.

This patches fixes the INET6 data type.
After merging to 10.9, this patch should also fix same problems in UUID.

Add @ to the allowed characters in a username.

- Moving the code from a public function trim_whitespaces()
  to the class Lex_cstring as methods. This code may
  be useful in other contexts, and also this code becomes
  visible inside sql_class.h

- Adding a helper method THD::strmake_lex_cstring_trim_whitespaces()

- Unifying the way how CREATE PROCEDURE/CREATE FUNCTION and
  CREATE PACKAGE/CREATE PACKAGE BODY work:

  a) Now CREATE PACKAGE/CREATE PACKAGE BODY also calls
  Lex->sphead->set_body_start() to remember the cpp body start inside
  an sp_head member.

  b) adding a "const char *cpp_body_end" parameter to
  sp_head::set_stmt_end().

  These changes made it possible to reuse sp_head::set_stmt_end() inside
  LEX::create_package_finalize() and remove the duplucate code.

- Renaming sp_head::m_body_begin to m_cpp_body_begin and adding a comment
  to make it clear that this member is used only during parsing, and
  points to a fragment inside the cpp buffer.

- Changed sp_head::set_body_start() and sp_head::set_stmt_end()
  to skip the calls related to "body_utf8" in cases when m_parent is not NULL.
  A non-NULL m_parent means that we're inside a package routine.
  "body_utf8" in such case belongs not to the current sphead itself,
  but to parent (the package) sphead.
  So an sphead instance of a package routine should neither initialize,
  nor finalize, nor change in any other ways the "body_utf8" related
  members of Lex_input_stream, and should not take over or copy "body_utf8"
  data from Lex_input_stream to "this".

MDEV-28017 Illegal mix of collations (cp1251_general_ci,IMPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'between'

Correct test fix is to disable service connection to create views in connection with correct charset settings.