lp:maria

Author: Oleksandr "Sanja" Byelkin
Author Date: 2022-03-23 09:35:38 UTC

MDEV-27957 Select from view with subselect fails with lost connection

The problem is in getting select number of excluded EXPLAIN-node,
because on excluding links in exclued unit removed.

IMHO it is simplier to get the number in other way and let the EXPLAIN
proceed then fix EXPLAIN. (the other solution is to do not create nodes
for excluded units)

Author: Vicențiu Ciorbaru
Author Date: 2022-03-23 09:52:22 UTC

MEDV-14443: Show create database denies

Author: Sergey Petrunia
Author Date: 2022-03-22 07:07:39 UTC

Update test results (8)

Author: Julius Goryavsky
Author Date: 2022-03-22 03:40:04 UTC

MDEV-26971 post-fix: fixes for SST scripts

1) Fixed a bug with incorrect calculation of the payload length
   when using the --innodb-undo-directory option on the command
   line (not in the .cnf file);
2) Fixed a bug with incorrect processing of progress=none in the
   presence of *.qp-files;
3) Progress processing for *.qp-files has been made more logically
   unified with the semantics of the adjust_progress function;
4) Added forgotten option "--maxdepth 1" when searching for
   *.qp-files;
5) Eliminated "cosmetic" differences with the SST scripts in
   previous versions, which otherwise will cause problems when
   automatically migrating future changes;
6) Fixed a bug caused by automatic migration of the MDEV-27524.

Author: Vlad Lesin
Author Date: 2022-03-14 17:46:40 UTC

MDEV-27557 InnoDB unnecessarily commits mtr during secondary index search to preserve clustered index latching order

New function to release latches till savepoint was added in mtr_t. As there is
no longer need to limit MDEV-20605 fix usage for locking reads only, the
limitation is removed.

Author: Julius Goryavsky
Author Date: 2022-03-21 01:35:20 UTC

Additional fixes

Author: Monty
Author Date: 2022-03-17 17:37:51 UTC

Last sync of functions for drop table between 10.2 and 10.7

Functions checked / synced between 10.2 and 10.7:

handler.cc
ha_delete_table()
int handler::delete_table(const char *name)
static my_bool delete_table_force(THD *thd, plugin_ref plugin, void *arg);
int ha_delete_table_force(THD *thd, const char *path, const LEX_CSTRING *db,
                          const LEX_CSTRING *alias);

sql_table.cc
mysql_rm_table_no_locks()
- Things related to drop force synced

Usage of ha_delete_table()

Author: Monty
Author Date: 2022-03-17 14:58:43 UTC

Fixed warning for maria.maria-recovery2 about crashed table

The bug was a missing va_start in eprint() which caused a wrong table
name to be printed.
Patch backported from 10.3.

Author: Jan Lindström
Author Date: 2022-03-15 10:48:29 UTC

MDEV-24143 : Galera nodes "randomly" crashing in Item_func_release_lock::val_int

Fixed on MDEV-27713. Added additional test case.

Author: Daniel Black
Author Date: 2022-03-18 01:20:09 UTC

MDEV-28011: debian autobake cleanup (10.6, pmem +uring)

Fixing the version of debian/ubuntu depencies in 10.6
removes the apt-cache checking of libpmem and liburing
dependencies.

By arraging the checks earliest to latest, we unconditionally
change the dependences in earlier versions, and in later versions
we perform architecture checks to see if there is a dependency
on this architure before removing/changing.

This takes from the architecture information on Ubuntu[1,2]
and Debian[3,4].

[1] https://packages.ubuntu.com/search?suite=all&arch=any&searchon=names&keywords=liburing-dev
[2] https://packages.ubuntu.com/search?suite=all&arch=any&searchon=names&keywords=libpmem-dev
[3] https://packages.debian.org/search?suite=all&section=all&arch=any&searchon=names&keywords=liburing-dev
[4] https://packages.debian.org/search?suite=all&section=all&arch=any&searchon=names&keywords=libpmem-dev

Author: Julius Goryavsky
Author Date: 2020-07-13 17:12:00 UTC

MDEV-19281: Plugin implementation for the Hashicorp Vault KMS

- Authentication is done using the Hashicorp Vault's token
  authentication method;
- If additional client authentication is required, then the
  path to the CA authentication bundle file may be passed
  as a plugin parameter;
- The creation of the keys and their management is carried
  out using the Hashicorp Vault KMS and their tools;
- Key values stored as hexadecimal strings;
- Key values caching is supported.
- Implemented a time-invalidated cache for key values and
  for key version numbers received from the Hashicorp Valult
  server;
- The plugin uses libcurl (https) as an interface to
  the HashiCorp Vault server;
- JSON parsing is performed through the JSON service
  (through the include/mysql/service_json.h);
- HashiCorp Vault 1.2.4 was used for development and testing.

Author: Jan Lindström
Author Date: 2022-03-16 08:14:48 UTC

Fix compile error.

Author: Alexey Botchkov
Author Date: 2022-03-15 12:28:33 UTC

MDEV-27159 Re-design the upper level of handling DML commands.

Sql_cmd_insert class introduced.

Author: Julius Goryavsky
Author Date: 2022-03-10 08:58:26 UTC

MENT-1437: Failure in hashicorp_key_rotation_age test

Fixed a failure in the hashicorp key_rotation_age test -
the test made more robust and working with the latest
changes in the innodb.

Also improved error handling during plugin initialization
(memory deallocation is performed in the case of failure).

Author: Angelique Sklavounos
Author Date: 2022-03-14 16:47:29 UTC

MDEV-27849: test timeout increase

Author: Elena Stepanova
Author Date: 2022-03-13 22:59:44 UTC

MDEV-28036 gcol.gcol_supported_sql_funcs_xxx fail in FIPS mode

Author: Marko Mäkelä
Author Date: 2022-03-11 15:46:38 UTC

Merge 10.9 and revert 1766a18e06a056155031dabefb88ce7f201ad921

Author: Vladislav Vaintroub
Author Date: 2022-03-07 09:36:16 UTC

MDEV-26603 asynchronous redo log write

Split redo log write submission and completion.

Currently, AIO is not really used much, because it turns out to be 10-15%
slower than synchronous IO, in write-heavy benchmarks.

It looks like that AIO with callbacks in threadpool currently adds more
context switches, if submitting thread waits for it.

With this patch, AIO is only used where latency is not that important,
in "fire-and-forget" manner, e.g log_write_up_to() retry logic, or prior to
LRU flushing,or in srv_master periodic timer task.

Still, thanks to AIO, internal group commit logic could be improved and
sped up. Apparently, "next groupcommit lead candidate" thread waiting was
removed.

After some benchmarking, spins in group_commit_lock::acquire() were removed
as they did not have any positive effect.

So overall the effect of that patch should be positive.

Author: Oleg Smirnov
Author Date: 2022-03-07 07:38:04 UTC

MDEV-27776 Make EXPLAIN show filtered and partitions columns

Author: Sergey Petrunia
Author Date: 2021-12-17 17:29:41 UTC

MDEV-27188: Suppress optimizer output when executing prepare

- Do not write anything into Optimizer Trace at Prepare phase
- When the query gets an error at Prepare phase, make sure there
  is no trace written, either. This is important as we need to
  produce the same trace for "mtr --ps-protocol" and regular mtr run.
- For other kinds of errors, trace is still produced as it might be
  valuable.

(variant 3)

Author: Julius Goryavsky
Author Date: 2022-03-04 13:02:25 UTC

galera_3nodes.galera_2_cluster test: ignoring expected warnings

Author: Brandon Nesterenko
Author Date: 2022-03-03 16:16:21 UTC

MDEV-27850

Temporary commit for buildbot testing of DEBUG_SYNC
race detection and test fixes.

Author: Monty
Author Date: 2021-11-01 10:34:24 UTC

Update row and key fetch cost models to take into account data copy costs

Before this patch, when calculating the cost of fetching and using a
row/key from the engine, we took into account the cost of finding a
row or key from the engine, but did not consistently take into account
index only accessed, clustered key or covered keys for all access
paths.

The cost of the WHERE clause (TIME_FOR_COMPARE) was not consistently
considered in best_access_path(). TIME_FOR_COMPARE was used in
calculation in other places, like greedy_search(), but was in some
cases (like scans) done an a different number of rows than was
accessed.

The cost calculation of row and index scans didn't take into account
the number of rows that where accessed, only the number of accepted
rows.

When using a filter, the cost of index_only_reads and cost of
accessing and disregarding 'filtered rows' where not taken into
account, which made filters cost less than there actually where.

To remedy the above, the following key & row fetch related costs
has been added:

- The cost of fetching and using a row is now split into different costs:
  - key + Row fetch cost (as before) but multiplied with the variable
  'optimizer_cache_cost' (default to 0.5). This allows the user to
  tell the optimizer the likehood of finding the key and row in the
  engine cache.
- RECORD_COPY_COST, The cost copying a row from the engine to the
  sql layer or creating a row from the join_cache to the record
  buffer. Mostly affects table scan costs.
- INDEX_COPY_COST the cost of finding the next key and copying it from
  the engine to the SQL layer. This is used when we calculate the cost
  index only reads. It makes index scans more expensive than before if
  they cover a lot of rows. (main.index_merge_myisam)
- INDEX_LOOKUP_COST, the cost of finding the first key in a range.
  This replaces the old define IDX_LOOKUP_COST, but with a higher cost.
- INDEX_NEXT_FIND_COST, the cost of finding the next key (and rowid).
  when doing a index scan and comparing the rowid to the filter.
  Before this cost was assumed to be 0.
- ROW_LOOKUP_COST, the cost of fetching a row by rowid.

All of the above constants/variables are now tuned to be somewhat in
proportion of executing complexity to each other. There is tuning
need for these in the future, but that can wait until the above are
made user variables as that will make tuning much easier.

To make the usage of the above easy, there are new (not virtual)
cost calclation functions in handler:
- ha_read_time(), like read_time(), but take optimizer_cache_cost into
  account.
- ha_read_and_copy_time(), like ha_read_time() but take into account
  RECORD_COPY_TIME
- ha_read_and_compare_time(), like ha_read_and_copy_time() but take
  TIME_FOR_COMPARE into account.
- ha_read_with_rowid(). Read row with row id, taking RECORD_COPY_COST
  into account. This is used with filesort where we don't need
  to execute the WHERE clause again.
- ha_keyread_time(), like keyread_time() but take
  optimizer_cache_cost into account.
- ha_keyread_and_copy_time(), like ha_keyread_time(), but add
  INDEX_COPY_COST.
- ha_key_scan_time(), like key_scan_time() but take
  optimizer_cache_cost nto account.
- ha_key_scan_and_compare_time(), like ha_key_scan_time(), but add
  INDEX_COPY_COST & TIME_FOR_COMPARE.

I also added some setup costs for doing different types of scans and
creating temporary tables (on disk and in memory). This encourages
the optimizer to not use these for simple 'a few row' lookups if
there are adequate key lookup strategies.
- TABLE_SCAN_SETUP_COST, cost of starting a table scan.
- INDEX_SCAN_SETUP_COST, cost of starting an index scan.
- HEAP_TEMPTABLE_CREATE_COST, cost of creating in memory
  temporary table.
- DISK_TEMPTABLE_CREATE_COST, cost of creating an on disk temporary
  table.

When calculating cost of fetching ranges, we had a cost of
IDX_LOOKUP_COST (0.125) for doing a key div for a new range. This is
now replaced with 'io_cost * INDEX_LOOKUP_COST (1.0) *
optimizer_cache_cost', which matches the cost we use for 'ref' and
other key lookups. The effect is that the cost is now a bit higher
when we have many ranges for a key.

Allmost all calculation with TIME_FOR_COMPARE is now done in
best_access_path(). 'JOIN::read_time' now includes the full
cost for finding the rows in the table.

In the result files, many of the changes are now again close to what
they where before the "Update cost for hash and cached joins" commit,
as that commit didn't fix the filter cost (too complex to do
everything in one commit).

The above changes showed a lot of a lot of inconsistencies in
optimizer cost calculation. The main objective with the other changes
was to calculation as similar (and accurate) as possible to make
different plans more comparable.

Detailed list of changes:

- Calculate index_only_cost consistently and correctly for all scan
  and ref accesses. The row fetch_cost and index_only_cost now
  takes into account clustered keys, covered keys and index only accesses.
- cost_for_index_read now returns both full cost and index_only_cost
- Fixed cost calculation of get_sweep_read_cost() to match other
  similar costs. This is bases on the assumption that data is more
  often stored on SSD than a hard disk.
- Replaced constant 2.0 with new define TABLE_SCAN_SETUP_COST.
- Some scan cost estimates did not take into account
  TIME_FOR_COMPARE. Now all scan costs takes this into
  account. (main.show_explain)
- Added session variable optimizer_cache_hit_ratio (default 50%). By
  adjusting this on can reduce or increase the cost of index or direct
  record lookups. The effect of the default is that key lookups is now
  a bit cheaper than before. See usage of 'optimizer_cache_cost' in
  handler.h.
- JOIN_TAB::scan_time() did not take into account index only scans,
  which produced a wrong cost when index scan was used. Changed
  JOIN_TAB:::scan_time() to take into consideration clustered and
  covered keys. The values are now cached and we only have to call
  this function once. Other calls are changed to use the cached
  values. Function renamed to JOIN_TAB::estimate_scan_time().
- Fixed that most index cost calculations are done the same way and
  more close to 'range' calculations. The cost is now lower than
  before for small data sets and higher for large data sets as we take
  into account how many keys are read (main.opt_trace_selectivity,
  main.limit_rows_examined).
- Ensured that index_scan_cost() ==
  range(scan_of_all_rows_in_table_using_one_range) +
  MULTI_RANGE_READ_INFO_CONST. One effect of this is that if there
  is choice of doing a full index scan and a range-index scan over
  almost the whole table then index scan will be preferred (no
  range-read setup cost). (innodb.innodb, main.show_explain,
  main.range)
  - Fixed the EQ_REF and REF takes into account clustered and covered
    keys. This changes some plans to use covered or clustered indexes
    as these are much cheaper. (main.subselect_mat_cost,
    main.state_tables_innodb, main.limit_rows_examined)
  - Rowid filter setup cost and filter compare cost now takes into
    account fetching and checking the rowid (INDEX_NEXT_FIND_COST).
    (main.partition_pruning heap.heap_btree main.log_state)
  - Added INDEX_NEXT_FIND_COST to
    Range_rowid_filter_cost_info::lookup_cost to account of the time
    to find and check the next key value against the container
  - Introduced ha_keyread_time(rows) that takes into account finding
    the next row and copying the key value to 'record'
    (INDEX_COPY_COST).
  - Introduced ha_key_scan_time() for calculating an index scan over
    all rows.
  - Added IDX_LOOKUP_COST to keyread_time() as a startup cost.
  - Added index_only_fetch_cost() as a convenience function to
    OPT_RANGE.
  - keyread_time() cost is slightly reduced to prefer shorter keys.
    (main.index_merge_myisam)
  - All of the above caused some index_merge combinations to be
    rejected because of cost (main.index_intersect). In some cases
    'ref' where replaced with index_merge because of the low
    cost calculation of get_sweep_read_cost().
  - Some index usage moved from PRIMARY to a covering index.
    (main.subselect_innodb)
- Changed cost calculation of filter to take INDEX_LOOKUP_COST and
  TIME_FOR_COMPARE into account. See sql_select.cc::apply_filter().
  filter parameters and costs are now written to optimizer_trace.
- Don't use matchings_records_in_range() to try to estimate the number
  of filtered rows for ranges. The reason is that we want to ensure
  that 'range' is calculated similar to 'ref'. There is also more work
  needed to calculate the selectivity when using ranges and ranges and
  filtering. This causes filtering column in EXPLAIN EXTENDED to be
  100.00 for some cases where range cannot use filtering.
  (main.rowid_filter)
- Introduced ha_scan_time() that takes into account the CPU cost of
  finding the next row and copying the row from the engine to
  'record'. This causes costs of table scan to slightly increase and
  some test to changed their plan from ALL to RANGE or ALL to ref.
  (innodb.innodb_mysql, main.select_pkeycache)
  In a few cases where scan time of very small tables have lower cost
  than a ref or range, things changed from ref/range to ALL.
  (main.myisam, main.func_group, main.limit_rows_examined,
  main.subselect2)
- Introduced ha_scan_and_compare_time() which is like ha_scan_time()
  but also adds the cost of the where clause (TIME_FOR_COMPARE).
- Added small cost for creating temporary table for
  materialization. This causes some very small tables to use scan
  instead of materialization.
- Added checking of the WHERE clause (TIME_FOR_COMPARE) of the
  accepted rows to ROR costs in get_best_ror_intersect()
- Removed '- 0.001' from 'join->best_read' and optimize_straight_join()
  to ensure that the 'Last_query_cost' status variable contains the same
  value as the one that was calculated by the optimizer.
- Take avg_io_cost() into account in handler::keyread_time() and
  handler::read_time(). This should have no effect as it's 1.0 by
  default, except for heap that overrides these functions.
- Some 'ref_or_null' accesses changed to 'range' because of cost
  adjustments (main.order_by)
- Added scan type "scan_with_join_cache" for optimizer_trace. This is
  just to show in the trace what kind of scan was used.
- When using 'scan_with_join_cache' take into account number of
  preceding tables (as have to restore all fields for all previous
  table combination when checking the where clause)
  The new cost added is:
  (row_combinations * RECORD_COPY_COST * number_of_cached_tables).
  This increases the cost of join buffering in proportion of the
  number of tables in the join buffer. One effect is that full scans
  are now done earlier as the cost is then smaller.
  (main.join_outer_innodb, main.greedy_optimizer)
- Removed the usage of 'worst_seeks' in cost_for_index_read as it
  caused wrong plans to be created; It prefered JT_EQ_REF even if it
  would be much more expensive than a full table scan. A related issue
  was that worst_seeks only applied to full lookup, not to clustered
  or index only lookups, which is not consistent. This caused some
  plans to use index scan instead of eq_ref (main.union)
- Changed federated block size from 4096 to 1500, which is the typical
  size of an IO packet.
- Added costs for reading rows to Federated. Needed as there is no
  caching of rows in the federated engine.
- Added ha_innobase::read_with_rowid() cost function.
- A lot of extra things added to optimizer trace
  - More costs, especially for materialization and index_merge.
  - Make lables more uniform
  - Fixed a lot of minor bugs
  - Added 'trace_started()' around a lot of trace blocks.
- When calculating ORDER BY with LIMIT cost for using an index
  the cost did not take into account the number of row retrivals
  that has to be done or the cost of comparing the rows with the
  WHERE clause. The cost calculated would be just a fraction of
  the real cost. Now we calculate the cost as we do for ranges
  and 'ref'.
- 'Using index for group-by' is used a bit as we now take into account
   the WHERE clause cost when comparing with 'ref' and prefer the
   method with fewer row combinations. (main.group_min_max).

Bugs fixed:
- Fixed that we don't calculate TIME_FOR_COMPARE twice for some plans,
  like in optimize_straight_join() and greedy_search()
- Fixed bug in save_explain_data where we could test for the wrong
  index when displaying 'Using index'. This caused some old plans to
  show 'Using index'. (main.subselect_innodb, main.subselect2)
- Fixed bug in get_best_ror_intersect() where 'min_cost' was not
  updated, and the cost we compared with was not the one that was
  used.
- Fixed very wrong cost calculation for priority queues in
  check_if_pq_applicable(). (main.order_by now correctly uses priority
  queue)
- When calculating cost of EQ_REF or REF, we added the cost of
  comparing the WHERE clause with the found rows, not all row
  combinations. This made ref and eq_ref to be regarded way to cheap
  compared to other access methods.
- FORCE INDEX cost calculation didn't take into account clustered or
  covered indexes.
- JT_EQ_REF cost was estimated as avg_io_cost(), which is half the
  cost of a JT_REF key. This may be true for InnoDB primary key, but
  not for other unique keys or other engines. Now we use handler
  function to calculate the cost, which allows us to handle
  consistently clustered, covered keys and not covered keys.
- ha_start_keyread() didn't call extra_opt() if keyread was already
  enabled but still changed the 'keyread' variable (which is wrong).
  Fixed by not doing anything if keyread is already enabled.
- multi_range_read_info_cost() didn't take into account io_cost when
  calculating the cost of ranges.
- fix_semijoin_strategies_for_picked_join_order() used the wrong
  record_count when calling best_access_path() for SJ_OPT_FIRST_MATCH
  and SJ_OPT_LOOSE_SCAN.
- Hash joins didn't provide correct best_cost to the upper level, which
  means that the cost for hash_joins more expensive than calculated
  in best_access_path (a difference of 10x * TIME_OF_COMPARE).
  This is fixed in the new code thanks to that we now include
  TIME_OF_COMPARE cost in 'read_time'.

Other things:
- Added some 'if (thd->trace_started())' to speed up code
- Removed not used function Cost_estimate::is_zero()
- Simplified testing of HA_POS_ERROR in get_best_ror_intersect().
  (No cost changes)
- Moved ha_start_keyread() from join_read_const_table() to join_read_const()
  to enable keyread for all types of JT_CONST tables.
- Made a few very short functions inline in handler.h

Notes:
- In main.rowid_filter the join order of order and lineitem is swapped.
  This is because the cost of doing a range fetch of lineitem(98 rows) is
  almost as big as the whole join of order,lineitem. The filtering will
  also ensure that we only have to do very small key fetches of the rows
  in lineitem.
- main.index_merge_myisam had a few changes where we are now using
  less keys for index_merge. This is because index scans are now more
  expensive than before.
- handler->optimizer_cache_cost is updated in ha_external_lock().
  This ensures that it is up to date per statements.
  Not an optimal solution (for locked tables), but should be ok for now.
- 'DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a' does not take cost of
  filesort into consideration when table scan is chosen.
  (main.myisam_explain_non_select_all)
- perfschema.table_aggregate_global_* has changed because an update
  on a table with 1 row will now use table scan instead of key lookup.

TODO in upcomming commits:
- Fix selectivity calculation for ranges with and without filtering and
  when there is a ref access but scan is chosen.
  For this we have to store the lowest known value for
  'accepted_records' in the OPT_RANGE structure.
- Change that records_read does not include filtered rows.
- test_if_cheaper_ordering() needs to be updated to properly calculate costs.
  This will fix tests like main.order_by_innodb, main.single_delete_update
- Extend get_range_limit_read_cost() to take into considering
  cost_for_index_read() if there where no quick keys. This will reduce
  the computed cost for ORDER BY with LIMIT in some cases.
  (main.innodb_ext_key)
- Change all optimizer cost constants to user variables. This will make it
  possible for the users to tune the cost model if needed instead of having
  to ask for a new server build.
- Fix that we take into account selectivity when counting the number of rows
  we have to read when considering using a index table scan to resolve
  ORDER BY.
- Add new calculation for reaed_with_rowid() where we take into account the
  benefit of reading multiple rows from the same page.

fixup to be combined with previous commit

Another fixup, to be combined with the previous commits

Fixed a lot of inconsistencies in optimizer cost calculation. The main
objective was get cost calculation as similar (and accurate) as
possible to make different plans more comparable.

- Replaced constant 2.0 with new define TABLE_SCAN_SETUP_COST.
- Added RECORD_COPY_COST, the cost of finding the next row and copying
  it to record for table scans.
- Added INDEX_COPY_COST, the cost of finding the next key and copying it
  to record for index scans.
- Added INDEX_NEXT_FIND_COST, the cost of finding the next index entry and
  checking it against filter.
- Some scan cost estimates did not take into account
  TIME_FOR_COMPARE. Now all scan costs takes this into
  account. (main.show_explain)
- Added session variable optimizer_cache_hit_ratio (default 50%). By
  adjusting this on can reduce or increase the cost of index or direct
  record lookups. The effect of the default is that key lookups is now
  a bit cheaper than before. See usage of 'optimizer_cache_cost' in
  handler.h.
- JOIN_TAB::scan_time() did not take into account index only scans,
  which produced a wrong cost when index scan was used. Fixed by
  adding support for covering keys. Cached also the calculated values
  to avoid future calls during optimization phase.
- Fixed that most index cost calculations are done the same way and
  more close to 'range' calculations. The cost is now lower than before for
  small data sets and higher for large data sets as we take into account
  how many keys are read.
  - Ensured that index_scan_cost() ==
    range(scan_of_all_rows_in_table_using_one_range) +
    MULTI_RANGE_READ_INFO_CONST. One effect of this is that if there is
    choice of doing a full index scan and a range-index scan over almost
    the whole table then index scan will be preferred (no range-read
    setup cost).
    (innodb.innodb, main.show_explain, main.range)
  - Fixed the EQ_REF and REF takes into account clustered and covered keys.
    This changes some plans to use covered or clustered indexes as these are
    much cheaper.
    (main.subselect_mat_cost main.state_tables_innodb)
  - Rowid filter setup cost and filter compare cost now takes into account
    fetching and checking the rowid (INDEX_NEXT_FIND_COST).
   (main.partition_pruning heap.heap_btree main.log_state)
  - Introduced ha_keyread_time(rows) that takes into account finding the
    next row and copying the key value to 'record' (INDEX_COPY_COST).
  - Introduced ha_key_scan_time() for calculating an index scan over all
    rows.
  - Added IDX_LOOKUP_COST to keyread_time() as a startup cost.
  - Added index_only_fetch_cost() as a convenience function to
    OPT_RANGE.
  - keyread_time() cost is slightly reduced to prefer shorter keys.
    (main.index_merge_myisam)
  - All of the above caused some index_merge combinations to be
    rejected because of cost (main.index_intersect). In some cases
    'ref' where replaced with index_merge because of the low
    cost calculation of get_sweep_read_cost().
  - Some index usage moved from PRIMARY to a covering index.
    (main.subselect_innodb)
- Don't use matching_records_in_range() to try to estimate the number of
  filtered rows for ranges. The reason is that we want to ensure that 'range'
  is calculated similar to 'ref'. There is also more work needed to
  calculate the selectivity when using ranges and ranges and filtering.
  This causes filtering column in EXPLAIN EXTENDED to be 100.00 for
  some cases where range cannot use filtering.
  (main.rowid_filter)
- Introduced ha_scan_time() that takes into account the CPU cost of
  finding the next row and copying the row from the engine to
  'record'. This causes costs of table scan to slightly increase and
  some test to changed their plan from ALL to RANGE or ALL to ref.
  (innodb.innodb_mysql, main.select_pkeycache)
- Introduced ha_scan_and_compare_time() which is like ha_scan_time()
  but also adds the cost of checking the where clause
  (TIME_FOR_COMPARE).
- Introduced ha_read_with_rowid() that takes into account
  RECORD_COPY_COST.
- Added checking of the WHERE clause of the accepted rows to ROR costs
  in get_best_ror_intersect()
- Removed '- 0.001' from 'join->best_read' and optimize_straight_join()
  to ensure that the 'Last_query_cost' status variable contains the same
  value as the one that was calculated by the optimizer.
- Added INDEX_NEXT_FIND_COST to Range_rowid_filter_cost_info::lookup_cost
  to account of the time to find and check the next key value against the
  container
- Changed 'JOIN_TAB:::scan_time() to take into consideration clustered and
  covered keys. The values are now cached and we only have to call this
  function once. Other calls are changed to use the cached values.
  Function renamed to JOIN_TAB::estimate_scan_time().
- Take avg_io_cost() into account in handler::keyread_time() and
  handler::read_time(). This should have no effect as it's 1.0 by
  default, except for heap that overrides these functions.
- Some 'ref_or_null' accesses changed to 'range' because of cost
  adjustments (main.order_by)
- Added scan type "scan_with_join_cache" for optimizer_trace. This is just
  to show what kind of scan was used.
-I had to remove the usage of 'worst_seeks' in cost_for_index_read as it
 cases wrong plans to be created; It prefered JT_EQ_REF even if it would
 be much more expensive than a full table scan. A related issue was that
 worst_seeks only applied to full lookup, not to clustered or index only
 lookups, which is not consistent. This caused some plans to use index
 scan instead of eq_ref (main.union)

Bugs fixed:
- Fixed that we don't calculate TIME_FOR_COMPARE twice for some plans,
  like in optimize_straight_join() and greedy_search()
- Fixed bug in save_explain_data where we could test for the wrong
  index when displaying 'Using index'. This caused some old plans to
  show 'Using index'. (main.subselect_innodb, main.subselect2)
- Fixed bug in get_best_ror_intersect() where 'min_cost' was not updated,
  and the cost we compared with was not the one that was used.
- Fixed very wrong cost calculation for priority queues in
  check_if_pq_applicable().
- When calculating cost of EQ_REF or REF, we added the cost of comparing
  the WHERE clause with the found rows, not all row combinations. This
  made ref and eq_ref to be regarded way to cheap compared to other
  access methods.
- FORCE INDEX cost calculation didn't take into account clustered or
  covered indexes.
- JT_EQ_REF cost was estimated as avg_io_cost(), which is half the
  cost of a JT_REF key. This may be true for InnoDB primary key, but
  not for other unique keys or other engines. Now we use handler
  function to calculate the cost, which allows us to handle
  consistently clustered, covered keys and not covered keys.
- ha_start_keyread() didn't call extra_opt() if keyread was already
  enabled but still changed the 'keyread' variable (which is wrong).
  Fixed by not doing anything if keyread is already enabled.
- multi_range_read_info_cost() didn't take into account io_cost when
  calculating the cost of ranges.

Other things:
- Added some 'if (thd->trace_started())' to speed up code
- Removed not used function Cost_estimate::is_zero()
- Simplified testing of HA_POS_ERROR in get_best_ror_intersect().
  (No cost changes)
- Moved ha_start_keyread() from join_read_const_table() to join_read_const()
  to enable keyread for all types of JT_CONST tables.
- Made a few very short functions inline in handler.h

TODO in upcomming commit:
- Fix selectivity calculation for ranges with and without filtering and
  when there is a ref access but scan is chosen.
  For this we have to store the lowest known value for
  'accepted_records' in the OPT_RANGE structure.
- test_if_cheaper_ordering() needs to be updated to properly calculate costs.
  This will fix tests like main.order_by_innodb.
- Extend get_range_limit_read_cost() to take into considering
  cost_for_index_read() if there where no quick keys. This will reduce
  the computed cost for ORDER BY with LIMIT in some cases.
  (main.innodb_ext_key)

COMMENTS:
- In main.rowid_filter the join order of order and lineitem is swapped.
  This is because the cost of doing a range fetch of lineitem(98 rows) is
  almost as big as the whole join of order,lineitem. The filtering will
  also ensure that we only have to do very small key fetches of the rows
  in lineitem.
- handler->optimizer_cache_cost is updated in ha_external_lock().
  This ensures that it is up to date per statements.
  Not an optimal solution (for locked tables), but should be ok for now.

Another temporary commit to be combined with previous ones

Author: Andrei
Author Date: 2022-03-01 12:07:07 UTC

MDEV-27963 multisource_for_channel sometimes fails in bb with result content mismatch

The mismatch with the result file was caused by asynchronicity
START-SLAVE caused relay log rotation and the respective
SHOW-SLAVE-STATUS' field check.

Fixed with masking two possible results with a single pattern.

A more complicated alternative solution of waiting for the end of
relay-log rotation is waived because the test does not really require
the exact relay-log name.

Author: Jan Lindström
Author Date: 2022-01-28 07:00:38 UTC

MDEV-24845 : Oddities around innodb_fatal_semaphore_wait_threshold and global.innodb_disallow_writes

We will remove variable innodb_disallow_writes because it is badly
designed and implemented. Parameter will be marked as removed.

Instead we will be using
* Galera provider is paused i.e. all commits will wait
* FLUSH TABLES WITH READ LOCK (FTWRL) to avoid any DDL during SST
* We set max_dirty_pages_pct to 0.0 to flush all dirty pages from buffer pool
* We force flushing all dirty pages from buffer pool and force InnoDB checkpoint
* We set max_dirty_pagec_pct to 99.9 to avoid flushing any pages
* We set server to read-only
* Encryption, purge and FTS-optimize threads will acquire MDL_BACKUP_DML
before continuing. This will conflict with lock acquired in FTWRL.
Note that we will not use waiting. If MDL-lock can't be acquired
we will skip the operation.

xtrabackup.cc
  Remove INNODB_DISALLOW_WRITES code

handler.cc
handler.h
  Add new API function ha_force_checkpoint to force checkpoint
  inside InnoDB.

mysqld.cc
  Mark innodb-disallow-writes variable as removed.

sql_class.cc
  New functions to get and release backup lock.

wsrep_sst.cc
  Add functions to set innodb_max_dirty_pages_pct,
  innodb_max_dirty_pages_pct_lwm and read_only.
  We set them as 0.0 and then call new API function
  to force buffer pool flush and full checkpoint.
  After it has finished, we set them as 99.9.

fil0crypt.cc
fil_crypt_start_encrypting_space()
fil_crypt_thread()
  Acquire backup lock and release it afte we have done

fts0opt.cc
fts_optimize_sync_table()
fts_optimize_callback()
  Acquire backup lock and release it afte we have done

ha_innodb.cc
  Remove all WITH_INNODB_DISALLOW_WRITES code
wsrep_force_checkpoint()
  New API function to flush dirty pages from buffer pool and
  force full checkpoint.

trx_purge,cc
trx_purge()
  Acquire and release global MDL_BACKUP_DML lock

Author: Sergey Petrunia
Author Date: 2021-12-09 17:13:35 UTC

Code cleanup: don't call subquery_types_allow_materialization() on prepare

For subqueries that are processed as semi-joins.

Author: Nayuta Yanagisawa
Author Date: 2022-01-24 06:01:32 UTC

MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default tmp SE

Setting Spider as the default tmp storage engine results in the
server crash at its shutdown.

The crash is due to the lack of NULL checking in intern_plugin_lock().

Author: Nayuta Yanagisawa
Author Date: 2022-01-24 06:01:32 UTC

MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default tmp SE

Setting Spider as the default tmp storage engine results in the
server crash at its shutdown.

Spider cannot maintain any physical data, and thus setting it as
the default tmp storage engine is non-sense. We simply prohibit this
configuration by setting HTON_TEMPORARY_NOT_SUPPORTED to Spider.

Author: Julius Goryavsky
Author Date: 2022-02-22 02:53:10 UTC

MDEV-27602: New features for mtr

Author: Julius Goryavsky
Author Date: 2022-02-19 17:12:20 UTC

Proposed mtr changes

Author: Vladislav Vaintroub
Author Date: 2022-02-18 17:10:34 UTC

For Nikita : illustrate of queuing APC, when connection is sleeping

Author: Vladislav Vaintroub
Author Date: 2022-02-20 19:48:31 UTC

MDEV-27901 Windows : expensive system calls used to calculate file system block size

The result is not used anywhere but in the output of Innodb information
schema, but this can take as much as 7%CPU (only) on a benchmark.

Fix to move fs blocksize calculate to where it is used.

Author: Marko Mäkelä
Author Date: 2022-02-20 14:51:23 UTC

WIP: Implement mcs_lock

Author: midenok
Author Date: 2022-02-19 13:34:08 UTC

TABLE::referenced_by_foreign_table()

Author: Julius Goryavsky
Author Date: 2022-02-19 00:50:11 UTC

Experimental

Author: Vlad Lesin
Author Date: 2021-11-30 15:22:39 UTC

MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

Backported from 10.5 20e9e804c131c6522bc7c469e4863e8d1eaa3ee0 and
5948d7602ec7f61937c368dcb134e6ec226a2990.

sel_restore_position_for_mysql() moves forward persistent cursor
position after btr_pcur_restore_position() call if cursor relative position
is BTR_PCUR_ON and the cursor points to the record with NOT the same field
values as in a stored record(and some other not important for this case
conditions).

It was done because btr_pcur_restore_position() sets
page_cur_mode_t mode to PAGE_CUR_LE for cursor->rel_pos == BTR_PCUR_ON
before opening cursor. So we are searching for the record less or equal
to stored one. And if the found record is not equal to stored one, then
it is less and we need to move cursor forward.

But there can be a situation when the stored record was purged, but the
new one with the same key but different value was inserted while
row_search_mvcc() was suspended. In this case, when the thread is
awaken, it will invoke sel_restore_position_for_mysql(), which, in turns,
invoke btr_pcur_restore_position(), which will return false because found
record don't match stored record, and
sel_restore_position_for_mysql() will move forward cursor position.

The above can lead to the case when awaken row_search_mvcc() do not see
records inserted by other transactions while it slept. The mtr test case
shows the example how it can be.

The fix is to return special value from persistent cursor restoring
function which would notify its caller that uniq fields of restored
record and stored record are the same, and in this case
sel_restore_position_for_mysql() don't move cursor forward.

Delete-marked records are correctly processed in row_search_mvcc().
Non-unique secondary indexes are "uniquified" by adding the PK, the
index->n_uniq should then be index->n_fields. So there is no need in
additional checks in the fix.

If transaction's readview can't see the changes made in secondary index
record, it requests clustered index record in row_search_mvcc() to check
its transaction id and get the correspondent record version. After this
row_search_mvcc() commits mtr to preserve clustered index latching
order, and starts mtr. Between those mtr commit and start secondary
index pages are unlatched, and purge has the ability to remove stored in
the cursor record, what causes rows duplication in result set for
non-locking reads, as cursor position is restored to the previously
visited record.

To solve this the changes are just switched off for non-locking reads,
it's quite simple solution, besides the changes don't make sense for
non-locking reads.

The more complex and effective from performance perspective solution is
to create mtr savepoint before clustered record requesting and rolling
back to that savepoint after that. See MDEV-27557.

One more solution is to have per-record transaction id for secondary
indexes. See MDEV-17598.

If any of those is implemented, just remove select_lock_type argument in
sel_restore_position_for_mysql().

Author: Vladislav Vaintroub
Author Date: 2022-02-17 10:56:14 UTC

Merge remote-tracking branch 'origin/10.6' into 10.7

Author: Dmitry Shulga
Author Date: 2022-02-15 05:20:43 UTC

MDEV-5816: Stored programs: validation of stored program statements

Added storing of sql expression for instruction being parsed inside
the classes derived from the class sp_lex_instr.

Stored sql expression is returned by the abstract method
  sp_lex_instr::get_expr_query
redefined in the derived classes.

The virtual method sp_lex_instr::get_query() has beens added to return
a parseable string for a statement that corresponds to the given
instruction.

Author: Nayuta Yanagisawa
Author Date: 2022-02-16 13:21:25 UTC

MDEV-27260 Implement and evaluate select handlers for Spider

Do nothing implementation

Author: Alexey Botchkov
Author Date: 2022-02-15 07:35:18 UTC

MDEV-27831 Let the SQL SERVICE user set the current user name.

The 'user' argument added to the mysql_real_connect_local.

Author: Julius Goryavsky
Author Date: 2022-02-11 23:59:15 UTC

MDEV-27737: FreeBSD compatibility changes for SST scripts

This commit fixes problems due to bugs and quirks in bsdtar
(the FreeBSD version of tar). Separate tests are not required,
because without these fixes, many other tests fail when tested
in the FreeBSD environment.

Also, the grep patterns for reading utility version numbers
has been made more robust. The notation of some options of
the "cut" utility has been changed.

Author: Vlad Lesin
Author Date: 2021-12-01 07:11:28 UTC

MDEV-27148 running multiple instances of mtr with different var dirrectories can cause .reject and generated from .rdiff .result files overwriting

The code which writes .reject file to the location of .result file is
removed. Also tmp dirrectory is located only in tests var directory.

Author: Sergey Petrunia
Author Date: 2022-02-10 11:23:20 UTC

MDEV-25636: Bug report: abortion in sql/sql_parse.cc:6294

The asserion failure was caused by this query

  select /*id=1*/ from t1
  where
   col= ( select /*id=2*/ from ... where corr_cond1
          union
          select /*id=4*/ from ... where corr_cond2)

Here,
- select with id=2 was correlated due to corr_cond1.
- select with id=4 was initially correlated due to corr_cond2, but then
  the optimizer optimized away the correlation, making the select with id=4
  uncorrelated.

However, since select with id=2 remained correlated, the execution had to
re-compute the whole UNION. When it tried to execute select with id=4, it
hit an assertion (join buffer already free'd).

This is because select with id=4 has freed its execution structures after
it has been executed once. The select is uncorrelated, so it did not expect
it would need to be executed for the second time.

Fixed this by adding this logic in
st_select_lex::optimize_unflattened_subqueries():

  If a member of a UNION is correlated, mark all its members as
  correlated, so that they are prepared to be executed multiple times.

Author: Alexey Botchkov
Author Date: 2022-02-10 06:56:51 UTC

ha_partition::open_part2() implemented.

Author: Teemu Ollakka
Author Date: 2022-02-03 12:06:25 UTC

MDEV-27737 Wsrep SST scripts not working on FreeBSD

- Changed SST scripts to use /usr/bin/env bash instead of
  /bin/bash for better portability.
- Fixed use of mktemp on non-Linux platforms to produce
  temporary file instead of directory.

Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>

Author: Sergei Golubchik
Author Date: 2022-02-03 11:44:09 UTC

MDEV-27683 EXCHANGE PARTITION allows different index direction, but causes further errors

Author: Marko Mäkelä
Author Date: 2022-02-03 10:18:14 UTC

MDEV-27736 Allow seamless upgrade despite ROW_FORMAT=COMPRESSED

In commit 9bc874a594edbc0e124131d0ff30b44f5fade52d (MDEV-23497)
the configuration option innodb_read_only_compressed was introduced
to giver users advance notice of a plan to remove ROW_FORMAT=COMPRESSED
support for InnoDB.

Based on user feedback, this plan has been scrapped.
Even though ROW_FORMAT=COMPRESSED is a dead end and causes some
overhead for InnoDB data structures, we can live with that.

Now that we know that some users really want to keep using
ROW_FORMAT=COMPRESSED, the previous default value of the parameter
innodb_read_only_compressed=ON should be changed to OFF, to allow
smooth upgrades to 10.6 and later versions, without requiring users
to update any configuration file.

Author: Vladislav Vaintroub
Author Date: 2022-02-03 00:24:14 UTC

MDEV-27142 - fix Connect engine reliance on textmode stdio on Windows...

by removing a couple ifdef(_WIN32)

Author: Eugene Kosov
Author Date: 2022-02-01 11:40:35 UTC

tmp

Author: Andrei
Author Date: 2022-02-02 15:17:27 UTC

MDEV-11675. rpl_start_alter_ftwrl.test is refined

The test could fail sporadically because of not anticipated
race on slave between CREATE and ALTER queries.

Fixed to synchronize slave and master wrt CREATE.

Author: Julius Goryavsky
Author Date: 2022-02-02 11:52:40 UTC

MDEV-27524 addendum: simplification and reduction of the code

Author: Rucha Deodhar
Author Date: 2022-01-10 15:25:19 UTC

MDEV-27462 : SET STATEMENT allows variables that cannot be set per query
basis

Analysis: Some system variables can be set per query basis because flag NO_SET_STMT
is missing.
Fix: Added NO_SET_STMT flag. List of disallowed variables taken from
documentation:
autocommit, character_set_client, character_set_connection,
character_set_filesystem, collation_connection, default_master_connection,
debug_sync, interactive_timeout, gtid_domain_id, last_insert_id,
log_slow_filter, log_slow_rate_limit, log_slow_verbosity, long_query_time,
min_examined_row_limit, profiling, profiling_history_size, query_cache_type,
rand_seed1, rand_seed2, skip_replication, slow_query_log, sql_log_off,
tx_isolation, wait_timeout

Author: Nayuta Yanagisawa
Author Date: 2022-02-01 04:50:18 UTC

MDEV-27657 Spider: remove #ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS

Author: Nayuta Yanagisawa
Author Date: 2022-02-01 04:25:56 UTC

MDEV-27659 Spider: remove #ifdef HANDLER_HAS_DIRECT_UPDATE_ROWS_WITH_HS

Author: Oleg Smirnov
Author Date: 2021-12-24 14:27:03 UTC

MDEV-27021 Implement SHOW ANALYZE command

Author: Nikita Malyavin
Author Date: 2022-01-29 19:21:32 UTC

MDEV-25774 profile build speed of the server code base

using precompiled headers was tried here

Author: Anel Husakovic
Author Date: 2019-05-22 08:54:47 UTC

MDEV-14448: Ctrl-C should not exit the client

Reviewed by: <>

Author: Oleksandr "Sanja" Byelkin
Author Date: 2022-01-27 07:45:36 UTC

Merge branch '10.2' into bb-10.3-release

Author: Sujatha Sivakumar
Author Date: 2022-01-25 04:18:52 UTC

MDEV-7850 MariaDB doesn't show thread_id for ROW-based events in binlog

Add thread id to Gtid_log_event. FL_EXTRA_THREAD_ID flag is added to detect if
there is thread_id in GTID_EVENT or not

Author: Alexey Botchkov
Author Date: 2022-01-24 15:49:58 UTC

Fix for the problem created by SQL SERVICE for binlogging.

Author: Vlad Lesin
Author Date: 2022-01-24 14:05:47 UTC

Abort on rows count mismatch in InnoDB's CHECK TABLE.

This commit is only for debug, remove it after testing.

Author: Oleksandr "Sanja" Byelkin
Author Date: 2019-04-17 13:50:59 UTC

MDEV-17124: mariadb 10.1.34, views and prepared statements: ERROR 1615 (HY000): Prepared statement needs to be re-prepared

The problem is that if table definition cache (TDC) is full of real tables
which are in tables cache, view definition can not stay there so will be
removed by its own underlying tables.
In situation above old mechanism of detection matching definition in PS
and current version always require reprepare and so prevent executing
the PS.

One work around is to increase TDC, other - improve version check for
views/triggers (which is done here). Now in suspicious cases we check:
 - timestamp (ms) of the view to be sure that version really have changed;
 - time (ms) of creation of a trigger related to time (ms) of statement
   preparation.

Author: Rucha Deodhar
Author Date: 2022-01-22 11:21:21 UTC

MDEV-26843: Inconsistent behavior of ROW_NUMBER upon resignalling from
function

Analysis: m_current_row_for_warning is reset to 0 earlier so new value '0'
of the counter gets recorded for error condition.
Fix: reset m_current_row_for_warning after other error conditions.

Author: Sergey Petrunia
Author Date: 2022-01-21 16:47:24 UTC

MDEV-25447: MyRocks use "tmpdir" rather than using "rocksdb_tmpdir"

- Move mysql_tmpfile_path() from inside InnoDB onto the SQL layer.
- Make MyRocks use it, like its upstream does.

Author: Sergey Petrunia
Author Date: 2022-01-20 19:52:41 UTC

MDEV-27149: Add rocksdb_ignore_datadic_errors

Add a --rocksdb_ignore_datadic_errors plugin option for MyRocks.

The default is 0, and this means MyRocks will call abort() if it detects
a DDL mismatch.

Setting rocksdb_ignore_datadic_errors=1 makes MyRocks to try to ignore the
errors and allow to start the server for repairs.

Author: Dmitry Shulga
Author Date: 2022-01-20 15:05:15 UTC

MDEV-20516: Assertion `!lex->proc_list.first && !lex->result && !lex->param_list.elements' failed in mysql_create_view

Execution of the CREATE VIEW statement sent via binary protocol
where the flags of the COM_STMT_EXECUTE request a cursor to be opened
before running the statement results in an assert failure.

This assert fails since the data member thd->lex->result has not null
value pointing to an instance of the class Select_materialize.
The data member thd->lex->result is assigned a pointer to the class
Select_materialize in the function mysql_open_cursor() that invoked
in case the packet COM_STMT_EXECUTE requests a cursor to be opened.

After thd->lex->result is assigned a pointer to an instance of the
class Select_materialize the function mysql_create_view() is called
(indirectly via the function mysql_execute_statement()) and the assert
fails.

The assert
  DBUG_ASSERT(!lex->proc_list.first && !lex->result &&
              !lex->param_list.elements);

was added by the commit 591c06d4b771124cc2cf453fbf51d5d99a4ad95e.
Unfortunately , the condition
  !lex->result
was specified incorrect. It was supposed that the thd->lex->result
is set only by parser on handling the clauses SELECT ... INTO
but indeed it is also set inside mysql_open_cursor() and
that fact was missed by assert.

So, the fix for this issue is to just remove the condition
  !lex->result
from the failing assert.

Author: Alexey Botchkov
Author Date: 2021-10-05 08:56:11 UTC

MDEV-26768 Spider table crashes the server after the mysql_list_fields() client's call and produces weird result for SHOW FIELDS.

Suppress errors in ha_spider::info() called from mysqld_show_fields()

Author: Vladislav Vaintroub
Author Date: 2022-01-19 09:24:56 UTC

test

Author: Brandon Nesterenko
Author Date: 2022-01-18 14:38:29 UTC

tmp

Author: Marko Mäkelä
Author Date: 2022-01-17 07:17:08 UTC

MDEV-27519 2-argument CRC32 call upon Columnstore table returns a wrong value

Author: Eugene Kosov
Author Date: 2020-06-18 14:26:01 UTC

MDEV-22441 implement a generic way to change a value of a variable in a scope

Example:
{
  auto _= make_scope_value(var, tmp_value);
}

make_scope_value(): a function which returns RAII object which temporary
changes a value of a variable

detail::Scope_value: actual implementation of such RAII class.
It shouldn't be used directly! That's why it's inside a namespace detail.

Author: Georg Richter
Author Date: 2022-01-16 07:16:02 UTC

updated connector/c

Author: Sergey Petrunia
Author Date: 2022-01-13 12:53:44 UTC

MDEV-27382: OFFSET is ignored when combined with DISTINCT

A query in form

  SELECT DISTINCT expr_that_is_inferred_to_be_const LIMIT 0 OFFSET n

produces one row when it should produce none. The issue was in
JOIN_TAB::remove_duplicates() in the piece of logic that tried to
avoid duplicate removal for such cases but didn't account for possible
"LIMIT 0".

Fixed in two places:
- Make JOIN::optimize_inner be able to infer "Zero limit" for
   "LIMIT 0 OFFSET some_non_zero_value" (in addition to just "LIMIT 0")

- Make JOIN_TAB::remove_duplicates not apply its optimization for cases
  with non-zero OFFSET clause.

Author: Marko Mäkelä
Author Date: 2022-01-11 15:54:49 UTC

Test: Merge buf_pool.flush_order_mutex to log_sys.mutex

This will actually reduce throughput by increasing contention
on log_sys.mutex.

Author: Jan Lindström
Author Date: 2022-01-09 07:37:44 UTC

MDEV-25201 : Assertion `thd->wsrep_trx_meta.gtid.seqno == (-1)' failed in int wsrep_to_isolation_begin(THD*, const char*, const char*, const TABLE_LIST*, Alter_info*)

Test case does not assert anymore but works incorrectly. We should
not replicate PREPARE using TOI.

Author: Jan Lindström
Author Date: 2022-01-09 07:37:44 UTC

MDEV-25201 : Assertion `thd->wsrep_trx_meta.gtid.seqno == (-1)' failed in int wsrep_to_isolation_begin(THD*, const char*, const char*, const TABLE_LIST*, Alter_info*)

Test case does not assert anymore but works incorrectly. We should
not replicate PREPARE using TOI.

Author: Elena Stepanova
Author Date: 2022-01-08 23:39:57 UTC

Test commit, not to be merged anywhere

Author: Marko Mäkelä
Author Date: 2022-01-05 13:08:02 UTC

MDEV-27208: Extend CRC32() and implement CRC32C()

We used to define a native unary function crc32() that computes the CRC-32
of a string using the ISO 3309 polynomial that is being used by zlib
and many others.

Often, CRC is computed in pieces. To faciliate this, we introduce a
2-ary variant of the function that inputs a previous CRC as the first
argument: CRC32('MariaDB')=CRC32(CRC32('Maria'),'DB').

InnoDB and MyRocks use a different polynomial, which was implemented
in SSE4.2 instructions that were introduced in the
Intel Nehalem microarchitecture. This is commonly called CRC-32C
(Castagnoli).

We introduce a native function that uses the Castagnoli polynomial:
CRC32C('MariaDB')=CRC32C(CRC32C('Maria'),'DB'). This allows
SELECT...INTO DUMPFILE to be used for the creation of files with
valid checksums, such as a logically empty InnoDB redo log file
ib_logfile0 corresponding to a particular log sequence number.

Author: midenok
Author Date: 2021-12-29 13:13:14 UTC

alias_name_used fix

Author: Julius Goryavsky
Author Date: 2021-12-26 15:35:34 UTC

Duplicate line deleted

Author: Sergey Petrunia
Author Date: 2021-12-20 20:51:55 UTC

Reverse-ordered indexes: remove SEL_ARG::is_ascending

Get the information from the array of KEY_PART structures that describes
the [pseudo-]index that is being analyzed.

Author: Sergei Krivonos
Author Date: 2021-12-01 01:11:14 UTC

MDEV-27036: re-enable my_json_writer-t unit test

Author: Sergei Krivonos
Author Date: 2021-12-19 21:26:26 UTC

MCOL-4944: fixed systemd/sd-daemon.h detection: may not use HAVE_SYSTEMD_SD_DAEMON_H name

Author: Daniel Black
Author Date: 2021-12-16 09:11:48 UTC

MDEV-27289: mtr test for WITH_SERVER_EMBEDDED=ON reenable

mtr is checking the wrong path for the embedded executable
on out of tree builds.

The is_embedded.inc tests are also checking the version rather
than the MTR MYSQL_EMBEDDED environment variable.

As a result, a few tests are out of date in the result recordings.

Author: Daniel Black
Author Date: 2021-12-16 07:25:22 UTC

MDEV-27107: mariadb-upgrade exclusive locks on info_file

Work in progress. File modes aren't totally right
a second instance aborts with

client/mysql_upgrade: Can't create/write to file '/tmp/build-mariadb-server-10.2-datadir/mysql_upgrade_info' (Errcode: 17 "File exists")

Author: Alexander Barkov
Author Date: 2021-11-23 07:35:53 UTC

MDEV-27265 Improve contraction performance in UCA collations

Adding a hash table for contractions.

The old code iterated through all items in MY_CONTRACTIONS,
and was much slower, especially for those contractions
in the end of the list.

Author: Daniel Black
Author Date: 2021-12-02 03:30:26 UTC

MDEV-25342: autosize innodb_buffer_pool_chunk_size

The previous default innodb_buffer_pool_chunk_size of 128M
made sense when the innodb buffer pool size was a few GB.

When the pool size is 128GB this means the chunk size is 0.1%
of this. Fine tuning the buffer pool size on such a fine
increment doesn't make practical sense. Also on extremely
large buffer pool systems, initializing on the default 128M can
also take a considerable amount of time.

When large pages are enabled, the chunk size has to be a multiple
of an available large page size or memory allocation without
use can occur.

Previously the default 0 was documented as disabling resizing.
With srv_buf_pool_chunk_unit > 0 assertions in the code and the
minimium value set, I doubt this was ever the case.

As such the autosizing (based on default 0) takes place as follows:
* a 64th of the innodb_buffer_pool_size
* if large pages, this is rounded down the the nearest multiple
  of the large page size.
* If less than 1MB, set to 1MB.

This does mean the new default innodb_buffer_pool_chunk size is
2MB, derived form the above formular with 128MB as the buffer pool
size.

The innodb_buffer_pool_chunk_size is changed to a size_t for
better compatiblity with the memory allocations which use size_t.
The previous upper limit is changed to the maxium of a size_t. The
maximium value used is the buffer pool size anyway.

Getting this default value of the chunk size to a more practical
size facilitates further development of more automated resizing
without significant overhead or memory fragmentation.

innodb_buffer_pool_resize test adjusted based on 1M default
chunk size thanks Wlad.

Author: Nikita Malyavin
Author Date: 2021-12-15 15:52:03 UTC

fix savepoints in myisam

Author: Sergei Krivonos
Author Date: 2021-12-14 14:16:05 UTC

MDEV-27238: detecting Json_writer_struct nesting issues

Author: Jan Lindström
Author Date: 2021-12-15 11:16:29 UTC

Disable following tests from galera_3nodes suite

* galera_pc_bootstrap
* galera_ipv6_mariabackup
* galera_ipv6_mariabackup_section
* galera_ipv6_rsync
* galera_ipv6_rsync_section
* galera_ssl_reload
* galera_toi_vote
* galera_wsrep_schema_init

because MTR sporadaically fails: Failed to start mysqld or mysql_shutdown failed

Author: Daniel Black
Author Date: 2021-03-30 06:32:36 UTC

MDEV-25282 Auto-shutdown on idle when socket-activated

Adds max_idle_execution system variable that corresponds
to the time in seconds under which the mariadbd executable will
run in an idle state (since last query) with no connections.

Under systemd socket activation this variable will get a 10 minute
default value, otherwise 0, representing no timeout. This will
enable a service to be activated on connection and fall back to
a shutdown state after 10 minutes of no queries. The systemd
socket activation can restart the service on the next connection
transparently.

The maxmium max_idle_execution is given by the following
factors given its dependance on the OS system calls.

Windows WaitForMultipleObjects takes a DWORD (unsigned)
measure in milliseconds. Poll takes a signed int milliseconds,
and negative values are treated as infinite so we can't overflow.
Select, the fall back if poll isn't there, takes a seconds value
in a timeval.time_t structure. As such the interface maximiums are:
Windows: UINT_MAX / 1000
Poll: INT_MAX / 1000
Select: UINT_MAX (or higher)

As even the smallest value here, INT_MAX(32) / 1000 is ~25 days,
sufficient for the typical use case, its used in all environment for
simplicity of documentation and test cases.

A (non-exposed) global variable of server_last_activity is updated
on accepted connections (when max_idle_execution !=0)
and when the connection count (standard or extra) is down
to <= 1 to keep the number of updates on a single variable
low.

When the main accept loop times out on the max_idle_execution
seconds, and then the server_last_activity is checked along
with if current connection count (standard + extra) is 0
(in case a recently started connection hasn't finished
a query).

To make this neater, in non-Windows main accept loop moved
code to handle_new_socket_connection that encompases accepting
a connection and the timeout mechanism has been separated too.

Changed when looping though possible connections, loop until
the end of the connection list and hereby assume two connection can
occur on the same poll/select call and both will be accepted.

The interactive_timeout and wait_timeout inherit the
max_idle_execution time (if set) compared to their previous defaults.

Thanks Sergei for the review.

Author: Jan Lindström
Author Date: 2021-12-15 10:43:35 UTC

test

Author: Rucha Deodhar
Author Date: 2021-09-01 13:39:01 UTC

MDEV-26238: Remove inconsistent behaviour of --default-* options
in my_print_defaults

Analysis: --defaults* option is recognized anywhere in the commandline
instead of only at the beginning because handle_options() recognizes
options in any order.
Fix: use get_defaults_options() which recognizes --defaults* options only at
the beginning. After this is done, we only want to recognize other options
given in any order which can be done using handle_options(). So only skip
--defaults* options and pass rest of them to handle_options().
Also, removed -e, -g and -c because only my_print_defaults supports them.

Author: Daniel Black
Author Date: 2021-12-03 01:12:14 UTC

MDEV-27158: humanize the bytes in innodb info/error messages

Log messages like total size = 17179869184, chunk size = 134217728
get hard to read. If we normalize it down to IEC units is easier.

Idea thanks to Axel Schwenke.

Review thanks to Eugene Kosov and Marko Mäkelä

$ mariadblocal --innodb-buffer-pool-size=30G --innodb-log-file-size=128M
Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.7-datadir' ...
2021-12-09 9:54:04 0 [Note] /home/dan/repos/build-mariadb-server-10.7/sql/mysqld (server 10.7.2-MariaDB) starting as process 250473 ...
2021-12-09 9:54:04 0 [Note] InnoDB: The first data file './ibdata1' did not exist. A new tablespace will be created!
2021-12-09 9:54:04 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-12-09 9:54:04 0 [Note] InnoDB: Number of transaction pools: 1
2021-12-09 9:54:04 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-12-09 9:54:04 0 [Note] InnoDB: Using liburing
2021-12-09 9:54:04 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 128.000MiB
2021-12-09 9:54:04 0 [Note] InnoDB: Completed initialization of buffer pool
2021-12-09 9:54:04 0 [Note] InnoDB: Setting O_DIRECT on file ./ibdata1 failed
2021-12-09 9:54:04 0 [Note] InnoDB: Setting file './ibdata1' size to 12.000MiB. Physically writing the file full; Please wait ...
2021-12-09 9:54:04 0 [Note] InnoDB: File './ibdata1' size is now 12.000MiB.
2021-12-09 9:54:04 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 96.000MiB
2021-12-09 9:54:04 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-12-09 9:54:04 0 [Note] InnoDB: New log file created, LSN=10317
2021-12-09 9:54:04 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2021-12-09 9:54:04 0 [Note] InnoDB: Doublewrite buffer created
2021-12-09 9:54:04 0 [Note] InnoDB: 128 rollback segments are active.
2021-12-09 9:54:04 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-12-09 9:54:04 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2021-12-09 9:54:04 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2021-12-09 9:54:04 0 [Note] InnoDB: 10.7.2 started; log sequence number 0; transaction id 3
OK
2021-12-09 9:54:04 0 [Note] sql/mysqld (server 10.7.2-MariaDB) starting as process 250501 ...
2021-12-09 9:54:04 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-12-09 9:54:04 0 [Note] InnoDB: Number of transaction pools: 1
2021-12-09 9:54:04 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-12-09 9:54:04 0 [Note] InnoDB: Using liburing
2021-12-09 9:54:04 0 [Note] InnoDB: Initializing buffer pool, total size = 30.000GiB, chunk size = 128.000MiB
2021-12-09 9:54:04 0 [Note] InnoDB: Completed initialization of buffer pool
2021-12-09 9:54:04 0 [Note] InnoDB: Setting O_DIRECT on file ./ibdata1 failed
2021-12-09 9:54:04 0 [Note] InnoDB: Resizing redo log from 96.000MiB to 128.000MiB; LSN=41361
2021-12-09 9:54:04 0 [Note] InnoDB: Starting to delete and rewrite log file.
2021-12-09 9:54:04 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 128.000MiB
2021-12-09 9:54:04 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-12-09 9:54:04 0 [Note] InnoDB: New log file created, LSN=41361
2021-12-09 9:54:04 0 [Note] InnoDB: 128 rollback segments are active.
2021-12-09 9:54:04 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-12-09 9:54:04 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2021-12-09 9:54:04 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2021-12-09 9:54:04 0 [Note] InnoDB: 10.7.2 started; log sequence number 41349; transaction id 14
2021-12-09 9:54:04 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/build-mariadb-server-10.7-datadir/ib_buffer_pool
2021-12-09 9:54:04 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-12-09 9:54:04 0 [Note] InnoDB: Buffer pool(s) load completed at 211209 9:54:04
2021-12-09 9:54:04 0 [Note] sql/mysqld: ready for connections.
Version: '10.7.2-MariaDB' socket: '/tmp/build-mariadb-server-10.7.sock' port: 0 Source distribution
2021-12-09 9:56:57 0 [Note] sql/mysqld (initiated by: unknown): Normal shutdown
2021-12-09 9:56:57 0 [Note] InnoDB: FTS optimize thread exiting.
2021-12-09 9:56:57 0 [Note] InnoDB: Starting shutdown...
2021-12-09 9:56:57 0 [Note] InnoDB: Dumping buffer pool(s) to /tmp/build-mariadb-server-10.7-datadir/ib_buffer_pool
2021-12-09 9:56:57 0 [Note] InnoDB: Buffer pool(s) dump completed at 211209 9:56:57
2021-12-09 9:56:57 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2021-12-09 9:56:57 0 [Note] InnoDB: Shutdown completed; log sequence number 42602; transaction id 15
2021-12-09 9:56:57 0 [Note] sql/mysqld: Shutdown complete

Author: Julius Goryavsky
Author Date: 2021-12-14 02:20:37 UTC

Additional changes

Author: Sergei Krivonos
Author Date: 2021-12-14 01:21:46 UTC

Update explain_json result

Author: Nikita Malyavin
Author Date: 2021-12-13 12:47:27 UTC

add notembedded

Author: Sergei Golubchik
Author Date: 2021-11-24 15:50:21 UTC

MDEV-26938 Support descending indexes internally in InnoDB (server part)

* preserve DESC index property in the parser
* store it in the frm (only for HA_KEY_ALG_BTREE)
* read it from the frm
* show it in SHOW CREATE
* skip DESC indexes in opt_range.cc and opt_sum.cc
* ORDER BY test

Author: Sergei Krivonos
Author Date: 2021-12-09 09:05:14 UTC

MDEV-27206: [ERROR] Duplicated key: cause, Assertion `is_uniq_key' failed with optimizer trace

Author: Nayuta Yanagisawa
Author Date: 2021-11-25 08:03:02 UTC

MDEV-27106 Spider: specify connection to data node by engine-defined attributes

We introduce engine-defined attributes to specify remote data nodes.

The engine attributes do not cover all the existing DSN parameters
because most of them need not be specified at the table level.
We introduce the following three attributes: REMOTE_SERVER,
REMOTE_DATABASE, REMOTE_TABLE.

One cannot specify both DSN parameter, in COMMENT or CONNECT, and
engine-defined attribute that are for the same SPIDER_SHARE attribute.
For example, Spider returns an error if both COMMENT='table "t1"'
and REMOTE_TABLE="t2" are specified for a single Spider table or
a single partition in a Spider table.