- developers - lists.mariadb.org

Re: [Maria-developers] Review request for MDEV-12325 Unexpected data type and truncation when using CTE
by Alexander Barkov 06 Aug '22

06 Aug '22

Hello Monty. The patch looks OK for me. I have just one question below: On 8/5/22 7:15 PM, Michael Widenius wrote: > Hi! > > Just to make things easy, here is the patch: > commit a556c4d972eaa09bb8ad586b9bda6cfb2a7b574a > Author: Monty <monty(a)mariadb.org> > Date: Fri Aug 5 17:57:27 2022 +0300 > > MDEV-12325 Unexpected data type and truncation when using CTE > > When creating a recursive CTE, the column types are taken from the > non recursive part of the CTE (this is according to the SQL standard). > > This patch adds code to abort the CTE if the calculated values in the > recursive part does not fit in the fields in the created temporary table. > > The new code only affects recursive CTE, so it should not cause any notable > problems for old applications. > <cut> > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index 48d8c16db68..d271a919a4e 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -111,6 +111,8 @@ int select_unit::send_data(List<Item> &values) > { > int rc; > int not_reported_error= 0; > + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; > + > if (unit->offset_limit_cnt) > { // using limit offset,count > unit->offset_limit_cnt--; > @@ -118,8 +120,10 @@ int select_unit::send_data(List<Item> &values) > } > if (thd->killed == ABORT_QUERY) > return 0; > + > if (table->no_rows_with_nulls) > table->null_catch_flags= CHECK_ROW_FOR_NULLS_TO_REJECT; > + > if (intersect_mark) > { > fill_record(thd, table, table->field + 1, values, TRUE, FALSE); > @@ -127,6 +131,8 @@ int select_unit::send_data(List<Item> &values) > } > else > fill_record(thd, table, table->field, values, TRUE, FALSE); > + thd->count_cuted_fields= save_count_cuted_fields; > + > if (unlikely(thd->is_error())) > { > rc= 1; > @@ -297,7 +303,13 @@ bool select_unit::send_eof() > Why do you need above changes in select_unit::send_data()? Shouldn't it be enough just to have below changes in select_union_recursive::send_data()? > int select_union_recursive::send_data(List<Item> &values) > { > + Abort_on_warning_instant_set handle_abort(thd, 1); > + enum_check_fields save_count_cuted_fields= thd->count_cuted_fields; > + > + /* For recursive CTE's give warnings for wrong field info */ > + thd->count_cuted_fields= CHECK_FIELD_WARN; > int rc= select_unit::send_data(values); > + thd->count_cuted_fields= save_count_cuted_fields; > > if (rc == 0 && > write_err != HA_ERR_FOUND_DUPP_KEY && <cut>

1 1

Re: [Maria-developers] a507f126b03: MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability
by Sergei Golubchik 05 Aug '22

05 Aug '22

Hi, Alexander, a thought about the error message text and a couple of questions about changes in results, please, see below: On Aug 05, Alexander Barkov wrote: > revision-id: a507f126b03 (mariadb-10.7.4-39-ga507f126b03) > parent(s): 97d16c7544c > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-08-05 09:42:46 +0400 > message: > > MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability > > 1. Store assignment failures on incompatible data types now cause errors if: > - STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and > - IGNORE is not used > > Otherwise, only a warning is raised and the statement continues. > > 2. Changing the error/warning test as follows: > > -ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET' > +ERROR HY000: Unknown CAST(int AS inet6) in assignment of 'target' Better not to write 'CAST(int AS inet6)' if there's no explicit CAST() function, I think it might be confusing. I'd say Cannot cast int as inet6 in assignment of 'target' > so in case of a big table it's easier to see which column has the problem. > The new error text is aslo applied to SP variables. s/aslo/also/ > diff --git a/mysql-test/main/sp-vars.result b/mysql-test/main/sp-vars.result > index 127a0993e2b..feef67ccb1d 100644 > --- a/mysql-test/main/sp-vars.result > +++ b/mysql-test/main/sp-vars.result > @@ -1026,11 +1026,11 @@ BEGIN > SELECT arg; > END| > CALL p1((1, 2)); > -ERROR HY000: Illegal parameter data types tinyint and row for operation 'SET' > +ERROR 21000: Operand should contain 1 column(s) Why not "Unknown CAST" like elsewhere? > diff --git a/mysql-test/main/type_geometry_mix_int.result b/mysql-test/main/type_geometry_mix_int.result > index e5d00c63727..0d0a21a8d29 100644 > --- a/mysql-test/main/type_geometry_mix_int.result > +++ b/mysql-test/main/type_geometry_mix_int.result > @@ -6,6 +6,7 @@ > # > CREATE TABLE t1 (target GEOMETRY DEFAULT POINT(1,1), source INT DEFAULT 0); > # Start of type_store_assignment_incompatible.inc > +SET @sql_mode_save= (SELECT @@sql_mode); strange, why not `SET @sql_mode_save= @@sql_mode` ? > SET @source_type= (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS > WHERE COLUMN_NAME='source' > AND TABLE_NAME='t1' > @@ -47,6 +52,7 @@ INSERT INTO t3 VALUES > (2, > (SELECT source FROM t2 ORDER BY id LIMIT 1), > (SELECT source FROM t2 ORDER BY id LIMIT 1)); > +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field What's that? It wasn't an error, and now it is? > SELECT * FROM v3; > id target source > 1 POINT(1 1) 0 Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 2

Re: [Maria-developers] f3fc58149dd: MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability
by Sergei Golubchik 05 Aug '22

05 Aug '22

Hi, Alexander, A couple of comments: On Aug 03, Alexander Barkov wrote: > revision-id: f3fc58149dd (mariadb-10.7.4-39-gf3fc58149dd) > parent(s): 97d16c7544c > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-08-03 11:28:31 +0400 > message: > > MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability > > Store assignment failures on incompatible data types now cause errors if: > - STRICT_ALL_TABLES or STRICT_TRANS_TABLES sql_mode is used, and > - IGNORE is not used > > Otherwise, only a warning is raised and the statement continues. > > TODO: add MTR tests for "loose" modes. > > diff --git a/sql/field.cc b/sql/field.cc > index 249269a6b1d..e997e2cbd69 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -964,7 +964,7 @@ Type_handler::aggregate_for_result_traditional(const Type_handler *a, > } > > > -bool Field::check_assignability_from(const Type_handler *from) const > +bool Field::check_assignability_from(const Type_handler *from, bool error) const "error" is very confusing here, I had to read the whole patch to understand what it means. Better names: * only_warning * ignore * strict * issue_error > { > /* > Using type_handler_for_item_field() here to get the data type handler > @@ -982,6 +982,15 @@ bool Field::check_assignability_from(const Type_handler *from) const > type_handler_for_item_field()); > if (th.aggregate_for_result(from->type_handler_for_item_field())) > { > + if (!error) > + { > + THD *thd= get_thd(); > + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > + ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, > + ER_THD(thd, ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION), > + type_handler()->name().ptr(), from->name().ptr(), "SET"); > + return false; > + } > my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0), > type_handler()->name().ptr(), from->name().ptr(), "SET"); > return true; if you wouldn't want to change the error message in a followup commit, I'd suggested something like my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(error ? 0 : ME_WARNING), type_handler()->name().ptr(), from->name().ptr(), "SET"); return error; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

[Maria-developers] Review: MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently
by Alexander Barkov 01 Aug '22

01 Aug '22

Hello Sergei, I have a couple of suggestions: > commit 7b8304045272111a6f4d44196d6b37cbfef06f37 > Author: Sergei Golubchik <serg(a)mariadb.org> > Date: Wed Jul 20 17:31:48 2022 +0200 > > MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently > > move the OPTION_EXPLICIT_DEF_TIMESTAMP check from the parsing step > to the execution > <cut> > > diff --git a/sql/field.cc b/sql/field.cc > index 2e0c70d3d13..23ebb07b7f7 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -10824,6 +10824,7 @@ Column_definition::Column_definition(THD *thd, Field *old_field, > comment= old_field->comment; > vcol_info= old_field->vcol_info; > option_list= old_field->option_list; > + explicitly_nullable= !(old_field->flags & NOT_NULL_FLAG); This introduces asymmetry in how NULL and NOT NULL attributes are handled. I suggest considering these ways: 1. Change "bool explicitly_nullable" to "bool explicit_nullability", which will be: - false if nothing was specified - true if NULL or NOT NULL was specified. 2. Or don't store NOT_NULL_FLAG in Column_definition::flags at all. Add a new Column_definition enum member with three values for: - not specified - explicit NULL - explicit NOT NULL As discussed on slack, the latter will need some changes. But I think not that much, as only Column_definition needs changes, while Field does not seem to need at this point. > compression_method_ptr= 0; > versioning= VERSIONING_NOT_SET; > invisible= old_field->invisible; > diff --git a/sql/field.h b/sql/field.h > index 5d5be5204cd..87a30bd9f95 100644 > --- a/sql/field.h > +++ b/sql/field.h > @@ -5281,7 +5281,7 @@ class Column_definition: public Sql_alloc, > uint flags, pack_length; > List<String> interval_list; > engine_option_value *option_list; > - > + bool explicitly_nullable; > > /* > This is additinal data provided for any computed(virtual) field. > @@ -5303,7 +5303,7 @@ class Column_definition: public Sql_alloc, > comment(null_clex_str), > on_update(NULL), invisible(VISIBLE), char_length(0), > flags(0), pack_length(0), > - option_list(NULL), > + option_list(NULL), explicitly_nullable(false), > vcol_info(0), default_value(0), check_constraint(0), > versioning(VERSIONING_NOT_SET), period(NULL) > { > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 3eeabe20c71..8c31f988a05 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -2316,6 +2316,8 @@ void promote_first_timestamp_column(List<Create_field> *column_definitions) > if (column_definition.is_timestamp_type() || // TIMESTAMP > column_definition.unireg_check == Field::TIMESTAMP_OLD_FIELD) // Legacy > { > + if (!column_definition.explicitly_nullable) > + column_definition.flags|= NOT_NULL_FLAG; I think this does not work well with PS. On the very first execution the field becomes NOT NULL forever, if the current OPTION_EXPLICIT_DEF_TIMESTAMP says so. > DBUG_PRINT("info", ("field-ptr:%p", column_definition.field)); > if (first && > (column_definition.flags & NOT_NULL_FLAG) != 0 && // NOT NULL, > diff --git a/sql/sql_type.cc b/sql/sql_type.cc > index 44a51428280..dc0c0b1e0cf 100644 > --- a/sql/sql_type.cc > +++ b/sql/sql_type.cc > @@ -4258,18 +4258,6 @@ void Type_handler_temporal_with_date::Item_update_null_value(Item *item) const > (void) item->get_date(thd, &ltime, Datetime::Options(thd)); > } > > -bool > -Type_handler_timestamp_common:: > -Column_definition_set_attributes(THD *thd, > - Column_definition *def, > - const Lex_field_type_st &attr, > - column_definition_type_t type) const > -{ > - Type_handler::Column_definition_set_attributes(thd, def, attr, type); > - if (!(thd->variables.option_bits & OPTION_EXPLICIT_DEF_TIMESTAMP)) > - def->flags|= NOT_NULL_FLAG; > - return false; > - > > void Type_handler_string_result::Item_update_null_value(Item *item) const > { > diff --git a/sql/sql_type.h b/sql/sql_type.h > index 7ff4bc64679..e5f54535ece 100644 > --- a/sql/sql_type.h > +++ b/sql/sql_type.h > @@ -6655,11 +6655,6 @@ class Type_handler_timestamp_common: public Type_handler_temporal_with_date > bool Item_func_min_max_get_date(THD *thd, Item_func_min_max*, > MYSQL_TIME *, date_mode_t fuzzydate) > const override; > - bool Column_definition_set_attributes(THD *thd, > - Column_definition *def, > - const Lex_field_type_st &attr, > - column_definition_type_t type) > - const override; > }; > > > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 5416cec49b5..f326d8c0cc5 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -5831,7 +5831,6 @@ field_def: > | opt_generated_always AS virtual_column_func > { > Lex->last_field->vcol_info= $3; > - Lex->last_field->flags&= ~NOT_NULL_FLAG; // undo automatic NOT NULL for timestamps > } > vcol_opt_specifier vcol_opt_attribute > { > @@ -6313,7 +6312,12 @@ attribute_list: > ; > > attribute: > - NULL_SYM { Lex->last_field->flags&= ~ NOT_NULL_FLAG; $$.init(); } > + NULL_SYM > + { > + Lex->last_field->flags&= ~NOT_NULL_FLAG; > + Lex->last_field->explicitly_nullable= true; > + $$.init(); > + } > | DEFAULT column_default_expr { Lex->last_field->default_value= $2; $$.init(); } > | ON UPDATE_SYM NOW_SYM opt_default_time_precision > {

3 3

[Maria-developers] review for (MDEV-28632) bugfix: DEFAULT NULL was allowed for NOT NULL columns
by Alexander Barkov 29 Jul '22

29 Jul '22

Hello Sergei, > commit 5a362d486b30fdaf3c7a360737331767154b4ee8 > Author: Sergei Golubchik <serg(a)mariadb.org> > Date: Mon Jul 18 22:53:27 2022 +0200 > > bugfix: DEFAULT NULL was allowed for NOT NULL columns > > this was detected during parsing (so NOT NULL DEFAULT NULL was an error), > but if a column was made NOT NULL later (e.g. as a part of primary key, > in mysql_prepare_create_table()), DEFAULT NULL was accepted and ignored. > > to fix this the NOT NULL DEFAULT NULL was moved into > mysql_prepare_create_table(). I'm not sure that in case of auto_increment we should really disallow DEFAULT NULL under terms of this TIMESTAMP task. Look at this script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1 VALUES (NULL); Query OK, 1 row affected (0.012 sec) NULL can be inserted without errors. Yes, it's inserted with a side effect - it's replaced to the next auto increment value. But why this side effect makes NULL an invalid default? Note, I'm not against this change. Stricter behavior is good :) But I'm worried that: - It can break compatibility on one hand. - It goes without it's own MDEV on the other hand. This change deserves it's own MDEV (with motivation and consequences explained) and should be properly documented and mentioned in release notes. <cut> > index 712b8629c09..d8471894db9 100644 > --- a/mysql-test/main/view.result > +++ b/mysql-test/main/view.result > @@ -4082,12 +4082,12 @@ DROP TABLE t1,t2; > # MDEV-6251: SIGSEGV in query optimizer (in set_check_materialized > # with MERGE view) > # > -CREATE TABLE t1 (a1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t2 (b1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t3 (c1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t4 (d1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t5 (e1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > -CREATE TABLE t6 (f1 INT(11) NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t1 (a1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t2 (b1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t3 (c1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t4 (d1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t5 (e1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); > +CREATE TABLE t6 (f1 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY); It seems you removed all cases with the old syntax. So now it's not recorded in MTR what happens on this statement: CREATE TABLE t1 (a INT NOT NULL DEFAULT NULL AUTO_INCREMENT PRIMARY KEY); Please cover this scenario in some relevant *.test file. <cut> > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index cf0bbfb1130..3eeabe20c71 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc <cut> > @@ -3647,6 +3643,15 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, > { > Field::utype type= (Field::utype) MTYP_TYPENR(sql_field->unireg_check); > > + if (sql_field->default_value && > + sql_field->default_value->expr->type() == Item::NULL_ITEM && > + sql_field->flags & NOT_NULL_FLAG && > + !(sql_field->flags & AUTO_INCREMENT_FLAG)) > + { > + my_error(ER_INVALID_DEFAULT, MYF(0), sql_field->field_name.str); > + DBUG_RETURN(TRUE); > + } ^^^ now obvious errors are caught only during EXECUTE, although it's already clear at the PREPARE stage that the DEFAULT is wrong: MariaDB [test]> PREPARE stmt FROM 'CREATE TABLE T1 (a INT NOT NULL DEFAULT NULL)'; Query OK, 0 rows affected (0.000 sec) Statement prepared MariaDB [test]> EXECUTE stmt; ERROR 1067 (42000): Invalid default value for 'a' I suggest we still catch non-unambiguous cases (when nothing depends on system variables) during PREPARE. Inside some existing or a new Type_handler method. Also, please add PS tests: - for unambiguous scenarios like here - for ambiguous scenarios, e.g. with TIMESTAMP columns changing OPTION_EXPLICIT_DEF_TIMESTAMP between PREPARE and EXECUTE. It seems this code needs to be changed: > /* > Set NO_DEFAULT_VALUE_FLAG if this field doesn't have a default value and > it is NOT NULL, not an AUTO_INCREMENT field, not a TIMESTAMP and not > updated trough a NOW() function. > */ > if (!sql_field->default_value && > !sql_field->has_default_function() && > (sql_field->flags & NOT_NULL_FLAG) && > (!sql_field->is_timestamp_type() || > (thd->variables.option_bits & OPTION_EXPLICIT_DEF_TIMESTAMP))&& > !sql_field->vers_sys_field()) > { > sql_field->flags|= NO_DEFAULT_VALUE_FLAG; > sql_field->pack_flag|= FIELDFLAG_NO_DEFAULT; > } It can only add NO DEFAULT related flags. Shouldn't it also remove NO DEFAULT flags if the current PS EXECUTE time option OPTION_EXPLICIT_DEF_TIMESTAMP is unset? <cut> > diff --git a/storage/spider/mysql-test/spider/bg/t/spider_fixes.test b/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > index b222f494ba1..41f87e61416 100644 > --- a/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > +++ b/storage/spider/mysql-test/spider/bg/t/spider_fixes.test > @@ -294,14 +294,14 @@ if ($USE_CHILD_GROUP2) > --disable_query_log > echo CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1; > eval CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT5_2_1; > --enable_query_log Why this change ^^^ ? > diff --git a/storage/spider/mysql-test/spider/t/spider_fixes.test b/storage/spider/mysql-test/spider/t/spider_fixes.test > index 47bc225d614..75f108f1164 100644 > --- a/storage/spider/mysql-test/spider/t/spider_fixes.test > +++ b/storage/spider/mysql-test/spider/t/spider_fixes.test > @@ -294,14 +294,14 @@ if ($USE_CHILD_GROUP2) > --disable_query_log > echo CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1; > eval CREATE TABLE ta_l ( > a int(11) NOT NULL DEFAULT '0', > - b char(1) DEFAULT NULL, > - c datetime DEFAULT NULL, > + b char(1), > + c datetime, > PRIMARY KEY (a, b, c) > ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT5_2_1; > --enable_query_log Same question.

2 2

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