- developers - lists.mariadb.org

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

13 Jul '22

Andrei Elkin <andrei.elkin(a)mariadb.com> writes: > Kristian, salve! Hi Andrei, thanks for interesting comments and discussion! >> 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 > > Well, the circularity is not necessary for (4). The ex-master just needs > to be configured as slave. Right, I see now. > Also let me call in the following this stage of receiving/accepting own > events as 'slave recovery'. But thinking more, there's another point that I don't understand, around the use of --tc-heuristic-recover=rollback. From memory, and also from reading docs on https://mariadb.com/kb/en/heuristic-recovery-with-the-transaction-coordinat…, this will forcibly roll back any prepared-but-not-committed InnoDB transaction, but _not_ roll back any corresponding binlog entry. Doesn't this leave the binlog inconsistent with the storage engine? Of the last few transactions in the binlog, some may be in InnoDB, some may not. And if so, doesn't this make the binlog useless for deciding which of our own transactions to ignore? When the old-master-now-slave gets one of its own events that's also in the binlog, we don't know if it is in the engine. If we apply it, we may end up with it double. If we do not apply it, we may be missing it. Is there some mechanims used in this feature/MDEV that handles this correctly that I'm missing? >> 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)? > > That's how it should normally do, yet it's an optimization (to save on > traffic) part of the solution. Interesting. Can you elaborate on this optimization - how does it save on traffic? > In the worst case `gtid_slave_pos` can be even empty for the > ex-master's domain, so the ex-master slave might keep receiving a bulk > of own events, only to ignore the most of them. > And that's the patch's base code logics as well. I guess there's something here that I'm not understanding. If MASTER_DEMOTE_TO_SLAVE is used to copy the gtid_binlog_pos into the gtid_slave_pos, how can the gtid_slave_pos be empty for the ex-master's domain? > ... , `gtid_ignore_duplicate = OFF` would have to go in my perception. > And if it goes, we'd remain with the only "choice" of > `gtid_ignore_duplicate = ON` :-) so the option would be gone. Hm. So are you suggesting that the current behaviour when --gtid-ignore-duplicates=ON becomes the default behaviour - in fact the only behaviour? As I remember it, the --gtid-ignore-duplicates=ON option is a very dangerous option unless the servers are managed very strictly and correctly. In fact I was expecting to find a dire warning written by myself about this, but didn't find anything in a quick web search ;-) I think the main problem is if an incorrect transaction done on a slave somewhere introduces an out-of-order GTID sequence, Then the replication will silently ignore potentially a large number of transactions that have their sequence number smaller than the incorrect out-of-order transaction. Out-of-order will normally be an error. But mistakes happen, and it is bad if this causes replication to become silently corrupt, as opposed to flagging the error (assuming --gtid-strict-mode=ON). What do you think? Again, it's cool that things are being worked on in MariaDB GTID, and I hope my comments don't come out as purely negative. While MariaDB/MySQL replication is in some ways overly complex, this is also what gives it the enormous flexibility for advanced users to do crazy^H^H^H^H^Hfantastic stuff! So there's some leeway for allowing complexity. But there are also some very delicate pitfalls that the original design tried hard to avoid, with decisions that may not always be obvious. So anything I can do there to help with history and design principles, I'm happy to assist with... - Kristian.

1 0

Re: [Maria-developers] ce5cc8fb905: MDEV-29021 ALTER TABLE fails when a stored virtual column is dropped+added
by Sergei Golubchik 13 Jul '22

13 Jul '22

Hi, Nikita, This is good, but I think fill_extra_persistent_columns() shouldn't be used at all. It doesn't handle default values. I've changed rpl_alter_extra_persistent test as -alter table t1 add column z1 int as(a+1) virtual, add column z2 int as (a+2) persistent; +alter table t1 add column z1 int as(a+1) virtual, add column z2 int default (a+2); and, of course, new column z2 wasn't updated properly. An easy fix would be to use your code in all cases and remove fill_extra_persistent_columns(). Could you do that too, please? And add the test case too. May be just, append , add column z3 int default (a+2) On Jul 07, Nikita Malyavin wrote: > revision-id: ce5cc8fb905 (mariadb-10.6.1-506-gce5cc8fb905) > parent(s): 49ad875902e > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2022-07-04 15:55:03 +0300 > message: > > MDEV-29021 ALTER TABLE fails when a stored virtual column is dropped+added > > We shouldn't rely on `fill_extra_persistent_columns`, as it only updates > fields which have an index > cols->n_bits (replication bitmap width). > > Normal update_virtual_fields+update_default_fields shoudl be done. > > diff --git a/sql/rpl_record.cc b/sql/rpl_record.cc > index 17731e64600..42f476fc112 100644 > --- a/sql/rpl_record.cc > +++ b/sql/rpl_record.cc > @@ -412,6 +412,26 @@ int unpack_row(rpl_group_info *rgi, TABLE *table, uint const colcnt, > { > copy->do_copy(copy); > } > + if (table->default_field) > + { > + error= table->update_default_fields(table->in_use->lex->ignore); > + if (unlikely(error)) > + DBUG_RETURN(error); > + } > + if (table->vfield) > + { > + error= table->update_virtual_fields(table->file, VCOL_UPDATE_FOR_WRITE); > + if (unlikely(error)) > + DBUG_RETURN(error); > + } > + } > + else > + { > + /* > + Add Extra slave persistent columns > + */ > + if (unlikely(error= fill_extra_persistent_columns(table, cols->n_bits))) > + DBUG_RETURN(error); > } > > /* > @@ -439,12 +459,6 @@ int unpack_row(rpl_group_info *rgi, TABLE *table, uint const colcnt, > } > } > > - /* > - Add Extra slave persistent columns > - */ > - if (unlikely(error= fill_extra_persistent_columns(table, cols->n_bits))) > - DBUG_RETURN(error); > - > /* > We should now have read all the null bytes, otherwise something is > really wrong. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 4

Re: [Maria-developers] 3e2d297830b: MDEV-29013 ER_KEY_NOT_FOUND/lock timeout upon online alter with long unique
by Sergei Golubchik 13 Jul '22

13 Jul '22

Hi, Nikita, On Jul 08, Nikita Malyavin wrote: > revision-id: 3e2d297830b (mariadb-10.6.1-511-g3e2d297830b) > parent(s): c1feb8c3c32 > author: Nikita Malyavin > committer: Nikita Malyavin > timestamp: 2022-07-05 15:20:04 +0300 > message: > > MDEV-29013 ER_KEY_NOT_FOUND/lock timeout upon online alter with long unique > > 1. ER_KEY_NOT_FOUND > Some virtual columns were not updated because were not included in read_set > to the moment of calculation. > > In Row_logs_event::do_apply_event some fields are excluded based on m_cols > value, the number of replicated rows. the number of replicated *columns* > We can't rely on this. Basically, we'd be satisfied, if all columns were > just set, at least for now. > > 2. ER_LOCK_WAIT_TIMEOUT > This is a long unique specific problem. > > Sometimes, lookup_handler is created for to->file. To properly free it, > ha_reset should be called. It is usually done by calling > close_thread_table, but ALTER TABLE makes it differently. Hence, a single > ha_reset call is added to mysql_alter_table. > > Also, the lifetime of lookup_handler is corrected: it's been allocated on > thd->mem_root, which in case of online alter > is event's mem_root, and can vary other ways. > It is changed to table->mem_root, which is more appropriate: > ha_reset is called only when table is closed, or in a few additional cases, > which correspond to a statement end. So memory leaks are unlikely here. > > diff --git a/sql/handler.cc b/sql/handler.cc > index d71c0fc83a3..fbebfd27203 100644 > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -3212,7 +3212,7 @@ int handler::create_lookup_handler() > handler *tmp; > if (lookup_handler != this) > return 0; > - if (!(tmp= clone(table->s->normalized_path.str, table->in_use->mem_root))) > + if (!(tmp= clone(table->s->normalized_path.str, &table->mem_root))) that's wrong, the lifetime of a lookup handler is one statement, it's destroyed at the end. You cannot keep allocating new handlers on the table->mem_root for every statement. > return 1; > lookup_handler= tmp; > return lookup_handler->ha_external_lock(table->in_use, F_RDLCK); > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > index 89ca2a41937..2c19bf67586 100644 > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -6007,6 +6007,11 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > if (m_width == table->s->fields && bitmap_is_set_all(&m_cols)) > set_flags(COMPLETE_ROWS_F); > > + Rpl_table_data rpl_data{}; > + if (rgi) rgi->get_table_data(table, &rpl_data); > + > + if (!rpl_data.copy_fields) > + { > /* > Set tables write and read sets. > > @@ -6027,17 +6027,19 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > bitmap_set_all(table->read_set); > if (get_general_type_code() == DELETE_ROWS_EVENT || > get_general_type_code() == UPDATE_ROWS_EVENT) > bitmap_intersect(table->read_set,&m_cols); > > bitmap_set_all(table->write_set); > > /* WRITE ROWS EVENTS store the bitmap in m_cols instead of m_cols_ai */ > MY_BITMAP *after_image= ((get_general_type_code() == UPDATE_ROWS_EVENT) ? > &m_cols_ai : &m_cols); > bitmap_intersect(table->write_set, after_image); > > this->slave_exec_mode= slave_exec_mode_options; // fix the mode > + } I'm sorry, I don't quite understand. "some fields are excluded based on m_cols value" - what columns were excluded? What was the value of m_cols here? > table->rpl_write_set= table->write_set; > > // Do event specific preparations > error= do_before_row_operations(rli); > @@ -6045,8 +6052,6 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > extra columns on the slave. In that case, do not force > MODE_NO_AUTO_VALUE_ON_ZERO. > */ > - Rpl_table_data rpl_data{}; > - if (rgi) rgi->get_table_data(table, &rpl_data); > sql_mode_t saved_sql_mode= thd->variables.sql_mode; > if (!is_auto_inc_in_extra_columns()) > thd->variables.sql_mode= (rpl_data.copy_fields ? saved_sql_mode : 0) > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 29c4d09cd39..a3efcdd9785 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -11011,6 +11011,9 @@ do_continue:; > !(table->file->ha_table_flags() & HA_REUSES_FILE_NAMES) && > !(new_table->file->ha_table_flags() & > HA_REUSES_FILE_NAMES)); > + > + // Close lookup_handler. > + new_table->file->ha_reset(); please, put it not here, but inside the following thd->drop_temporary_table() call. It'll also fix Sanja's bug MDEV-26433. And please coordinate with him to put it on the same place so that git would detect a conflict and we could merge it to have only one reset call in that method, not two. > /* > Close the intermediate table that will be the new table, but do > not delete it! Even though MERGE tables do not have their children > @@ -11854,8 +11857,11 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to, > > // We'll be filling from->record[0] from row events > bitmap_set_all(from->write_set); > - // We restore bitmaps, because update event is going to mess up with them. > + // Use all columns. UPDATE/DELETE events reset read_set and write_set to > + // def_*_set after each row operation, so using all_set won't work. > to->default_column_bitmaps(); > + bitmap_set_all(&to->def_read_set); > + bitmap_set_all(&to->def_write_set); > > end_read_record(&info); > init_read_record_done= false; > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] 73c4fd41afc: Do not ignore sql_mode when replicating
by Sergei Golubchik 13 Jul '22

13 Jul '22

Hi, Nikita, On Jul 07, Nikita Malyavin wrote: > > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > index aba846453e9..89ca2a41937 100644 > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -6045,9 +6045,12 @@ int Rows_log_event::do_apply_event(rpl_group_info *rgi) > extra columns on the slave. In that case, do not force > MODE_NO_AUTO_VALUE_ON_ZERO. > */ > + Rpl_table_data rpl_data{}; > + if (rgi) rgi->get_table_data(table, &rpl_data); > sql_mode_t saved_sql_mode= thd->variables.sql_mode; > if (!is_auto_inc_in_extra_columns()) > - thd->variables.sql_mode= MODE_NO_AUTO_VALUE_ON_ZERO; > + thd->variables.sql_mode= (rpl_data.copy_fields ? saved_sql_mode : 0) you start using this condition in many places and it's quite cryptic for those, who don't know the code. I'd suggest to add method, like struct Rpl_table_data { ... bool is_online_alter() { return copy_fields != NULL; } ... }; btw, it fits very well in your Rpl_table_data simplification. > + | MODE_NO_AUTO_VALUE_ON_ZERO; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

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

11 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