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.