diff options
author | Nikita Malyavin <nikitamalyavin@gmail.com> | 2022-08-17 18:46:04 +0300 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2022-10-12 20:49:45 +0300 |
commit | 3cd2c1e8b6fa8435e634360c2ff63f5d645b65dc (patch) | |
tree | 9cb684504929ed314b124e1d1c4ddb0f90bbe818 /mysql-test/suite | |
parent | 4fec99a2ba6034592d273d918402540d3d4fe772 (diff) | |
download | mariadb-git-3cd2c1e8b6fa8435e634360c2ff63f5d645b65dc.tar.gz |
MDEV-29299 SELECT from table with vcol index reports warning
As of now innodb does not store trx_id for each record in secondary index.
The idea behind is following: let us store only per-page max_trx_id, and
delete-mark the records when they are deleted/updated.
If the read starts, it rememders the lowest id of currently active
transaction. Innodb refers to it as trx->read_view->m_up_limit_id.
See also ReadView::open.
When the page is fetched, its max_trx_id is compared to m_up_limit_id.
If the value is lower, and the secondary index record is not delete-marked,
then this page is just safe to read as is. Else, a clustered index could be
needed ato access. See page_get_max_trx_id call in row_search_mvcc, and the
corresponding switch (row_search_idx_cond_check(...)) below.
Virtual columns are required to be updated in case if the record was
delete-marked. The motivation behind it is documented in
Row_sel_get_clust_rec_for_mysql::operator() near
row_sel_sec_rec_is_for_clust_rec call.
This was basically a description why virtual column computation can
normally happen during SELECT, and, generally, a vcol index access.
Sometimes stats tables are updated by innodb. This starts a new
transaction, and it can happen that it didn't finish to the moment of
SELECT execution, forcing virtual columns recomputation. If the result was
a something that normally outputs a warning, like division by zero, then
it could be outputted in a racy manner.
The solution is to suppress the warnings when a column is computed
for the described purpose.
ignore_wrnings argument is added innobase_get_computed_value.
Currently, it is only true for a call from
row_sel_sec_rec_is_for_clust_rec.
Diffstat (limited to 'mysql-test/suite')
-rw-r--r-- | mysql-test/suite/gcol/r/innodb_virtual_index.result | 31 | ||||
-rw-r--r-- | mysql-test/suite/gcol/t/innodb_virtual_index.test | 29 |
2 files changed, 60 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result index e63d47391c9..d5ad08cd4bf 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_index.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result @@ -1,3 +1,4 @@ +SET default_storage_engine= innodb; SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; # @@ -310,3 +311,33 @@ ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE; # Cleanup DROP TABLE t1; # End of 10.2 tests +# +# MDEV-29299 SELECT from table with vcol index reports warning +# +CREATE TABLE t(fld1 INT NOT NULL, +fld2 INT AS (100/fld1) VIRTUAL, +KEY(fld1), KEY(fld2)); +CREATE TABLE t_odd(id int); +INSERT INTO t(fld1) VALUES(1), (2); +connect stop_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO t_odd VALUES(10000); +connection default; +UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2; +UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3; +UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4; +Warnings: +Warning 1365 Division by 0 +SELECT fld2 FROM t FORCE INDEX(fld2); +fld2 +NULL +100 +SELECT fld2 FROM t FORCE INDEX(fld1); +fld2 +100 +NULL +Warnings: +Warning 1365 Division by 0 +disconnect stop_purge; +DROP TABLE t, t_odd; +# End of 10.3 tests diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test index 46ffadcdd8c..10a7ac51562 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_index.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test @@ -1,6 +1,8 @@ --source include/have_innodb.inc --source include/have_sequence.inc +SET default_storage_engine= innodb; + # Ensure that the history list length will actually be decremented by purge. SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; @@ -334,3 +336,30 @@ DROP TABLE t1; --echo # End of 10.2 tests +--echo # +--echo # MDEV-29299 SELECT from table with vcol index reports warning +--echo # + +CREATE TABLE t(fld1 INT NOT NULL, + fld2 INT AS (100/fld1) VIRTUAL, + KEY(fld1), KEY(fld2)); +CREATE TABLE t_odd(id int); +INSERT INTO t(fld1) VALUES(1), (2); + +--connect stop_purge,localhost,root +# This prevents purge for records in t +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO t_odd VALUES(10000); + +--connection default +UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2; +UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3; +UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4; +SELECT fld2 FROM t FORCE INDEX(fld2); +SELECT fld2 FROM t FORCE INDEX(fld1); + +--disconnect stop_purge +DROP TABLE t, t_odd; + + +--echo # End of 10.3 tests |