Backported from MYSQL
Bug #25331425: DISTINCT CLAUSE DOES NOT WORK IN GROUP_CONCAT
Issue:
------
The problem occurs when:
1) GROUP_CONCAT (DISTINCT ....) is used in the query.
2) Data size greater than value of system variable:
tmp_table_size.

The result would contain values that are non-unique.

Root cause:
-----------
An in-memory structure is used to filter out non-unique
values. When the data size exceeds tmp_table_size, the
overflow is written to disk as a separate file. The
expectation here is that when all such files are merged,
the full set of unique values can be obtained.

But the Item_func_group_concat::add function is in a bit of
hurry. Even as it is adding values to the tree, it wants to
decide if a value is unique and write it to the result
buffer. This works fine if the configured maximum size is
greater than the size of the data. But since tmp_table_size
is set to a low value, the size of the tree is smaller and
hence requires the creation of multiple copies on disk.

Item_func_group_concat currently has no mechanism to merge
all the copies on disk and then generate the result. This
results in duplicate values.

Solution:
---------
In case of the DISTINCT clause, don't write to the result
buffer immediately. Do the merge and only then put the
unique values in the result buffer. This has be done in
Item_func_group_concat::val_str.

Note regarding result file changes:
-----------------------------------
Earlier when a unique value was seen in
Item_func_group_concat::add, it was dumped to the output.
So result is in the order stored in SE. But with this fix,
we wait until all the data is read and the final set of
unique values are written to output buffer. So the data
appears in the sorted order.

This only fixes the cases when we have DISTINCT without ORDER BY clause
in GROUP_CONCAT.

Part#2: forgot to commit the adjustments for the testcases.

This is a race condition where a table on which a 10.3-style
instant ADD COLUMN is emptied during the execution of
ALTER TABLE ... DROP COLUMN ..., DROP INDEX ..., ALGORITHM=NOCOPY.

In commit 2c4844c9 the
function instant_metadata_lock() would prevent this race condition.
But, it would also hold a page latch on the leftmost leaf page of
clustered index for the duration of a possible DROP INDEX operation.

The race could be fixed by restoring the function
instant_metadata_lock() that was removed in
commit ea37b144
but it would be more future-proof to prevent the
dict_index_t::clear_instant_add() call from being issued at all.

We at some point support DROP COLUMN ..., ADD INDEX ..., ALGORITHM=NOCOPY
and that would spend a non-trivial amount of
execution time in ha_innobase::inplace_alter(),
making a server hang possible. Currently this is not supported
and our added test case will notice when the support is introduced.

dict_index_t::must_avoid_clear_instant_add(): Determine if
a call to clear_instant_add() must be avoided.

btr_discard_only_page_on_level(): Preserve the metadata record
if must_avoid_clear_instant_add() holds.

btr_cur_optimistic_delete_func(), btr_cur_pessimistic_delete():
Do not remove the metadata record even if the table becomes empty
but must_avoid_clear_instant_add() holds.

btr_pcur_store_position(): Relax a debug assertion.

This is joint work with Thirunarayanan Balathandayuthapani.

Apply this patch from Percona Server (amended for 10.5):

commit cd7201514fee78aaf7d3eb2b28d2573c76f53b84
Author: Laurynas Biveinis <laurynas.biveinis@gmail.com>
Date:   Tue Nov 14 06:34:19 2017 +0200

    Fix bug 1704195 / 87065 / TDB-83 (Stop ANALYZE TABLE from flushing table definition cache)

    Make ANALYZE TABLE stop flushing affected tables from the table
    definition cache, which has the effect of not blocking any subsequent
    new queries involving the table if there's a parallel long-running
    query:

    - new table flag HA_ONLINE_ANALYZE, return it for InnoDB and TokuDB
      tables;
    - in mysql_admin_table, if we are performing ANALYZE TABLE, and the
      table flag is set, do not remove the table from the table
      definition cache, do not invalidate query cache;
    - in partitioning handler, refresh the query optimizer statistics
      after ANALYZE if the underlying handler supports HA_ONLINE_ANALYZE;
    - new testcases main.percona_nonflushing_analyze_debug,
      parts.percona_nonflushing_abalyze_debug and a supporting debug sync
      point.

    For TokuDB, this change exposes bug TDB-83 (Index cardinality stats
    updated for handler::info(HA_STATUS_CONST), not often enough for
    tokudb_cardinality_scale_percent). TokuDB may return different
    rec_per_key values depending on dynamic variable
    tokudb_cardinality_scale_percent value. The server does not have a way
    of knowing that changing this variable invalidates the previous
    rec_per_key values in any opened table shares, and so does not call
    info(HA_STATUS_CONST) again. Fix by updating rec_per_key for both
    HA_STATUS_CONST and HA_STATUS_VARIABLE. This also forces a re-record
    of tokudb.bugs.db756_card_part_hash_1_pick, with the new output
    seeming to be more correct.

MDEV-15053 did not remove all unnecessary buf_pool.page_hash S-latch
acquisition. There are code paths where we are holding buf_pool.mutex
(which will sufficiently protect buf_pool.page_hash against changes)
and unnecessarily acquire the latch. Many invocations of
buf_page_hash_get_locked() can be replaced with the much simpler
buf_pool.page_hash_get_low().

In the worst case the thread that is holding buf_pool.mutex will become
a victim of MDEV-22871, suffering from a spurious reader-reader conflict
with another thread that genuinely needs to acquire a buf_pool.page_hash
S-latch.

In many places, we were also evaluating page_id_t::fold() while holding
buf_pool.mutex. Low-level functions such as buf_pool.page_hash_get_low()
must get the page_id_t::fold() as a parameter.

buf_buddy_relocate(): Defer the hash_lock acquisition to the critical
section that starts by calling buf_page_t::can_relocate().

if mysql_create_view is aborted when `view` isn't unlinked,
it should not be linked back on cleanup

On large hard disks (> 2TB), the plugin won't function correctly, always
showing 2 TB of available space due to integer overflow. Upgrade table
fields to bigint to resolve this problem.

The prefix of error log message out of a failed BINLOG applying
is corrected to be the sql command name.

CHECK constraint is checked by check_expression() which walks its
items and gets into Item_field::check_vcol_func_processor() to check
for conformity with foreign key list.

WITHOUT OVERLAPS is checked for same conformity in
mysql_prepare_create_table().

Long uniques are already impossible with InnoDB foreign keys. See
ER_CANT_CREATE_TABLE in test case.

2 accompanying bugs fixed (test main.constraints failed):

1. check->name.str lived on SP execute mem_root while "check" obj
itself lives on SP main mem_root. On second SP execute check->name.str
had garbage data. Fixed by allocating from thd->stmt_arena->mem_root
which is SP main mem_root.

2. CHECK_CONSTRAINT_IF_NOT_EXISTS value was mixed with
VCOL_FIELD_REF. VCOL_FIELD_REF is assigned in check_expression() and
then detected as CHECK_CONSTRAINT_IF_NOT_EXISTS in
handle_if_exists_options().

Existing cases for MDEV-16932 in main.constraints cover both fixes.

Made the test stable by adding more rows so the range scan is cheaper than table scan.

fil_space_t::freed_ranges: Store ranges of freed page numbers.

fil_space_t::last_freed_lsn: Store the most recent LSN of
freeing a page.

fil_space_t::freed_mutex: Protects freed_ranges, last_freed_lsn.

fil_space_create(): Initialize the freed_range mutex.

fil_space_free_low(): Frees the freed_range mutex.

range_set: Ranges of page numbers.

buf_page_create(): Removes the page from freed_ranges when page
is being reused.

btr_free_root(): Remove the PAGE_INDEX_ID invalidation. Because
btr_free_root() and dict_drop_index_tree() are executed in
the same atomic mini-transaction, there is no need to
invalidate the root page.

buf_release_freed_page(): Split from buf_flush_freed_page().
Skip any I/O

buf_flush_freed_pages(): Get the freed ranges from tablespace and
Write punch-hole or zeroes of the freed ranges.

buf_flush_try_neighbors(): Handles the flushing of freed ranges.

mtr_t::freed_pages: Variable to store the list of freed pages.

mtr_t::add_freed_pages(): To add freed pages.

mtr_t::clear_freed_pages(): To clear the freed pages.

mtr_t::m_freed_in_system_tablespace: Variable to indicate whether page has
been freed in system tablespace.

mtr_t::m_trim_pages: Variable to indicate whether the space has been trimmed.

mtr_t::commit(): Add the freed page and update the last freed lsn
in the tablespace and clear the tablespace freed range if space is
trimmed.

file_name_t::freed_pages: Store the freed pages during recovery.

file_name_t::add_freed_page(), file_name_t::remove_freed_page(): To
add and remove freed page during recovery.

store_freed_or_init_rec(): Store or remove the freed pages while
encountering FREE_PAGE or INIT_PAGE redo log record.

recv_init_crash_recovery_spaces(): Add the freed page encountered
during recovery to respective tablespace.

as cmake manual says

  If a sequential execution of multiple commands is required, use multiple
  ``execute_process()`` calls with a single ``COMMAND`` argument.

MDEV-21831: Assertion `length == pack_length()' failed in Field_inet6::sort_string upon INSERT into RocksDB table

For INET6 columns the values are stored as BINARY columns and returned to the client in TEXT format.
For rocksdb the indexes store mem-comparable images for columns, so use the pack_length() to store
the mem-comparable form for INET6 columns. This would also remain consistent with CHAR columns.

For reads, the buf_pool.page_hash is protected by buf_pool.mutex or
by the hash_lock. There is no need to compute or acquire hash_lock
if we are not modifying the buf_pool.page_hash.

However, the buf_pool.page_hash latch must be held exclusively
when changing buf_page_t::in_file(), or if we desire to prevent
buf_page_t::can_relocate() or buf_page_t::buf_fix_count()
from changing.

rw_lock_lock_word_decr(): Add a comment that explains the polling logic.

buf_page_t::set_state(): When in_file() is to be changed, assert that
an exclusive buf_pool.page_hash latch is being held. Unfortunately
we cannot assert this for set_state(BUF_BLOCK_REMOVE_HASH) because
set_corrupt_id() may already have been called.

buf_LRU_free_page(): Check buf_page_t::can_relocate() before
aqcuiring the hash_lock.

buf_block_t::initialise(): Initialize also page.buf_fix_count().

buf_page_create(): Initialize buf_fix_count while not holding
any mutex or hash_lock. Acquire the hash_lock only for the
duration of inserting the block to the buf_pool.page_hash.

buf_LRU_old_init(), buf_LRU_add_block(),
buf_page_t::belongs_to_unzip_LRU(): Do not assert buf_page_t::in_file(),
because buf_page_create() will invoke buf_LRU_add_block()
before acquiring hash_lock and buf_page_t::set_state().

buf_pool_t::validate(): Rely on the buf_pool.mutex and do not
unnecessarily acquire any buf_pool.page_hash latches.

buf_page_init_for_read(): Clarify that we must acquire the hash_lock
upfront in order to prevent a race with buf_pool_t::watch_remove().

This regression was introduced in
commit dd77f072 (MDEV-22841).

ut_filename_hash(): Add better casts to please the compiler:

warning C4307: '*': integral constant overflow

This regression was introduced in
commit dd77f072 (MDEV-22841).

That doesn't support STRING(APPEND ..)

MDEV-22819: Wrong result or Assertion `ix > 0' failed in read_to_buffer upon select with GROUP BY and GROUP_CONCAT

In the merge_buffers phase for sorting, the sort buffer size is divided between the number of chunks.
The chunks have a start and end position (m_buffer_start and m_buffer_end).
Then we read the as many records that fit in this buffer for a chunk of the file.
The issue here was we were resetting the end of buffer(m_buffer_end) to the number of bytes that was
read, this was causing a problem because with dynamic size of sort keys it is possible that later
we would not be able to accommodate even one key inside a chunk of file.
So the fix was to not reset the end of buffer for a chunk of file.

Add missing call for handler->prepare_for_insert() in Rows_log_event::do_apply_event

MONITOR_SRV_MEM_VALIDATE_MICROSECOND, MEM_PERIODIC_CHECK,
SRV_MASTER_MEM_VALIDATE_INTERVAL: Remove. These were unused
ever since UNIV_MEM_DEBUG was removed.

MONITOR_SRV_PURGE_MICROSECOND: Remove. This was always unused.

CREATE PROCEDURE did not detect unknown SP variables in assignments like this:

  SET var=a_long_var_name_with_a_typo;

The error happened only during the SP execution time, and only of the control
flow reaches the erroneous statement.

Fixing most expressions to detect unknown identifiers.
This includes simple subqueries without tables:

- Query specification: SELECT list, WHERE,
  HAVING (inside aggregate functions) clauses, e.g.
    SET var= (SELECT unknown_ident+1);
    SET var= (SELECT 1 WHERE unknown_identifier);
    SET var= (SELECT 1 HAVING SUM(unknown_identifier);

- Table value constructor: VALUES clause, e.g.:
    SET var= (VALUES(unknown_ident));

Note, in some more complex subquery cases unknown variables are still not detected
(this will be fixed separately):

- Derived tables:
  SET a=(SELECT unknown_ident FROM (SELECT 1 AS alias) t1);
  SET res=(SELECT * FROM t1 LEFT OUTER JOIN (SELECT unknown_ident) t2 USING (c1));

- CTE:
  SET a=(WITH cte1 (a) AS (SELECT unknown_ident) SELECT * FROM cte1);
  SET a=(WITH cte1 (a,b) AS (VALUES (unknown,2),(3,4)) SELECT * FROM cte1);
  SET a=(WITH cte1 (a,b) AS (VALUES (1,2),(3,4)) SELECT unknown_ident FROM cte1);

- SELECT .. GROUP BY unknown_identifier
- SELECT .. ORDER BY unknown_identifier
- HAVING with an unknown identifier outside of any aggregate functions:
  SELECT .. HAVING unknown_identifier;

Problematic mutex is dict_sys.mutex.
Idea of the patch: unlink() fd under that mutex while
it's still open. This way unlink() will be fast and
actual file removal will happen on close().
And close() will be called outside of dict_sys.mutex.
This should be safe against crash which may happen between
unlink() and close(): file will be removed by OS anyway.
The same applies to both *nix and Windows.

I created and removed a 4G file on some NVMe SSD on ext4:

write(3, "\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1"..., 1048576) = 1048576 <0.000519>
fdatasync(3)                            = 0 <3.533763>
close(3)                                = 0 <0.000011>
unlink("file")                          = 0 <0.411563>

write(3, "\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1\1"..., 1048576) = 1048576 <0.000520>
fdatasync(3)                            = 0 <3.544938>
unlink("file")                          = 0 <0.000029>
close(3)                                = 0 <0.407057>

Such systems can benefit of this patch.

fil_node_t::detach(): closes fil_node_t but not file handle,
  returns that file handle

fil_node_t::prepare_to_close_or_deatch(): 'closes' fil_node_t

fil_node_t:close_to_free(): new argument detach_handle

fil_system_t::detach(): now can detach file handles

fil_delete_tablespace(): now can detach file handles

row_drop_table_for_mysql(): performs actual file removal

To install Spider one can simply drop a /etc/mysql/conf.d/spider.cnf like

  [mariadb]
  plugin-load-add=ha_spider.so

This is automatically generated and installed when plugin is correctly
registered to plugin.cmake with its own component name. Many other plugins
such as Connect and RocksDB install in the same way.

This solved MDEV-19917 as the mere adding and removing of spider.cnf
automatically installs and uninstalls it.

Remove the overly complex and uncecessary install.sql from Spider,
if should not be needed in modern times anymore.

With this change there is no need for a uninstall.sql either.

- Recommend max_allowed_packet=1G which is the same as the default client
  value.
- Remove thread_concurrency removed in 10.5.
- Remove query cache, not recommended practice anymore.
- Remove binlog_*, should not recommend those too easily but rather require
  the database administrator to read up on those themselves.
- Remove chroot setting, not relevant in modern container era.
- Show explicitly innodb_buffer_pool_size example as the most likely thing
  a database administrator should change.
- Don't recommend rate limiting in slow log, logging once in a 1000
  would not be optimal for the basic case, hence bad example.
- Install the example configs in /usr/share/mysql.
- Use correct path /run/ instead of /var/run/.

Split the big my.cnf into multiple smaller files with the same filenames
and contents as official Debian/Ubuntu packaging has.

The config contents stays the same apart from following additions
which the original MariaDB upstream configs had and probably needs
to be kept:
- lc-messages=en_US and skip-external-locking in server config

Configs the original MariaDB upstream had that are seemingly
unnecessary and thus removed:
- port=3306 removed from the client config
- log_warnings=2 removed from server config

Also adopt update-alternatives system using
mysql-common/configure-symlinks. This way it is aligned with
downstream Debian/Ubuntu packaging.

Analysis:
========
List of values provided for "replicate_ignore_table" and "replicate_do_table"
are stored in HASH.  When an empty list is provided the HASH structure doesn't
get initialized. Existing code treats empty element list as an error and tries
to clean the uninitialized HASH. This results in above MSAN issue.

Fix:
===
The clean up should be initiated only when there is an error while parsing the
'replicate_do_table' or 'replicate_ignore_table' list and the HASH is in
initialized state. Otherwise for empty list it should simply return success.

This corrects build failures on ppc64{,le} with the
WITH_EMBEDDED_SERVER option enabled.

MDEV-22641 added an unusual case in which the same object
file in was included twice with a different function
defination. The original cmake/merge_archives_unix.cmake
did not tolerate such eventualities.

So we move to the highest voted answer on Stack Overflow
for the merging of static libraries.
https://stackoverflow.com/questions/3821916/how-to-merge-two-ar-static-libraries-into-one

Thin archives generated compile failures and the libtool
mechanism would of been another dependency and using .la
files that isn't part of a normal cmake output. The straight
Apple mechanism of libtool with static archives also failed
on Linux.

This leaves the MRI script mechansim which was implemented
in this change.

To install Spider one can simply drop a /etc/mysql/conf.d/spider.cnf like

  [mariadb]
  plugin-load-add=ha_spider.so

This is automatically generated and installed when plugin is correctly
registered to plugin.cmake with its own component name. Many other plugins
such as Connect and RocksDB install in the same way.

This solved MDEV-19917 as the mere adding and removing of spider.cnf
automatically installs and uninstalls it.

Remove the overly complex and uncecessary install.sql from Spider,
if should not be needed in modern times anymore.

With this change there is no need for a uninstall.sql either.

- Recommend max_allowed_packet=1G which is the same as the default client
  value.
- Remove thread_concurrency removed in 10.5.
- Remove query cache, not recommended practice anymore.
- Remove binlog_*, should not recommend those too easily but rather require
  the database administrator to read up on those themselves.
- Remove chroot setting, not relevant in modern container era.
- Show explicitly innodb_buffer_pool_size example as the most likely thing
  a database administrator should change.
- Don't recommend rate limiting in slow log, logging once in a 1000
  would not be optimal for the basic case, hence bad example.
- Install the example configs in /usr/share/mysql.
- Use correct path /run/ instead of /var/run/.

Split the big my.cnf into multiple smaller files with the same filenames
and contents as official Debian/Ubuntu packaging has.

The config contents stays the same apart from following additions
which the original MariaDB upstream configs had and probably needs
to be kept:
- lc-messages=en_US and skip-external-locking in server config

Configs the original MariaDB upstream had that are seemingly
unnecessary and thus removed:
- port=3306 removed from the client config
- log_warnings=2 removed from server config

Also adopt update-alternatives system using
mysql-common/configure-symlinks. This way it is aligned with
downstream Debian/Ubuntu packaging.

Analysis:
========
List of values provided for "replicate_ignore_table" and "replicate_do_table"
are stored in HASH.  When an empty list is provided the HASH structure doesn't
get initialized. Existing code treats empty element list as an error and tries
to clean the uninitialized HASH. This results in above MSAN issue.

Fix:
===
The clean up should be initiated only when there is an error while parsing the
'replicate_do_table' or 'replicate_ignore_table' list and the HASH is in
initialized state. Otherwise for empty list it should simply return success.

This corrects build failures on ppc64{,le} with the
WITH_EMBEDDED_SERVER option enabled.

MDEV-22641 added an unusual case in which the same object
file in was included twice with a different function
defination. The original cmake/merge_archives_unix.cmake
did not tolerate such eventualities.

So we move to the highest voted answer on Stack Overflow
for the merging of static libraries.
https://stackoverflow.com/questions/3821916/how-to-merge-two-ar-static-libraries-into-one

Thin archives generated compile failures and the libtool
mechanism would of been another dependency and using .la
files that isn't part of a normal cmake output. The straight
Apple mechanism of libtool with static archives also failed
on Linux.

This leaves the MRI script mechansim which was implemented
in this change.