- developers - lists.mariadb.org

Re: [Maria-developers] Move on with Gtid strict mode
by Kristian Nielsen 12 Jul '22

12 Jul '22

Hi Andrei, Good to hear from you, and to see that things are still going on with MariaDB GTID replication! I tried looking at the MDEVs that you referred to. Here is how I understand the motivation for this, correct me if I'm wrong: 1. We're considering a slave with "lossless semi-sync slave", where phantom reads are avoided by delaying storage engine commit until at least one slave has acknowledged a transaction. 2. If a master crashes, and comes back up after recovery, we want to prevent it from having any extra transactions that are not replicated to the rest of the replication servers (one of which will have been promoted as a new master). Such a transaction would prevent demoting the old master as a slave, as it would contain a rouge transaction not known to the rest of the replication topology. 3. To solve (2), we recover the old master with --tc-heuristic-recover=rollback. This makes it rollback (=discard) any transaction that was not fully committed to disk (does it also roll back the binary log for such transactions? I don't recall this detail). 4. Because of (3), there might be a transaction that originated on the old master (has it's server_id), but which is now missing from that server (now a slave) as it was deliberately discarded. 5. To solve (4), we will use circular replication so that the missing transaction gets replicated back to the old master (now slave). And we want to have a slave replicate its own transactions (replicate_same_sever_id), which is normally not done. And we want to use the server's binlog to know which transactions to replicate and which to ignore. Is that correctly understood? I must admit, this sounds horribly complex to me. So my question would be, do we really need to do this to solve the fundamental use case? In my belief, the main drawback with MariaDB replication and GTID is the complexity of especially the more advanced setups, which is barely feasible for even a skilled DBA to fully understand. Does this come from trying to port over some specific MySQL replication/GTID feature to MariaDB GTID? One important point is that MariaDB GTID was originally designed to keep the concept of master/binlog and slave/relaylog cleanly separated. A master transaction needs only be written to the binlog, there is no need to update any replication state (=overhead). Similarly, a slave transaction needs only be replicated to the storage engine, there is no need to write it to the binlog (=overhead). IIRC, MySQL (GTID) replication required the binlog (and --log-slave-updates) to be enabled on a slave) This is one of the improvements in the design of MariaDB GTID. So I don't particularly like the idea of now involving the binlog in how a slave replicates transactions. On the contrary, the MASTER_USE_GTID=current_pos feature is one thing I regret from the original GTID design, precisely because it violates the principle. And I was happy to see work to deprecate this, which I think is the prefered direction. So isn't there a better way to achieve the underlying use case? For example, why not just use the new MASTER_DEMOTE_TO_SLAVE option to set the correct slave position from which to replicate from in the old master (now slave)? And then use the existing --gtid-ignore-duplicate option? Since this is GTID-based circular replication, there must already be configured a separate gtid_domain_id for each master? Then you suggest to change the current --gtid-strict-mode and --gtid-ignore-duplicate options to a single option. I feel less strongly about this change; as you say, --gtid-strict-mode=OFF + --gtid-ignore-duplicates=ON may not make much sense. Still, I wonder what is the the need for such a change? This would be a not-backwards-compatible change, with little real benefit, as the semantics would be the same? Also, the --gtid-strict-mode option is an option that is highly recommended for any GTID use, and which should preferably be as simple as possible to understand to be able to be widely used by DBAs. On the contrary --gtid-ignore-duplicates is a very complex option only useful for very complex replication topologies, and with many subtle pitfalls that is very hard to fully understand. So there is real benefit to keeping them separate. As to the --gtid-strict-mode=OFF. The main original motivation for this option (and its default value of "OFF") was to be able to enable GTID by default, for any server installation upgraded (to was it 10.0 that introduced GTID? Long time back!). This is another big improvement (so I think) in MariaDB GTID over MySQL, that GTID is available by default, and compatible with any replication topology that works with old-style MySQL replication. Is it really so that no-one is using old-style, non-GTID replication any more, with all its sloppy setups possible, Sure, then non-strict GTID mode can be deprecated, I suppose - but it seems doubtful that this would be the case for *all* users? I hope I managed to explain where I didn't fully understand the motivation and need for some of these proposals? I'd be very interested to understand better any underlying necessities that I didn't catch from your mail, and maybe refine my comments. And once again, great to see the work still going on with MariaDB replication and GTID! Hope this helps, - Kristian. Andrei Elkin <andrei.elkin(a)mariadb.com> writes: > Two points of concern of this email deal with observations around GTID strict mode. > > > 1. About gtid_ignore_duplicates > > Introduced by > > hash: 2c2478b82260 > author: unknown > date: 2014-03-09 10:27:38 > > MDEV-5804: If same GTID is ... > Before, the arrival of same GTID twice in multi-source replication > would cause double-apply or in gtid strict mode an error. > > with a clear motivation to overcome the gtid strict mode error policy. > That is `gtid_ignore_duplicates = ON' softens `gtid_strict_mode = ON' > to ignore a duplicate gtid without a slave error/interruption. > As `gtid_strict_mode = OFF', `gtid_ignore_duplicates = ON' combination is > hardly ever relevant/practical, the `gtid_ignore_duplicates = ON' then can be fairly > perceived as `gtid_strict_mode=ON' 's "mild" submode to suggest > enum `gtid_stict_mode' { OFF, ON, "ON_BUT_IGNORE_DUPLICATES_RATHER_THAT_ERROR" } > which would obsolete `gtid_ignore_duplicates'. > > Why I'd prefer this change actually also deals with the circular replication > that - MDEV-28609 - forces us to do searching for duplicates > in the slave's binlog. > MDEV-28609 presents a case where a same server-id transaction received > by a semisync slave requires a more sophisticated decision making than > before (the same server-id based) for weather it to be ignored or > accepted. > > The accept policy of even own server-id gtids (transactions) is necessary for > MDEV-21117 semisync slave recovery, and the legacy > `replicate_same_sever_id' filtering can't help being too coarse as we need to consider > the gtid seq_no. > So under those conditions the same server-id transaction is made to be accepted > when it does not exist in the slave's binlog. > Otherwise, when it does exist in binlog, it'd be (like) a duplicate, arriving through > a circular topology from a "source" of the slave local binlog, only > to be ignored, like the same server-id gtids normally are. > The ignore policy in this case corresponds to the proposed > `gtid_stict_mode = "ON_BUT_IGNORE..."'. > > It should be naturally default as it inherits its pre-gtid equivalent of > `replicate_same_sever_id = NO' > and is safe (the slave's gtid state) for a real multi-source configuration as well. > > `gtid_stict_mode = ON' still would make sense as a method to > catch misconfigurations of the likes of Change-Master's do,ignore- domain_id ones. > > Your take? > > 2. But it's not clear what `gtid_strict_mode = OFF' is for. > > The ON mode is pretty flexible allowing gtid seq_no holes that is setting the gtid state > to any greater than the current (logical timestamp) value. > To set the gtid "clock" backward is still possible though requires slave restart. > > So how would you comment on an idea to deprecate OFF? > > Cheers, > > Andrei

1 0

Re: [Maria-developers] [Commits] 78370ea: MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE
by Oleksandr Byelkin 11 Jul '22

11 Jul '22

Hi, Igor! Generally all looks good, but see my notes and couple of questions below. On Thu, Jul 7, 2022 at 6:57 AM IgorBabaev <igor(a)mariadb.com> wrote: > revision-id: 78370ea0fd21f66a4084d488ddcebac541249241 > (mariadb-10.6.1-477-g78370ea) > parent(s): 2db18fdb3d68d906fbd188ec570a64502ba55849 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2022-07-06 21:57:23 -0700 > message: > > MDEV-28965 Assertion failure when preparing UPDATE with derived table in > WHERE > > This patch fixes not only the assertion failure in the function > Field_iterator_table_ref::set_field_iterator() but also: > - fixes the problem of forced materialization of derived tables used > in subqueries contained in WHERE clauses of single-table and multi-table > UPDATE and DELETE statements > - fixes the problem of MDEV-17954 that prevented execution of multi-table > DELETE statements if they use in their WHERE clauses references to > the tables that are updated. > > The patch must be considered a complement to the patch for MDEV-28883. > > --- > mysql-test/main/delete_use_source.result | 184 ++++++++++- > mysql-test/main/delete_use_source.test | 120 ++++++++ > mysql-test/main/derived.result | 336 > +++++++++++++++++++++ > mysql-test/main/derived.test | 210 +++++++++++++ > mysql-test/main/derived_cond_pushdown.result | 27 +- > mysql-test/main/derived_cond_pushdown.test | 2 +- > mysql-test/main/multi_update.result | 2 - > mysql-test/main/multi_update.test | 2 - > mysql-test/main/subselect.result | 1 - > mysql-test/main/subselect.test | 1 - > mysql-test/main/subselect_no_exists_to_in.result | 1 - > mysql-test/main/subselect_no_mat.result | 1 - > mysql-test/main/subselect_no_opts.result | 1 - > mysql-test/main/subselect_no_scache.result | 1 - > mysql-test/main/subselect_no_semijoin.result | 1 - > .../engines/iuds/r/update_delete_number.result | 22 +- > .../suite/engines/iuds/t/update_delete_number.test | 20 +- > sql/opt_subselect.cc | 56 +++- > sql/sql_base.cc | 41 ++- > sql/sql_delete.cc | 63 ++-- > sql/sql_delete.h | 14 +- > sql/sql_lex.cc | 28 +- > sql/sql_lex.h | 1 + > sql/sql_update.cc | 21 +- > sql/sql_update.h | 16 +- > sql/sql_yacc.yy | 13 + > sql/table.cc | 10 +- > 27 files changed, 1106 insertions(+), 89 deletions(-) > > [skip] > > --- a/mysql-test/suite/engines/iuds/t/update_delete_number.test > +++ b/mysql-test/suite/engines/iuds/t/update_delete_number.test > @@ -285,8 +285,18 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; > DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2; > --sorted_result > SELECT * FROM t1,t2 WHERE t2.c1=t1.c2; > ---error ER_UPDATE_TABLE_USED > -DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1); > +TRUNCATE TABLE t1; > +TRUNCATE TABLE t2; > +INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5); > +INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5); > +# After the patch for MDEV-28883 this should not report > +# --error ER_UPDATE_TABLE_USED anymore > Why is the above left as a comment? (the same in other cases) [skip] > diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc > index fa338f0..ac47566 100644 > --- a/sql/opt_subselect.cc > +++ b/sql/opt_subselect.cc > @@ -30,6 +30,8 @@ > #include "sql_base.h" > #include "sql_const.h" > #include "sql_select.h" > +#include "sql_update.h" > +#include "sql_delete.h" > it would be nice to have comment why you include it as it only for one function as I can see, something like: #include "sql_update.h" // processing_as_multitable_update_prohibited (the same for delete and for including in #include "sql_update.h" & "sql_base.cc") #include "filesort.h" > #include "opt_subselect.h" > #include "sql_test.h" > @@ -532,6 +534,48 @@ bool is_materialization_applicable(THD *thd, > Item_in_subselect *in_subs, > return FALSE; > } > > [skip] > diff --git a/sql/sql_base.cc b/sql/sql_base.cc > index 2b41b78..447573b 100644 > --- a/sql/sql_base.cc > +++ b/sql/sql_base.cc > @@ -47,6 +47,8 @@ > #include "sql_prepare.h" > #include "sql_statistics.h" > #include "sql_cte.h" > +#include "sql_update.h" > +#include "sql_delete.h" > see above > #include <m_ctype.h> > #include <my_dir.h> > #include <hash.h> > @@ -1164,7 +1166,7 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST > *table, TABLE_LIST *table_list, > /* > If we found entry of this table or table of SELECT which already > processed in derived table or top select of > multi-update/multi-delete > - (exclude_from_table_unique_test) or prelocking placeholder. > + (exclude_from_table_unique_test) or prelocking placeholder. > Please fix formatting above > */ > DBUG_PRINT("info", > ("found same copy of table or table which we should > skip")); > @@ -1175,16 +1177,43 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST > *table, TABLE_LIST *table_list, > We come here for queries of type: > INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp); > > - Try to fix by materializing the derived table > + Try to fix by materializing the derived table if one can't do > without it. > */ > TABLE_LIST *derived= res->belong_to_derived; > if (derived->is_merged_derived() && !derived->derived->is_excluded()) > { > - DBUG_PRINT("info", > + bool materialize= true; > + if (thd->lex->sql_command == SQLCOM_UPDATE) > + { > + Sql_cmd_update *cmd= (Sql_cmd_update *) (thd->lex->m_sql_cmd); > + if (cmd->is_multitable()) > + materialize= false; > + else if (!cmd->processing_as_multitable_update_prohibited(thd)) > + { > + cmd->set_as_multitable(); > + materialize= false; > + } > + } > + else if (thd->lex->sql_command == SQLCOM_DELETE) > + { > + Sql_cmd_delete *cmd= (Sql_cmd_delete *) (thd->lex->m_sql_cmd); > + if (cmd->is_multitable()) > + materialize= false; > + if (!cmd->processing_as_multitable_delete_prohibited(thd)) > + { > + cmd->set_as_multitable(); > + materialize= false; > + } > + } > + if (materialize) > + { > + DBUG_PRINT("info", > ("convert merged to materialization to resolve the > conflict")); > - derived->change_refs_to_fields(); > - derived->set_materialized_derived(); > - goto retry; > + derived->change_refs_to_fields(); > + derived->set_materialized_derived(); > + // derived->field_translation= 0; > above comment has no big sens, looks not cleaned up some chenges? > + goto retry; > + } > } > } > DBUG_RETURN(res); > > [skip] > diff --git a/sql/sql_delete.h b/sql/sql_delete.h > index e1d5044..ffb8173 100644 > --- a/sql/sql_delete.h > +++ b/sql/sql_delete.h > @@ -44,11 +44,12 @@ class Sql_cmd_delete final : public Sql_cmd_dml > { > public: > Sql_cmd_delete(bool multitable_arg) > - : multitable(multitable_arg), save_protocol(NULL) {} > + : orig_multitable(multitable_arg), save_protocol(NULL) > + { multitable= orig_multitable; } > why do not use the same initializers? : multitable(multitable_arg), rig_multitable(multitable_arg), save_protocol(NULL) {} > enum_sql_command sql_command_code() const override > { > - return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; > + return orig_multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; > } > > DML_prelocking_strategy *get_dml_prelocking_strategy() > > [skip] > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 78c067a..4059c0e 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -37,6 +37,8 @@ > #include "sql_partition.h" > #include "sql_partition_admin.h" // > Sql_cmd_alter_table_*_part > #include "event_parse_data.h" > +#include "sql_update.h" > +#include "sql_delete.h" > see above > > void LEX::parse_error(uint err_number) > { > @@ -4037,9 +4039,8 @@ bool LEX::can_use_merged() > SYNOPSIS > LEX::can_not_use_merged() > > - @param no_update_or_delete Set to 1 if we can't use merge with > multiple-table > - updates, like when used from > - TALE_LIST::init_derived() > + @param forced_no_merge_for_update_delete Set to 1 if we can't use merge > with > + multiple-table updates/deletes > > DESCRIPTION > Temporary table algorithm will be used on all SELECT levels for > queries > @@ -4050,7 +4051,7 @@ bool LEX::can_use_merged() > TRUE - VIEWs with MERGE algorithms can be used > */ > > -bool LEX::can_not_use_merged(bool no_update_or_delete) > +bool LEX::can_not_use_merged(bool forced_no_merge_for_update_delete) > { > switch (sql_command) { > case SQLCOM_CREATE_VIEW: > @@ -4064,18 +4065,29 @@ bool LEX::can_not_use_merged(bool > no_update_or_delete) > return TRUE; > > case SQLCOM_UPDATE_MULTI: > - case SQLCOM_DELETE_MULTI: > - if (no_update_or_delete) > + if (forced_no_merge_for_update_delete) > return TRUE; > /* Fall through */ > > case SQLCOM_UPDATE: > - if (no_update_or_delete && m_sql_cmd && > - (m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI || > + if (forced_no_merge_for_update_delete && > + (((Sql_cmd_update *) m_sql_cmd)->is_multitable() || > query_tables->is_multitable())) > return TRUE; > + return FALSE; > + > + case SQLCOM_DELETE_MULTI: > + if (forced_no_merge_for_update_delete) > + return TRUE; > /* Fall through */ > > + case SQLCOM_DELETE: > + if (forced_no_merge_for_update_delete && > + (((Sql_cmd_delete *) m_sql_cmd)->is_multitable() || > + query_tables->is_multitable())) > + return TRUE; > + return FALSE; > + > default: > return FALSE; > } > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index e733b4f..402e3bd 100644 > > [skip] > diff --git a/sql/sql_update.cc b/sql/sql_update.cc > index 6b14c4f..7769777 100644 > --- a/sql/sql_update.cc > +++ b/sql/sql_update.cc > @@ -2807,6 +2807,23 @@ bool multi_update::send_eof() > > > /** > + @brief Check whether conversion to multi-table update is prohibited > + > + @param thd global context the processed statement > + @returns true if conversion is prohibited, false otherwise > + > + @todo > + Introduce handler level flag for storage engines that would prohibit > + such conversion for any single-table update. > +*/ > + > +bool Sql_cmd_update::processing_as_multitable_update_prohibited(THD *thd) > +{ > + return false; > +} > + > + > +/** > @brief Perform precheck of table privileges for update statements > > @param thd global context the processed statement > @@ -2889,7 +2906,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd) > "updating and querying the same temporal periods table"); > DBUG_RETURN(TRUE); > } > - multitable= true; > + if (!table_list->is_multitable() && > + !processing_as_multitable_update_prohibited(thd)) > + multitable= true; > } > } > > diff --git a/sql/sql_update.h b/sql/sql_update.h > index d0fc7cb..4aff77a 100644 > --- a/sql/sql_update.h > +++ b/sql/sql_update.h > @@ -46,12 +46,12 @@ class Sql_cmd_update final : public Sql_cmd_dml > { > public: > Sql_cmd_update(bool multitable_arg) > - : multitable(multitable_arg) > - { } > + : orig_multitable(multitable_arg) > + { multitable= orig_multitable; } > why do not use construction for both? Sql_cmd_update(bool multitable_arg) : multitable(multitable_arg) : orig_multitable(multitable_arg) {} > > enum_sql_command sql_command_code() const override > { > - return multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE; > + return orig_multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE; > } > > DML_prelocking_strategy *get_dml_prelocking_strategy() > @@ -59,6 +59,12 @@ class Sql_cmd_update final : public Sql_cmd_dml > return &multiupdate_prelocking_strategy; > } > > + bool processing_as_multitable_update_prohibited(THD *thd); > + > + bool is_multitable() { return multitable; } > + > + void set_as_multitable() { multitable= true; } > + > protected: > /** > @brief Perform precheck of table privileges for update statements > @@ -89,13 +95,15 @@ class Sql_cmd_update final : public Sql_cmd_dml > */ > bool multitable; > > + /* Original value of the 'multitable' flag set by constructor */ > + const bool orig_multitable; > + > /* The prelocking strategy used when opening the used tables */ > Multiupdate_prelocking_strategy multiupdate_prelocking_strategy; > > public: > /* The list of the updating expressions used in the set clause */ > List<Item> *update_value_list; > - > }; > > #endif /* SQL_UPDATE_INCLUDED */ > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index a587a37..13dc602 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -13370,8 +13370,21 @@ delete_single_table: > YYPS->m_lock_type, > YYPS->m_mdl_type, > NULL, > + 0))) > + MYSQL_YYABORT; > + Select->table_list.save_and_clear(&Lex->auxiliary_table_list); > + Lex->table_count= 1; > Why it is not enough to have this counter (table_count) set by open_tables? May be it would be better to use mysql_init_select() to set everything needed by SELECT for sure? > + Lex->query_tables= 0; > + Lex->query_tables_last= &Lex->query_tables; > + if (unlikely(!Select-> > + add_table_to_list(thd, $2, NULL, > TL_OPTION_UPDATING, > + YYPS->m_lock_type, > + YYPS->m_mdl_type, > + NULL, > $3))) > MYSQL_YYABORT; > + Lex->auxiliary_table_list.first->correspondent_table= > + Lex->query_tables; > YYPS->m_lock_type= TL_READ_DEFAULT; > YYPS->m_mdl_type= MDL_SHARED_READ; > } > [skip]

1 0

[Maria-developers] Account locked and USERS view?
by Martijn Tonies (Upscene Productions) 06 Jul '22

06 Jul '22

Hi, Any reason why the ‘account locked’ attribute isn’t included in the USERS view? https://mariadb.com/kb/en/account-locking/ It could be added like this: if(json_value(mysql.global_priv.Priv, '$.account_locked') = 1, 'Y', 'N') AS is_locked With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] MDEV-28881 Server crashes in Dep_analysis_context::create_table_value
by Sergey Petrunia 05 Jul '22

05 Jul '22

Hi Oleg, > commit 812f8cbfb3a7a02705f65d8bb7e30ae2faf13b5d > Author: Oleg Smirnov <olernov(a)gmail.com> > Date: Tue Jun 21 16:20:17 2022 +0700 > > MDEV-28881 Server crashes in Dep_analysis_context::create_table_value > > SELECT_LEX::first_select()->join may be NULL for some scenarios. > This commit adds a check for this. Looking into Jira I can infer that the patch fixes two issues, but the commit comment doesn't say that. Please avoid doing this in the future. Please split the commit into two: - first one fixing the SELECT_LEX::first_select()->join == NULL part - the second fixing the memory management. For the second part, I think it's not a good approach when most of the Table Elimination data resides in a MEM_ROOT with exception of std::set which keeps its data on the heap. I was wondering if there's a way to get STL containers to reside on MEM_ROOT as well, and found that yes there is, and MySQL actually already has it: https://dev.mysql.com/worklog/task/?id=6074 https://github.com/mysql/mysql-server/blob/8.0/sql/mem_root_allocator.h Can we copy mem_root_allocator.h from them (in a separate commit) and then fix memory management here by making std::set use it? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] 9ec0b36ee8b: MDEV-28838 password_reuse_check plugin mixes username and password
by Sergei Golubchik 04 Jul '22

04 Jul '22

Hi, Oleksandr, On Jul 04, Oleksandr Byelkin wrote: > revision-id: 9ec0b36ee8b (mariadb-10.7.4-27-g9ec0b36ee8b) > parent(s): ac0af4ec4ab > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-06-29 17:25:44 +0200 > message: > > MDEV-28838 password_reuse_check plugin mixes username and password > > To prevent the problem of mixing user name and password and > host name and user name we add length of the hostname and user name > to the hash. > > diff --git a/plugin/password_reuse_check/password_reuse_check.c b/plugin/password_reuse_check/password_reuse_check.c > index ff0364ce007..4d07cda878a 100644 > --- a/plugin/password_reuse_check/password_reuse_check.c > +++ b/plugin/password_reuse_check/password_reuse_check.c > @@ -165,9 +166,11 @@ static int validate(const MYSQL_CONST_LEX_STRING *username, > return 1; > } > > - memcpy(buff, hostname->str, hostname->length); > - memcpy(buff + hostname->length, username->str, username->length); > - memcpy(buff + hostname->length + username->length, password->str, > + int2store(buff, hostname->length); > + int2store(buff + 2, username->length); > + memcpy(buff + 4, hostname->str, hostname->length); > + memcpy(buff + 4 + hostname->length, username->str, username->length); > + memcpy(buff + 4 + hostname->length + username->length, password->str, > password->length); why? let's use the standard length,string format, why risk inventing something unusual here for no benefits at all? I'd do like: static char *store_str(char *to, MYSQL_CONST_LEX_STRING from) { int2store(to, from.length); memcpy(to+2, from.str, from.length); return to+2+from.length; } and buff= store_str(buff, hostname); buff= store_str(buff, username); buff= store_str(buff, password); > buff[key_len]= 0; > memset(hash, 0, sizeof(hash)); > @@ -235,7 +238,7 @@ maria_declare_plugin(password_reuse_check) > 0x0100, ^^^ this should be 0x0101 (or 0x0200 as I suggest below) > NULL, > sysvars, > - "1.0", > + "1.1", make it 2.0 please, this is an incompatible change > MariaDB_PLUGIN_MATURITY_GAMMA > } > maria_declare_plugin_end; > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] e9293e31ec6: MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE
by Sergei Golubchik 30 Jun '22

30 Jun '22

Hi, Nikita, This is very good. Just one comment, see below: On Jun 29, Nikita Malyavin wrote: > revision-id: e9293e31ec6 (mariadb-10.6.1-499-ge9293e31ec6) > parent(s): 6cfcb36d027 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2022-06-29 20:16:19 +0300 > message: > > MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE > > ALTER ONLINE TABLE acquires table with TL_READ. Myisam normally acquires > TL_WRITE for DML, which makes it hang until table is freed. > > We deadlock once ALTER upgrades its MDL lock. > > Solution: > Unlock table earlier. We don't need to hold TL_READ once we finished > copying. Relay log replication requires no data locks on `from` table. > > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > index 425de469a7f..90f6283d9df 100644 > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -5734,7 +5734,7 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > used in the transaction and proceed with execution of the actual > event. > */ > - if (!thd->lock) > + if (!thd->lock && rgi->tables_to_lock_count) > { > /* > Lock_tables() reads the contents of thd->lex, so they must be Instead of adding a second condition to the execution path for every row event in the replication, consider this: - if (!thd->lock) + if (!thd->open_tables) this will work identically for replication, I hope, but will be always false in your case, as you still have the table open. I mean, that's the idea. I run the rpl suite with it, but haven't done any further testing, so not completely sure it'll work. When making this change, don't forget to update the comment right above this if(). Ok to push into bb-10.10-MDEV-16329 after that. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

[Maria-developers] How to know when a FUNCTION is AGGREGATE?
by Martijn Tonies (Upscene Productions) 17 Jun '22

17 Jun '22

Hi all, Since MariaDB 10.3, you can use an AGGREGATE stored function. https://mariadb.com/kb/en/stored-aggregate-functions/ When examining the meta data, how does one distinguish between aggregate and normal stored functions? Can’t find anything in the information_schema about this. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.

3 3

Re: [Maria-developers] 49ecf935415: MDEV-27009 Add UCA-14.0.0 collations
by Sergei Golubchik 16 Jun '22

16 Jun '22

Hi, Alexander, On Mar 14, Alexander Barkov wrote: > revision-id: 49ecf935415 (mariadb-10.6.1-335-g49ecf935415) > parent(s): c67789f63c8 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-02-28 14:04:58 +0400 > message: > > MDEV-27009 Add UCA-14.0.0 collations please, list all user visible changes there. Mainly that collations are now decoupled from charsets. New syntax in CREATE TABLE, changes in I_S tables, etc. > diff --git a/mysql-test/include/ctype_utf_uca1400_ids.inc b/mysql-test/include/ctype_utf_uca1400_ids.inc > new file mode 100644 > index 00000000000..09cf49fc0e7 > --- /dev/null > +++ b/mysql-test/include/ctype_utf_uca1400_ids.inc > @@ -0,0 +1,17 @@ file names are confusing. better rename ctype_ucs_uca1400_ids.inc to something like ctype_convert_uca1400_ids and ctype_utf_uca1400_ids to ctype_set_names_uca1400_ids or something like that, to show what they do. > + > +--disable_ps_protocol > +--enable_metadata > +DELIMITER $$; > +FOR rec IN (SELECT COLLATION_NAME > + FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY > + WHERE CHARACTER_SET_NAME=@charset > + AND COLLATION_NAME RLIKE 'uca1400' > + ORDER BY ID) > +DO > + EXECUTE IMMEDIATE CONCAT('SET NAMES ',@charset,' COLLATE ', rec.COLLATION_NAME); > + SELECT rec.COLLATION_NAME; > +END FOR; > +$$ > +DELIMITER ;$$ > +--disable_metadata > +--enable_ps_protocol > diff --git a/include/m_ctype.h b/include/m_ctype.h > index 4c6628b72b3..706764ead2a 100644 > --- a/include/m_ctype.h > +++ b/include/m_ctype.h > @@ -34,7 +34,9 @@ enum loglevel { > extern "C" { > #endif > > -#define MY_CS_NAME_SIZE 32 > +#define MY_CS_CHARACTER_SET_NAME_SIZE 32 > +#define MY_CS_COLLATION_NAME_SIZE 64 That's FULL_COLLATION_NAME_SIZE, right? > + > #define MY_CS_CTYPE_TABLE_SIZE 257 > #define MY_CS_TO_LOWER_TABLE_SIZE 256 > #define MY_CS_TO_UPPER_TABLE_SIZE 256 > @@ -240,6 +242,46 @@ typedef enum enum_repertoire_t > } my_repertoire_t; > > > +/* ID compatibility */ > +typedef enum enum_collation_id_type > +{ > + MY_COLLATION_ID_TYPE_PRECISE= 0, > + MY_COLLATION_ID_TYPE_COMPAT_100800= 1 > +} my_collation_id_type_t; > + > + > +/* Collation name display modes */ > +typedef enum enum_collation_name_mode > +{ > + MY_COLLATION_NAME_MODE_FULL= 0, > + MY_COLLATION_NAME_MODE_CONTEXT= 1 > +} my_collation_name_mode_t; > + > + > +/* Level flags */ > +#define MY_CS_LEVEL_BIT_PRIMARY 0x00 > +#define MY_CS_LEVEL_BIT_SECONDARY 0x01 > +#define MY_CS_LEVEL_BIT_TERTIARY 0x02 > +#define MY_CS_LEVEL_BIT_QUATERNARY 0x03 > + > +#define MY_CS_COLL_LEVELS_S1 (1<<MY_CS_LEVEL_BIT_PRIMARY) > + > +#define MY_CS_COLL_LEVELS_AI_CS (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) > + > +#define MY_CS_COLL_LEVELS_S2 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) > + > +#define MY_CS_COLL_LEVELS_S3 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) | \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) AI_CS and S3 don't seem to be used yet > + > +#define MY_CS_COLL_LEVELS_S4 (1<<MY_CS_LEVEL_BIT_PRIMARY)| \ > + (1<<MY_CS_LEVEL_BIT_SECONDARY) | \ > + (1<<MY_CS_LEVEL_BIT_TERTIARY) | \ > + (1<<MY_CS_LEVEL_BIT_QUATERNARY) > + > + > /* Flags for strxfrm */ > #define MY_STRXFRM_LEVEL1 0x00000001 /* for primary weights */ > #define MY_STRXFRM_LEVEL2 0x00000002 /* for secondary weights */ > diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc > index 86c6e9a27f8..9ddd482ad57 100644 > --- a/sql/sql_alter.cc > +++ b/sql/sql_alter.cc > @@ -546,6 +546,7 @@ bool Sql_cmd_alter_table::execute(THD *thd) > > result= mysql_alter_table(thd, &select_lex->db, &lex->name, > &create_info, > + lex->create_info.default_charset_collation, I don't see why you need a new argument here. It's create_info.default_charset_collation, so, mysql_alter_table already gets it in create_info. All other mysql_alter_table invocations also take create_info argument and can get default_charset_collation from there > first_table, > &alter_info, > select_lex->order_list.elements, > diff --git a/sql/sql_partition_admin.cc b/sql/sql_partition_admin.cc > index fb1ae0d5fc7..4188dde252b 100644 > --- a/sql/sql_partition_admin.cc > +++ b/sql/sql_partition_admin.cc > @@ -211,6 +211,7 @@ bool compare_table_with_partition(THD *thd, TABLE *table, TABLE *part_table, > part_table->use_all_columns(); > table->use_all_columns(); > if (unlikely(mysql_prepare_alter_table(thd, part_table, &part_create_info, > + Lex_maybe_default_charset_collation(), Same. Can be in part_create_info > &part_alter_info, &part_alter_ctx))) > { > my_error(ER_TABLES_DIFFERENT_METADATA, MYF(0)); > diff --git a/sql/sql_i_s.h b/sql/sql_i_s.h > index bed2e886718..5ff06d32231 100644 > --- a/sql/sql_i_s.h > +++ b/sql/sql_i_s.h > @@ -162,6 +162,11 @@ class Yesno: public Varchar > { > public: > Yesno(): Varchar(3) { } > + static LEX_CSTRING value(bool val) > + { > + return val ? Lex_cstring(STRING_WITH_LEN("Yes")) : > + Lex_cstring(); > + } eh... please, rename the class from Yesno to something like Yesempty or Yes_or_empty, something that says that the second should not be Lex_cstring(STRING_WITH_LEN("No")) > }; > > > diff --git a/sql/table.cc b/sql/table.cc > index a683a78ff49..c28cb2bd928 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -3491,6 +3493,16 @@ int TABLE_SHARE::init_from_sql_statement_string(THD *thd, bool write, > else > thd->set_n_backup_active_arena(arena, &backup); > > + /* > + THD::reset_db() does not set THD::db_charset, > + so it keeps pointing to the character set and collation > + of the current database, rather than the database of the > + new initialized table. Hmm, is that correct? Could you check other invocation of thd->reset_db()? Perhaps they all need to switch charset? In that case it should be done inside THD::reset_db(). Or may be they have to use mysql_change_db_impl() instead? > + Let's call get_default_db_collation() before reset_db(). > + This forces the db.opt file to be loaded. > + */ > + db_cs= get_default_db_collation(thd, db.str); > + > thd->reset_db(&db); > lex_start(thd); > > @@ -3498,6 +3510,11 @@ int TABLE_SHARE::init_from_sql_statement_string(THD *thd, bool write, > sql_unusable_for_discovery(thd, hton, sql_copy)))) > goto ret; > > + if (!(thd->lex->create_info.default_table_charset= > + thd->lex->create_info.default_charset_collation. > + resolved_to_character_set(db_cs, db_cs))) > + DBUG_RETURN(true); How could this (and similar if()'s in other files) fail? > + > thd->lex->create_info.db_type= hton; > #ifdef WITH_PARTITION_STORAGE_ENGINE > thd->work_part_info= 0; // For partitioning > diff --git a/sql/mysys_charset.h b/sql/mysys_charset.h > new file mode 100644 > index 00000000000..86eaeedd432 > --- /dev/null > +++ b/sql/mysys_charset.h > @@ -0,0 +1,44 @@ > +#ifndef MYSYS_CHARSET > +#define MYSYS_CHARSET > + > +/* Copyright (c) 2021, MariaDB Corporation. > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ > + > + > +#include "my_sys.h" > + > + > +class Charset_loader_mysys: public MY_CHARSET_LOADER > +{ > +public: > + Charset_loader_mysys() > + { > + my_charset_loader_init_mysys(this); > + } > + void raise_unknown_collation_error(const char *name, > + CHARSET_INFO *name_cs) const; > + CHARSET_INFO *get_charset(const char *cs_name, uint cs_flags, myf my_flags); > + CHARSET_INFO *get_exact_collation(const char *name, myf utf8_flag); > + CHARSET_INFO *get_contextually_typed_collation(CHARSET_INFO *cs, > + const char *name); > + CHARSET_INFO *get_contextually_typed_collation(const char *name); > + CHARSET_INFO *get_contextually_typed_collation_or_error(CHARSET_INFO *cs, > + const char *name); > + CHARSET_INFO *find_default_collation(CHARSET_INFO *cs); > + CHARSET_INFO *find_bin_collation_or_error(CHARSET_INFO *cs); > +}; you can have C++ code in mysys too, you know, no need to put it in sql/mysys* > + > +#endif // MYSYS_CHARSET > + > diff --git a/strings/ctype-simple.c b/strings/ctype-simple.c > index b579f0af203..d09dfba86ed 100644 > --- a/strings/ctype-simple.c > +++ b/strings/ctype-simple.c > @@ -1940,13 +1941,26 @@ my_bool my_propagate_complex(CHARSET_INFO *cs __attribute__((unused)), > } > > > +void my_ci_set_strength(struct charset_info_st *cs, uint strength) > +{ > + DBUG_ASSERT(strength > 0 && strength <= MY_STRXFRM_NLEVELS); don't use && in asserts, please create two separate asserts instead: DBUG_ASSERT(strength > 0); DBUG_ASSERT(strength <= MY_STRXFRM_NLEVELS); > + cs->levels_for_order= ((1 << strength) - 1); why do you still use the old concept of "strength"? Why not to use bitmap consistently everywhere? > +} > + > + > +void my_ci_set_level_flags(struct charset_info_st *cs, uint flags) > +{ > + DBUG_ASSERT(flags < (1<<MY_STRXFRM_NLEVELS)); > + cs->levels_for_order= flags; > +} > + > /* > Normalize strxfrm flags > > SYNOPSIS: > my_strxfrm_flag_normalize() > + cs - the CHARSET_INFO pointer > flags - non-normalized flags > - nlevels - number of levels > > NOTES: > If levels are omitted, then 1-maximum is assumed. > diff --git a/sql/handler.h b/sql/handler.h > index 8ad521e189a..1e82f37b1e7 100644 > --- a/sql/handler.h > +++ b/sql/handler.h > @@ -2409,7 +2386,32 @@ struct Table_specification_st: public HA_CREATE_INFO, > { > HA_CREATE_INFO::options= 0; > DDL_options_st::init(); > + default_charset_collation.init(); > + } > + > + bool > + add_alter_list_item_convert_to_charset(const Lex_charset_collation_st &cl) > + { > + /* > + cs cannot be NULL, as sql_yacc.yy translates > + CONVERT TO CHARACTER SET DEFAULT > + to > + CONVERT TO CHARACTER SET <character-set-of-the-current-database> > + TODO: Shouldn't we postpone resolution of DEFAULT until the > + character set of the table owner database is loaded from its db.opt? > + */ > + DBUG_ASSERT(cl.charset_collation()); > + DBUG_ASSERT(!cl.is_contextually_typed_collation()); > + alter_table_convert_to_charset= cl.charset_collation(); > + default_charset_collation.Lex_charset_collation_st::operator=(cl); looks quite ugly. can you do, like, default_charset_collation.set(cl) ? > + used_fields|= (HA_CREATE_USED_CHARSET | HA_CREATE_USED_DEFAULT_CHARSET); > + return false; > } > + bool add_table_option_default_charset(CHARSET_INFO *cs); > + bool add_table_option_default_collation(const Lex_charset_collation_st &cl); > + bool resolve_db_charset_and_collation(THD *thd, > + const LEX_CSTRING &db, > + bool is_alter); > }; > > > diff --git a/strings/ctype-uca1400data.h b/strings/ctype-uca1400data.h > new file mode 100644 > index 00000000000..da95dcfde54 > --- /dev/null > +++ b/strings/ctype-uca1400data.h > @@ -0,0 +1,44151 @@ > +/* > + Generated from allkeys.txt version '14.0.0' > +*/ if it's generated, do you need to check it in? perhaps it should be generated during the build? you've checked in allkeys1400.txt anyway. > +static const uint16 uca1400_p000[]= { /* 0000 (4 weights per char) */ > +0x0000,0x0000,0x0000,0x0000, 0x0000,0x0000,0x0000,0x0000, /* 0000 */ > +0x0000,0x0000,0x0000,0x0000, 0x0000,0x0000,0x0000,0x0000, /* 0002 */ > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 6ca10267187..d115401a855 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -542,6 +542,30 @@ bool LEX::add_alter_list(LEX_CSTRING name, LEX_CSTRING new_name, bool exists) > } > > > +bool LEX::add_alter_list_item_convert_to_charset( > + THD *thd, > + CHARSET_INFO *cs, > + const Lex_charset_collation_st &cl) > +{ > + if (!cs) > + { > + Lex_charset_collation_st tmp; > + tmp.set_charset_collate_default(thd->variables.collation_database); Hmm, what if one is doing ALTER TABLE db.test CHARSET DEFAULT and current db is not `db` but `test` ? > + if (!(cs= tmp.charset_collation())) > + return true; // Should not actually happen assert? > + } > + > + Lex_explicit_charset_opt_collate tmp(cs, false); > + if (tmp.merge_opt_collate_or_error(cl) || > + create_info.add_alter_list_item_convert_to_charset( > + Lex_charset_collation(tmp))) > + return true; > + > + alter_info.flags|= ALTER_CONVERT_TO; > + return false; > +} > + > + > void LEX::init_last_field(Column_definition *field, > const LEX_CSTRING *field_name) > { > @@ -11871,29 +11869,41 @@ CHARSET_INFO *Lex_collation_st::find_default_collation(CHARSET_INFO *cs) > "def" is the upper level CHARACTER SET clause (e.g. of a table) > */ > CHARSET_INFO * > -Lex_collation_st::resolved_to_character_set(CHARSET_INFO *def) const > +Lex_charset_collation_st::resolved_to_character_set(CHARSET_INFO *def) const > { > DBUG_ASSERT(def); > - if (m_type != TYPE_CONTEXTUALLY_TYPED) > - { > - if (!m_collation) > - return def; // Empty - not typed at all > - return m_collation; // Explicitly typed > + > + switch (m_type) { > + case TYPE_EMPTY: > + return def; > + case TYPE_CHARACTER_SET: > + DBUG_ASSERT(m_ci); > + return m_ci; > + case TYPE_COLLATE_EXACT: > + DBUG_ASSERT(m_ci); > + return m_ci; > + case TYPE_COLLATE_CONTEXTUALLY_TYPED: > + break; > } > > // Contextually typed > - DBUG_ASSERT(m_collation); > + DBUG_ASSERT(m_ci); > > - if (m_collation == &my_charset_bin) // CHAR(10) BINARY > - return find_bin_collation(def); > + Charset_loader_mysys loader; > + if (is_contextually_typed_binary_style()) // CHAR(10) BINARY > + return loader.find_bin_collation_or_error(def); > > - if (m_collation == &my_charset_latin1) // CHAR(10) COLLATE DEFAULT > - return find_default_collation(def); > + if (is_contextually_typed_collate_default()) // CHAR(10) COLLATE DEFAULT > + return loader.find_default_collation(def); > + > + const LEX_CSTRING context_name= collation_name_context_suffix(); I'd rather put this in assert, not in if(). Like - if (!strncasecmp(context_name.str, STRING_WITH_LEN("uca1400_"))) + DBUG_ASSERT(!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))); > + if (!strncasecmp(context_name.str, STRING_WITH_LEN("uca1400_"))) > + return loader.get_contextually_typed_collation_or_error(def, > + context_name.str); > > /* > - Non-binary and non-default contextually typed collation. > + Non-binary, non-default, non-uca1400 contextually typed collation. > We don't have such yet - the parser cannot produce this. > - But will have soon, e.g. "uca1400_as_ci". > */ > DBUG_ASSERT(0); > return NULL; > @@ -11944,58 +11972,106 @@ bool Lex_collation_st:: > CHAR(10) BINARY .. COLLATE latin1_bin > CHAR(10) COLLATE uca1400_as_ci .. COLLATE latin1_bin > */ > - if (collation() == &my_charset_latin1 && > - !(cl.collation()->state & MY_CS_PRIMARY)) > + if (is_contextually_typed_collate_default() && > + !cl.charset_collation()->default_flag()) > { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "COLLATE ", "DEFAULT", "COLLATE ", > - cl.collation()->coll_name.str); > + error_conflicting_collations_or_styles(*this, cl); > return true; > } > - if (collation() == &my_charset_bin && > - !(cl.collation()->state & MY_CS_BINSORT)) > + > + if (is_contextually_typed_binary_style() && > + !cl.charset_collation()->binsort_flag()) > { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "", "BINARY", "COLLATE ", cl.collation()->coll_name.str); > + error_conflicting_collations_or_styles(*this, cl); > return true; > } > *this= cl; > return false; > } > > - if (cl.is_contextually_typed_collation()) > - { > + DBUG_ASSERT(0); > + return false; > +} > + > + > +bool > +Lex_explicit_charset_opt_collate:: > + merge_collate_or_error(const Lex_charset_collation_st &cl) > +{ > + DBUG_ASSERT(cl.type() != Lex_charset_collation_st::TYPE_CHARACTER_SET); > + > + switch (cl.type()) { > + case Lex_charset_collation_st::TYPE_EMPTY: > + return false; > + case Lex_charset_collation_st::TYPE_CHARACTER_SET: > + DBUG_ASSERT(0); > + return false; > + case Lex_charset_collation_st::TYPE_COLLATE_EXACT: > /* > - EXPLICIT + CONTEXT > - CHAR(10) COLLATE latin1_bin .. COLLATE DEFAULT - not supported > - CHAR(10) COLLATE latin1_bin .. COLLATE uca1400_as_ci - not yet > + EXPLICIT + EXPLICIT > + CHAR(10) CHARACTER SET latin1 .. COLLATE latin1_bin > + CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > + CHAR(10) CHARACTER SET latin1 BINARY .. COLLATE latin1_bin > */ > - DBUG_ASSERT(0); // Not possible yet > + if (m_with_collate && m_ci != cl.charset_collation()) > + { > + my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > + "COLLATE ", m_ci->coll_name.str, > + "COLLATE ", cl.charset_collation()->coll_name.str); > + return true; > + } > + if (!my_charset_same(m_ci, cl.charset_collation())) > + { > + my_error(ER_COLLATION_CHARSET_MISMATCH, MYF(0), > + cl.charset_collation()->coll_name.str, m_ci->cs_name.str); > + return true; > + } > + m_ci= cl.charset_collation(); > + m_with_collate= true; > return false; > - } > > - /* > - EXPLICIT + EXPLICIT > - CHAR(10) CHARACTER SET latin1 .. COLLATE latin1_bin > - CHAR(10) CHARACTER SET latin1 COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) COLLATE latin1_bin .. COLLATE latin1_bin > - CHAR(10) CHARACTER SET latin1 BINARY .. COLLATE latin1_bin > - */ > - if (type() == TYPE_EXPLICIT && collation() != cl.collation()) > - { > - my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), > - "COLLATE ", collation()->coll_name.str, > - "COLLATE ", cl.collation()->coll_name.str); > - return true; > - } > - if (!my_charset_same(collation(), cl.collation())) > - { > - my_error(ER_COLLATION_CHARSET_MISMATCH, MYF(0), > - cl.collation()->coll_name.str, collation()->cs_name.str); > - return true; > + case Lex_charset_collation_st::TYPE_COLLATE_CONTEXTUALLY_TYPED: > + if (cl.is_contextually_typed_collate_default()) > + { > + /* > + SET NAMES latin1 COLLATE DEFAULT; > + ALTER TABLE t1 CONVERT TO CHARACTER SET latin1 COLLATE DEFAULT; > + */ > + CHARSET_INFO *tmp= Charset_loader_mysys().find_default_collation(m_ci); > + if (!tmp) > + return true; > + m_ci= tmp; > + m_with_collate= true; > + return false; > + } > + else > + { > + /* > + EXPLICIT + CONTEXT > + CHAR(10) COLLATE latin1_bin .. COLLATE DEFAULT not possible yet > + CHAR(10) COLLATE latin1_bin .. COLLATE uca1400_as_ci > + */ > + > + const LEX_CSTRING context_cl_name= cl.collation_name_context_suffix(); > + if (!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))) Like above, better DBUG_ASSERT(!strncasecmp(context_cl_name.str, STRING_WITH_LEN("uca1400_"))) > + { > + CHARSET_INFO *tmp; > + Charset_loader_mysys loader; > + if (!(tmp= loader.get_contextually_typed_collation_or_error(m_ci, > + context_cl_name.str))) > + return true; > + m_with_collate= true; > + m_ci= tmp; > + return false; > + } > + > + DBUG_ASSERT(0); // Not possible yet > + return false; > + } > } > - *this= cl; > + DBUG_ASSERT(0); > return false; > } > > diff --git a/strings/ctype-uca.c b/strings/ctype-uca.c > index b89916f3b20..3e6b4e4ce43 100644 > --- a/strings/ctype-uca.c > +++ b/strings/ctype-uca.c > @@ -30542,7 +30613,7 @@ static const char vietnamese[]= > Myanmar, according to CLDR Revision 8900. > http://unicode.org/cldr/trac/browser/trunk/common/collation/my.xml > */ > -static const char myanmar[]= "[shift-after-method expand][version 5.2.0]" > +static const char myanmar[]= "[shift-after-method expand]" What's going on with myanmar? You removed a version here and added &my_uca_v520 below in its charset_info_st. What does this change mean? > /* Tones */ > "&\\u108C" > "<\\u1037" > @@ -37627,7 +37825,7 @@ struct charset_info_st my_charset_utf32_myanmar_uca_ci= > NULL, /* to_lower */ > NULL, /* to_upper */ > NULL, /* sort_order */ > - NULL, /* uca */ > + &my_uca_v520, /* uca */ What does this change? > NULL, /* tab_to_uni */ > NULL, /* tab_from_uni */ > &my_unicase_unicode520,/* caseinfo */ Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

[Maria-developers] Review #3 for: MDEV-26278 Elimination: consider GROUP BY as unique key
by Sergey Petrunia 13 Jun '22

13 Jun '22

Hi Oleg, Please fix compile errors on this platform: https://buildbot.mariadb.org/#/builders/146/builds/13126/steps/5/logs/stdio also please apply the attached diff that fixes the coding style and wording of comments (input and/or adjustments are welcome). After this, the patch is ok to push. The next steps are to create a branch with name like preview-10.10-mdev26278-table-elimination and to officially submit it for preview tree build and testing. > commit 7275bf4939248959b5017314d6b0393296831c0c (HEAD, origin/bb-10.9-MDEV-26278-look) > Author: Oleg Smirnov <olernov(a)gmail.com> > Date: Thu Feb 17 22:53:37 2022 +0700 > > MDEV-26278 Add functionality to eliminate derived tables from the query > > Elimination of unnecessary tables from SQL queries is already present > in MariaDB. But it only works for regular tables and not for derived ones. > > Imagine we have a view: > CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b > > Due to "GROUP BY a, b" the values of combinations {a, b} are unique, > and this fact can be treated as like derived table "v1" has a unique key > on fields {a, b}. > > Suppose we have a SQL query: > SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b > > 1. Since {v1.a, v1.b} is unique and both these fields are bound to t2, > "v1" is functionally dependent on t2. > This means every record of "t2" will be either joined with > a single record of "v1" or NULL-complemented. > 2. No fields of "v1" are present on the SELECT list > > These two facts allow the server to completely exclude (eliminate) > the derived table "v1" from the query. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

2 1

Re: [Maria-developers] 8c2c0e6079a: MDEV-27265 Improve contraction performance in UCA collations
by Sergei Golubchik 11 Jun '22

11 Jun '22

Hi, Alexander, Why haven't you used HASH? On Jun 11, Alexander Barkov wrote: > revision-id: 8c2c0e6079a (mariadb-10.9.1-13-g8c2c0e6079a) > parent(s): 27fa5c10eff > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-05-26 13:17:34 +0400 > message: > > 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. > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0