Issue:
Mariadb acquires additional MDL locks on UPDATE/INSERT/DELETE statements
on table with foreign keys. For example, table t1 references t2, an
UPDATE to t1 will MDL lock t2 in addition to t1.
A replica may deliver an ALTER t1 and UPDATE t2 concurrently for
applying. Then the UPDATE may acquire MDL lock for t1, followed by a
conflict when the ALTER attempts to MDL lock on t1. Causing a BF-BF
conflict.
Solution:
Additional keys for the referenced/foreign table needs to be added
to avoid potential MDL conflicts with concurrent update and DDLs.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>
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
- add a comment that opt_hints_global->check_unresolved() is never called
- improve comments
- rename everything with "resolved_children" to "fully_resolved_children"
- Opt_hints_table::adjust_key_hints() now returns value
- less "reach-back-to-parent" logic
- rename Hint "adjustment" and "resolution" (yes, both terms were used)
to "fixing". "Resolution" is already used for parse-tree objects
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.
- Using Lex_ident_sys to scan identifiers, like the SQL parser does.
This fixes handling of double-quote-delimited and backtick-delimited identifiers,
as well as handling of non-ASCII identifiers.
Unescaping and converting from the client character set to the system
character set is now done using Lex_ident_cli_st and Lex_ident_sys,
like it's done in the SQL tokenizer/parser.
Adding helper methods to_ident_cli() and to_ident_sys()
in Optimizer_hint_parser::Token.
- Fixing the hint parser to report a syntax error when an empty identifiers:
SELECT /*+ BKA(``) */ * FROM t1;
- Moving a part of the code from opt_hints_parser.h to opt_hints_parser.cc
Moving these method definitions:
- Optimizer_hint_tokenizer::find_keyword()
- Optimizer_hint_tokenizer::get_token()
to avoid huge pieces of the code in the header file.
- A Lex_ident_cli_st cleanup
Fixing a few Lex_ident_cli_st methods to return Lex_ident_cli_st &
instead of void, to use them easier in the caller code.
- Fixing the hint parser to display the correct line number
Adding a new data type Lex_comment_st
(a combination of LEX_CSTRING and a line number)
Using it in sql_yacc.yy
- Getting rid of redundant dependencies on sql_hints_parser.h
Moving void LEX::resolve_optimizer_hints() from sql_lex.h to sql_lex.cc
Adding a class Optimizer_hint_parser_output, deriving from
Optimizer_hint_parser::Hint_list. Fixing the hint parser to
return a pointer to an allocated instance of Optimizer_hint_parser_output
rather than an instance of Optimizer_hint_parser::Hint_list.
This allows to use a forward declaration of Optimizer_hint_parser_output
in sql_lex.h and thus avoid dependencies on sql_hints_parser.h.
This commit introduces:
- the infrastructure for optimizer hints;
- hints for join buffering: BNL(), NO_BNL(), BKA(), NO_BKA();
- NO_ICP() hint for disabling index condition pushdown;
- MRR(), MO_MRR() hint for multi-range reads control;
- NO_RANGE_OPTIMIZATION() for disabling range optimization;
- QB_NAME() for assigning names for query blocks.
Valgrind is single threaded and only changes threads as part of
system calls or waits.
Some busy loops were identified and fixed where the server assumes
that some other thread will change the state, which will not happen
with valgrind.
Based on patch by Monty. Original patch introduced VALGRIND_YIELD,
which emits pthread_yield() only in valgrind builds. However it was
agreed that it is a good idea to emit yield() unconditionally, such
that other affected schedulers (like SCHED_FIFO) benefit from this
change. Also avoid pthread_yield() in favour of standard
std::this_thread::yield().
Valgrind is single threaded and only changes threads as part of
system calls or waits.
I found some busy loops where the server assumes that some other
thread will change the state, which will not happen with valgrind.
Added VALGRIND_YIELD to the loops, which calls pthread_yield() if
HAVE_VALGRIND is defined.
Added pthread_yield() to the loops in table_cache.
We should consider changing some of the VALGRIND_YIELD calls to call
pthread_yield() as busy loop without any sleep() is usually a bad
thing.
Reviewer: svojtovich@gmail.com
Get rid of need of matherialization for usual INSERT (cache results in
Item_cache* if needed)
- subqueries in VALUE do not see new records in the table we are
inserting to
- subqueries in RETIRNING prohibited to use the table we are inserting to
"t1 JOIN t2 USING(col1,...)" calls mark_common_columns() to mark the
listed columns as used in both used tables, t1 and t2.
Due to a typo bug, it would mark the wrong column in the second table
(t2): instead of t2.col1 it would mark the last column in t2.
The harmful effects included JOIN_TAB(t2)->covering_keys not being
set correctly. This changed the cost to access the table and then
caused different query plans depending on which table was the second
in the JOIN ... USING syntax.
let's unlock and relock hlindexes under LOCK TABLES.
an alternative would be to do start_stmt for them too,
but this is simpler and keeps them locked only for statements
that actually use them
Newer gcc reports:
error: 'rfield' may be used uninitialized [-Werror=maybe-uninitialized]
9041 | unwind_stored_field_offsets(fields, rfield);
After investigation, it turned to be an impossible case:
1. The only way it could be broken is if
if (!(field= fld->field_for_view_update()))
line case would succeed from the first time.
2. Consequent checks initialize rfield.
fld may return NULL in field_for_view_update() only for views.
3. Before fill_record, UPDATE first calls check_fields, where
field_for_view_update() result is already checked. INSERT calls
check_view_insertability that checks that all view fields are
updateable.
It all means that field_for_view_update() cannot be NULL in fill_record,
so the if can be converted to DBUG_ASSERT.
This essentially shifts the responsibility on preliminary
field_for_view_update() check to the caller.
In this patch:
1. convert field_for_view_update() check to DBUG_ASSERT
2. harden unwind_stored_field_offsets function so that it can be used
even if field_for_view_update() is NULL
3. As a consequence, `field` is passed instead of `rfield` as a
terminator.
4. Initialize `field` to NULL to bypass a false-positive warning!
MDEV-34171 denied removing indirect routines/tables after
recover_from_failed_open() for auto-create partition case. Now we are
going further and keep them for any failed table reopen.
MDEV-34171 did not handle correctly open_and_process_routine() after
that skip of sp_remove_not_own_routines(). Now it is fixed by
sroutine_to_open correct usage.
Propagate discard/import tablespace request to hlindexes.
Let FLUSH TABLES ... FOR EXPORT open/lock hlindexes, so that InnoDB
prepares hlindexes for export.
Moved reset_hlindexes() to external_lock(F_UNLCK), so that hlindexes
are available for export until UNLOCK TABLES.
Closes#3631
* rpl.rpl_system_versioning_partitions updated for MDEV-32188
* innodb.row_size_error_log_warnings_3 changed error for MDEV-33658
(checks are done in a different order)
After the fix in the previous commit, we should never end up in a
situation where select_lex->first_cond_optimization=false (i.e, "it's done")
but select_lex->leaf_tables_saved==false ("not done").
So, in setup_tables(), revert the the condition added in the fix for
MDEV-25008. Add an assert instead.
InnoDB is limited to row-logging when transaction isolation level is
READ COMMITTED or READ UNCOMMITTED. This limitation is enforced by
a check within ha_innobase::external_lock().
InnoDB also expects number of "successful external locks" to match
number of "external unlocks", which is controlled by the assertion
in subject.
However "unlock" was called even after failing "lock" for high-level
indexes.
Fixed by calling "unlock" only after "successful lock".
