MDEV-24139: CHECK_CLAUSE field in INFORMATION_SCHEMA.CHECK_CONSTRAINTS truncate check constraints expressions

- Reviewed by: daniel@mariadb.org

Diagnostics_area::sql_errno upon query from I_S with LIMIT ROWS EXAMINED

open_normal_and_derived_table() fails because the query was already killed
as rows examined by the query are more than the limit. However, this isn't a
real error.
Fix: Check if there is actually an error before calling thd->sql_errno()
and later send a warning in handle_select() if no real error.

The code in fill_schema_schemata() did not take into account that
make_db_list() can leave empty db_names if the requested database
name was too long, so the call for db_names.at(0) crashed on assert.

- Moving the code testing if the database directory exists
  into a separate function verify_database_directory_exists()
- Modifying the test to check if db_names is not empty

MDEV-22019: Sig 11 in next_breadth_first_tab | max_sort_length setting + double GROUP BY leads to crash

No need to create a temp table for aggregation if we have encountered some error.

(Variant #2 of the patch, which keeps the sp_head object inside the
MEM_ROOT that sp_head object owns)
(10.3 requires extra work due to sp_package, will commit a separate
patch for it)

sp_head::operator new() and operator delete() were dereferencing sp_head*
pointers to memory that didn't hold a valid sp_head object (it was
not created/already destroyed).
This caused UBSan to crash when looking up type information.

Fixed by providing static sp_head::create() and sp_head::destroy() methods.

PR#1127: Fix is_check_constraints.result to be compatibile with 10.3

The patch is done according to the original patch for MDEV-14474
1edd09c325525cba33152 and not one which is merged on server
d526679e.
This patch includes:
- Rename from `is_check_constraint` to `is_check_constraints` to tests
and results
- Per review, change the order of fields in IS check_constraints table by adding
the column `table_name` before `constraint_name`. According to the standard
2006 there is no `table_name` column.
- Original patch and one in `10.3` supports embedded server this patch doesn't
support. After the merge `10.3` will not support also.
- Don't use patch c8b8b01b61 to change the length of `CHECK_CLAUSE` field

PR#1150: MDEV-18440: Information_schema.check_constraints possible data leak

This patch is extension of PR 1127 and includes:
- Check for table grants
- Additional test according to the MDEV specification
Signed-off-by: Vicențiu Ciorbaru <vicentiu@mariadb.org>

Basicaly it's an uninitialized read. 165 is 0xa5 which comes from TRASH_ALLOC()

Fix by calling a class ctor which initializes problematic
TMP_TABLE_PARAM::force_copy_fields field

switch to definer privileges when populating I_S tables

Data should be sent with length.

             read_statistics_for_tables_if_needed

Regression after 279a907f, read_statistics_for_tables_if_needed() was
called after open_normal_and_derived_tables() failure.

Fixed by moving read_statistics_for_tables() call to a branch of
get_schema_stat_record() where result of open_normal_and_derived_tables()
is checked.

Removed THD::force_read_stats, added read_statistics_for_tables() instead.
Simplified away statistics_for_command_is_needed().

This patch fixes 10.2 issue reported in MDEV-16467 by partial backport of
c2118a08. Specifically "Remove not needed LOCK_thread_count from
thd_get_error_context_description()".

* Update wrong zip-code

to the patch for MDEV-17605

MDEV-17654 Incorrect syntax returned for column with CHECK constraint in the "SHOW CREATE TABLE ..." result

Prepend COMMENT before CHECK constraint in SHOW CREATE

Closes #924

This patch fixes an invalid read in fill_effective_table_privileges
triggered by a grant_version increase between a PREPARE for a
statement creating a view from I_S and EXECUTE.
A tmp table was created and free'd while preparing the statement,
TABLE_LIST::table_name was set to point to the tmp table
TABLE_SHARE::table_name which no longer existed after preparing was
done.
The grant version increase made fill_effective_table_privileges
called during EXECUTE to try fetch the updated grant info and
this is where the dangling table name was used.

The command SHOW INDEXES ignored setting of the system variable
use_stat_tables to the value of 'preferably' and and showed statistical
data received from the engine. Similarly queries over the table
STATISTICS from INFORMATION_SCHEMA ignored this setting. It happened
because the function fill_schema_table_by_open() did not read any data
from statistical tables.

There were two newly enabled warnings:
1. cast for a function pointers. Affected sql_analyse.h, mi_write.c
   and ma_write.cc, mf_iocache-t.cc, mysqlbinlog.cc, encryption.cc, etc

2. memcpy/memset of nontrivial structures. Fixed as:
* the warning disabled for InnoDB
* TABLE, TABLE_SHARE, and TABLE_LIST got a new method reset() which
  does the bzero(), which is safe for these classes, but any other
  bzero() will still cause a warning
* Table_scope_and_contents_source_st uses `TABLE_LIST *` (trivial)
  instead of `SQL_I_List<TABLE_LIST>` (not trivial) so it's safe to
  bzero now.
* added casts in debug_sync.cc and sql_select.cc (for JOIN)
* move assignment method for MDL_request instead of memcpy()
* PARTIAL_INDEX_INTERSECT_INFO::init() instead of bzero()
* remove constructor from READ_RECORD() to make it trivial
* replace some memcpy() with c++ copy assignments

Make mysqltest to use --ps-protocol more

use prepared statements for everything that server supports
with the exception of CALL (for now).

Fix discovered test failures and bugs.

tests:
* PROCESSLIST shows Execute state, not Query
* SHOW STATUS increments status variables more than in text protocol
* multi-statements should be avoided (see tests with a wrong delimiter)
* performance_schema events have different names in --ps-protocol
* --enable_prepare_warnings

mysqltest.cc:
* make sure run_query_stmt() doesn't crash if there's
  no active connection (in wait_until_connected_again.inc)
* prepare all statements that server supports

protocol.h
* Protocol_discard::send_result_set_metadata() should not send
  anything to the client.

sql_acl.cc:
* extract the functionality of getting the user for SHOW GRANTS
  from check_show_access(), so that mysql_test_show_grants() could
  generate the correct column names in the prepare step

sql_class.cc:
* result->prepare() can fail, don't ignore its return value
* use correct number of decimals for EXPLAIN columns

sql_parse.cc:
* discard profiling for SHOW PROFILE. In text protocol it's done in
  prepare_schema_table(), but in --ps it is called on prepare only,
  so nothing was discarding profiling during execute.
* move the permission checking code for SHOW CREATE VIEW to
  mysqld_show_create_get_fields(), so that it would be called during
  prepare step too.
* only set sel_result when it was created here and needs to be
  destroyed in the same block. Avoid destroying lex->result.
* use the correct number of tables in check_show_access(). Saying
  "as many as possible" doesn't work when first_not_own_table isn't
  set yet.

sql_prepare.cc:
* use correct user name for SHOW GRANTS columns
* don't ignore verbose flag for SHOW SLAVE STATUS
* support preparing REVOKE ALL and ROLLBACK TO SAVEPOINT
* don't ignore errors from thd->prepare_explain_fields()
* use select_send result for sending ANALYZE and EXPLAIN, but don't
  overwrite lex->result, because it might be needed to issue execute-time
  errors (select_dumpvar - too many rows)

sql_show.cc:
* check grants for SHOW CREATE VIEW here, not in mysql_execute_command

sql_view.cc:
* use the correct function to check privileges. Old code was doing
  check_access() for thd->security_ctx, which is invoker's sctx,
  not definer's sctx. Hide various view related errors from the invoker.

sql_yacc.yy:
* initialize lex->select_lex for LOAD, otherwise it'll contain garbage
  data that happen to fail tests with views in --ps (but not otherwise).

Implement according to standard SQL specification 2008.
The check_constraints table is used for fetching metadata about
the constraints defined for tables in all databases.

There were some result files which failed after running mtr.
These files are updated with newly create record with mtr --record.

MDEV-16792 Assertion `m_status == DA_ERROR' failed in Diagnostics_area::sql_errno or wrong result upon SHOW TABLE STATUS after adding partition under ANSI_QUOTES

if opening a table fails with a syntax error, the error shouldn't
be suppressed completely as if it didn't happen at all.

instead of returning strings for CASCADE/RESTRICT
from every storage engine, use enum values

Backport of a3614d33

MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view

Print constant ISNULL value independent.
Fix of printing of view FRM and CREATE VIEW output

make it return the same Info values as for the standalone server.

This fixes plugins.processlist for --embedded

make thd_get_error_context_description() to show not just
thd->proc_info, but exactly the same thread state that SHOW PROCESSLIST
shows.

             time and don't let server shut down

Queries from I_S in "Filling schema table" state didn't check killed
flag. For large tables this phase may take a while to complete.

Fixed by adding thd->killed flag check for each processed row.

Solve 3 way deadlock between plugin_initialiaze(), THD::init() and
mysql_sys_var_char().

The deadlock exists because of the lock order inversion between
LOCK_global_system_variables mutex and LOCK_system_variables_hash
read-write lock-

In this case, it is enough to change LOCK_system_variables_hash to prefer
reads to fix the deadlock, i.e change it to mysql_prlock_t

TRASH was mapped to TRASH_FREE and was supposed to be used for memory
that should not be accessed anymore, while TRASH_ALLOC() is to be
used for uninitialized but to-be-used memory.

But sometimes TRASH() was used in the latter sense.

Remove TRASH() macro, always use explicit TRASH_ALLOC() or TRASH_FREE().

Other things, mainly to get
create_mysqld_error_find_printf_error tool to work:

- Added protection to not include mysqld_error.h twice
- Include "unireg.h" instead of "mysqld_error.h" in server
- Added protection if ER_XX messages are already defined
- Removed wrong calls to my_error(ER_OUTOFMEMORY) as
  my_malloc() and my_alloc will do this automatically
- Added missing %s to ER_DUP_QUERY_NAME
- Removed old and wrong calls to my_strerror() when using
  MY_ERROR_ON_RENAME (wrong merge)
- Fixed deadlock error message from Galera. Before the extra
  information given to ER_LOCK_DEADLOCK was missing because
  ER_LOCK_DEADLOCK doesn't provide any extra information.

I kept #ifdef mysqld_error_find_printf_error_used in sql_acl.h
to make it easy to do this kind of check again in the future

Part2: make MyRocks add its directory into @@ignore_db_dirs when starting.

This is necessary because apparently not everybody are using plugin's my.cnf
So load ha_rocksdb.{so,dll} manually and then hit MDEV-12451, MDEV-14461
etc.

mostly caused by -Wimplicit-fallthrough

             in buildbot

Removed plugin_array_version: it is being checked without mutex protection and
thus is prone to data race and race conditions. In effect plugins are not
protected from concurrent destruction.

Removed state_mask inversion: doesn't seem to make any sense.

When collecting local plugins list, only add plugins that match state_mask.

Use plugin ref counting to protect against concurrent plugin destruction.

Optimizations:
* avoid faster ext_table_discovery_simple() *only* when there are
  engines with discover_table_names() method *and* they look at
  file names. P_S implements discover_table_names(), but it's not
  a reason to use slower extension_based_table_discovery().
* don't pre-sort table names if ORDER BY or GROUP BY was specified
* starting from 10.3 only sort table names for SHOW commands