Commit graph

1382 commits

Author SHA1 Message Date
Vladislav Vaintroub
934db2efb6 MDEV-32875 SERVER_STATUS_AUTOCOMMIT set after connecting, if autocommit=0
After successful connection, server always sets SERVER_STATUS_AUTOCOMMIT
in server_status in the OK packet. This is wrong, if global variable
autocommit=0.

Fixed THD::init(), added mysql_client_test test.

Thanks to Diego Dupin for the providing the patch.

Signed-off-by: Vladislav Vaintroub <vvaintroub@gmail.com>
2023-11-24 19:27:16 +01:00
Oleksandr Byelkin
0427c4739e MariaDB 11.1.3 release
-----BEGIN PGP SIGNATURE-----
 
 iQIzBAABCgAdFiEEF39AEP5WyjM2MAMF8WVvJMdM0dgFAmVSLiUACgkQ8WVvJMdM
 0dhj4A/7B2GIx75Mv4IcExE2s4bfR7sOKZzvjWbMHysovMHhsHAV5fHN7dRQojyV
 HxmSY8lxykm/LMoJ8RASmrojRZsgvkJ84z+fLK7is327Vms7fW7ZWc3eqotIgs7I
 m9Dz3+wiexvl6NKeHnafTZtkJOe8MEqZEGPV1e8V4I3SAJQWyQLnRr4si/VmjAMi
 miKuieTuKoZUYSkdNLwicEFHXysgg6/U8367sgMsJe9V3HYSD3pVQJ/nboTL5uZL
 vTbmEPS1pICKPvPu75DdedSdxSASMyXis9/IWtk13NqPPzX16uHtjkhffAuBT3+k
 CUgRggTYAuoF3MjvyspIS3pdC/73PBb1O+w/9vlHPiwSXVn3d48Ay55uvFgM/pVB
 UKLorw+As0oH2N1HWUp/d4Rbvrnjdq5OgzhmMTrWDAtYyrNU9Jw5S1CAp+G/s2dD
 5j+FUPBBnHo5UfxI+EVTqUggm56R+vJTx4H3q82n05bdJTJYNJ+nixvsYuf7hS3f
 oEqJAUizgGI3h6FGPD9bN0HSYGblEeNgAYv1YogfVX/Eq10RriWic9PtxxOxgOmE
 n+UhdH4YTTyaTv0jssWTJVmVNzjjXMvI4aB8A1FkXeIz2iohSziSkJzaBuzNq2QY
 kKHr8XqiyNnckcoRxfoxNPtrWcmiykpHOBFnuyMRWoXPKcr7idc=
 =ShdC
 -----END PGP SIGNATURE-----

Merge tag '11.1' into 11.2

MariaDB 11.1.3 release
2023-11-14 18:28:37 +01:00
Oleksandr Byelkin
48af85db21 Merge branch '10.11' into 11.0 2023-11-08 17:09:44 +01:00
Oleksandr Byelkin
fecd78b837 Merge branch '10.10' into 10.11 2023-11-08 16:46:47 +01:00
Oleksandr Byelkin
04d9a46c41 Merge branch '10.6' into 10.10 2023-11-08 16:23:30 +01:00
Oleksandr Byelkin
b83c379420 Merge branch '10.5' into 10.6 2023-11-08 15:57:05 +01:00
Oleksandr Byelkin
6cfd2ba397 Merge branch '10.4' into 10.5 2023-11-08 12:59:00 +01:00
Yuchen Pei
d0f8dfbcf0
Merge branch '11.1' into 11.2 2023-10-27 18:11:56 +11:00
Marko Mäkelä
be24e75229 Merge 10.11 into 11.0 2023-10-19 08:12:16 +03:00
Marko Mäkelä
2ecc0443ec Merge 10.10 into 10.11 2023-10-17 16:04:21 +03:00
Marko Mäkelä
d5e15424d8 Merge 10.6 into 10.10
The MDEV-29693 conflict resolution is from Monty, as well as is
a bug fix where ANALYZE TABLE wrongly built histograms for
single-column PRIMARY KEY.
Also includes a fix for safe_malloc error reporting.

Other things:
- Copied main.log_slow from 10.4 to avoid mtr issue

Disabled test:
- spider/bugfix.mdev_27239 because we started to get
  +Error	1429 Unable to connect to foreign data source: localhost
  -Error	1158 Got an error reading communication packets
- main.delayed
  - Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
    This part is disabled for now as it fails randomly with different
    warnings/errors (no corruption).
2023-10-14 13:36:11 +03:00
Vladislav Vaintroub
87d1ab9ad9 MDEV-28561 Assertion failed: !pfs->m_idle || (state == PSI_SOCKET_STATE_ACTIVE)
The error was specific to threadpool/compressed protocol.
set_thd_idle() set socket state to idle twice, causing assert failure.

This happens if unread compressed data on connection,after query was
finished. On a protocol level, this means a single compression packet
contains multiple command packets.
2023-09-29 09:30:49 +02:00
Marko Mäkelä
0dd25f28f7 Merge 10.5 into 10.6 2023-09-11 14:46:39 +03:00
Marko Mäkelä
f8f7d9de2c Merge 10.4 into 10.5 2023-09-11 11:29:31 +03:00
Alexander Barkov
5544ea2eda MDEV-32130 Port MySQL test on protocol bug to MariaDB
Port the test case from MySQL to MariaDB:

MySQL fix Bug#33813951, Change-Id: I2448e3f2f36925fe70d882ae5681a6234f0d5a98.

Function test_simple_temporal() from MySQL ported from C++ to pure C.

This includes one change:
-  DIE_UNLESS(field->type == MYSQL_TYPE_DATETIME);
+  DIE_UNLESS(field->type == MYSQL_TYPE_TIMESTAMP);

The bound param of SELECT ? is TIMESTAMP in this code.
MySQL returns it back as DATETIME. MariaDB preserves TIMESTAMP.

Code packaged for commit by Daniel Black.
2023-09-08 18:20:57 +10:00
Sergei Golubchik
18ddde4826 Merge branch '11.1' into 11.2 2023-08-18 00:59:16 +02:00
Nikita Malyavin
ab4bfad206 MDEV-16329 [5/5] ALTER ONLINE TABLE
* Log rows in online_alter_binlog.
* Table online data is replicated within dedicated binlog file
* Cached data is written on commit.
* Versioning is fully supported.
* Works both wit and without binlog enabled.

* For now savepoints setup is forbidden while ONLINE ALTER goes on.
  Extra support is required. We can simply log the SAVEPOINT query events
  and replicate them together with row events. But it's not implemented
  for now.

* Cache flipping:

  We want to care for the possible bottleneck in the online alter binlog
  reading/writing in advance.

  IO_CACHE does not provide anything better that sequential access,
  besides, only a single write is mutex-protected, which is not suitable,
  since we should write a transaction atomically.

  To solve this, a special layer on top Event_log is implemented.
  There are two IO_CACHE files underneath: one for reading, and one for
  writing.

  Once the read cache is empty, an exclusive lock is acquired (we can wait
  for a currently active transaction finish writing), and flip() is emitted,
  i.e. the write cache is reopened for read, and the read cache is emptied,
  and reopened for writing.

  This reminds a buffer flip that happens in accelerated graphics
  (DirectX/OpenGL/etc).

  Cache_flip_event_log is considered non-blocking for a single reader and a
  single writer in this sense, with the only lock held by reader during flip.

  An alternative approach by implementing a fair concurrent circular buffer
  is described in MDEV-24676.

* Cache managers:
  We have two cache sinks: statement and transactional.
  It is important that the changes are first cached per-statement and
  per-transaction.
  If a statement fails, then only statement data is rolled back. The
  transaction moves along, however.

  Turns out, there's no guarantee that TABLE well persist in
  thd->open_tables to the transaction commit moment.
  If an error occurs, tables from statement are purged.
  Therefore, we can't store te caches in TABLE. Ideally, it should be
  handlerton, but we cut the corner and store it in THD in a list.
2023-08-15 10:16:11 +02:00
Oleksandr Byelkin
51f9d62005 Merge branch '10.11' into 11.0 2023-08-09 07:53:48 +02:00
Oleksandr Byelkin
036df5f970 Merge branch '10.10' into 10.11 2023-08-08 14:57:31 +02:00
Oleksandr Byelkin
ced243a099 Merge branch '10.9' into 10.10 2023-08-05 20:34:09 +02:00
Oleksandr Byelkin
34a8e78581 Merge branch '10.6' into 10.9 2023-08-04 08:01:06 +02:00
Oleksandr Byelkin
6bf8483cac Merge branch '10.5' into 10.6 2023-08-01 15:08:52 +02:00
Oleksandr Byelkin
4235c133ae Merge branch '10.4' into 10.5 2023-07-31 10:14:46 +02:00
Marko Mäkelä
f182de2ec8 MDEV-30159 fixup: Plug a memory leak in the test 2023-07-31 09:28:28 +03:00
Oleksandr Byelkin
7564be1352 Merge branch '10.4' into 10.5 2023-07-26 16:02:57 +02:00
Oleksandr Byelkin
620aeb44db MDEV-30159: Client can crash the server with a mysql_list_fields("view") call
Do not get value of expensive constants.
2023-07-20 13:10:02 +02:00
Oleksandr Byelkin
f52954ef42 Merge commit '10.4' into 10.5 2023-07-20 11:54:52 +02:00
Robin Newhouse
a63c558b20 Implement mysql_upgrade upgrade testing in CI
Performs an upgrade of mariadb from an earlier version to the rpms built
in CI. Then checks whether log contains evidence of upgrade in the form
of "Needs upgrade" or "Table rebuild required". Designed to check minor
version upgrades which should not trigger rebuilds.

The test is written in bash script so it can be executed from other CI
systems.

$ test_upgrade.sh source_version target_version
$ test_upgrade.sh source_version # defaults to rpm/
$ test_upgrade.sh source_version --rpm-dir <directory>

Binaries must be created with performance schema enabled, or
mysql_upgrade complains about missing tables.

Upgrade testing is parallelized with a "matrix" of source versions.
Others can be introduced later.

This was partially designed to catch issues like that seen in
https://jira.mariadb.org/browse/MDEV-28727 where a minor version upgrade
(e.g. 10.4.8 -> 10.4.26) triggered a system table rebuild.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer
Amazon Web Services, Inc.
2023-07-05 10:42:32 +01:00
Sergei Golubchik
d32fc5b8e0 MDEV-31461 mariadb SIGSEGV when built with -DCLIENT_PLUGIN_DIALOG=STATIC 2023-06-19 12:12:21 +02:00
Robin Newhouse
f4ce1e487e All-green GitLab CI in 10.4 branch
Note to mergers: Do not merge this commit to 10.5+. An additional PR
will be created for the 10.5 branch which is compatible with later
branches.

Include cppcheck and FlawFinder for SAST scanning.

From 10.6, cherry-picked 12bf5c46 (Remove unused French translations in
Connect engine) and c6072ed9 (Ensure that source files contain only
valid UTF8 encodings). Necessary for FlawFinder to execute and useful
anyway.

Removing MSAN build and test as it was not introduced until 10.5 and
does not successfully build.

Remove failing upgrade test since Fedora installs MariaDB 10.5 and the
10.5->10.4 upgrade rightfully complains

Add to skiplist failing test: main.func_math (MDEV-20966)

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer
Amazon Web Services, Inc.
2023-05-19 13:21:34 +01:00
Robin Newhouse
9c287c0a90 All-green GitLab CI in 11.0 branch
Include cppcheck and FlawFinder for SAST scanning.
Ignorelists are present for both, so only new problems will trigger a CI
failure.

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer
Amazon Web Services, Inc.
2023-03-30 14:55:24 +03:00
Monty
15e889c300 MDEV-30699: Updated prev_record_reads() to be more exact
The old code in prev_record_reads() did give wrong estimates when a
join_buffer was used or if the table was depending on more than one
other tables. When join_cache is used, it will cause a re-order of row
combinations, which causes more calls to the engine for tables that
are depending on tables before the join_cached one.

The new prev_records_read() code provides more exact estimates and
should never give a 'too low estimate', assuming that the data to the
function is correct

The definition of prev_record_read() is also updated.
The new definition is:
  "Estimate the number of engine ha_index_read_calls for EQ_REF tables
  when taking into account the one-row-cache in join_read_always_key()"

The cost of using prev_record_reads() value is changed. The value is
now used similar as before to calculate the cost of the storage engine
calls. However the cost of the WHERE cost is changed to take into
account the total number of row combinations as the WHERE has to be
checked even if the one-row-cache is used. This makes the cost
slightly higher than before (for the same prev_record_reads() value).

Other things:
- Cached return value of prev_record_read() in best_access_path() to
  avoid some function calls.
- Fixed bug where position[].use_join_buffer was set in
  best_acess_path() when join buffer was not used. This confused the
  semi join optimizer to try to reoptimize plans that did not need to be
  reoptimized.
  The effect of the bug fix is that we avoid doing some re-optimziations
  with semi-joins when join_buffer is not used. In these cases the value
  shown for the 'Filtering' column in EXPLAIN EXTENDED may change.
- Added 'prev_record.cc' that was used to verify the logic in
  prev_record_reads().

Changes in test suite:
- EQ_REF tables are moved up to be earlier. This is because either the
  higher WHERE cost when EQ_REF is used with more row combination or
  change of cost when using join_cache.
- Filtered has changed (to the better) for some cases using semi-joins
  subselect_sj.test subselect_sj_jcl6.test
2023-02-21 15:36:39 +03:00
Monty
bd2cebb8b1 Fixed check_costs.pl to always create table if table does not exists
This allows one to always use --skip-create-table for repeated runs.
2023-02-15 15:50:21 +03:00
Monty
356a860155 Changed 'check_costs' so that --init-query can be used to override setup_engine() 2023-02-10 12:58:50 +02:00
Monty
b66cdbd1ea Changing all cost calculation to be given in milliseconds
This makes it easier to compare different costs and also allows
the optimizer to optimizer different storage engines more reliably.

- Added tests/check_costs.pl, a tool to verify optimizer cost calculations.
  - Most engine costs has been found with this program. All steps to
    calculate the new costs are documented in Docs/optimizer_costs.txt

- User optimizer_cost variables are given in microseconds (as individual
  costs can be very small). Internally they are stored in ms.
- Changed DISK_READ_COST (was DISK_SEEK_BASE_COST) from a hard disk cost
  (9 ms) to common SSD cost (400MB/sec).
- Removed cost calculations for hard disks (rotation etc).
- Changed the following handler functions to return IO_AND_CPU_COST.
  This makes it easy to apply different cost modifiers in ha_..time()
  functions for io and cpu costs.
  - scan_time()
  - rnd_pos_time() & rnd_pos_call_time()
  - keyread_time()
- Enhanched keyread_time() to calculate the full cost of reading of a set
  of keys with a given number of ranges and optional number of blocks that
  need to be accessed.
- Removed read_time() as keyread_time() + rnd_pos_time() can do the same
  thing and more.
- Tuned cost for: heap, myisam, Aria, InnoDB, archive and MyRocks.
  Used heap table costs for json_table. The rest are using default engine
  costs.
- Added the following new optimizer variables:
  - optimizer_disk_read_ratio
  - optimizer_disk_read_cost
  - optimizer_key_lookup_cost
  - optimizer_row_lookup_cost
  - optimizer_row_next_find_cost
  - optimizer_scan_cost
- Moved all engine specific cost to OPTIMIZER_COSTS structure.
- Changed costs to use 'records_out' instead of 'records_read' when
  recalculating costs.
- Split optimizer_costs.h to optimizer_costs.h and optimizer_defaults.h.
  This allows one to change costs without having to compile a lot of
  files.
- Updated costs for filter lookup.
- Use a better cost estimate in best_extension_by_limited_search()
  for the sorting cost.
- Fixed previous issues with 'filtered' explain column as we are now
  using 'records_out' (min rows seen for table) to calculate filtering.
  This greatly simplifies the filtering code in
  JOIN_TAB::save_explain_data().

This change caused a lot of queries to be optimized differently than
before, which exposed different issues in the optimizer that needs to
be fixed.  These fixes are in the following commits.  To not have to
change the same test case over and over again, the changes in the test
cases are done in a single commit after all the critical change sets
are done.

InnoDB changes:
- Updated InnoDB to not divide big range cost with 2.
- Added cost for InnoDB (innobase_update_optimizer_costs()).
- Don't mark clustered primary key with HA_KEYREAD_ONLY. This will
  prevent that the optimizer is trying to use index-only scans on
  the clustered key.
- Disabled ha_innobase::scan_time() and ha_innobase::read_time() and
  ha_innobase::rnd_pos_time() as the default engine cost functions now
  works good for InnoDB.

Other things:
- Added  --show-query-costs (\Q) option to mysql.cc to show the query
  cost after each query (good when working with query costs).
- Extended my_getopt with GET_ADJUSTED_VALUE which allows one to adjust
  the value that user is given. This is used to change cost from
  microseconds (user input) to milliseconds (what the server is
  internally using).
- Added include/my_tracker.h  ; Useful include file to quickly test
  costs of a function.
- Use handler::set_table() in all places instead of 'table= arg'.
- Added SHOW_OPTIMIZER_COSTS to sys variables. These are input and
  shown in microseconds for the user but stored as milliseconds.
  This is to make the numbers easier to read for the user (less
  pre-zeros).  Implemented in 'Sys_var_optimizer_cost' class.
- In test_quick_select() do not use index scans if 'no_keyread' is set
  for the table. This is what we do in other places of the server.
- Added THD parameter to Unique::get_use_cost() and
  check_index_intersect_extension() and similar functions to be able
  to provide costs to called functions.
- Changed 'records' to 'rows' in optimizer_trace.
- Write more information to optimizer_trace.
- Added INDEX_BLOCK_FILL_FACTOR_MUL (4) and INDEX_BLOCK_FILL_FACTOR_DIV (3)
  to calculate usage space of keys in b-trees. (Before we used numeric
  constants).
- Removed code that assumed that b-trees has similar costs as binary
  trees. Replaced with engine calls that returns the cost.
- Added Bitmap::find_first_bit()
- Added timings to join_cache for ANALYZE table (patch by Sergei Petrunia).
- Added records_init and records_after_filter to POSITION to remember
  more of what best_access_patch() calculates.
- table_after_join_selectivity() changed to recalculate 'records_out'
  based on the new fields from best_access_patch()

Bug fixes:
- Some queries did not update last_query_cost (was 0). Fixed by moving
  setting thd->...last_query_cost in JOIN::optimize().
- Write '0' as number of rows for const tables with a matching row.

Some internals:
- Engine cost are stored in OPTIMIZER_COSTS structure.  When a
  handlerton is created, we also created a new cost variable for the
  handlerton. We also create a new variable if the user changes a
  optimizer cost for a not yet loaded handlerton either with command
  line arguments or with SET
  @@global.engine.optimizer_cost_variable=xx.
- There are 3 global OPTIMIZER_COSTS variables:
  default_optimizer_costs   The default costs + changes from the
                            command line without an engine specifier.
  heap_optimizer_costs      Heap table costs, used for temporary tables
  tmp_table_optimizer_costs The cost for the default on disk internal
                            temporary table (MyISAM or Aria)
- The engine cost for a table is stored in table_share. To speed up
  accesses the handler has a pointer to this. The cost is copied
  to the table on first access. If one wants to change the cost one
  must first update the global engine cost and then do a FLUSH TABLES.
  This was done to be able to access the costs for an open table
  without any locks.
- When a handlerton is created, the cost are updated the following way:
  See sql/keycaches.cc for details:
  - Use 'default_optimizer_costs' as a base
  - Call hton->update_optimizer_costs() to override with the engines
    default costs.
  - Override the costs that the user has specified for the engine.
  - One handler open, copy the engine cost from handlerton to TABLE_SHARE.
  - Call handler::update_optimizer_costs() to allow the engine to update
    cost for this particular table.
  - There are two costs stored in THD. These are copied to the handler
    when the table is used in a query:
    - optimizer_where_cost
    - optimizer_scan_setup_cost
- Simply code in best_access_path() by storing all cost result in a
  structure. (Idea/Suggestion by Igor)
2023-02-02 23:54:45 +03:00
Sergei Golubchik
eb26bf6e09 unify client/tool version string
it should now always be

/path/to/exe Ver <tool version> Distrib <server version> for <OS> (<ARCH>)

in all tools and clients
2023-01-19 12:39:28 +01:00
Oleksandr Byelkin
ad937cf33a Merge branch '10.10' into 10.11 2022-11-02 13:08:01 +01:00
Oleksandr Byelkin
49a22c5897 Merge branch '10.9' into 10.10 2022-11-01 11:55:28 +01:00
Oleksandr Byelkin
1ebfa2af62 Merge branch '10.6' into 10.7 2022-10-29 19:22:04 +02:00
Marko Mäkelä
aeccbbd926 Merge 10.5 into 10.6
To prevent ASAN heap-use-after-poison in the MDEV-16549 part of
./mtr --repeat=6 main.derived
the initialization of Name_resolution_context was cleaned up.
2022-10-25 14:25:42 +03:00
Marko Mäkelä
9a0b9e3360 Merge 10.4 into 10.5 2022-10-25 11:26:37 +03:00
Marko Mäkelä
667d3fbbb5 Merge 10.3 into 10.4 2022-10-25 10:04:37 +03:00
Anel Husakovic
1a057a923b MDEV-15530: Variable replicate_rewrite_db cannot be found in "show global variables"
- Add `replicate_rewrite_db` status variable, that may accept comma
separated key-value pairs.
- Note that option `OPT_REPLICATE_REWRITE_DB` already existed in `mysqld.h`
  from this commit 23d8586dbf

Reviewer:Brandon Nesterenko <brandon.nesterenko@mariadb.com>
2022-10-21 14:49:05 -06:00
Dmitry Shulga
bd9274faa4 MDEV-16128: Server crash in Item_func::print_op on 2nd execution of PS
For some queries that involve tables with different but convertible
character sets for columns taking part in the query, repeatable
execution of such queries in PS mode or as part of a stored routine
would result in server abnormal termination.

For example,
  CREATE TABLE t1 (a2 varchar(10));
  CREATE TABLE t2 (u1 varchar(10) CHARACTER SET utf8);
  CREATE TABLE t3 (u2 varchar(10) CHARACTER SET utf8);
  PREPARE stmt FROM
    "SELECT t1.* FROM (t1 JOIN t2 ON (t2.u1 = t1.a2))
     WHERE (EXISTS (SELECT 1 FROM t3 WHERE t3.u2 = t1.a2))";

  EXECUTE stmt;
  EXECUTE stmt; <== Running this prepared statement the second time
                    results in server crash.

The reason of server crash is that an instance of the class
Item_func_conv_charset, that created for conversion of a column
from one character set to another, is allocated on execution
memory root but pointer to this instance is stored in an item
placed on prepared statement memory root. Below is calls trace to
the place where an instance of the class Item_func_conv_charset
is created.

setup_conds
 Item_func::fix_fields
  Item_bool_rowready_func2::fix_length_and_dec
   Item_func::setup_args_and_comparator
    Item_func_or_sum::agg_arg_charsets_for_comparison
     Item_func_or_sum::agg_arg_charsets
      Item_func_or_sum::agg_item_set_converter
       Item::safe_charset_converter

And the following trace shows the place where a pointer to
the instance of the class Item_func_conv_charset is passed
to the class Item_func_eq, that is created on a memory root of
the prepared statement.

Prepared_statement::execute
 mysql_execute_command
  execute_sqlcom_select
   handle_select
    mysql_select
     JOIN::optimize
      JOIN::optimize_inner
       convert_join_subqueries_to_semijoins
        convert_subq_to_sj

To fix the issue, switch to the Prepared Statement memory root
before calling the method Item_func::setup_args_and_comparator
in order to place any created Items on permanent memory root.
It may seem that such approach would result in a memory
leakage in case the parameter marker '?' is used in the query
as in the following example
  PREPARE stmt FROM
    "SELECT t1.* FROM (t1 JOIN t2 ON (t2.u1 = t1.a2))
     WHERE (EXISTS (SELECT 1 FROM t3 WHERE t3.u2 = ?))";
  EXECUTE stmt USING convert('A' using latin1);
but it wouldn't since for such case any of the parameter markers
is treated as a constant and no subquery to semijoin optimization
is performed.
2022-10-17 15:05:17 +07:00
Alexander Barkov
133446828c MDEV-27009 Add UCA-14.0.0 collations
- Added one neutral and 22 tailored (language specific) collations based on
  Unicode Collation Algorithm version 14.0.0.

  Collations were added for Unicode character sets
  utf8mb3, utf8mb4, ucs2, utf16, utf32.

  Every tailoring was added with four accent and case
  sensitivity flag combinations, e.g:

  * utf8mb4_uca1400_swedish_as_cs
  * utf8mb4_uca1400_swedish_as_ci
  * utf8mb4_uca1400_swedish_ai_cs
  * utf8mb4_uca1400_swedish_ai_ci

  and their _nopad_ variants:

  * utf8mb4_uca1400_swedish_nopad_as_cs
  * utf8mb4_uca1400_swedish_nopad_as_ci
  * utf8mb4_uca1400_swedish_nopad_ai_cs
  * utf8mb4_uca1400_swedish_nopad_ai_ci

- Introducing a conception of contextually typed named collations:

  CREATE DATABASE db1 CHARACTER SET utf8mb4;
  CREATE TABLE db1.t1 (a CHAR(10) COLLATE uca1400_as_ci);

  The idea is that there is no a need to specify the character set prefix
  in the new collation names. It's enough to type just the suffix
  "uca1400_as_ci". The character set is taken from the context.

  In the above example script the context character set is utf8mb4.
  So the CREATE TABLE will make a column with the collation
  utf8mb4_uca1400_as_ci.

  Short collations names can be used in any parts of the SQL syntax
  where the COLLATE clause is understood.

- New collations are displayed only one time
  (without character set combinations) by these statements:

     SELECT * FROM INFORMATION_SCHEMA.COLLATIONS;
     SHOW COLLATION;

  For example, all these collations:
  - utf8mb3_uca1400_swedish_as_ci
  - utf8mb4_uca1400_swedish_as_ci
  - ucs2_uca1400_swedish_as_ci
  - utf16_uca1400_swedish_as_ci
  - utf32_uca1400_swedish_as_ci
  have just one entry in INFORMATION_SCHEMA.COLLATIONS and SHOW COLLATION,
  with COLLATION_NAME equal to "uca1400_swedish_as_ci", which is the suffix
  without the character set name:

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME LIKE '%uca1400_swedish_as_ci';

+-----------------------+
| COLLATION_NAME        |
+-----------------------+
| uca1400_swedish_as_ci |
+-----------------------+

  Note, the behaviour of old collations did not change.
  Non-unicode collations (e.g. latin1_swedish_ci) and
  old UCA-4.0.0 collations (e.g. utf8mb4_unicode_ci)
  are still displayed with the character set prefix, as before.

- The structure of the table INFORMATION_SCHEMA.COLLATIONS was changed.

  The NOT NULL constraint was removed from these columns:
  - CHARACTER_SET_NAME
  - ID
  - IS_DEFAULT
  and from the corresponding columns in SHOW COLLATION.

  For example:

SELECT COLLATION_NAME, CHARACTER_SET_NAME, ID, IS_DEFAULT
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME LIKE '%uca1400_swedish_as_ci';
+-----------------------+--------------------+------+------------+
| COLLATION_NAME        | CHARACTER_SET_NAME | ID   | IS_DEFAULT |
+-----------------------+--------------------+------+------------+
| uca1400_swedish_as_ci | NULL               | NULL | NULL       |
+-----------------------+--------------------+------+------------+

  The NULL value in these columns now means that the collation
  is applicable to multiple character sets.
  The behavioir of old collations did not change.
  Make sure your client programs can handle NULL values in these columns.

- The structure of the table
  INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY was changed.

  Three new NOT NULL columns were added:
  - FULL_COLLATION_NAME
  - ID
  - IS_DEFAULT

  New collations have multiple entries in COLLATION_CHARACTER_SET_APPLICABILITY.
  The column COLLATION_NAME contains the collation name without the character
  set prefix. The column FULL_COLLATION_NAME contains the collation name with
  the character set prefix.

  Old collations have full collation name in both FULL_COLLATION_NAME and
  COLLATION_NAME.

SELECT COLLATION_NAME, FULL_COLLATION_NAME, CHARACTER_SET_NAME, ID, IS_DEFAULT
FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE FULL_COLLATION_NAME RLIKE '^(utf8mb4|latin1).*swedish.*ci$';
+-----------------------------+-------------------------------------+--------------------+------+------------+
| COLLATION_NAME              | FULL_COLLATION_NAME                 | CHARACTER_SET_NAME | ID   | IS_DEFAULT |
+-----------------------------+-------------------------------------+--------------------+------+------------+
| latin1_swedish_ci           | latin1_swedish_ci                   | latin1             |    8 | Yes        |
| latin1_swedish_nopad_ci     | latin1_swedish_nopad_ci             | latin1             | 1032 |            |
| utf8mb4_swedish_ci          | utf8mb4_swedish_ci                  | utf8mb4            |  232 |            |
| uca1400_swedish_ai_ci       | utf8mb4_uca1400_swedish_ai_ci       | utf8mb4            | 2368 |            |
| uca1400_swedish_as_ci       | utf8mb4_uca1400_swedish_as_ci       | utf8mb4            | 2370 |            |
| uca1400_swedish_nopad_ai_ci | utf8mb4_uca1400_swedish_nopad_ai_ci | utf8mb4            | 2372 |            |
| uca1400_swedish_nopad_as_ci | utf8mb4_uca1400_swedish_nopad_as_ci | utf8mb4            | 2374 |            |
+-----------------------------+-------------------------------------+--------------------+------+------------+

- Other INFORMATION_SCHEMA queries:

  SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS;
  SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.PARAMETERS;
  SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES;
  SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
  SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.ROUTINES;
  SELECT COLLATION_CONNECTION FROM INFORMATION_SCHEMA.EVENTS;
  SELECT DATABASE_COLLATION FROM INFORMATION_SCHEMA.EVENTS;
  SELECT COLLATION_CONNECTION FROM INFORMATION_SCHEMA.ROUTINES;
  SELECT DATABASE_COLLATION FROM INFORMATION_SCHEMA.ROUTINES;
  SELECT COLLATION_CONNECTION FROM INFORMATION_SCHEMA.TRIGGERS;
  SELECT DATABASE_COLLATION FROM INFORMATION_SCHEMA.TRIGGERS;
  SELECT COLLATION_CONNECTION FROM INFORMATION_SCHEMA.VIEWS;

  display full collation names, including character sets prefix,
  for all collations, including new collations.

  Corresponding SHOW commands also display full collation names
  in collation related columns:

  SHOW CREATE TABLE t1;
  SHOW CREATE DATABASE db1;
  SHOW TABLE STATUS;
  SHOW CREATE FUNCTION f1;
  SHOW CREATE PROCEDURE p1;
  SHOW CREATE EVENT ev1;
  SHOW CREATE TRIGGER tr1;
  SHOW CREATE VIEW;

  These INFORMATION_SCHEMA queries and SHOW statements may change in
  the future, to display show collation names.
2022-08-10 15:04:24 +02:00
Sergei Golubchik
fd132be117 Merge branch '10.6' into 10.7 2022-05-11 11:25:33 +02:00
Sergei Golubchik
3bc98a4ec4 Merge branch '10.5' into 10.6 2022-05-10 14:01:23 +02:00
Sergei Golubchik
ef781162ff Merge branch '10.4' into 10.5 2022-05-09 22:04:06 +02:00
Sergei Golubchik
a70a1cf3f4 Merge branch '10.3' into 10.4 2022-05-08 23:03:08 +02:00
Oleksandr Byelkin
9614fde1aa Merge branch '10.2' into 10.3 2022-05-03 10:59:54 +02:00