- developers - lists.mariadb.org

Re: [Maria-developers] 1c3182a99e5: MDEV-5215 prerequisite of prerequisite: if DB is not mentioned in connect ignore errors of switching to it
by Sergei Golubchik 25 Aug '22

25 Aug '22

Hi, Oleksandr, On Aug 25, Oleksandr Byelkin wrote: > revision-id: 1c3182a99e5 (mariadb-10.6.1-482-g1c3182a99e5) > parent(s): 7253cdf8928 > author: Oleksandr Byelkin > committer: Oleksandr Byelkin > timestamp: 2022-08-16 11:49:41 +0200 > message: > > MDEV-5215 prerequisite of prerequisite: if DB is not mentioned in connect ignore errors of switching to it > > diff --git a/mysql-test/main/init_connect.test b/mysql-test/main/init_connect.test > index e96d02fe0d1..60549c3458e 100644 > --- a/mysql-test/main/init_connect.test > +++ b/mysql-test/main/init_connect.test > @@ -10,31 +10,31 @@ > > --source include/add_anonymous_users.inc > > -connect (con0,localhost,root,,); > +connect (con0,localhost,root,,test); why did you specify a db explicitly (here and in many other places)? I thought the whole point was to modify mysqltest in a way that would avoid the need of changing tests. > connection con0; > select hex(@a); Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] b3844107287: MDEV-16546 System versioning setting to allow history modification
by Sergei Golubchik 24 Aug '22

24 Aug '22

Hi, Aleksey! On Jun 08, Aleksey Midenkov wrote: > revision-id: b3844107287 (mariadb-10.5.2-424-gb3844107287) > parent(s): 27d66d644cf > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2021-03-01 17:43:34 +0300 > message: > > MDEV-16546 System versioning setting to allow history modification > > 1. force_fields_visible session variable makes system-invisible and > user-invisible fields visible on next table open. FLUSH TABLES > required for already opened tables. * system_versioning_insert_history * allows pseudocolumns ROW_START and ROW_END be specified in INSERT * doesn't affect invisible columns * FLUSH TABLES requirememnt is rather confusing, but let's have it, at least until I could suggest something better > 2. If secure_timestamp allows to modify timestamp variable then > following DML commands: INSERT, INSERT..SELECT and LOAD DATA can > specify row_start and row_end system field values. when system_versioning_insert_history=1 > 3. Cleaned up select_insert::send_data() from setting vers_write as > this parameter is now set on TABLE initialization. I don't see where it's set. This commit removes two table->vers_write= table->versioned(); and adds one from_field->table->vers_write= false; in Item_field::fix_fields. But where is vers_write set to true? Was it done in some other commit? > diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result > index 2645d0184e8..00d1bb705c4 100644 > --- a/mysql-test/suite/versioning/r/insert.result > +++ b/mysql-test/suite/versioning/r/insert.result > @@ -112,3 +112,95 @@ x y > 9 9001 > drop table t1; > drop table t2; > +# > +# MDEV-16546 System versioning setting to allow history modification > +# > +# restart: --secure-timestamp=NO > +set @@session.time_zone='+00:00'; > +create table t1(x int) with system versioning; > +insert into t1(x) values (1); > +insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); > +ERROR 42S22: Unknown column 'row_start' in 'field list' > +set session force_fields_visible= 1; > +flush tables; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `x` int(11) DEFAULT NULL, > + `row_start` timestamp(6) GENERATED ALWAYS AS ROW START, > + `row_end` timestamp(6) GENERATED ALWAYS AS ROW END, shouldn't be visible here, only in insert and load. > + PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) > +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING > +insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01'); please, add tests for insert into t1 values (4) this should work, row_start/row_end must be mentioned explicitly. Also insert into t1 set x=5, row_start=..., row_end=... this should work too. And when only one row_start or row_end is mentioned as well. But update t1 set row_start=... insert into t1 (x, row_start, row_end) values (...) on duplicate key update row_start=... this should fail. While replace load data ... replace should work, but as DELETE+INSERT (I mean that DELETE in versioned tables doesn't actually delete history). If that's too difficult, then an error too. > +update t1 set x= x + 1; > +select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x; ... > diff --git a/mysql-test/suite/versioning/t/insert.opt b/mysql-test/suite/versioning/t/insert.opt > new file mode 100644 > index 00000000000..a74d68957ef > --- /dev/null > +++ b/mysql-test/suite/versioning/t/insert.opt > @@ -0,0 +1 @@ > +--secure-timestamp=yes why is this? > -- source suite/versioning/common_finish.inc > diff --git a/mysql-test/suite/versioning/t/insert2.opt b/mysql-test/suite/versioning/t/insert2.opt > new file mode 100644 > index 00000000000..a74d68957ef > --- /dev/null > +++ b/mysql-test/suite/versioning/t/insert2.opt > @@ -0,0 +1 @@ > +--secure-timestamp=yes and this? > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index d23b190b2c5..93f8a8434e4 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -7604,6 +7604,20 @@ void THD::set_last_commit_gtid(rpl_gtid &gtid) > #endif > } > > +bool THD::vers_modify_sys_field() const don't call it vers_modify_sys_field, your previous name vers_modify_history was better. There may be other sys fields that will never be directly writable (like ROWID, for example). > +{ > + if (lex->sql_command != SQLCOM_INSERT && > + lex->sql_command != SQLCOM_INSERT_SELECT && > + lex->sql_command != SQLCOM_LOAD) > + return false; I don't think this is enough to reject insert ... on duplicate key update row_start=xxx > + if (opt_secure_timestamp >= SECTIME_REPL || this is fixed in a later commit, so ok > + (opt_secure_timestamp == SECTIME_SUPER && > + !(security_ctx->master_access & SUPER_ACL))) > + return false; > + return true; > +} > + > + > void > wait_for_commit::reinit() > { > diff --git a/sql/sql_class.h b/sql/sql_class.h > index 2913a4a8350..520ea1d63bf 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -820,6 +820,7 @@ typedef struct system_variables > > vers_asof_timestamp_t vers_asof_timestamp; > ulong vers_alter_history; > + my_bool force_fields_visible; system_versioning_insert_history > } SV; > > /** > @@ -5400,6 +5401,7 @@ class THD: public THD_count, /* this must be first */ > Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); > bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); > > + bool vers_modify_sys_field() const; vers_modify_history > }; > > > diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc > index f0b84ab126f..84c2ae4ab36 100644 > --- a/sql/sql_insert.cc > +++ b/sql/sql_insert.cc > @@ -2014,7 +2014,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) > !table->file->referenced_by_foreign_key() && > (!table->triggers || !table->triggers->has_delete_triggers())) > { > - if (table->versioned(VERS_TRX_ID)) > + if (table->versioned(VERS_TRX_ID) && table->vers_write) does it even make sense? I mean direct insertion of transaction ids. > { > bitmap_set_bit(table->write_set, table->vers_start_field()->field_index); > table->file->column_bitmaps_signal(); > @@ -2029,7 +2029,7 @@ int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) > info->deleted++; > if (!table->file->has_transactions()) > thd->transaction->stmt.modified_non_trans_table= TRUE; > - if (table->versioned(VERS_TIMESTAMP)) > + if (table->versioned(VERS_TIMESTAMP) && table->vers_write) This (and the previous hunk) are in the DUP_REPLACE branch. There should be tests for these changes. > { > store_record(table, record[2]); > error= vers_insert_history_row(table); > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index f817f9086a9..4429308a097 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -6737,3 +6737,7 @@ static Sys_var_ulonglong Sys_max_rowid_filter_size( > SESSION_VAR(max_rowid_filter_size), CMD_LINE(REQUIRED_ARG), > VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(128*1024), > BLOCK_SIZE(1)); > + > +static Sys_var_mybool Sys_force_fields_visible( > + "force_fields_visible", "Make invisible fields visible on next table open", "system_versioning_insert_history" "Allows direct inserts into ROW_START and ROW_END columns" > + SESSION_VAR(force_fields_visible), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); > diff --git a/sql/table.cc b/sql/table.cc > index f4bdbdeac5a..520f3738c9e 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -4024,6 +4024,9 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, > { > if (!((*field_ptr)= share->field[i]->clone(&outparam->mem_root, outparam))) > goto err; > + if (thd->variables.force_fields_visible && > + (*field_ptr)->invisible <= INVISIBLE_SYSTEM) > + (*field_ptr)->invisible= VISIBLE; Not really, system_versioning_insert_history should not affect visibility of anything besides ROW_START and ROW_END pseudocolumns in the INSERT and LOAD DATA statements. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 5

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