Bug #121749 VIRTUAL column index corrupted by UPDATE/DELETE under a different div_precision_increment or block_encryption_mode

Submitted: 11 Nov 20:55
Reporter: Ke Han Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:26.10.0 (trunk 06bcb0eb0ad) OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 20:55] Ke Han
Description:
The value of some expressions depends on a session variable that the
expression reads implicitly:
- '/' on DECIMAL/INT operands: the result scale is the operand scale plus
  div_precision_increment (Item_func_div::resolve_type() copies
  thd->variables.div_precincrement);
- AES_ENCRYPT()/AES_DECRYPT(): the mode is block_encryption_mode
  (read in Item_func_aes_encrypt::val_str()).
Both are accepted in VIRTUAL generated columns and functional key parts with
a secondary index. The index entry is computed with the writing session's
setting. When a session with another setting runs an UPDATE of a base column
(or, once the table has been opened again - FLUSH TABLES, a restart, a
table-cache eviction - a DELETE), InnoDB recomputes the old value of the
virtual column with the current session's setting, does not find the old
index entry, logs
  [ERROR] [MY-012869] [InnoDB] Record in index `k_unit` of table ... was not found on update
and continues. The stale entry stays in the index and points at the primary
key of a live (updated) or deleted row. The client gets no error or warning.

Consequences:
- covering index scans return phantom rows, and SELECT COUNT(*) (answered
  from the secondary index) counts them: 3 rows in a 2-row table;
- CREATE TABLE ... SELECT and INSERT ... SELECT store the phantom rows and the
  wrong SUM/COUNT;
- CHECK TABLE ... EXTENDED reports "Index 'k_unit' contains 4 entries, should
  be 3." / Corrupt and marks the index corrupted; afterwards every read through
  it fails with ERROR 1712 until the index is rebuilt.

Both variables are per-session and can be set by any client (for example a
reporting connection that sets div_precision_increment = 0 for display). The
manual's rule that variables are not permitted in generated column
expressions is evaded because the variable is read implicitly.

Bug #121157 reports the same class of problem (session-dependent expressions
accepted in generated columns) for time_zone and lc_time_names, with UNIQUE
violations and wrong results through generated-column substitution; its
suggested fix (reject time-zone- and lc_time_names-dependent expressions at
DDL time) would not cover '/' and AES_ENCRYPT, and it does not mention the
InnoDB index corruption shown here (time_zone-dependent indexed VIRTUAL
columns corrupt the index in the same way).

Bug #121231 (Open) shows the same copying of div_precision_increment at
resolve time for a prepared statement (EXECUTE keeps the scale of the session
setting at PREPARE time); it does not involve generated columns or indexes.

How to repeat:
Each block is a separate client session (e.g. a separate mysql invocation).

-- session A (defaults: div_precision_increment = 4)
CREATE DATABASE m205;
CREATE TABLE m205.price (id INT PRIMARY KEY, total DECIMAL(12,2), qty INT,
  unit DECIMAL(20,8) AS (total / qty) VIRTUAL, KEY k_unit (unit));
INSERT INTO m205.price (id, total, qty) VALUES (1, 10.00, 3), (2, 20.00, 7), (3, 1.00, 8);

-- session B
SET SESSION div_precision_increment = 0;
UPDATE m205.price SET qty = 4 WHERE id = 2;      -- 1 row affected, no warning

-- session C (defaults)
USE m205;
SELECT id, unit FROM price ORDER BY unit;        -- Covering index scan on price using k_unit
SELECT id, unit FROM price IGNORE INDEX (k_unit) ORDER BY unit;
SELECT SUM(unit), COUNT(unit) FROM price;
CREATE TABLE price_copy AS SELECT id, unit FROM price ORDER BY unit;
SELECT * FROM price_copy;
CHECK TABLE price EXTENDED;
SELECT id, unit FROM price ORDER BY unit;

Result on 26.10.0 (session C):
  SELECT id, unit FROM price ORDER BY unit
    3  0.12500000
    2  2.85714300      <- phantom: the old version of row 2
    1  3.33333300
    2  5.00000000
  ... IGNORE INDEX (k_unit) ORDER BY unit:  (3, 0.125), (1, 3.333333), (2, 5.0)
  SELECT SUM(unit), COUNT(unit): 11.31547600, 4      (correct: 8.45833300, 3)
  price_copy: the same 4 rows as the first query
  CHECK TABLE price EXTENDED:
    Warning  InnoDB: Index 'k_unit' contains 4 entries, should be 3.
    error    Corrupt
  SELECT id, unit FROM price ORDER BY unit:
    ERROR 1712 (HY000): Index k_unit is corrupted
The error log has "[ERROR] [MY-012869] [InnoDB] Record in index `k_unit` of
table `m205`.`price` was not found on update".

AES_ENCRYPT and block_encryption_mode:

-- session A
CREATE TABLE m205.users (id INT PRIMARY KEY, email VARCHAR(40),
  tok VARBINARY(64) AS (AES_ENCRYPT(email, 'k')) VIRTUAL, KEY k_tok (tok));
INSERT INTO m205.users (id, email) VALUES (1, 'a@x.org'), (2, 'b@x.org');
-- session B
SET SESSION block_encryption_mode = 'aes-256-ecb';
UPDATE m205.users SET email = 'c@x.org' WHERE id = 1;
-- session C
SELECT COUNT(*) FROM m205.users;                                   -- 3 (the table has 2 rows)
SELECT id, email FROM m205.users FORCE INDEX (k_tok) ORDER BY tok; -- 2 b@x.org, 1 c@x.org, 1 c@x.org
CHECK TABLE m205.users EXTENDED;    -- Index 'k_tok' contains 3 entries, should be 2. / Corrupt

DELETE after the table was opened again:

-- session A
CREATE TABLE m205.price2 (id INT PRIMARY KEY, total DECIMAL(12,2), qty INT,
  unit DECIMAL(20,8) AS (total / qty) VIRTUAL, KEY k_unit (unit));
INSERT INTO m205.price2 (id, total, qty) VALUES (1, 10.00, 3), (2, 20.00, 7), (3, 1.00, 8);
FLUSH TABLES m205.price2;
-- session B
SET SESSION div_precision_increment = 0;
DELETE FROM m205.price2 WHERE id = 1;
-- session C
SELECT COUNT(*) FROM m205.price2;                    -- 3 (2 rows left)
SELECT id, unit FROM m205.price2 ORDER BY unit;      -- still returns the deleted row 1 (3.33333300)
CHECK TABLE m205.price2 EXTENDED;   -- Index 'k_unit' contains 3 entries, should be 2. / Corrupt

Expected: the index matches the table in every case, or the expressions are
rejected (or made independent of the session) when the column is defined.

Suggested fix:
- For '/', the scale could be fixed when the generated column is created, as
  is already done for WEEK(d), which is stored as week(d,0) so that
  default_week_format does not matter; e.g. store the expression with an
  explicit scale, or resolve m_prec_increment from the definition instead of
  the session.
- For AES_ENCRYPT/AES_DECRYPT without an explicit mode argument (and the other
  session-dependent functions), reject them in generated column expressions
  and functional key parts (check_function_as_value_generator() currently
  rejects only UNIX_TIMESTAMP and the non-deterministic functions), as
  Bug #121157 proposes for time_zone and lc_time_names.
- Independently, InnoDB recomputes the old value of an indexed virtual column
  with the current session's settings when it updates or deletes the row
  (update->old_vrow from calc_row_difference(), and
  innobase_get_computed_value() in row_upd_store_v_row(),
  storage/innobase/row/row0upd.cc); when the old entry is then not found, it
  only logs MY-012869 and continues, leaving the index inconsistent. Failing
  the statement there would at least prevent the silent corruption.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.