`save_window_function_values()` did not take into account that one can
get `HA_ERR_RECORD_FILE_FULL` on `ha_update_row()`. With blob support in
heap, it can now happen more easily (expressions wider than 512
characters are promoted to TEXT in tmp tables, and each update of a blob
result column allocates a continuation record). However, it could also
happen with Aria tables, which is apparently not tested. The error was
returned as a plain `true` with an empty diagnostics area: debug builds
hit `Assertion '0'` in `Protocol::end_statement()`, release builds send
a bare OK packet after the result set metadata, which the client
mis-parses (appears as a hang / lost connection).
Errors of the computation are now exposed:
`save_window_function_values()` calls `handler::print_error()` for any
failure it does not recover from, the previously ignored `ha_rnd_pos()`
return values are checked and reported, and `compute_window_func()`
distinguishes a read error from EOF and stops the row scan as soon as
the per-row loop fails.
The overflow itself is recovered from by converting the tmp table to
the disk-based tmp engine in place: the rows keep their identity and
their contents, only their positions change, and those are translated
in the rowid sequence that the computation is built on.
The filesort result of a window sort is a sequence of row positions
that covers every row of the table exactly once (the sort is set up
without a limit) and is the only place where the positions are kept:
the row scan and all frame cursors read the rows through it. The
conversion (`Window_rowid_remapper`) therefore copies the rows in the
order of that sequence, which makes the new position of a row known as
soon as the row has been written, and stores it back into the very slot
the old position was read from.
For the new position to always fit into the slot, a window sort stores
the row positions in slots of a fixed 8 bytes
(`Filesort::min_ref_length`, set to the new `TMP_TABLE_MAX_REF_LENGTH`),
which holds the position of any engine an internal tmp table can use: a
pointer into memory or a data file offset. `make_sortkey()` zero-pads a
shorter position to its slot, and `SORT_INFO::ref_length` carries the
slot width to the readers, so `init_read_record()` no longer re-derives
it from `handler::ref_length`, which changes when the table is
converted. Sorts that do not set a minimum, and engines whose positions
are wider than 8 bytes, are unchanged byte for byte. A sequence held in
memory is thereby rewritten in place, keeping its layout, so the frame
cursors' places in it stay valid.
A sequence held in a temporary file is written and read through an
`IO_CACHE`, which encrypts the file when tmp file encryption is
enabled, so the file can not be rewritten in place: the old sequence is
streamed out of its cache and the translated sequence into a new cached
temporary file, which then replaces the old one under the cache. The
frame cursors' slave caches stay linked to the master through
`next_file_user` across the replacement, and are re-created from the
new master afterwards (`Frame_cursor::rowids_rewritten()`). Should
re-creating one fail, the cursor forgets its already-released cache, so
that it is not released a second time when the cursor is destroyed, and
reports the failure.
The row whose update did not fit is written from `record[0]`, which
holds its new image, instead of being read from the table, so the
conversion applies the update that failed. Blob values of `record[0]`
that were read from the HEAP table can point into the handler's shared
blob reassembly buffer (`hp_read_blobs()`, blob values that span
multiple allocation blocks), which reading the other rows overwrites,
so they are first given memory of their own.
When the window sort was set up with a deferred filter (a HAVING clause
deferred to the final ORDER BY sort), the sequence omits the rows the
filter rejected and the conversion drops them: every later reader of
the table applies the same filter, either directly or by reading the
table through a sort that does, so such rows can never reach the
result.
`create_internal_tmp_table_from_heap()` gains a `Tmp_table_row_copier`
hook for the copy from the HEAP table to the Aria or MyISAM table that
replaces it. The plain copy loop and the append of the pending
`record[0]` become `Tmp_table_default_copier`, used when the caller
passes no copier, so both cases of the conversion run through the same
code, defined together with `Window_rowid_remapper` just before the
function. `ha_end_bulk_insert()` is called also when the copy fails, so
the new handler does not keep bulk insert state when its table is
dropped; the pending row is thereby written while the bulk insert is
still active, which does not delay its duplicate detection, as bulk
insert does not cache unique keys (`maria_init_bulk_insert()` skips
`HA_NOSAME` keys). An ignored duplicate of the pending row is passed to
the caller in `Tmp_table_row_copier::duplicate_row_error`, which sets
`*is_duplicate`.
Carrying the running computation over the conversion, instead of
re-running it, has two visible consequences:
- Compound expressions containing window functions (`items_to_copy`)
are evaluated exactly once per row, as they are when the table does
not overflow, so the conversion does not have to be refused when
such an expression is non-deterministic (`RAND_TABLE_BIT`: a user
variable assignment, a non-deterministic stored function); those
statements produce their result.
- The statement sorts once, so sort related aggregate warnings, e.g.
the `max_sort_length` truncation counts, are reported for one pass.
The rows are copied in the order of the sequence, so the converted
table does not hold them in the order they were inserted in. This is
safe even when that order is what the table was built for
(`TABLE::keep_row_order`, set for `ROWNUM()` with GROUP BY or ORDER
BY): before HEAP supported blobs, such a tmp table was created in the
disk engine from the start and these statements simply ran, so refusing
here would be a regression against that behavior.
1. `ROWNUM()` values are materialized into the tmp table rows during
the fill, before the window computation begins; the copy moves them
verbatim, so their pairing with the rows can not change.
2. The rowid sequence is translated in place, so the running window
computation continues over exactly the same row order, and
tie-sensitive window function values (`ROW_NUMBER`, frames over tied
keys) keep the values the interrupted pass had already produced.
3. Consumers that scan the converted table afterwards see the rows in
the copy order. Among rows with equal sort keys that order differs
from the insertion order, but such tie order is unspecified and
already differs between the memory and disk tmp engines.
The converted table keeps `keep_row_order= true` so the copy order is
also the order the disk engine preserves from then on.
Tests, in `heap.blob_window_overflow` unless noted: a single window
over all rows and a partitioned window overflow mid-computation and
must transparently convert; the side-effecting window expression test
verifies that the user variable is assigned exactly once per row,
against the same query that does not overflow; a fourth test covers the
sequence being an array in memory instead of a merge file; a fifth test
covers the `keep_row_order` conversion, verifying that every `ROWNUM()`
value keeps its insertion-order pairing with its row across the
conversion; a sixth test covers blob values larger than a HEAP
allocation block surviving the conversion of the pending row;
`heap.blob_window_overflow_encrypt` runs the conversion with encrypted
temporary files; `heap.blob_window_overflow_debug` covers the failure
to re-create a slave cache of the sequence
(`simulate_window_seq_slave_oom`), which previously hung the server in
the cursor destructor.
Seven code fixes, a new test, and test re-recordings for issues found
by CI on PR #5222.
**NULL dereference in `create_tmp_field()`**: `SYS_REFCURSOR` plugin
returns NULL from `make_new_field()` (cursor values cannot be
materialized). The feature added `result->flags |= FIELD_PART_OF_TMP_UNIQUE`
without a NULL check. Added `if (result)` guard.
**xmltype identity loss and recursive CTE reclength mismatch in
`Item_type_holder::create_tmp_field_ex()`**: the blob_key dispatch now
requires both: (1) `type_handler_for_tmp_table()` returns
`blob_key_type_handler()`, AND (2) `dynamic_cast<Type_handler_blob_common*>`
confirms the original type is a native blob. Condition 1 excludes xmltype
(its override returns itself). Condition 2 excludes VARCHAR types promoted
via `varstring_type_handler()` -> `too_big_for_varchar()` ->
`blob_type_handler()`. Without condition 2, wide VARCHAR in recursive CTEs
(e.g. `cast('...' as varchar(1000))`) was promoted to `Field_blob_key` in
the main UNION DISTINCT table (`part_of_unique_key=true`) but stayed as
`Field_varchar` in the incremental table (`part_of_unique_key=false`),
causing a `reclength` mismatch assertion in
`select_union_recursive::send_data()` (`main.json_equals` crash).
**Spurious `reclength > HA_MAX_REC_LENGTH` in `pick_engine()`**: the
original `choose_engine()` (both 10.11 and upstream/main) never had a
reclength check. MDEV-38975 introduced it when replacing the
`blob_fields` condition. HEAP has no internal reclength limit --
`hp_create.c` stores `uint reclength` and allocates blocks of that size;
`max_supported_record_length()` is only checked in `unireg.cc` during
user-facing CREATE TABLE. I_S tables like SLAVE_STATUS routinely have
reclength ~880KB (13 bare `Varchar()` columns). The check forced them to
Aria where `fill_slave_status()` returned 0 rows. Removed the check and
the unused `reclength` parameter from `pick_engine()`.
**Multi-update `tmp_memory_table_size` override**: the 10.11 feature
overrode `big_tables=FALSE` for multi-update dedup tables. The forward-port
translated this as `tmp_memory_table_size=SIZE_T_MAX` when the variable
was 0. But `big_tables=FALSE` was a soft "don't force disk" hint, while
`tmp_memory_table_size=SIZE_T_MAX` overrides the user's explicit
`tmp_memory_table_size=0` directive. Since main removed `big_tables`
entirely (MDEV-19713), the override is not needed. Removed.
**Zero-length key rejection in `check_tmp_key()`**: reject `key_len == 0`
to prevent useless zero-length keys from being created by `add_tmp_key()`.
Reachable when all key parts are CHAR(0) NOT NULL: `key_length()` returns
0, the field is not nullable (no HA_KEY_NULL_LENGTH) and not
VARCHAR/BLOB/GEOMETRY (no HA_KEY_BLOB_LENGTH), so `fld_store_len` is 0
for every part. Without this guard, `check_tmp_key()` would accept the
key (0 <= max_key_length), and the optimizer would create a ref key that
cannot distinguish any rows. Added `heap.char0_key` test exercising this
via a materialized derived table with CHAR(0) NOT NULL join columns.
**Non-deterministic `column_compression` test**: HEAP blob support allows
compressed VARCHAR/TEXT temp tables to stay in HEAP instead of falling to
Aria, changing row iteration order. Added `--sorted_result` to the two
MDEV-24726 subqueries that lack `ORDER BY`.
Test changes:
- `spatial_utility_function_collect`: added ORDER BY to window function
that lacked it (results were engine-row-order-dependent)
- `tmp_space_usage`: removed multi-update override; forced disk for
MDEV-34016/34060 Aria-specific test sections (blob I_S tables now
stay in MEMORY)
- `blob_update_overflow`: replaced `SHOW STATUS LIKE 'Created_tmp_%'`
with targeted I_S query (Created_tmp_files varies on sanitizer builds)
- 'funcs_1.is_tables_is' ; re-recorded test as
INFORMATION_SCHEMA.SLAVE_STATUS is now back as MEMORY table
- `column_compression`: added `--sorted_result` for MDEV-24726 queries
- `char0_key` (new): CHAR(0) NOT NULL derived table ref key rejection
- Re-recorded 8 tests for expected "temp table stays in MEMORY" changes
Remove the HA_NO_BLOBS restriction from the HEAP engine, allowing
the optimizer to keep temporary tables with BLOB/TEXT columns in
memory when they fit within max_heap_table_size / tmp_memory_table_size
limits. Additionally, advertise HA_CAN_GEOMETRY so explicit
CREATE TABLE ... ENGINE=MEMORY with GEOMETRY columns works.
Unlike other HEAP blob implementations (e.g. Percona), this patch
provides full HASH index support on blob columns, enabling efficient
lookups, GROUP BY, and DISTINCT operations directly in HEAP without
falling back to disk.
Architecture
------------
BLOB data is stored using continuation records -- additional fixed-size
records allocated from the same HP_BLOCK that holds regular rows. This
reuses existing allocation, free list, and size accounting with minimal
structural change, and avoids per-blob my_malloc() calls.
The existing single-byte visibility flag is extended into a flags byte
with bits for HP_ROW_HAS_CONT, HP_ROW_IS_CONT, HP_ROW_CONT_ZEROCOPY,
HP_ROW_SINGLE_REC, and HP_ROW_MULTIPLE_REC. Continuation records are
grouped into variable-length runs -- contiguous sequences within a leaf
block. Only the first record of each run carries a 10-byte header
(next_cont pointer + run_rec_count); inner records are pure payload.
Three storage formats, detected by flag bits via inline predicates:
Case A (HP_ROW_SINGLE_REC): single record, no header, data at
offset 0. Zero-copy read.
Case B (HP_ROW_CONT_ZEROCOPY): single run, multiple records.
Header in rec 0, data contiguous in rec 1..N-1. Zero-copy read
via chain + recbuffer.
Case C (HP_ROW_MULTIPLE_REC): one or more runs linked via
next_cont. Reassembly into blob_buff required.
Run allocation uses a two-phase strategy: (1) peek-then-unlink walk
of the free list detecting contiguous groups, (2) tail allocation
from HP_BLOCK for remaining data. A Step 3 scavenge fallback
walks the entire free list when tail allocation fails.
HP_SHARE::total_records tracks all physical records (primary +
continuation), while HP_SHARE::records remains the logical count
used by hash bucket mapping.
Reassembly buffer (HP_INFO::blob_buff) follows the same pattern as
InnoDB's blob_heap -- allocated once, grown via my_realloc, freed
on heap_reset()/close. Zero-copy cases (A/B) return pointers
directly into HP_BLOCK with no copy.
Full HASH index key handling for BLOB columns: hp_rec_hashnr(),
hp_rec_key_cmp(), hp_key_cmp(), hp_make_key(), hp_hashnr() are
extended for HA_BLOB_PART segments. Hash pre-check optimization
skips expensive blob materialization when hashes differ. PAD SPACE
collation semantics are preserved for blob key comparisons.
Field_blob_key (Monty) produces HEAP-native key format (4-byte length
+ 8-byte data pointer) directly, eliminating key buffer translation
between the SQL layer and HEAP engine.
SQL layer changes
-----------------
pick_engine() (new, extracted from choose_engine()): replaced the
blob_fields check with a reclength > HA_MAX_REC_LENGTH guard.
choose_engine() calls pick_engine() with the real reclength.
pick_engine() is also called early in finalize() with reclength=0
to predict whether the engine will be HEAP, enabling blob-aware
GROUP BY key setup that avoids unnecessary m_using_unique_constraint.
finalize(): HEAP+blob uses fixed-width rows; GROUP BY key setup sets
key_part_flag from field, uses item max_length for blob key sizing.
store_length initialized for all GROUP BY key parts. key_type uses
field->binary() to determine FIELDFLAG_BINARY vs text collation.
DISTINCT key setup skips null-bits helper for HEAP.
remove_duplicates(): blob check moved before HEAP check to fall
through to remove_dup_with_compare().
Aggregator_distinct::add(): overflow-to-disk conversion via
create_internal_tmp_table_from_heap() for non-dup write errors.
Expression cache disabled for HEAP+blob (key format incompatibility).
FULLTEXT early detection in mysql_derived_prepare(): forces disk
engine via TMP_TABLE_FORCE_MYISAM when outer query uses MATCH.
Deferred blob chain free (MDEV-39732): heap_delete() saves chain
pointers to pending_blob_chains, flushed on next mutation or
heap_reset()/close. Prevents dangling zero-copy pointers during
binlog_log_row().
REPLACE safety (MDEV-39825): HP_SHARE::write_can_replace flag
forces copy mode in hp_read_blobs(), preventing blob data corruption
from freed-then-reused continuation records during REPLACE.
Geometry GROUP_CONCAT fix (MDEV-39761): downgrade Field_geom to
Field_blob for GROUP_CONCAT temp tables in both expression creation
paths. Type_handler_geometry::type_handler_for_tmp_table() added.
Geometry GROUP BY key fix (MDEV-39871): detect when new_key_field()
produced non-blob Field_varstring for a blob column, replace with
Field_blob_key.
Performance
-----------
Non-blob tables: zero regression. Every blob-specific code path is
guarded by if (share->blob_count). No new allocations, no format
changes, no hash function changes for non-blob keys.
Blob tables: eliminates file creation/deletion overhead and page cache
management. For single-run blobs (common case), the read path is
entirely zero-copy.
Limitations
-----------
1. No BTREE indexes on blob columns (HASH only)
2. No partial-key prefix indexing for blobs
3. 2x memory for Case C reads only (A/B are 1x)
4. No blob compression
5. 65,535 records per run (uint16 cap, auto-split)
6. max_heap_table_size applies to continuation records
7. Expression cache disabled for HEAP+blob
8. FULLTEXT forces disk engine
Linked bugs fixed:
- MDEV-39703: mroonga fulltext test ordering
- MDEV-39723: ER_DUP_ENTRY on GROUP BY with blob column
- MDEV-39724: crash in hp_is_single_rec with GROUP BY
- MDEV-39732: slave crash in hp_free_run_chain on blob replication
- MDEV-39761: Field_geom::store() assertion in GROUP_CONCAT
- MDEV-39782: RBR ER_KEY_NOT_FOUND on HEAP blob UPDATE
- MDEV-39825: blob data corruption on REPLACE into HEAP table
- MDEV-39871: crash in my_hash_sort_bin on GROUP BY with geometry
Reviewed by: Michael Widenius <monty@mariadb.org>
Monty reviewed the entire patch. Areas where he suggested changes
or contributed code:
- Field_blob_key class (HEAP-native blob key format, 4-byte length +
data pointer)
- Duplicate key fix on HEAP-to-Aria conversion
- hp_blob_key_length() uint32 fix
- hp_rec_hashnr_stored removal
- type_handler_for_tmp_table() param cleanup
- Type_handler_geometry::type_handler_for_tmp_table() virtual
- blob pointer bzero()
- find_unique_row() double-materialization fix
- Tail reclaim review
- Batch tail allocation review
- hp_update.c cleanup
- Field_blob_compressed temp table fix
- row_pack_length() dedup
- pack_length_no_ptr() removal
- Race condition fix in HEAP
- MDEV-39703 mroonga test fix
- MDEV-39825 write_can_replace optimization
- is_text_key_segment removal (field->binary() simplification)
- Documentation (Docs/internal-temporary-tables.txt)
Contribution by: Alexander Barkov <bar@mariadb.com>
Type_handler::make_and_init_table_field_ex() -- refactored temp table
field creation from inline code in sql_select.cc into type handler
virtual methods (sql_type.cc, sql_type_geom.cc), enabling clean
per-type-handler field creation for HEAP blob promotion.
When starting to do a full table/index scan without a WHERE or JOIN
condition, tell the storage engine so and the corresponding
ulong-truncated LIMIT.
Include an innodb implementation: added an innodb switch
table_lock_on_full_scan, so that when the switch is on, on receiving
the full scan signal from the sql layer, if the truncated LIMIT is
ULONG_MAX (likely no LIMIT), attempt to acquire a table lock.
Updated tests that have different results with the switch on.
The three deadlock_*_race tests cannot reach their DEBUG_SYNC race
under a table lock (it degenerates to a timeout), and the three I_S
tests only restate a lock-mode change already covered
by innodb_full_scan.test.
(Comment and code edited by Sergei Petrunia <sergey@mariadb.com> and
Thirunarayanan Balathandayuthapani <thiru@mariadb.com>)
This is regression caused by MDEV-28750 commit 1f349968.
In multi-table update table list could contain tables
that are not yet opened because update does not really
change them. This can happen e.g. when update changes
table that is referenced by foreign key by table that
is not part of multi-table update.
Fixed by first checking is wsrep write set size limited.
If it is not multi-table update can continue normally.
If write set size is limited then check has update
updated both transactional and non-transactional tables
and those tables that have not yet been opened can be
safely skipped as they are not updated.
Analysis:
OLD_VALUE() swapped only field->ptr, leaving null_ptr pointing to the
current record. This caused incorrect NULL results.
Fix:
Store null_ptr_old for record[1] and swap it together with ptr to
preserve correct NULL semantics.
of type 'multi_update' in sql/sql_update.cc
| Sql_cmd_update::update_single_table
Analysis:
the 'result' object was being incorrectly used which maybe of the type
multi_update.This caused UBSAN error due to an invalid downcast in
Sql_cmd_update::update_single_table().
Fix:
Introduce a dedicated returning_result object for handling RETURNING output
instead of reusing result. This ensures the correct result handler is used
and avoids unsafe casts.
The patch introduces the OLD_VALUE() expression to reference the value
of a column before it was updated. The parser is extended to support
RETURNING and OLD_VALUE(), and RETURNING expressions are stored in a
separate returning_list in SELECT_LEX with independent wildcard tracking.
RETURNING is rejected for multi-table UPDATE.
During setup of RETURNING fields, THD::is_setting_returning is used
when resolving fields, particularly for updates through views.
When resolving view fields, Item_direct_view_ref may point to the
view's item_list, losing the information about whether the value
should be old or new. The original item in returning_list still
contains the correct is_old_value_reference flag, which is copied
back to the resolved item.
OLD_VALUE() is implemented by extending Item_field with a new
Item_old_field class. Item_field::set_field() initializes
Field::ptr_old to the corresponding location in record[1],
which stores the old row during UPDATE execution.
When sending result rows, Item_old_field::send() temporarily switches
the field pointer from record[0] (current row) to ptr_old so
OLD_VALUE() returns the value before the update.
The UPDATE execution path is modified to send a result set when
RETURNING is present instead of an OK packet.
Reason:
=======
Following the changes in MDEV-38734, the server no longer marks
all indexed virtual columns during an UPDATE operation.
Consequently, ha_innobase::update() populates the upd_t vector
with old_vrow but omits the actual data for these virtual columns.
Despite this omission, trx_undo_page_report_modify() continues to
write metadata for indexed virtual columns into the undo log. Because
the actual values are missing from the update vector, the undo log
entry is recorded without the historical data for these columns.
When the purge thread processes the undo log to reconstruct a
previous record state for MVCC, it identifies an indexed virtual
column but finds no associated data.
The purge thread incorrectly interprets this missing data as a NULL
value, rather than a "missing/unrecorded" value. The historical
record is reconstructed with an incorrect NULL for the virtual column.
This causes the purge thread to incorrectly identify and purge
records that are not actually delete-marked, leading to abort
of server.
Solution:
=========
ha_innobase::column_bitmaps_signal(): Revert the column-marking
logic to the state prior to commit a4e4a56720, ensuring all
indexed virtual columns are unconditionally marked during an UPDATE.
The previous "optimization" attempted to manually detect indexed
column changes before marking virtual columns. The manual check
for indexed column modifications is redundant. InnoDB already
provides the UPD_NODE_NO_ORD_CHANGE flag within row_upd_step().
This flag is being used in trx_undo_page_report_modify() and
trx_undo_read_undo_rec() should log or read virtual column values.
Refactored column_bitmaps_signal() to accept a mark_for_update
parameter that controls when indexed virtual columns are marked.
TABLE::mark_columns_needed_for_update() is the only place that needs
mark_for_update=true because only UPDATE operations need to mark
indexed virtual columns for InnoDB's undo logging mechanism.
INSERT operation is already handled by
TABLE::mark_virtual_columns_for_write(insert_fl=true).
Even the commit a4e4a56720 changes are
going to affect TABLE::mark_virtual_column_for_write(false) and
It is called during UPDATE operation, and that's when
column_bitmaps_signal() needs to mark indexed virtual columns.
Online DDL has separate code path which is handled by
row_log_mark_virtual_cols() for all DML operations
Problem was that in multi-table update that contains tables that are not
transactional and tables that are transactional error handling does not
work correctly if write set size is limited. Because, Galera
replication for MyISAM is experimental it is better to avoid
multi-table updates in this case.
Fixed by not allowing multi-table updates if statement contains
non transactional and transactional tables and write set size
is limited.
ft_handler isn't getting initialized for subqueries inside explain
delete/update queries. However, ft_handler is accessed inside ha_ft_read(),
and is the reason for NULL pointer exception.
This is not the case with non-explain delete/update queries, as
well as explain/non-explain select queries.
Follow the approach the SELECT statements are using in
JOIN::optimize_constant_subqueries(): remove SELECT_DESCRIBE
flag when invoking optimization of constant subqueries.
Single-table UPDATE/DELETEs have SELECT_LEX but don't have JOIN.
So, we make optimize_constant_subqueries() not to be a member
of JOIN class, and instead move it to SELECT_LEX, and then
invoke it from single-table UPDATE/DELETE as well as for SELECT queries.
in SIMULTANEOUS_ASSIGNMENT there is no need to switch value items
to new nullable copies of table Field's - they must refer to old
values in the row, which can never be null anyway.
skipping this redundant step simplifies moving field to record[1]
and back in fill_record()
We extend from the SQL standard to match the functionality of other
databases that allow the inclusion of a CTE definition prior to update
and delete statements.
These CTEs are currently read only, like other derived tables, so
cannot have their columns updated in updates set clause, nor have rows
removed in the delete statement.
Approved by: Oleksandr Byelkin (sanja@mariadb.com)
after 633417308f (MDEV-37312) lookup_handler is locked with F_WRLCK,
because it may be used for deleting rows.
And lookup_handler is locked with F_WRLCK after prune_partitions(),
but the main handler is locked before, and might expects all
partitions to be in the read least, non-pruned.
Let's prepare the lookup handler before prune_partitions().
main/statistics_json.result is updated for f8ba5ced55 (MDEV-36099)
The test uses 'delete from t1' in many places and then populates
the table again. The natural order of rows in a MyISAM table is well
defined and the test was implicitly relying on that.
before f8ba5ced55 delete was deleting rows one by one, using
ha_myisam::delete_row() because the connection was stuck in rbr mode.
This caused rows to be shown in the reverse insertion order (because of
the delete link list).
MDEV-36099 fixes this bug and the server now correctly uses
ha_myisam::delete_all_rows(). This makes rows to be shown in the
insertion order as expected.
mysql_update() aborts if SQL_SELECT::check_quick() fails.
SQL_SELECT::check_quick() fails when thd->killed is set.
Thus, mysql_update() must also test thd->killed to know how to
terminate - normally (impossible where) or abnormally.
in 11.4 multi_update::prepare() is called both for single and multi update.
as it's invoked before lock_tables(), it cannot do prepare_for_insert(),
which calls external_lock() and only can be used on an already locked table
Also expand vcol field index coverings to include indexes covering all
the fields in the expression. The reasoning goes as follows: let f(c1,
c2, ..., cn) be a function on applied to columns c1, c2, ..., cn, if
f(...) is covered by an index, so should vc whose expression is
f(...).
For example, if t.vf = t.c1 + t.c2, and t has three indexes (vf), (c1,
c2), (c1).
Before this change, vf's index covering is a singleton {(vf)}. Let's call
that the "conventional" index covering.
After this change vf's index covering is now {(vf), (c1, c2)}, since
(c1, c2) covers both c1 and c2. Let's call (c1, c2) in this case the
"extra" covering.
With the coverings updated, when an index in the "extra" covering is
chosen for keyread, the vcol also needs to be calculated. In this case
we mark vcol in the table read_set, and ensure it is computed.
With these changes, we see various improvements, including from using
full table scan + filesort to full index scan + filesort when ORDER BY
an indexed vcol (here vc = c + 1 is a vcol and both c and vc are
indexes):
explain select c + 1 from t order by vc;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t ALL NULL NULL NULL NULL 10000 Using filesort
+1 SIMPLE t index NULL c 5 NULL 10000 Using index; Using filesort
The substitutions are followed updates to all_fields which include a
copy of the ORDER BY/GROUP BY item pointers, as well as corresponding
updates to ref_pointer_array so that the all_fields and
ref_pointer_array remain in sync.
Another, related change is the recomputation of table index covering
on substitutions. It not only reflects the correct table index
covering after the substitutions, but also improve executions where
the vcol index can be chosen, such as this example (here vc = c + 1
and vc is the only index in the table), from full table scan +
filesort to full index scan:
select vc from t order by c + 1;
We do it in SELECT as well as in single table DELETE/UPDATE.
When processing queries like
INSERT INTO t1 (..) SELECT .. FROM t1, t2 ...,
there is a single query block (i.e., a single SELECT_LEX) for both INSERT and
SELECT parts. During hints resolution, when hints are attached to particular
TABLE_LIST's, the search is performed by table name across the whole
query block.
So, if a table mentioned in an optimizer hint is present in the INSERT part,
the hint is attached to the that table. This is obviously wrong as
optimizer hints are supposed to only affect the SELECT part of
an INSERT..SELECT clause.
This commit disables possible attaching hints to tables in the INSERT part
and fixes some other bugs related to INSERT..SELECT statements processing
It places a limit N (a timeout value in milliseconds) on how long
a statement is permitted to execute before the server terminates it.
Syntax:
SELECT /*+ MAX_EXECUTION_TIME(milliseconds) */ ...
Only top-level SELECT statements support the hint.
MDEV-36563 Assertion `!mysql_bin_log.is_open()' failed in
THD::mark_tmp_table_as_free_for_reuse
The purpose of this commit is to ensure that creation and changes of
temporary tables are properly and predicable logged to the binary
log. It also fixes some bugs where ROW logging was used in MIXED mode,
when STATEMENT would be a better (and expected) choice.
In this comment STATEMENT stands for logging to binary log in
STATEMENT format, MIXED stands for MIXED binlog format and ROW for ROW
binlog format.
New rules for logging of temporary tables
- CREATE of temporary tables are now by default binlogged only if
STATEMENT binlog format is used. If it is binlogged, 1 is stored in
TABLE_SHARE->table_creation_was_logged. The user can change this
behavior by setting create_temporary_table_binlog_formats to
MIXED,STATEMENT in which case the create is logged in statement
format also in MIXED mode (as before).
- Changes to temporary tables are only binlogged if and only if
the CREATE was logged. The logging happens under STATEMENT or MIXED.
If binlog_format=ROW, temporary table changes are not binlogged. A
temporary table that are changed under ROW are marked as 'not up to
date in binlog' and no future row changes are logged. Any usage of
this temporary table will force row logging of other tables in any
future statements using the temporary table to be row logged.
- DROP TEMPORARY is binlogged only of the CREATE was binlogged.
Changes done:
- Row logging is forced for any statement using temporary tables that
are not up to date in the binary log.
(Before the row logging was forced if the user has a temporary table)
- If there is any changes to the temporary table that is not binlogged,
the table is marked as not up to date.
- TABLE_SHARE->table_creation_was_logged has a new definition for
temporary tables:
0 Table creating was not logged to binary log
1 Table creating was logged to binary log and table is up to date.
2 Table creating was logged to binary log but some changes where
not logged to binary log.
Table is not up to date in binary log is defined as value 0 or 2.
- If a multi-table-update or multi-table-delete fails then
all updated temporary tables are marked as not up to date.
- Enforce row logging if the query is using temporary tables
that are not up to date.
Before row logging was enforced if the user had any
temporary tables.
- When dropping temporary tables use IF EXISTS. This ensures
that slave will not stop if it had crashed and lost the
temporary tables.
- Remove comment and version from DROP /*!4000 TEMPORARY.. generated when
a connection closes that has open temporary tables. Added 'generated by
server' at the end of the DROP.
Bugs fixed:
- When using temporary tables with commands that forced row based,
like INSERT INTO temporary_table VALUES (UUID()), this was never
logged which causes the temporary table to be inconsistent on
master and slave.
- Used binlog format is now clearly defined. It is now only depending
on the current binlog_format and the tables used.
Before it was depending on the user had ANY temporary tables and
the state of 'current_stmt_binlog_format' set by previous queries.
This also caused temporary tables to be logged to binary log in
some cases.
- CREATE TABLE t1 LIKE not_logged_temporary_table caused replication
to stop.
- Rename of not binlogged temporary tables where binlogged to binary log
which caused replication to stop.
Changes in behavior:
- By default create_temporary_table_binlog_formats=STATEMENT, which
means that CREATE TEMPORARY is not logged to binary log under MIXED
binary logging. This can be changed by setting
create_temporary_table_binlog_formats to MIXED,STATEMENT.
- Using temporary tables that was not logged to the binary log will
cause any query using them for updating other tables to be logged in
ROW format. Before all queries was logged in ROW format if the user had
any temporary tables, even if they were not used by the query.
- Generated DROP TEMPORARY TABLE is now always using IF EXISTS and
has a "generated by server" comment in the binary log.
The consequences of the above is that manipulations of a lot of rows
through temporary tables will by default be be slower in mixed mode.
For example:
BEGIN;
CREATE TEMPORARY TABLE tmp AS SELECT a, b, c FROM
large_table1 JOIN large_table2 ON ...;
INSERT INTO other_table SELECT b, c FROM tmp WHERE a <100;
DROP TEMPORARY TABLE tmp;
COMMIT;
By default this will create a huge entry in the binary log, compared
to just a few hundred bytes in statement mode. However the change in
this commit will make usage of temporary tables more reliable and
predicable and is thus worth it. Using statement mode or
create_temporary_table_binlog_formats can be used to avoid this issue.
