From 955fceb53fdc98996b087ff901bae70031543ed2 Mon Sep 17 00:00:00 2001 From: Nikita Malyavin Date: Wed, 17 Aug 2022 18:46:04 +0300 Subject: 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, 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 correctonding 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. Therefore, the solution is just to suppressthe warnings if the access was read-only. --- .../suite/gcol/r/innodb_virtual_index.result | 28 ++++++++++++++++++++++ mysql-test/suite/gcol/t/innodb_virtual_index.test | 23 ++++++++++++++++++ sql/sql_class.h | 16 +++++++++++++ sql/table.cc | 10 ++++++++ 4 files changed, 77 insertions(+) diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result index 34a0b0c66e8..d7240db8fa4 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_index.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result @@ -309,4 +309,32 @@ ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE; ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE; # Cleanup DROP TABLE t1; +# MDEV-29299 SELECT from table with vcol index reports warning +connect con2, localhost, root,,; +connection default; +set default_storage_engine= innodb; +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); +connection con2; +begin; +INSERT INTO t_odd VALUES(10000); +connection default; +UPDATE IGNORE t SET fld1= 0 WHERE fld1= 2; +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 con2; +DROP TABLE t, t_odd; # End of 10.2 tests diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test index 4a41623264e..6ff22bd9ff6 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_index.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test @@ -339,5 +339,28 @@ ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE; --echo # Cleanup DROP TABLE t1; +--echo # MDEV-29299 SELECT from table with vcol index reports warning + +--connect (con2, localhost, root,,) +--connection default +set default_storage_engine= innodb; + +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); +--connection con2 +begin; +INSERT INTO t_odd VALUES(10000); +--connection default +UPDATE IGNORE t SET fld1= 0 WHERE fld1= 2; +SELECT fld2 FROM t FORCE INDEX(fld2); +SELECT fld2 FROM t FORCE INDEX(fld1); + +--disconnect con2 +DROP TABLE t, t_odd; + + --echo # End of 10.2 tests diff --git a/sql/sql_class.h b/sql/sql_class.h index 2fd9bff01e2..8c290502205 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2038,6 +2038,22 @@ public: }; +struct Suppress_warnings_error_handler : public Internal_error_handler +{ +public: + bool handle_condition(THD *thd, + uint sql_errno, + const char *sqlstate, + Sql_condition::enum_warning_level *level, + const char *msg, + Sql_condition **cond_hdl) override + { + return *level == Sql_condition::WARN_LEVEL_WARN; + } +}; + + + /** Tables that were locked with LOCK TABLES statement. diff --git a/sql/table.cc b/sql/table.cc index 75ad563de69..b36bf07be8c 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8923,7 +8923,14 @@ int TABLE::update_virtual_field(Field *vf) DBUG_ENTER("TABLE::update_virtual_field"); Query_arena backup_arena; Counting_error_handler count_errors; + Suppress_warnings_error_handler warnings_handler; + bool ignore_warnings= pos_in_table_list + && pos_in_table_list->lock_type < TL_FIRST_WRITE; + in_use->push_internal_handler(&count_errors); + if (ignore_warnings) + in_use->push_internal_handler(&warnings_handler); + /* TODO: this may impose memory leak until table flush. See comment in @@ -8936,6 +8943,9 @@ int TABLE::update_virtual_field(Field *vf) vf->vcol_info->expr->save_in_field(vf, 0); DBUG_RESTORE_WRITE_SET(vf); in_use->restore_active_arena(expr_arena, &backup_arena); + + if (ignore_warnings) + in_use->pop_internal_handler(); in_use->pop_internal_handler(); DBUG_RETURN(count_errors.errors); } -- cgit v1.2.1