June 2021 - developers - lists.mariadb.org

Re: [Maria-developers] ef2519fee4e: MDEV-16546 System versioning setting to allow history modification
by Aleksey Midenkov 08 Jun '21

08 Jun '21

Hello, Sergei! On Fri, May 3, 2019 at 8:43 PM Sergei Golubchik <serg(a)mariadb.com> wrote: > > Hi, Aleksey! > > On May 03, Aleksey Midenkov wrote: > > revision-id: ef2519fee4e (versioning-1.0.5-17-gef2519fee4e) > > parent(s): 56145be2951 > > author: Aleksey Midenkov <midenok(a)gmail.com> > > committer: Aleksey Midenkov <midenok(a)gmail.com> > > timestamp: 2018-06-28 13:42:09 +0300 > > message: > > > > MDEV-16546 System versioning setting to allow history modification > > > > 1. Add server variable system_versioning_modify_history which will > > allow to set values for row_start, row_end in DML operations. > > > > 2. If secure_timestamp is YES or REPLICATION, > > system_versioning_modify_history does not have effect. If > > secure_timestamp is SUPER, system_versioning_modify_history requires > > special privilege (same as for setting current timestamp). > > I thought more about this idea. We don't really want to have the history > editable, do we? Well, I'm thinking about rollback table data to specific point in time. That could be a useful feature. > But it's needed for replication, to keep the master and > slave identical. That's what secure_timestamp is for. > > The idea was that this new variable, system_versioning_modify_history, > will be just a convenience feature, it will not allow history editing > any more than one can do without it. > > But now I suspect that even with secure_timestamp=NO one cannot truly > edit history. One can only insert new rows with arbitrary timestamps. > For example, to insert a row with row_start=1000 and row_end=2000, one > needs to do (if secure_timestamp=NO): > > set timestamp=1000; > insert row; > set timestamp=2000; > delete row; > > But I don't see how one can update or delete a history row with > secure_timestamp=NO. > > Now, with a SUPER privilege and secure_timestamp=NO or SUPER, one can > use the BINLOG command and truly edit the history arbitrarily, by faking > row events. I don't really get it why this is so important: since there is some limitation by configuration and privilege, we are just fine. Everything can be changed at filesystem level after all. > > The conclusion, I believe, is that system_versioning_modify_history > should allow INSERTs when secure_timestamp=NO, and it should allow > UPDATE/DELETE only for a SUPER user when secure_timestamp=NO or SUPER. I don't see a reason to argue on that. The only thing that is not clear, why we don't allow INSERTs when secure_timestamp=SUPER? > > The second thing I don't like at all, is when a table is created like > > CREATE TABLE t1 (a int) WITH SYSTEM VERSIONING > > with row_start/row_end implicit. You don't have it in the test, but > anyway one should be able to load history into such a table, while the > table does not have row_start and row_end columns. From the user point > of view these columns don't exist, they're pseudo-columns, like ROWID. > They just cannot be insertable-into, conceptually. But a user will want > to restore the history, right? I don't have a solution for this yet :( > Any ideas? We don't have to follow the conception if it doesn't help us. Since we have physical row_start/row_end, we don't have to pretend they don't exist. Who will win from that? > > See below a couple of minor comments about the patch itself. > > ... These are going to be fixed. > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org -- All the best, Aleksey Midenkov @midenok

2 15

[Maria-developers] Request for Discussion: Structure for JSON histogram objects
by Michael Okoko 04 Jun '21

04 Jun '21

Hi, MariaDB community! I'm implementing the patch for Using JSON as the on-disk format for MariaDB histograms(https://jira.mariadb.org/browse/MDEV-21130). as part of GSoC '21 and hoping to get insights and ideas from the community on how the JSON format for histograms should be structured. We are planning to change the columns of `mysql.column_stats` into something like: CREATE TABLE `column_stats` ( `db_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, `table_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, `column_name` varchar(64) COLLATE utf8mb3_bin NOT NULL, `histogram` blob ); The histogram column above will then be responsible for storing the JSON document for that row. Currently, the JSON structure we are looking at is based on the existing columns in `column_stats` table as well MySQL's JSON histogram implementation( https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html) Here's an example: { "min_value": 0, "max_value": 0, "hist_type": "singleton", "bucket": [ [1,0.3333333333333333], [2, 0.6666666666666666], [3,1] ], "nulls_ratios": 0, "last_updated": "2021-06-02 13:32:40.000000", "sampling_rate": 1, } - The min_value and max_values retain the meanings described at ( https://mariadb.com/kb/en/mysqlcolumn_stats-table/) - hist_type can be one of `SINGLETON` (where the number of buckets is >= the number of distinct values in the specified column) or `EQUI-HEIGHT` (where the number of buckets is < the number of distinct values found in the column). - nulls_ratio is a value between 0.0 and 1.0 indicating the fraction of column values that are SQL NULL values. If 0, the column contains no NULL values. - `last_updated` indicates when the histogram was generated, as a UTC value in YYYY-MM-DD hh:mm:ss.uuuuuu format. - `sampling_rate` is also a value between 0.0 and 1.0 indicating the fraction of column values that were sampled to create the histogram. A value of 1 means that all of the data was read (no sampling).

3 2

Re: [Maria-developers] 557ab2992dd: MDEV-21117 Incremental patch for a few review notes addressed.
by Sergei Golubchik 03 Jun '21

03 Jun '21

Hi, Andrei! On Jun 03, Andrei Elkin wrote: > >> - binlog_hton->commit= binlog_commit; > >> + binlog_hton->commit= binlog_commit_dummy; > >> binlog_hton->rollback= binlog_rollback; > >> binlog_hton->drop_table= [](handlerton *, const char*) { return -1; }; > > > > Oh did you actually need it? Or it's for completeness only? > > I am confused a bit, > 6c52931680a4 (Sergei Golubchik 2020-06-22 1703) binlog_hton->drop_table= [](handlerton *, const char*) { return -1; }; > > And I never reviewed the patch. Grrr. Sorry! When reviewing I diffed this patch with a previous version that I reviewed (a useful speedup technique at the late phase, when commits only change slightly) and this drop_table line was in the new patch and not in the old. Of course, it was because the new patch was in 10.6 and the old in 10.2, there were quite a few changes in the diff because of that. I tried to ignore them, but somehow missed this one. Note, there's no + before the drop_table line, it's not yours, it's indeed something that already was in 10.6, I should've noticed, my mistake :( Why I added it in 6c52931680a4? It was for DROP TABLE FORCE, that simply does plugin_foreach() and tries to hton->drop_table() in every hton available. In binlog_hton too. The goal was to solve cases like a table in InnoDB data dictionary but with no frm, or Aria table where MAD file exists, but no MAI - cases when DROP TABLE doesn't see the table, but CREATE TABLE still fails as some remnants of a table still exist. > >> @@ -9975,6 +9977,154 @@ int TC_LOG::using_heuristic_recover() > >> + else > >> + { > >> + char buf[21]; > >> + my_stat(file_name, &s, MYF(0)); > >> + my_off_t new_size= s.st_size; > > > > do you need it? new_size == pos, isn't it? You yourself use the word > > "size" to describe pos in sql_print_error just few lines above :) > > Sure. Let turn it into an > > assert( pos == new_size ) > > [x] and this is, what I think, you'd call "too much checks" :) I'd personally would've dropped my_stat and new_size, especially as I also suggest to remove them from the sql_print_information below. > >> + > >> + longlong10_to_str(ptr_gtid->seq_no, buf, 10); > >> + sql_print_information("Successfully truncated binlog file:%s " > >> + "to pos:%llu to remove transactions starting from " > >> + "GTID %u-%u-%s; " > >> + "former file size shrunk from %llu to %llu.", > > > > This isn't quite English. Better just "previous (or old) file > > size: %llu", that is not "former" and no need to specify the new size twice. > > > > Also, you say that you "truncated binlog file" here, but on failure > > you fail to "trim the binlog file". I suggest to use consistent > > wording, either always "trim" or always "truncate" (and always with > > or without "the") - both on success and on failure. Personally, I favor > > "truncate the binlog file" > > some two styles of two writers of these blocks mixed up :-). (Partly > relates to the size in above). > > [x] > Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] 8e79d168614: MDEV-25672 table alias from previous statement interferes later commands.
by Sergei Golubchik 02 Jun '21

02 Jun '21

Hi, Aleksey! On Jun 02, Aleksey Midenkov wrote: > revision-id: 8e79d168614 (mariadb-10.2.31-989-g8e79d168614) > parent(s): 433dd490d33 > author: Aleksey Midenkov <midenok(a)gmail.com> > committer: Aleksey Midenkov <midenok(a)gmail.com> > timestamp: 2021-05-31 16:20:38 +0300 > message: > > MDEV-25672 table alias from previous statement interferes later commands. I'm sorry. I don't understand what all this new code for. To make sure that vcol's field->table_name is refreshed for every statement? What is vcol's field->table_name used for besides your CREATE TABLE check? If it's not used for anything then a much simpler fix would be narrow the check - it only needs to be run for new vcols in CREATE TABLE, disabling it for old vcols in ALTER TABLE will do the trick. Like - if (p.table_name.length && table_name) + if (!field && p.table_name.length && table_name) Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

Re: [Maria-developers] e92037989f7: MDEV-21117: refine the server binlog-based recovery for semisync
by Sergei Golubchik 01 Jun '21

01 Jun '21

Hi, Andrei! On Jun 01, Andrei Elkin wrote: > >>>> > >>>> It's actually turns out not to be easy. A sequence of execution events > >>>> > >>>> E1.prepare(trx), E2.prepare(trx), E1.commit(trx), *crash* > >>>> > >>>> does not make E1 such as Innodb committed persistently in file. > >>>> At recovery in E1 trx may be found in prepared state, unless > >>>> before the crash I'd do something like Binlog CheckPoint (BCP) > >>>> notification request and wait of it. > >>> > The committed state is done asynchronously (by virtue of MDEV-232 that > implements a part of BCP). Oh. Indeed. Sorry, I've missed that. Still a prepare of another transaction can force this commit to disk, as far as I understand. So what about this: you stop the thread on a debug sync after the first commit then another connection starts committing in InnoDB, it stops on a debug sync after the prepare - before anything is written into binlog then you crash. looks like it'll create the case you need > >> No doubt it's technically possible to wait for the flushing and > >> crash, but still does not look easy. > >> Consider if we defer this sort of mtr test to MDEV-18959? > > Sujatha just contributed `74a13b4de2` with an mtr test that crashes > after the asynchronous fsync is done to the partially (2 engine case) > committed state. ... > Aslo now that `74a13b4de2` provides with a method to crash after the > "commmit" fsync we might also employ it for RQG. No, this is very different. This is a crash happening where Sujatha expects it to happen, there's no value in that now, it's needed for regression testing, to make sure the recovery won't be broken in the future. But it makes no sense to keep testing now how recovery works in case of a crash happening at exactly the place where Sujatha wanted it to happen and after she verified that crash at that exactly line does not break the recovery. The whole point of RQG tests is to try crashes where you did not expect them to happen. Adding DBUG_EXECUTE_IF() doesn't help this goal at all. Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0