lp:maria

Author: Yuchen Pei
Author Date: 2023-05-25 02:52:38 UTC

MDEV-30435 MDEV-30981 Fix ubsan errors w.r.t. memcpy in spd_trx.cc

Extract the indexed string memcopy pattern in spd_trx.cc to a static
inline function.

Also updated the ubsan check in mdev_26541.test (h/t roel).

Author: Yuchen Pei
Author Date: 2023-05-25 02:52:38 UTC

MDEV-30435 MDEV-30981 Fix ubsan errors w.r.t. memcpy in spd_trx.cc

Extract the indexed string memcopy pattern in spd_trx.cc to a static
inline function.

Also updated the ubsan check in mdev_26541.test (h/t roel).

Author: Brandon Nesterenko
Author Date: 2023-03-08 20:49:32 UTC

MDEV-13915: STOP SLAVE takes very long time on a busy system

The problem is that a parallel replica would not immediately stop
running/queued transactions when issued STOP SLAVE. That is, it
allowed the current group of transactions to run, and sometimes the
transactions which belong to the next group could be started and run
through commit after STOP SLAVE was issued too, if the last group
had started committing. This would lead to long periods to wait for
all waiting transactions to finish.

This patch updates a parallel replica to try and abort immediately
and roll-back any ongoing transactions. The exception to this is any
transactions which are non-transactional (e.g. those modifying
sequences or non-transactional tables), and any prior transactions,
will be run to completion.

The specifics are as follows:

 1. A new stage was added to SHOW PROCESSLIST output for the SQL
Thread when it is waiting for a replica thread to either rollback or
finish its transaction before stopping. This stage presents as
“Waiting for worker thread to stop”

 2. Worker threads which error or are killed no longer perform GCO
cleanup if there is a concurrently running prior transaction. This
is because a worker thread scheduled to run in a future GCO could be
killed and incorrectly perform cleanup of the active GCO.

 3. Refined cases when the FL_TRANSACTIONAL flag is added to GTID
binlog events to disallow adding it to transactions which modify
both transactional and non-transactional engines when the binlogging
configuration allow the modifications to exist in the same event,
i.e. when using binlog_direct_non_trans_update == 0 and
binlog_format == statement.

 4. A few existing MTR tests relied on the completion of certain
transactions after issuing STOP SLAVE, and were re-recorded
(potentially with added synchronizations) under the new rollback
behavior.

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

Author: Andrei
Author Date: 2023-06-05 16:31:32 UTC

MDEV-30421 arbitrary transaction dependency for optimistic parallel slave

This commit initiates a general framework to control data dependent transactions
on slave so that children of a particular dependency class (e.g defined
by the user as --parallel-ignore-db etc see SAMU-54) are to wait for
their parents' commits before they are allowed to start.

The data dependency condition is handled very similarly to one of
SPECULATE_WAIT, actually generalizing the latter as follows.
When two trx:s
                Ok-i -> Ok
(O - the notion of transaction from SAME-54, k-i, k - trx indexes in
binlog like gtid.seq_no)
depend say having updated the same "O" db), Ok has to
wait for Ok-i at the same execution point as a normal SPECULATE_WAIT
does. Afterward Ok registers to wait for ordered commit (by Tk-1 now)
and goes about its business.
The parent Ok-i normal workflow remains intact.

TODO:

This poc commit implies only one dependency class and the latter
simulated by "reusing" SET @@session.skip_parallel_replication=1
(as this commit is on the top of 10.4, rather that SAMU-54 branch).

- the limitation needs to be lifted
- the fact of trx belong to a specific data dependency class needs
  flagging in Gtid with a respective class' identifier
- SAMU-54 user interface to define dependencies needs some more
  thinking.
  E.g

  1. databases A,B listed in --parallel-ignore-db=A,B should represent
  two disjoint dependency classes (while of course in practice they
  may be not - through FK). If that's acceptable A and B should lead
  to different Gtid's dependency class identifiers.

  2. Dependency between trx:s are computable in ROW format to render
  void the purpose of --parallel-* rules/hints while the task of
  'computing' may need some design efforts.

Author: Brandon Nesterenko
Author Date: 2023-03-08 20:49:32 UTC

MDEV-13915: STOP SLAVE takes very long time on a busy system

The problem is that a parallel replica would not immediately stop
running/queued transactions when issued STOP SLAVE. That is, it
allowed the current group of transactions to run, and sometimes the
transactions which belong to the next group could be started and run
through commit after STOP SLAVE was issued too, if the last group
had started committing. This would lead to long periods to wait for
all waiting transactions to finish.

This patch updates a parallel replica to try and abort immediately
and roll-back any ongoing transactions. The exception to this is any
transactions which are non-transactional (e.g. those modifying
sequences or non-transactional tables), and any prior transactions,
will be run to completion.

The specifics are as follows:

 1. A new stage was added to SHOW PROCESSLIST output for the SQL
Thread when it is waiting for a replica thread to either rollback or
finish its transaction before stopping. This stage presents as
“Waiting for worker thread to stop”

 2. Worker threads which error or are killed no longer perform GCO
cleanup if there is a concurrently running prior transaction. This
is because a worker thread scheduled to run in a future GCO could be
killed and incorrectly perform cleanup of the active GCO.

 3. Refined cases when the FL_TRANSACTIONAL flag is added to GTID
binlog events to disallow adding it to transactions which modify
both transactional and non-transactional engines when the binlogging
configuration allow the modifications to exist in the same event,
i.e. when using binlog_direct_non_trans_update == 0 and
binlog_format == statement.

 4. A few existing MTR tests relied on the completion of certain
transactions after issuing STOP SLAVE, and were re-recorded
(potentially with added synchronizations) under the new rollback
behavior.

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

Author: Brandon Nesterenko
Author Date: 2023-03-08 20:49:32 UTC

MDEV-13915: STOP SLAVE takes very long time on a busy system

The problem is that a parallel replica would not immediately stop
running/queued transactions when issued STOP SLAVE. That is, it
allowed the current group of transactions to run, and sometimes the
transactions which belong to the next group could be started and run
through commit after STOP SLAVE was issued too, if the last group
had started committing. This would lead to long periods to wait for
all waiting transactions to finish.

This patch updates a parallel replica to try and abort immediately
and roll-back any ongoing transactions. The exception to this is any
transactions which are non-transactional (e.g. those modifying
sequences or non-transactional tables), and any prior transactions,
will be run to completion.

The specifics are as follows:

 1. A new stage was added to SHOW PROCESSLIST output for the SQL
Thread when it is waiting for a replica thread to either rollback or
finish its transaction before stopping. This stage presents as
“Waiting for worker thread to stop”

 2. Worker threads which error or are killed no longer perform GCO
cleanup if there is a concurrently running prior transaction. This
is because a worker thread scheduled to run in a future GCO could be
killed and incorrectly perform cleanup of the active GCO.

 3. Refined cases when the FL_TRANSACTIONAL flag is added to GTID
binlog events to disallow adding it to transactions which modify
both transactional and non-transactional engines when the binlogging
configuration allow the modifications to exist in the same event,
i.e. when using binlog_direct_non_trans_update == 0 and
binlog_format == statement.

 4. A few existing MTR tests relied on the completion of certain
transactions after issuing STOP SLAVE, and were re-recorded
(potentially with added synchronizations) under the new rollback
behavior.

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

Author: Sergey Petrunia
Author Date: 2023-06-05 13:40:08 UTC

MDEV-31403: Server crashes in st_join_table::choose_best_splitting

The code in choose_best_splitting() assumed that the join prefix is
in join->positions[].

This is not necessarily the case. This function might be called when
the join prefix is in join->best_positions[], too.
Follow the approach from best_access_path(), which calls this function:
pass the current join prefix as an argument,
"const POSITION *join_positions" and use that.

Author: tanruixiang
Author Date: 2022-06-16 07:05:35 UTC

MDEV-26182: Implement JSON_INTERSECT()

The idea is to have simple functions that the user can combine to produce
the exact result one wants, whether the user wants JSON object that has
common keys with another JSON object, or same key/value pair etc. So
making simpler function helps here.

We accomplish this by making three separate functions.
1) JSON_OBJECT_FILTER_KEYS(Obj, Arr_keys):
Put keys ( which are basically strings ) in hash, go over the object and
get key one by one. If the key is present in the hash,
add the key-value pair to result.

2) JSON_OBJECT_TO_ARRAY(Obj) : Create a string variable, Go over the json
object, and add each key value pair as an array into the result.

3) JSON_ARRAY_INTERSECT(arr1, arr2) :
Go over one of the json and add each item of the array
in hash (after normalizing each item). Go over the second array,
search the normalized item one by one in the hash. If item is found,
add it to the result.

Implementation Idea: Holyfoot ( Alexey Botchkov)
Author: tanruixiang and Rucha Deodhar

Author: Georg Richter
Author Date: 2023-06-05 11:42:26 UTC

docs test

Author: Lena Startseva
Author Date: 2023-05-31 04:57:45 UTC

MDEV-31003: Second execution for ps-protocol

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.

Author: Yuchen Pei
Author Date: 2023-06-05 02:17:50 UTC

MDEV-22979 [poc] init spider last and create udf by insertion

Author: Yuchen Pei
Author Date: 2023-06-02 00:48:18 UTC

MDEV-22534 fixing some ci complaint

Author: Rex Johnston
Author Date: 2023-06-01 03:38:56 UTC

MDEV-30944 Range_rowid_filter::fill() leaves file->keyread at MAX_KEY

Range_rowid_filter::fill() leaves keyread altered,
leading to assertion failure in join_tab->read_first_record().

Author: Sergey Petrunia
Author Date: 2023-06-01 11:06:06 UTC

MDEV-31380: Assertion `s->table->opt_range_condition_rows <= s->found_records' failed

LooseScan code set opt_range_condition_rows to be the

  MIN(loose_scan_plan->records, table->records)

totally ignoring possible quick range selects. If there was a quick
select $QUICK on another index with

  $QUICK->records < loose_scan_plan->records

this would create a situation where

   opt_range_condition_rows > $QUICK->records

which caused an assert.
Fixed by making opt_range_condition_rows to be the minimum #rows
of any quick select.

Author: Marko Mäkelä
Author Date: 2023-06-01 10:17:00 UTC

Merge 10.10 into 10.11

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-05-31 14:57:34 UTC

Fix of maturity

Author: Andrew Hutchings
Author Date: 2023-05-30 10:01:42 UTC

Fix COLUMNSTORE in Debian CI builds

Debian building tried to implicitly enable ColumnStore, but for this
to work, the boost/flex dependencies need to be there.

Also add `arm64` to the supported platforms for ColumnStore, that is
what it is identified as in Debian 11.

Author: Rex Johnston
Author Date: 2023-05-19 03:22:15 UTC

MDEV-31327 Range Histogram selectivity estimates added, not merged

When the optimizer is evaluating join order and estimating rows produced a join order, it loops through column constraints adding rather than merging selectivity estimates.

Author: Nikita Malyavin
Author Date: 2023-05-15 16:39:21 UTC

MDEV-30984 Online ALTER table is denied with non-informative error messages

Group all the checks in online_alter_check_supported().

There is now two groups of checks:
1. A technical availability of online, that is checked before open_tables,
and affects table_list->lock_type. It's supposed to be safe to make it
TL_READ even if COPY algorithm will fall back to not-online, since MDL is
SHARED_UPGRADEABLE anyway.
2. An 'online' availability for a COPY algorithm. It can be done as late as
just before the copy_data_between_tables call. The lock_type influence is
disclosed above, so the only other place it affects is
Alter_info::supports_lock, where `online` flag is only used to decide
whether to report the error at the inplace preparation stage. We'd want to
make that at the last resort, which is COPY preparation, if no algorithm is
chosen by the user. So it's event better now.

Some changes are required to the autoinc support detection, as the check
now happens after mysql_prepare_alter_table:
* alter_info->drop_list is empty
* instead, dropped columns are in tmp_set
* alter_info->create_list now has every field that's in the new table.
* the column definition's change.str will be nonnull whether the column
  remains in the new table (vs whether it was changed, as before).
  But it also has `field` field set.
* IF EXISTS doesn't have to be dealt anymore

This infers that the changes are now checked in more detail: a field's
definition shouldn't be changed, vs a field shouldn't be mentioned in
the CHANGE list, as it was before. This is reflected by the line 193 test.

Author: Yuchen Pei
Author Date: 2023-05-26 01:00:49 UTC

MDEV-22979 MDEV-27233 MDEV-28218 Fixing spider init bugs

We introduce a new set of synchronisation primitives ("query ready")
that are signaled when the server is ready to take queries, and make
spider initialisation use them instead of the primitives associated
with server start.

During spider initialisation, if the server is query ready, proceed
with the initialisation synchronously (requiring the sql
service). Otherwise, leave the init to the first sts bg thread as
usual.

When creating a spider handler before the spider init is done, if the
server is not query ready, just give up and return NULL. If the server
is query ready, then wait for a specific amount of time (5s) for the
spider init to be done, and on timeout give up and return NULL.

How this fixes / prevents various init bugs:

MDEV-27233 (spider installed and used in init file). Before -
spider_create_handler() called by read_init_file() waits for bg thread
to finish init, whereas the bg thread is waiting for server
start. After - spider init is run synchronously because the server is
query ready when read_init_file(). No funny business with concurrency.

MDEV-29904 (spider loaded with plugin-load-add). Before (before the
original patch to MDEV-27233 was reverted) - fully synchronous spider
init fails due to dependencies on Aria / acl init. After - spider init
is run async, and as soon as the server is query ready, the init
proceeds and succeeds.

MDEV-22979 (mysqld --bootstrap --plugin-load-add=ha_spider). Before -
bootstrap queries causes call to spider_create_handler(), which waits
for bg thread to finish init, whereas bg thread waits for server
start. After - spider init is run async, waiting for server to be
query ready. spider_create_hander() notices bg thread still trying to
init spider, and server is not query ready so it gives up.

MDEV-28218 (spider is loaded in a query after server start,
immediately before another query dropping a spider table). Before - bg
thread is created to init spider async, while the server proceeds to
the next query that tries to drop a spider system table. race
condition causes the situation where the server holds the mdl lock to
the table and waits for the bg thread in spider_create_handler(), and
the bg thread waiting for the mdl lock to create the table. After -
since server has already started, spider init is run synchronously,
thus no concurrency issues. Same applies to the variant of MDEV-28218
represented by mdev_28218_init_file where the queries are in an init
file.

Another variant of MDEV-28218 represented by mdev_28218_mixed loads
spider with plugin-load-add and executes the drop table query in an
init_file. Spider init is async because the server was not query
ready. As soon as the the server becomes query ready the server tries
to execute the drop table query. It waits on the init in
spider_create_handler(), but because of the deadlock it times out
after 5 seconds. The drop table query did not find any table, but
spider init then proceeds as it finally acquires the mdl lock, and
creates the table. The moral of the story is to advise users not to
mix plugin-load-add=ha_spider with init_file containing queries
related to spider system tables. Queries using spider engine to create
tables are still ok because there's no deadlock.

MDEV-30370 (mysqld --wsrep-recover --plugin-load-add=spider). The
timed wait in the patch for MDEV-30370 still applies, so it still
passes.

More on the design:

Compared to the server start primitives, the query ready primitives
are signaled earlier ("query ready point") to avoid unnecessary wait
and deadlocks.

The spider initialisation does not only require other plugins like
Aria to be initialised first, but also acl init (due to its creation
of udfs) as well as the creation of the `mysql` database (due to its
system tables being in this database). Thus the point where it can go
ahead with the initialisation should be after the bootstrap.

By the time the server can accept queries by reading from init files,
it can definitely accept all spider init queries. So the point of
spider initialisation can be before this point.

Looking at the lines between boostrap and reading init file, the most
natural position is just before the call to read_init_file(), where we
can also give the primitives a most meaningful name "query ready".

Author: Marko Mäkelä
Author Date: 2023-05-25 08:28:02 UTC

MDEV-31234 InnoDB does not free UNDO after the fix of MDEV-30671

trx_purge_truncate_rseg_history(): Add a parameter to specify if
the entire rollback segment is safe to be freed. If not, we may
still be able to invoke trx_undo_truncate_start() and free some pages,
but we will avoid leaking undo log pages that are not yet ready
to be processed. This fixes a regression that was introduced in
MDEV-30671.

trx_purge_free_segment(), trx_purge_truncate_rseg_history():
Replace some unreachable code with debug assertions.
A buffer-fix does prevent pages from being evicted
from the buffer pool; see buf_page_t::can_relocate().

trx_sys_t::any_active_transactions(): Separately count XA PREPARE
transactions.

srv_purge_should_exit(): Terminate slow shutdown if the history size
does not change and XA PREPARE transactions exist in the system.
This will avoid a hang of the test innodb.recovery_shutdown.

Author: Yuchen Pei
Author Date: 2023-05-25 02:52:38 UTC

MDEV-30435 MDEV-30981 Fix ubsan errors w.r.t. memcpy in spd_trx.cc

Extract the indexed string memcopy pattern in spd_trx.cc to a static
inline function.

Also updated the ubsan check in mdev_26541.test (h/t roel).

Author: Sergei Golubchik
Author Date: 2023-05-24 10:33:35 UTC

fix federatedx select pushdown after rebase

Author: Dmitry Shulga
Author Date: 2023-05-24 08:29:09 UTC

MDEV-14959: Control over memory allocated for SP/PS

Use sp_head::executed_counter only for debug output. The decision
to mark sp_head's mem_root as read only is performed based on
the fact that every SP instruction has been executed at least once.

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-05-24 07:38:18 UTC

Merge branch '10.6' into 10.8

Author: Yuchen Pei
Author Date: 2023-05-23 05:42:35 UTC

MDEV-31269 Fixing exists2in 2nd ps execution segfault

In the exists2in transformation, if 1) we are in a ps/sp execution and
2) the equalities picked out for the transformation intersect with the
temporary query arena free list, skip the transformation.

Further analysis:

Consider the following example:

--8<---------------cut here---------------start------------->8---
set optimizer_switch=default;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT);
PREPARE st FROM "
SELECT * FROM t2
HAVING 0 IN (
  SELECT a FROM t1
  WHERE EXISTS (
    SELECT a FROM t1
    WHERE b = a
  )
)
";
EXECUTE st;
EXECUTE st;
drop table t1, t2;
--8<---------------cut here---------------end--------------->8---

1. During the JOIN::prepare() of the first execution, the Item_field referring to t2.b is replaced by an Item_ref
2. The item_ref is moved around in the exists2in transformation and subsequent optimizations
3. At the end of the 1st ps execution, the Item_ref is freed due to its creation in the ps execution as part of the cleanup
4. During the JOIN::prepare() of the second execution, it tries to restore the optimized version of the statement, in doing so it does a sanity check and causes a sigsegv when trying to access the freed Item_ref.

Why the problem does not occur with the transformation disabled:

- during step 1 above, the replacement also adds an item to the change_list
- during step 2 above, the optimizations cause the equality {{b = a}} to be moved around as a whole, thereby moving its {{args}} which is an {{Item **}} instead of the {{item *}}'s as a whole
- during step 3 above, the change is rolled back, so the {{Item **}} that is the args of the {{Item_func_eq}} goes from {{b (Item_ref) = a}} becomes {{b (Item_field) = a}}
- during step 4 above, there's no use-after-free in the restored optimized statment because it automatically contains the {{b (Item_field) = a}}.

Why we can't do the same with the transformation enabled:

- during step 2, we break the {{b = a}} into the outer_expr (b) and local_field (a), add the outer_expr to left_expr and local_field to the inner select item list. we cannot move the equality as a whole during this transformation.

Author: Yuchen Pei
Author Date: 2023-05-23 05:42:35 UTC

MDEV-31269 Fixing exists2in 2nd ps execution segfault

In the exists2in transformation, if 1) we are in a ps/sp execution and
2) the equalities picked out for the transformation intersect with the
temporary query arena free list, skip the transformation.

Further analysis:

Consider the following example:

--8<---------------cut here---------------start------------->8---
set optimizer_switch=default;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT);
PREPARE st FROM "
SELECT * FROM t2
HAVING 0 IN (
  SELECT a FROM t1
  WHERE EXISTS (
    SELECT a FROM t1
    WHERE b = a
  )
)
";
EXECUTE st;
EXECUTE st;
drop table t1, t2;
--8<---------------cut here---------------end--------------->8---

1. During the JOIN::prepare() of the first execution, the Item_field referring to t2.b is replaced by an Item_ref
2. The item_ref is moved around in the exists2in transformation and subsequent optimizations
3. At the end of the 1st ps execution, the Item_ref is freed due to its creation in the ps execution as part of the cleanup
4. During the JOIN::prepare() of the second execution, it tries to restore the optimized version of the statement, in doing so it does a sanity check and causes a sigsegv when trying to access the freed Item_ref.

Why the problem does not occur with the transformation disabled:

- during step 1 above, the replacement also adds an item to the change_list
- during step 2 above, the optimizations cause the equality {{b = a}} to be moved around as a whole, thereby moving its {{args}} which is an {{Item **}} instead of the {{item *}}'s as a whole
- during step 3 above, the change is rolled back, so the {{Item **}} that is the args of the {{Item_func_eq}} goes from {{b (Item_ref) = a}} becomes {{b (Item_field) = a}}
- during step 4 above, there's no use-after-free in the restored optimized statment because it automatically contains the {{b (Item_field) = a}}.

Why we can't do the same with the transformation enabled:

- during step 2, we break the {{b = a}} into the outer_expr (b) and local_field (a), add the outer_expr to left_expr and local_field to the inner select item list. we cannot move the equality as a whole during this transformation.

Author: Marko Mäkelä
Author Date: 2023-05-23 14:20:35 UTC

Experiment: Remove handler::unlock_row() and friends

Failing test(s): innodb.innodb main.partition_innodb_semi_consistent innodb.innodb-semi-consistent innodb.innodb_lock_wait_timeout_1 main.concurrent_innodb_unsafelog main.unsafe_binlog_innodb innodb.create_table_insert_skip_locked rpl.rpl_unsafe_statements innodb.partition_locking innodb.skip_locked_nowait innodb.deadlock_wait_lock_race

Author: Marko Mäkelä
Author Date: 2023-05-23 12:32:20 UTC

Remove row_prebuilt_t::fetch_cache[]

Author: midenok
Author Date: 2023-05-22 16:11:21 UTC

MDEV-25495 AUTO argument to specify max number of partitions

Example:

  create table t1 (x int) with system versioning
  partition by system_time interval 1 hour auto 25;

Keeps 24 history partitions (and 1 current partition).

Note, MAX_SYM cannot be used for syntax expansion as this is function
name (sql_functions[] in lex.h)

fast_alter_partition_table() changes:

Auto-add like auto-drop now goes through REBUILD branch. MDL_EXCLUSIVE
is taken before mysql_change_partitions() like ALTER_PARTITION_ADD
branch does.

ha_partition::change_partitions() changes:

ADD PARTITION and DROP PARTITION in one command (as well as any other
alter partition operations) is not supported by the original code. The
patch partially solves this problem: ha_partition::change_partitions()
now can accept both PART_TO_BE_DROPPED and PART_TO_BE_ADDED states in
the partitions list. Originally change_partitions() was not used for
arbitrary drop of partitions, only REORGANIZE and COALESCE could be
processed by this call.

For REORGANIZE m_part_info->partitions contains new partitions set and
m_part_info->temp_partitions contains PART_TO_BE_REORGED
partitions. But we cannot use temp_partitions for dropping partitions
because they go through copy_partitions() phase (see how
m_reorged_parts initialized).

For COALESCE m_part_info->partitions in the beginning of the list
contains normal partitions that will remain when alter finishes, the
end of the list contains PART_REORGED_DROPPED partitions, so Step 4
can ignore them.

Step 4 for new_file_array preparation didn't skip PART_TO_BE_DROPPED
partitions in m_file. Now we increment orig_count for them.

Step 5 now properly iterates new_file_array in respect of skipping
PART_TO_BE_DROPPED while iterating m_part_info->partitions.

For full ADD+DROP support it would be needed to rework
prep_alter_part_table() as well. Now PART_TO_BE_DROPPED state is set
in vers_create_partitions() after prep_alter_part_table().

Author: Sergey Petrunia
Author Date: 2023-05-22 19:51:50 UTC

MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB - V3-Fix1

Variant #3, fix for trivial errors.

Author: Oleg Smirnov
Author Date: 2023-05-19 09:53:39 UTC

MDEV-29630 WIP: remove index initialization from join_read_always_key()

Author: Teemu Ollakka
Author Date: 2023-04-17 13:04:01 UTC

MDEV-29293 MariaDB stuck on starting commit state

This is a backport from 10.5.

The problem seems to be a deadlock between KILL command execution
and BF abort issued by an applier, where:
* KILL has locked victim's LOCK_thd_kill and LOCK_thd_data.
* Applier has innodb side global lock mutex and victim trx mutex.
* KILL is calling innobase_kill_query, and is blocked by innodb
  global lock mutex.
* Applier is in wsrep_innobase_kill_one_trx and is blocked by
  victim's LOCK_thd_kill.

The fix in this commit removes the TOI replication of KILL command
and makes KILL execution less intrusive operation. Aborting the
victim happens now by using awake_no_mutex() and ha_abort_transaction().
If the KILL happens when the transaction is committing, the
KILL operation is postponed to happen after the statement
has completed in order to avoid KILL to interrupt commit
processing.

Notable changes in this commit:
* wsrep client connections's error state may remain sticky after
  client connection is closed. This error message will then pop
  up for the next client session issuing first SQL statement.
  This problem raised with test galera.galera_bf_kill.
  The fix is to reset wsrep client error state, before a THD is
  reused for next connetion.
* Release THD locks in wsrep_abort_transaction when locking
  innodb mutexes. This guarantees same locking order as with applier
  BF aborting.
* BF abort from MDL was changed to do BF abort on server/wsrep-lib
  side first, and only then do the BF abort on InnoDB side. This
  removes the need to call back from InnoDB for BF aborts which originate
  from MDL and simplifies the locking.
* Removed wsrep_thd_set_wsrep_aborter() from service_wsrep.h.
  The manipulation of the wsrep_aborter can be done solely on
  server side. Moreover, it is now debug only variable and
  could be excluded from optimized builds.
* Remove LOCK_thd_kill from wsrep_thd_LOCK/UNLOCK to allow more
  fine grained locking for SR BF abort which may require locking
  of victim LOCK_thd_kill. Added explicit call for
  wsrep_thd_kill_LOCK/UNLOCK where appropriate.
* Wsrep-lib was updated to version which allows external
  locking for BF abort calls.

Changes to MTR tests:
* Disable galera_bf_abort_group_commit. This test is going to
  be removed (MDEV-30855).
* Record galera_gcache_recover_manytrx as result file was incomplete.
  Trivial change.
* Make galera_create_table_as_select more deterministic:
  Wait until CTAS execution has reached MDL wait for multi-master
  conflict case. Expected error from multi-master conflict is
  ER_QUERY_INTERRUPTED. This is because CTAS does not yet have open
  wsrep transaction when it is waiting for MDL, query gets interrupted
  instead of BF aborted. This should be addressed in separate task.
* A new test galera_kill_group_commit to verify correct behavior
  when KILL is executed while the transaction is committing.

Co-authored-by: Seppo Jaakola <seppo.jaakola@iki.fi>
Co-authored-by: Jan Lindström <jan.lindstrom@galeracluster.com>
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Teemu Ollakka
Author Date: 2023-04-19 13:51:55 UTC

MDEV-29293 MariaDB stuck on starting commit state

This commit contains a merge from 10.5-MDEV-29293-squash
into 10.6.

Although the bug MDEV-29293 was not reproducible with 10.6,
the fix contains several improvements for wsrep KILL query and
BF abort handling, and addresses the following issues:

* MDEV-30307 KILL command issued inside a transaction is
  problematic for galera replication:
  This commit will remove KILL TOI replication, so Galera side
  transaction context is not lost during KILL.
* MDEV-21075 KILL QUERY maintains nodes data consistency but
  breaks GTID sequence: This is fixed as well as KILL does not
  use TOI, and thus does not change GTID state.
* MDEV-30372 Assertion in wsrep-lib state: This was caused by
  BF abort or KILL when local transaction was in the middle
  of group commit. This commit disables THD::killed handling
  during commit, so the problem is avoided.
* MDEV-30963 Assertion failure !lock.was_chosen_as_deadlock_victim
  in trx0trx.h:1065: The assertion happened when the victim was
  BF aborted via MDL while it was committing. This commit changes
  MDL BF aborts so that transactions which are committing cannot
  be BF aborted via MDL. The RQG grammar attached in the issue
  could not reproduce the crash anymore.

Original commit message from 10.5 fix:

    MDEV-29293 MariaDB stuck on starting commit state

    The problem seems to be a deadlock between KILL command execution
    and BF abort issued by an applier, where:
    * KILL has locked victim's LOCK_thd_kill and LOCK_thd_data.
    * Applier has innodb side global lock mutex and victim trx mutex.
    * KILL is calling innobase_kill_query, and is blocked by innodb
      global lock mutex.
    * Applier is in wsrep_innobase_kill_one_trx and is blocked by
      victim's LOCK_thd_kill.

    The fix in this commit removes the TOI replication of KILL command
    and makes KILL execution less intrusive operation. Aborting the
    victim happens now by using awake_no_mutex() and ha_abort_transaction().
    If the KILL happens when the transaction is committing, the
    KILL operation is postponed to happen after the statement
    has completed in order to avoid KILL to interrupt commit
    processing.

    Notable changes in this commit:
    * wsrep client connections's error state may remain sticky after
      client connection is closed. This error message will then pop
      up for the next client session issuing first SQL statement.
      This problem raised with test galera.galera_bf_kill.
      The fix is to reset wsrep client error state, before a THD is
      reused for next connetion.
    * Release THD locks in wsrep_abort_transaction when locking
      innodb mutexes. This guarantees same locking order as with applier
      BF aborting.
    * BF abort from MDL was changed to do BF abort on server/wsrep-lib
      side first, and only then do the BF abort on InnoDB side. This
      removes the need to call back from InnoDB for BF aborts which originate
      from MDL and simplifies the locking.
    * Removed wsrep_thd_set_wsrep_aborter() from service_wsrep.h.
      The manipulation of the wsrep_aborter can be done solely on
      server side. Moreover, it is now debug only variable and
      could be excluded from optimized builds.
    * Remove LOCK_thd_kill from wsrep_thd_LOCK/UNLOCK to allow more
      fine grained locking for SR BF abort which may require locking
      of victim LOCK_thd_kill. Added explicit call for
      wsrep_thd_kill_LOCK/UNLOCK where appropriate.
    * Wsrep-lib was updated to version which allows external
      locking for BF abort calls.

    Changes to MTR tests:
    * Disable galera_bf_abort_group_commit. This test is going to
      be removed (MDEV-30855).
    * Make galera_var_retry_autocommit result more readable by echoing
      cases and expectations into result. Only one expected result for
      reap to verify that server returns expected status for query.
    * Record galera_gcache_recover_manytrx as result file was incomplete.
      Trivial change.
    * Make galera_create_table_as_select more deterministic:
      Wait until CTAS execution has reached MDL wait for multi-master
      conflict case. Expected error from multi-master conflict is
      ER_QUERY_INTERRUPTED. This is because CTAS does not yet have open
      wsrep transaction when it is waiting for MDL, query gets interrupted
      instead of BF aborted. This should be addressed in separate task.
    * A new test galera_bf_abort_registering to check that registering trx gets
      BF aborted through MDL.
    * A new test galera_kill_group_commit to verify correct behavior
      when KILL is executed while the transaction is committing.

    Co-authored-by: Seppo Jaakola <seppo.jaakola@iki.fi>
    Co-authored-by: Jan Lindström <jan.lindstrom@galeracluster.com>

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Teemu Ollakka
Author Date: 2023-04-17 13:04:01 UTC

MDEV-29293 MariaDB stuck on starting commit state

The problem seems to be a deadlock between KILL command execution
and BF abort issued by an applier, where:
* KILL has locked victim's LOCK_thd_kill and LOCK_thd_data.
* Applier has innodb side global lock mutex and victim trx mutex.
* KILL is calling innobase_kill_query, and is blocked by innodb
  global lock mutex.
* Applier is in wsrep_innobase_kill_one_trx and is blocked by
  victim's LOCK_thd_kill.

The fix in this commit removes the TOI replication of KILL command
and makes KILL execution less intrusive operation. Aborting the
victim happens now by using awake_no_mutex() and ha_abort_transaction().
If the KILL happens when the transaction is committing, the
KILL operation is postponed to happen after the statement
has completed in order to avoid KILL to interrupt commit
processing.

Notable changes in this commit:
* wsrep client connections's error state may remain sticky after
  client connection is closed. This error message will then pop
  up for the next client session issuing first SQL statement.
  This problem raised with test galera.galera_bf_kill.
  The fix is to reset wsrep client error state, before a THD is
  reused for next connetion.
* Release THD locks in wsrep_abort_transaction when locking
  innodb mutexes. This guarantees same locking order as with applier
  BF aborting.
* BF abort from MDL was changed to do BF abort on server/wsrep-lib
  side first, and only then do the BF abort on InnoDB side. This
  removes the need to call back from InnoDB for BF aborts which originate
  from MDL and simplifies the locking.
* Removed wsrep_thd_set_wsrep_aborter() from service_wsrep.h.
  The manipulation of the wsrep_aborter can be done solely on
  server side. Moreover, it is now debug only variable and
  could be excluded from optimized builds.
* Remove LOCK_thd_kill from wsrep_thd_LOCK/UNLOCK to allow more
  fine grained locking for SR BF abort which may require locking
  of victim LOCK_thd_kill. Added explicit call for
  wsrep_thd_kill_LOCK/UNLOCK where appropriate.
* Wsrep-lib was updated to version which allows external
  locking for BF abort calls.

Changes to MTR tests:
* Disable galera_bf_abort_group_commit. This test is going to
  be removed (MDEV-30855).
* Record galera_gcache_recover_manytrx as result file was incomplete.
  Trivial change.
* Make galera_create_table_as_select more deterministic:
  Wait until CTAS execution has reached MDL wait for multi-master
  conflict case. Expected error from multi-master conflict is
  ER_QUERY_INTERRUPTED. This is because CTAS does not yet have open
  wsrep transaction when it is waiting for MDL, query gets interrupted
  instead of BF aborted. This should be addressed in separate task.
* A new test galera_kill_group_commit to verify correct behavior
  when KILL is executed while the transaction is committing.

Co-authored-by: Seppo Jaakola <seppo.jaakola@iki.fi>
Co-authored-by: Jan Lindström <jan.lindstrom@galeracluster.com>
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Vlad Lesin
Author Date: 2023-05-12 09:11:53 UTC

MDEV-31185 rw_trx_hash_t::find() unpins pins too early

rw_trx_hash_t::find() acquires element->mutex, then unpins pins, used for
lf_hash element search. After that the "element" can be deallocated and
reused by some other thread.

If we take a look rw_trx_hash_t::insert()->lf_hash_insert()->lf_alloc_new()
calls, we will not find any element->mutex acquisition, as it was not
initialized yet before it's allocation. rw_trx_hash_t::insert() can reuse
the chunk, unpinned in rw_trx_hash_t::find().

The scenario is the following:

1. Thread 1 have just executed lf_hash_search() in
rw_trx_hash_t::find(), but have not acquired element->mutex yet.
2. Thread 2 have removed the element from hash table with
rw_trx_hash_t::erase() call.
3. Thread 1 acquired element->mutex and unpinned pin 2 pin with
lf_hash_search_unpin(pins) call.
4. Some thread purged memory of the element.
5. Thread 3 reused the memory for the element, filled element->id,
element->trx.
6. Thread 1 crashes with failed "DBUG_ASSERT(trx_id == trx->id)"
assertion.

Note that trx_t objects are also reused, see the code around trx_pools
for details.

The fix is to invoke "lf_hash_search_unpin(pins);" after element->trx is
stored in local variable in rw_trx_hash_t::find().

Reviewed by: Nikita Malyavin, Marko Mäkelä.

Author: Vlad Lesin
Author Date: 2023-05-12 09:11:53 UTC

MDEV-31185 rw_trx_hash_t::find() unpins pins too early

rw_trx_hash_t::find() acquires element->mutex, then unpins pins, used for
lf_hash element search. After that the "element" can be deallocated and
reused by some other thread.

If we take a look rw_trx_hash_t::insert()->lf_hash_insert()->lf_alloc_new()
calls, we will not find any element->mutex acquisition, as it was not
initialized yet before it's allocation. rw_trx_hash_t::insert() can reuse
the chunk, unpinned in rw_trx_hash_t::find().

The scenario is the following:

1. Thread 1 have just executed lf_hash_search() in
rw_trx_hash_t::find(), but have not acquired element->mutex yet.
2. Thread 2 have removed the element from hash table with
rw_trx_hash_t::erase() call.
3. Thread 1 acquired element->mutex and unpinned pin 2 pin with
lf_hash_search_unpin(pins) call.
4. Some thread purged memory of the element.
5. Thread 3 reused the memory for the element, filled element->id,
element->trx.
6. Thread 1 crashes with failed "DBUG_ASSERT(trx_id == trx->id)"
assertion.

Note that trx_t objects are also reused, see the code around trx_pools
for details.

The fix is to invoke "lf_hash_search_unpin(pins);" after element->trx is
stored in local variable in rw_trx_hash_t::find().

Reviewed by: Nikita Malyavin, Marko Mäkelä.

Author: Vlad Lesin
Author Date: 2023-05-12 09:11:53 UTC

MDEV-31185 rw_trx_hash_t::find() unpins pins too early

rw_trx_hash_t::find() acquires element->mutex, then unpins pins, used for
lf_hash element search. After that the "element" can be deallocated and
reused by some other thread.

If we take a look rw_trx_hash_t::insert()->lf_hash_insert()->lf_alloc_new()
calls, we will not find any element->mutex acquisition, as it was not
initialized yet before it's allocation. rw_trx_hash_t::insert() can reuse
the chunk, unpinned in rw_trx_hash_t::find().

The scenario is the following:

1. Thread 1 have just executed lf_hash_search() in
rw_trx_hash_t::find(), but have not acquired element->mutex yet.
2. Thread 2 have removed the element from hash table with
rw_trx_hash_t::erase() call.
3. Thread 1 acquired element->mutex and unpinned pin 2 pin with
lf_hash_search_unpin(pins) call.
4. Some thread purged memory of the element.
5. Thread 3 reused the memory for the element, filled element->id,
element->trx.
6. Thread 1 crashes with failed "DBUG_ASSERT(trx_id == trx->id)"
assertion.

Note that trx_t objects are also reused, see the code around trx_pools
for details.

The fix is to invoke "lf_hash_search_unpin(pins);" after element->trx is
stored in local variable in rw_trx_hash_t::find().

Reviewed by: Nikita Malyavin, Marko Mäkelä.

Author: Vlad Lesin
Author Date: 2023-02-09 10:51:57 UTC

MDEV-27701 Race on trx->lock.wait_lock between lock_rec_move() and lock_sys_t::cancel()

The initial issue was in assertion failure, which checked the equality
of lock to cancel with trx->lock.wait_lock in lock_sys_t::cancel().

If we analyze lock_sys_t::cancel() code from the perspective of
trx->lock.wait_lock racing, we won't find the error there, except the
cases when we need to reload it after the corresponding latches
acquiring.

So the fix is just to remove the assertion and reload
trx->lock.wait_lock after acquiring necessary latches.

Reviewed by: Marko Mäkelä <marko.makela@mariadb.com>

Author: midenok
Author Date: 2022-12-13 06:03:10 UTC

MDEV-27180 Fully atomic partitioning DDL operations

Atomic DDL for partitioning originally covers crash-safety but it does
not recover fully from failures. F.ex. if error happened during the
rename or drop of partitions the ALTER operation does not return the
table to its original state.

The patch solves the above problem similarly to MDEV-25292 (atomic
CREATE OR REPLACE): new partitions are created as temporary, old
partitions are backed up, everything is guarded by two DDL log chains:
rollback and cleanup. Rollback chain contains the actions that bring
back the original table. Cleanup chain deletes the backup files.

The generic operation of alter partition is as follows:

  1. Create new partitions as tmp partitions;
  2. Fill TMP partitions with data;
  3. Rename old and not needed partitions to backup partitions;
  4. Rename tmp partitions to original partititions;
  5. Do any required logging (mariabackup, binary);
  6. If everything is ok, drop the backup partitions;
  7. In the case any of pp.1-5 fails drop new partitions, rename back
     backup partitions.

Each rename operation is executed right after the corresponding DDL
logging. Originally they were done in different loops:
write_log_changed_partitions(), mysql_change_partitions(), etc. The
actual table operations were done in ha_partition handler:
ha_change_partitions(), ha_drop_partitions(),
ha_rename_partitions(). Now these calls are removed.

Instead the above deprecated interfaces the following classes now
handle DDL logging and table operations:

      Alter_partition_logger
      Alter_partition_add
      Alter_partition_change

Alter_partition_logger handles the basic operation, DDL logging and
table renames. Alter_partition_add does the stuff for adding the
partitions. Alter_partition_change combines Alter_partition_logger and
Alter_partition_add for complex operations such as REORGANIZE.

ha_partition::change_partitions() call was not fully removed. Stages
1-4 from that call was renamed to allocate_partitions() and that is
used by Alter_partition_add for initializing file handler arrays
(m_added_file, m_reorged_file). ha_partition::prepare_new_partition()
is renamed to create_partition() because it actually does ha_create()
and the new name better describes what it does (see previous commit
"ha_partition refactoring").

DDL_LOG_DELETE/DDL_LOG_RENAME/DDL_LOG_REPLACE are now pure file
operations. To process both .par and .frm files we now issue two
actions instead of one. That makes interface more simple and that will
be used by MDEV-16417 in future. All the table operations are done via
other actions, such as DDL_LOG_RENAME_TABLE, DDL_LOG_DROP_TABLE, etc.

DDL_LOG_RENAME_TABLE has new alter_partition option. With this option
it does simple ha_rename_table() which is needed for renaming the
partitions. The action for renaming the partitions must start from
phase DDL_RENAME_PHASE_TABLE.

The testing was refactored a bit. Many new fail/crash points was
added, the test was powered by binary and mariabackup logging, it
tests more ALTER commands with variations of partitions-only and
subpartitions, without and under LOCK TABLES.

EXCHANGE PARTITION was fixed: after failure under LOCK TABLES the
table became unlocked. Fixed by correcting place of
reopen_tables(). Tested by partition_debug.

HA_PARTITION_ONE_PHASE handling was removed from
fast_alter_partition() as native partitioning is not supported.

Author: midenok
Author Date: 2022-12-13 06:03:10 UTC

MDEV-27180 Fully atomic partitioning DDL operations

Atomic DDL for partitioning originally covers crash-safety but it does
not recover fully from failures. F.ex. if error happened during the
rename or drop of partitions the ALTER operation does not return the
table to its original state.

The patch solves the above problem similarly to MDEV-25292 (atomic
CREATE OR REPLACE): new partitions are created as temporary, old
partitions are backed up, everything is guarded by two DDL log chains:
rollback and cleanup. Rollback chain contains the actions that bring
back the original table. Cleanup chain deletes the backup files.

The generic operation of alter partition is as follows:

  1. Create new partitions as tmp partitions;
  2. Fill TMP partitions with data;
  3. Rename old and not needed partitions to backup partitions;
  4. Rename tmp partitions to original partititions;
  5. Do any required logging (mariabackup, binary);
  6. If everything is ok, drop the backup partitions;
  7. In the case any of pp.1-5 fails drop new partitions, rename back
     backup partitions.

Each rename operation is executed right after the corresponding DDL
logging. Originally they were done in different loops:
write_log_changed_partitions(), mysql_change_partitions(), etc. The
actual table operations were done in ha_partition handler:
ha_change_partitions(), ha_drop_partitions(),
ha_rename_partitions(). Now these calls are removed.

Instead the above deprecated interfaces the following classes now
handle DDL logging and table operations:

      Alter_partition_logger
      Alter_partition_add
      Alter_partition_change

Alter_partition_logger handles the basic operation, DDL logging and
table renames. Alter_partition_add does the stuff for adding the
partitions. Alter_partition_change combines Alter_partition_logger and
Alter_partition_add for complex operations such as REORGANIZE.

ha_partition::change_partitions() call was not fully removed. Stages
1-4 from that call was renamed to allocate_partitions() and that is
used by Alter_partition_add for initializing file handler arrays
(m_added_file, m_reorged_file). ha_partition::prepare_new_partition()
is renamed to create_partition() because it actually does ha_create()
and the new name better describes what it does (see previous commit
"ha_partition refactoring").

DDL_LOG_DELETE/DDL_LOG_RENAME/DDL_LOG_REPLACE are now pure file
operations. To process both .par and .frm files we now issue two
actions instead of one. That makes interface more simple and that will
be used by MDEV-16417 in future. All the table operations are done via
other actions, such as DDL_LOG_RENAME_TABLE, DDL_LOG_DROP_TABLE, etc.

DDL_LOG_RENAME_TABLE has new alter_partition option. With this option
it does simple ha_rename_table() which is needed for renaming the
partitions. The action for renaming the partitions must start from
phase DDL_RENAME_PHASE_TABLE.

The testing was refactored a bit. Many new fail/crash points was
added, the test was powered by binary and mariabackup logging, it
tests more ALTER commands with variations of partitions-only and
subpartitions, without and under LOCK TABLES.

EXCHANGE PARTITION was fixed: after failure under LOCK TABLES the
table became unlocked. Fixed by correcting place of
reopen_tables(). Tested by partition_debug.

HA_PARTITION_ONE_PHASE handling was removed from
fast_alter_partition() as native partitioning is not supported.

Author: Kristian Nielsen
Author Date: 2023-05-13 18:49:10 UTC

MDEV-30386: Test case for cleaning up extra domain_ids

Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>

Author: Daniele Sciascia
Author Date: 2023-03-16 08:29:10 UTC

MDEV-30473 Remove test galera.MDEV-27713

Remove test galera.MDEV-27713. This test relies on GET_LOCK() and has
stopped working since commit 844ddb1 (see MDEV-30473). This commit
disabled GET_LOCK() in combination with Galera.

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Sergey Petrunia
Author Date: 2023-05-11 12:15:53 UTC

MDEV-31240, MDEV-31241: Crashes in subselects in choose_best_splitting after upgrade

In JOIN_TAB::choose_best_splitting(), take into account that
best_param_tables may contain OUTER_REF_TABLE_BIT (and no table
in join->positions[] has this bit set).

Author: Marko Mäkelä
Author Date: 2023-05-11 06:00:27 UTC

Merge 10.6 into 10.8

Author: Daniel Bartholomew
Author Date: 2023-05-10 12:46:01 UTC

bump the VERSION

Author: Marko Mäkelä
Author Date: 2023-05-10 09:43:17 UTC

MDEV-29967 innodb_read_ahead_threshold (linear read-ahead) does not work

buf_read_ahead_linear(): Correct some calculations that were broken
in commit b1ab211dee599eabd9a5b886fafa3adea29ae041 (MDEV-15053).

Thanks to Daniel Black for providing a test case and initial debugging.

Author: Sergey Petrunia
Author Date: 2023-05-09 10:09:00 UTC

MDEV-31223: UBSan error: sql_select.h:969:7: runtime error: load of value...

In Loose_scan_opt::save_to_position, initialize
POSITION::firstmatch_with_join_buf.

Author: Sergey Petrunia
Author Date: 2023-05-03 12:15:37 UTC

MDEV-31022: SIGSEGV in maria_create from create_internal_tmp_table

The code in create_internal_tmp_table() didn't take into account that
now temporary (derived) tables may have multiple indexes:

- one index due to duplicate removal
   = In this example created by conversion of big-IN(...) into subquery
   = this index might be converted into a "unique constraint" if the key
     length is too large.
- one index added by derived_with_keys optimization.

Make create_internal_tmp_table() handle multiple indexes.

Before this patch, use of a unique constraint was indicated in
TABLE_SHARE::uniques. This was ok as unique constraint was the only index
in the table. Now it's no longer the case so TABLE_SHARE::uniques is removed
and replaced with an in-memory-only flag HA_UNIQUE_HASH.

This patch is based on Monty's patch.
Co-Author: Monty <monty@mariadb.org>

Author: Yuchen Pei
Author Date: 2023-05-08 10:21:54 UTC

MDEV-22534 [do-not-push] a PoC that works for the example case

Test case:

--8<---------------cut here---------------start------------->8---
set @@optimizer_switch='materialization=on,firstmatch=off';
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int, b int, c int);
insert into t1 select a,a,a from ten;
create table t2 select * from t1;

set optimizer_trace="enabled=on";
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;

set optimizer_trace="enabled=on";
explain select * from t1 where (a, b) in (select a, b from t2);
set optimizer_trace="enabled=off";
select * from information_schema.OPTIMIZER_TRACE;

drop table ten, t1, t2;
--8<---------------cut here---------------end--------------->8---

Exactly the same explain output:

--8<---------------cut here---------------start------------->8---
explain select * from t1 where a in (select a from t2 where t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 10
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10

explain select * from t1 where (a, b) in (select a, b from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 10
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10
--8<---------------cut here---------------end--------------->8---

Author: Hartmut Holzgraefe
Author Date: 2023-05-05 10:05:44 UTC

MDEV-30904 "rpm --setugids" breaks PAM authentication

Set auto_pam_tool_dir and auth_pam_tool ownership in RPM specs,
not in POSTINST hook script, so that they are properly restored
when running "rpm --setugids" instead of wrongly changing
auth_pam_tool_dir ownershipt from "mysql" to "root"

Author: Sergey Petrunia
Author Date: 2023-05-03 12:15:37 UTC

MDEV-31022: SIGSEGV in maria_create from create_internal_tmp_table

The code in create_internal_tmp_table() didn't take into account that
now temporary (derived) tables may have multiple indexes:

- one index due to duplicate removal
   = In this example created by conversion of big-IN(...) into subquery
   = this index might be converted into a "unique constraint" if the field(s)
     length is too large.
- one index added by derived_with_keys optimization.

Make it handle multiple indexes.

As a consequence of this, remove TABLE_SHARE::uniques and replace it with
per-index HA_UNIQUE_HASH flag.
- TABLE_SHARE::uniques made sense when it referred to the only index in the
  table.
- Now, we need to know which of table indexes should be a unique constraint,
  so we indicate that as an index flag.

This patch is based on Monty's patch:
Co-Author: Monty <monty@mariadb.org>

Author: Sergey Petrunia
Author Date: 2023-05-03 10:49:32 UTC

MDEV-26301: Split optimization refills: Optimizer Trace coverage

Add Optimizer Trace printouts.

Author: Yuchen Pei
Author Date: 2023-05-03 02:06:59 UTC

MDEV-22979 MDEV-27233 defer spider init from plugin-load-add to a temporary init file

- Move spider init query execution from a background thread to
  spider_db_init() so that it is fully synchronous, adapted from part
  of the reverted commit c160a115b8b6dcd54bb3daf1a751ee9c68b7ee47
- if spider is initialised too early from init_server_components then
  defer the init to a temporary init file
- also updated other init bug tests, for MDEV-29904 and MDEV-30370

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-05-02 18:13:48 UTC

New CC 3.1

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-05-02 18:13:48 UTC

New CC 3.1

Author: Elena Stepanova
Author Date: 2023-05-02 14:02:56 UTC

MacOS tests

Author: Igor Babaev
Author Date: 2023-05-02 07:31:57 UTC

MDEV-31162 Crash for query using ROWNUM over multi-table view with ORDER BY

This bug could cause a crash of the server when processing a query with
ROWNUM() if it used in its FROM list a reference to a mergeable view
defined as SELECT over more than one table that contained ORDER BY clause.
When a mergeable view with ORDER BY clause and without LIMIT clause is used
in the FROM list of a query that does not have ORDER BY clause the ORDER BY
clause of the view is moved to the query. The code that performed this
transformation forgot to delete the moved ORDER BY list from the view.
If a query contains ROWNUM() and uses a mergeable multi-table view with
ORDER BY then according to the current code of TABLE_LIST::init_derived()
the view has to be forcibly materialized. As the query and the view shared
the same items in its ORDER BY lists they could not be properly resolved
either in the query or in the view. This led to a crash of the server.

This patch has returned back the original signature of LEX::can_not_use_merged()
to comply with 10.4 code of the condition that checks whether a megeable
view has to be forcibly materialized.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

Author: Rex Johnston
Author Date: 2023-05-02 02:08:31 UTC

MDEV-31130 INSERT-SELECT with many when/case/if conditions running forever

recursive column definitions in stacked materialized tables causes issues.
detect depth of walk in the item tree and terminate if too deep.

Author: Julius Goryavsky
Author Date: 2023-05-02 02:57:30 UTC

MDEV-29755 correction: running wsrep_replicate_myisam_basic test with wsrep enabled

Author: Julius Goryavsky
Author Date: 2023-05-02 01:35:34 UTC

MDEV-29755 correction: running wsrep_replicate_myisam_basic test with wsrep enabled

Author: sara
Author Date: 2023-01-23 10:09:54 UTC

MDEV-30414 sporadic failures with galera var retry autocommit

changed tast case 2 to be deterministic

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Brandon Nesterenko
Author Date: 2023-03-08 20:50:11 UTC

MDEV-13915: Updates to existing MTR tests

Author: Yuchen Pei
Author Date: 2023-04-28 07:54:41 UTC

MDEV-22979 wip improving spider init queries

- make spider the last plugin to init (doing it the silly way now,
  will need refactoring later)
- remove procedures in init queries which requires `initialized` to be
  true which happens later in mysqld_main() during acl_init()
- simplification using `if not exists`
- move the udf creations to a background thread

Result: mdev_27233, mdev_29904, mdev_30370 and their variants
pass. mdev-22979 install_db command fails. ALL "NORMAL" tests seem to
be hanging...

Author: Yuchen Pei
Author Date: 2023-04-28 05:51:01 UTC

MDEV-22979 A silly patch showing problem solved when init spider last

simplifying init queries to depend on aria. passes spider/bugfix.mdev_29904

Author: Yuchen Pei
Author Date: 2023-04-26 23:51:34 UTC

MDEV-29676 Add query to set lock wait timeout when getting sts crd

Set the lock wait timeout to 1 beforehand, and reset it afterwards, to
avoid lock conflict caused by opening the same table twice in case of
self-reference.

Author: Yuchen Pei
Author Date: 2023-04-26 23:51:34 UTC

MDEV-29676 Add query to set lock wait timeout when getting sts crd

Set the lock wait timeout to 1 beforehand, and reset it afterwards, to
avoid lock conflict caused by opening the same table twice in case of
self-reference.

Author: Sergey Petrunia
Author Date: 2023-04-26 18:41:49 UTC

MDEV-31022 SIGSEGV in maria_create from create_internal_tmp_table

Make create_internal_tmp_table() handle the case where the
created table has multiple indexes.

Author: Yuchen Pei
Author Date: 2023-04-27 02:22:15 UTC

MDEV-29644 post-merge fixup

Applying b98375f9df0 instead of 9b32e4b1923 for version 10.5-10.8.
Applying 5075f4e0dae instead of 9b32e4b1923 for version 10.9+.

Author: Yuchen Pei
Author Date: 2023-04-26 23:51:34 UTC

MDEV-29676 Add query to set lock wait timeout when getting sts crd

Set the lock wait timeout to 1 beforehand, and reset it afterwards, to
avoid lock conflict caused by opening the same table twice in case of
self-reference.

Author: Yuchen Pei
Author Date: 2023-04-26 23:51:34 UTC

MDEV-29676 Add query to set lock wait timeout when getting sts crd

Set the lock wait timeout to 1 beforehand, and reset it afterwards, to
avoid lock conflict caused by opening the same table twice in case of
self-reference.

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-04-21 08:55:14 UTC

MDEV-31073 Server crash, assertion `table != 0 && view->field_translation != 0' failure with ROWNUM and view

Now the same rule applied to vews and derived tables so we should
allow merge of views (and derived) in queries with rownum (because
it do not change results, only makes query plans better)

Author: Yuchen Pei
Author Date: 2023-04-26 02:46:15 UTC

MDEV-22979 [do not push] Move spider init query execution from a background thread to spider_db_init()

This is the original fix by nayuta for MDEV-27233, but it fails
spider/bugfix.mdev_29904

Author: Yuchen Pei
Author Date: 2023-04-26 02:40:47 UTC

MDEV-22979 [do not push] remove wait in spider_create_handler()

This will fail mdev_22979_bootstrap due to race condition.

Author: Brandon Nesterenko
Author Date: 2023-04-05 16:43:28 UTC

MDEV-30430: Enabling system versioning on tables without primary key breaks replication

When replicating MDL events for a table that uses system versioning
without primary keys, ensure that for data sets with duplicate
records, the updates to these records with duplicates are enacted on
the correct row. That is, there was a bug (reported in MDEV-30430)
such that the function to find the row to update would stop after
finding the first matching record. However, in the absence of
primary keys, the version of the record is needed to compare the row
to ensure we are updating the correct one.

The fix, therefore, updates the record comparison functionality to
use system version columns when there are no primary keys on the
table.

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

Author: Brandon Nesterenko
Author Date: 2022-09-28 18:34:44 UTC

MDEV-28798: Previously Binlog Encrypted Master Segfaults on Binlog Dump with Using_Gtid=Slave_Pos

Problem:
========
A master can segfault if it can't set up decryption for its binary
log during a binlog dump with Using_Gtid=Slave_Pos. If slave
connects using GTID mode, the master will call into
log.cc::get_gtid_list_event(), which iterate through binlog events
looking for a Gtid_list_log_event. On an encrypted binlog that the
master cannot decrypt, the first event will be a
START_ENCRYPTION_EVENT which will call into the following decryption branch

if (fdle->start_decryption((Start_encryption_log_event*) ev))
  errormsg= ‘Could not set up decryption for binlog.’;

The event iteration however, does not stop in spite of this error.
The master will try to read the next event, but segfault while
trying to decrypt it because decryption failed to initialize.

Solution:
========
Break the event iteration if decryption cannot be set up.

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

Author: Brandon Nesterenko
Author Date: 2023-04-05 16:43:28 UTC

MDEV-30430: Enabling system versioning on tables without primary key breaks replication

When replicating MDL events for a table that uses system versioning
without primary keys, ensure that for data sets with duplicate
records, the updates to these records with duplicates are enacted on
the correct row. That is, there was a bug (reported in MDEV-30430)
such that the function to find the row to update would stop after
finding the first matching record. However, in the absence of
primary keys, the version of the record is needed to compare the row
to ensure we are updating the correct one.

The fix, therefore, updates the record comparison functionality to
use system version columns when there are no primary keys on the
table.

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

Author: Brandon Nesterenko
Author Date: 2023-04-05 16:43:28 UTC

MDEV-30430: Enabling system versioning on tables without primary key breaks replication

When replicating MDL events for a table that uses system versioning
without primary keys, ensure that for data sets with duplicate
records, the updates to these records with duplicates are enacted on
the correct row. That is, there was a bug (reported in MDEV-30430)
such that the function to find the row to update would stop after
finding the first matching record. However, in the absence of
primary keys, the version of the record is needed to compare the row
to ensure we are updating the correct one.

The fix, therefore, updates the record comparison functionality to
use system version columns when there are no primary keys on the
table.

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

Author: Vladislav Vaintroub
Author Date: 2023-04-25 12:24:39 UTC

MDEV-31095 tpool - do not create new worker, if thread creation is pending.

Use an std::atomic_flag to track thread creation in progress.
This is mainly a cleanup, the effect of this change was not measureable
in my tests.

Author: Sergey Petrunia
Author Date: 2023-04-24 14:57:45 UTC

MDEV-31121: ANALYZE statement produces 0 for all timings in embedded server

One must initialize the timer sybsystem with my_timer_init() call.
It was made only in mysqld_main(). Call it also from init_embedded_server().

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-04-17 13:06:52 UTC

MDEV-30889: 3 - Item_in_optimizer leak

Keep Item_in_optimizer cache always (but only once) in statement memory.

Author: Brandon Nesterenko
Author Date: 2023-04-24 15:52:28 UTC

MDEV-30430: Needed primary-replica sync in test

Author: Brandon Nesterenko
Author Date: 2022-09-29 19:40:51 UTC

MDEV-28798: Cosmetic Changes Only

Removed trailing whitespaces

Author: Igor Babaev
Author Date: 2023-04-21 20:46:14 UTC

MDEV-31102 Crash when pushing condition into view defined as union

This bug could manifest itself at the first execution of prepared statement
created for queries using a materialized view defined as union. A crash
could happen for sure if the query contained a condition pushable into
the view and this condition was over the column defined via a complex string
expression requiring implicit conversion from one charset to another for
some of its sub-expressions. The bug could cause crashes when executing
PS for some other queries whose optimization needed building clones for
such expressions.

This bug was introduced in the patch for MDEV-29988 where the class
Item_direct_ref_to_item was added. The implementations of the virtual
methods get_copy() and build_clone() were invalid for the class and this
could cause crashes after the method build_clone() was called for
expressions containing objects of the Item_direct_ref_to_item type.

Approved by Sergei Golubchik <serg@mariadb.com>

Author: Marko Mäkelä
Author Date: 2023-04-21 11:14:30 UTC

fixup! 1451bc6522dec0d2a64f398577d3c4c3956c4dd0

Author: Yuchen Pei
Author Date: 2023-04-21 06:13:52 UTC

Comparing test results under a broken spider

- mdev_22979: pass with failed post-test check
- mdev_22979_2: pass
- mdev_22979_bootstrap: ERROR: 12524 Can't open system table mysql.spider_table_sts

Author: Sergei Golubchik
Author Date: 2023-04-20 15:05:38 UTC

Merge branch 'merge-zlib' into 10.3

Author: midenok
Author Date: 2023-04-20 09:38:34 UTC

Tmp

Author: Yuchen Pei
Author Date: 2023-04-20 06:45:23 UTC

MDEV-27233-22979-30370 Add and update tests for spider init bugs

For development only - will squash this into another commit later - do
not push.

mdev_27233_v2: a variant of mdev-27233 where the spider plugin is
loaded in during server startup.

mdev_30370: we update this test to use mysqld_cmd instead of the
noisy mysqld_bootstrap_cmd. We have to kill the server first to avoid
"Can't lock aria control file"

With the change in commit c160a115b8b that was pushed to fix
mdev-27233 but later reverted because of mdev-29904, mdev_27233 and
the updated mdev_30370 would pass, but not mdev_27233_v2 ("[ERROR]
SPIDER plugin initialization failed at 'drop procedure if exists
mysql.spider_fix_one_table' by 'Unknown storage engine 'Aria''") or
mdev_30370 if it uses mysqld_bootstrap_cmd (post-test check will fail
when running this case with some other case, e.g. mtr mdev_30370
mdev_29562).

Author: Marko Mäkelä
Author Date: 2023-03-27 10:47:07 UTC

Cleanup: MONITOR_EXISTING trx_undo_slots_used, trx_undo_slots_cached

Let us remove explicit updates of MONITOR_NUM_UNDO_SLOT_USED
and MONITOR_NUM_UNDO_SLOT_CACHED, and let us compute the rough values
from trx_sys.rseg_array[] on demand.

Author: Sergey Petrunia
Author Date: 2023-04-19 12:15:27 UTC

MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram

Variant #2.

When Histogram::point_selectivity() sees that the point value of interest
falls into one bucket, it tries to guess whether the bucket has many
different (unpopular) values or a few popular values. (The number of
rows is fixed, as it's a Height-balanced histogram).
The basis for this guess is the "width" of the value range the bucket
covers. Buckets covering wider value ranges are assumed to contain
values with proportionally lower frequencies.

This is just a [brave] guesswork. For a very narrow bucket, it may
produce an estimate that's larger than total #rows in the bucket
or even in the whole table.

Remove the guesswork and replace it with basic logic: return
either the per-table average selectivity of col=const, or selectivity
of one bucket, whichever is lower.

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-04-04 05:17:20 UTC

MDEV-30996 insert.. select in presence of full text index freezes all other commits at commit time

- When binlog is enabled, trasaction takes a lot of time to
do sync operation on innodb fts table. This leads to block
of other transaction commit. To avoid this failure, remove
the fulltext sync operation during transaction commit.

- This patch does the revert of MDEV-25581 and also introduces
the multiple fts background threads to process and optimize
the fts table.

- Introduced the new variable innodb_fulltext_bg_threads
which does background processing of message and optimization.
Minimum value is 1 and Maximum value can be 255

- By introducing the above variable, InnoDB can do sync on
multiple tables parallel

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-04-19 09:43:20 UTC

MDEV-30996 INSERT..SELECT in presence of fulltext index
    freezes all other commits at commit time

- Introduced new variable innodb_fts_threads which processing
fulltext message, optimization of fulltext table.
Minimum value is 1, default value is 2 and maximum value is 255

- By having multiple fts threads, InnoDB can do sync of multiple
table at the same time.

- Introduce the class fts_slots_t, which can be used to store
the fts table in the slot.

Author: Marko Mäkelä
Author Date: 2023-04-19 09:12:54 UTC

MDEV-26827 fixup: Do not hog buf_pool.mutex

buf_flush_LRU_list_batch(): When evicting clean pages,
release and reacquire the buf_pool.mutex after every 32 pages.
Also, eliminate some conditional branches.

Author: Dmitry Shulga
Author Date: 2023-04-18 16:40:45 UTC

MDEV-30073: Stored Procedure Returns Corrupt Results When Run a Second Time

When name resolution is performed for a view involved in a query
executed as Prepared Statement, changes in an item list of view's select_lex
is resgistered for rollback with Item_change_list::rollback_item_tree_changes.
Later, EXISTS-to-IN transformation takes place and previously resolved items
are replace with new items created as part of EXISTS-to-IN transformation.
This change is supposed to be permanent, so it is not recorded for rollback.
On fininishing execution of a prepared statement clean-up is performed
that does rollback of changes made on phase of name resolutions.
In result, changes made by EXISTS-to-IN transformation is rolled back
that lead to output of incorrect results set.

To fix the original issue it is proposed to make permanent name resolution
for view's fields.

Unfortunately, this fix introduces another issue that takes plce when
join of a regular table and a view is run and there is condition
  'where exist (subquery)'
(see test case in the patch)

Author: Marko Mäkelä
Author Date: 2023-04-18 13:19:39 UTC

MDEV-30986 hybrid build with 10.6 InnoDB

Author: Sergey Petrunia
Author Date: 2023-04-18 10:25:27 UTC

MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram

When Histogram::point_selectivity() sees that the point value of interest
falls into one bucket, it tries to guess whether the bucket has many
different (unpopular) values or a few popular values. (The number of
rows is fixed, as it's a Height-balanced histogram).

The basis for this guess is the "width" of the value range the bucket
covers. Buckets covering wider value ranges are assumed to contain
values with proportionally lower frequencies.

Note that this is just a [brave] guesswork. For a very narrow bucket,
it may produce an estimate that's larger than total #rows in the table
or even in the whole table.

Make a conservative fix: make sure the produced estimate doesn't exceed
the number of rows in the histogram's bucket.

Author: Daniele Sciascia
Author Date: 2023-03-29 11:55:30 UTC

MDEV-30955 Explicit locks released too early in rollback path

Assertion `thd->mdl_context.is_lock_owner()` fires when a client is
disconnected, while transaction and and a table is opened through
`HANDLER` interface.
Reason for the assertion is that when a connection closes, its ongoing
transaction is eventually rolled back in
`Wsrep_client_state::bf_rollback()`. This method also releases explicit
which are expected to survive beyond the transaction lifetime.
This patch also removes calls to `mysql_ull_cleanup()`. User level
locks are not supported in combination with Galera, making these calls
unnecessary.

Author: Denis Protivensky
Author Date: 2023-03-14 11:08:12 UTC

MDEV-30804 Rollback multi-engine transaction requiring 2PC but committing in one phase

Author: Rex Johnston
Author Date: 2023-04-17 19:33:00 UTC

MDEV-31022 SIGSEGV in maria_create from create_internal_tmp_table

keydef incorrectly allocated on the stack in create_internal_tmp_table()