Atomic CREATE OR REPLACE allows to keep an old table intact if the
command fails or during the crash. That is done by renaming the
original table to temporary name, as a backup and restoring it if the
CREATE fails. When the command is complete and logged the backup
table is deleted.
Atomic replace algorithm
Two DDL chains are used for CREATE OR REPLACE:
ddl_log_state_create (C) and ddl_log_state_rm (D).
1. (C) Log rename of ORIG to TMP table (Rename TMP to original).
2. Rename orignal to TMP.
3. (C) Log CREATE_TABLE_ACTION of ORIG (drops ORIG);
4. Do everything with ORIG (like insert data)
5. (D) Log drop of TMP
6. Write query to binlog (this marks (C) to be closed in
case of failure)
7. Execute drop of TMP through (D)
8. Close (C) and (D)
If there is a failure before 6) we revert the changes in (C)
Chain (D) is only executed if 6) succeded (C is closed on
crash recovery).
Foreign key errors will be found at the 1) stage.
Additional notes
- CREATE TABLE without REPLACE and temporary tables is not affected
by this commit.
set @@drop_before_create_or_replace=1 can be used to
get old behaviour where existing tables are dropped
in CREATE OR REPLACE.
- CREATE TABLE is reverted if binlogging the query fails.
- Engines having HTON_EXPENSIVE_RENAME flag set are not affected by
this commit. Conflicting tables marked with this flag will be
deleted with CREATE OR REPLACE.
- Replication execution is not affected by this commit.
- Replication will first drop the conflicting table and then
creating the new one.
- CREATE TABLE .. SELECT XID usage is fixed and now there is no need
to log DROP TABLE via DDL_CREATE_TABLE_PHASE_LOG (see comments in
do_postlock()). XID is now correctly updated so it disables
DDL_LOG_DROP_TABLE_ACTION. Note that binary log is flushed at the
final stage when the table is ready. So if we have XID in the
binary log we don't need to drop the table.
- Three variations of CREATE OR REPLACE handled:
1. CREATE OR REPLACE TABLE t1 (..);
2. CREATE OR REPLACE TABLE t1 LIKE t2;
3. CREATE OR REPLACE TABLE t1 SELECT ..;
- Test case uses 6 combinations for engines (aria, aria_notrans,
myisam, ib, lock_tables, expensive_rename) and 2 combinations for
binlog types (row, stmt). Combinations help to check differences
between the results. Error failures are tested for the above three
variations.
- expensive_rename tests CREATE OR REPLACE without atomic
replace. The effect should be the same as with the old behaviour
before this commit.
- Triggers mechanism is unaffected by this change. This is tested in
create_replace.test.
- LOCK TABLES is affected. Lock restoration must be done after new
table is created or TMP is renamed back to ORIG
- Moved ddl_log_complete() from send_eof() to finalize_ddl(). This
checkpoint was not executed before for normal CREATE TABLE but is
executed now.
- CREATE TABLE will now rollback also if writing to the binary
logging failed. See rpl_gtid_strict.test
backup ddl log changes
- In case of a successfull CREATE OR REPLACE we only log
the CREATE event, not the DROP TABLE event of the old table.
ddl_log.cc changes
ddl_log_execute_action() now properly return error conditions.
ddl_log_disable_entry() added to allow one to disable one entry.
The entry on disk is still reserved until ddl_log_complete() is
executed.
On XID usage
Like with all other atomic DDL operations XID is used to avoid
inconsistency between master and slave in the case of a crash after
binary log is written and before ddl_log_state_create is closed. On
recovery XIDs are taken from binary log and corresponding DDL log
events get disabled. That is done by
ddl_log_close_binlogged_events().
On linking two chains together
Chains are executed in the ascending order of entry_pos of execute
entries. But entry_pos assignment order is undefined: it may assign
bigger number for the first chain and then smaller number for the
second chain. So the execution order in that case will be reverse:
second chain will be executed first.
To avoid that we link one chain to another. While the base chain
(ddl_log_state_create) is active the secondary chain
(ddl_log_state_rm) is not executed. That is: only one chain can be
executed in two linked chains.
The interface ddl_log_link_chains() was defined in "MDEV-22166
ddl_log_write_execute_entry() extension".
Atomic info parameters in HA_CREATE_INFO
Many functions in CREATE TABLE pass the same parameters. These
parameters are part of table creation info and should be in
HA_CREATE_INFO (or whatever). Passing parameters via single
structure is much easier for adding new data and
refactoring.
Aria changes:
- Fixed issue in Aria engine with CREATE + locked tables
that data was not properly commited in some cases in
case of crashes.
InnoDB related changes (by Marko Mäkelä):
- table_name_t::is_create_or_replace(): A new predicate to check for
CREATE OR REPLACE TABLE will rename an old table to
and eventually drop after creating the replacement.
- dict_table_t::parse_name(): Do acquire MDL on #sql-create- names
for partitioned tables.
- dict_table_rename_in_cache(): On CREATE OR REPLACE TABLE ... SELECT,
forget the original dict_table_t::mdl_name so that purge will
acquire MDL on the #sql-create- name instead. In this way, the
MDL_EXCLUSIVE that the CREATE OR REPLACE TABLE holds on the
user-visible name will not unnecessarily block any purge of old history
until the very end when the #sql-create- table will be dropped.
- ha_innobase::delete_table(): Do not check FOREIGN KEY consistency
when dropping an #sql-create- table.
- row_rename_table_for_mysql(): Update SYS_FOREIGN.ID also
when renaming to #sql-create- in order to avoid any
duplicate key error when CREATE OR REPLACE TABLE is
creating some FOREIGN KEY constraints by names
that existed in the old table.
Other changes:
- Removed some auto variables in log.cc for better code readability.
- Fixed old bug that CREATE ... SELECT would not be able to auto repair
a table that is part of the SELECT.
- Marked MyISAM that it does not support ROLLBACK (not required but
done for better consistency with other engines).
- maria_create_trn_for_mysql() does not register a new transaction
handler for commits. This was needed to ensure create or replace
will not end with an active transaction.
- We do not get anymore warnings about "Engine not supporting atomic
create" when doing a legal CREATE OR REPLACE on a table with
foreign key constraints.
- Updated VIDEX engine flags to disable CREATE SEQUENCE.
- Removed mysql_mutex_unlock(&LOCK_gdl) / mysql_mutex_lock(&LOCK_gdl)
around calls to binlog as these are unsafe. The binlog code uses
global variables that needs protection from other caller.
- EITS data is preserved if create or replace fails if
drop_before_create_or_replace=OFF. If ON, then create or replace
will drop EITS before the drop of the original table (as before).
- Using CREATE OR REPLACE on a encrypted table that the user cannot
decrypt will fail instead of replacing the encrypted table.
The encrypted table will unchanged.
Known issues:
- One cannot use create or replace on an InnoDB tables that has foreign
key references point to it
- CREATE OR REPLACE TEMPORARY table is not full atomic. Any conflicting
table will always be dropped before creating a new one. (Old behaviour).
Bug fixes related to this MDEV:
MDEV-36435 Assertion failure in finalize_locked_tables()
MDEV-36439 Assertion `thd_arg->lex->sql_command != SQLCOM_CREATE_SEQUENCE...
MDEV-36498 Failed CoR in non-atomic mode no longer generates DROP in RBR...
MDEV-36508 Temporary files #sql-create-....frm occasionally stay after
crash recovery
MDEV-38479 Crash in CREATE OR REPLACE SEQUENCE when new sequence cannot
be created
MDEV-36497 Assertion failure after atomic CoR with Aria under lock in
transactional context
MDEV-36501 EITS data is lost after failed attempt to CREATE OR REPLACE
table
MDEV-36493 Atomic CREATE OR REPLACE ... SELECT blocks InnoDB purge
MDEV-39367 MSAN/valgrind errors in temp_file_size_cb_func,
main.tmp_space_usage fails
MDEV-39446 Atomic CREATE OR REPLACE fails if a table cannot be decrypted
MDEV-40776 Atomic CREATE OR REPLACE silently breaks the foreign key
MDEV-40765 Assertion `"unexpected references" == 0' failed upon failing
CREATE OR REPLACE
MDEV-40994 Atomic CoR: Transaction not registered for MariaDB 2PC, but
transaction is active
Reverted commits:
- MDEV-36685 "CREATE-SELECT may lose in binlog side-effects of
stored-routine" as it did not take into account that it safe to
clear binlogs if the created table is non transactional and there
are no other non transactional tables used.
This was done because it caused extra logging when it is not
needed (not using any non transactional tables) and it also did
not solve side effects when using statement based loggging.
This is part of MDEV-25292 Atomic CREATE OR REPLACE TABLE.
Removed default values for arguments, added flags argument to specify
filename flags (FN_TO_IS_TMP, FN_FROM_IS_TMP) and forward the flag to
build_table_name().
Original patch from: Aleksey Midenkov <midenok@gmail.com>
* keep `vec_len >= subdist_part * 2` logic in one place only
* keep "distance-greater-than" mode logic in one place only
* simplify VECTOR_DIMENSIONS (no need to have a special ctx->vec_len
path if the other one always works)
* new plugin = maturity beta
* remove redundant casts, etc
* moved vector_indexes_fields_enum to the global scope to use it
for setting schema->idx_field1/schema->idx_field2
* open the hlindex graph table, if needed, otherwise most values
are unknown unless a user did vector search before
* added TABLE_CATALOG column
* remove CACHE_OVERFLOWS column, doesn't work as implemented,
the fix is complex and isn't worth it
* add privilege checks (MDEV-40793)
in the test:
* prefer query_vertical for readability
* select all columns at least once
* select INDEX_SIZE even if engine-dependent, use rdiff files
* test how get_all_tables only open one specific table, and
even only .frm file, if possible
Make show_create_table_ex/get_default_field_value() use
Field::real_maybe_null(), not maybe_null().
Before, the difference didn't matter as show_create_table_ex() was
only invoked in SHOW CREATE TABLE where columns are not made NULLable.
Optimizer context code invokes show_create_tableas show_create_table_ex()
in all queries, so now the distinction matters.
This task loads the stats of the tables that are used in a query,
from the trace into the optimizer. This feature is controlled
by optimizer_replay_context, and points to an user defined variable name,
that has the context information.
The stats such as num_of_records present in the table, indexes if present,
their names, along with the average number of records_per_key
with in each index, cost of reading an index are loaded from the trace.
Additionally, stats from range analysis i.e. ranges, and the
corresponding number of records, along with their costs are also
loaded from the trace.
The trace context which is in JSON format is firstly set into the
user defined session variable, and this variable name is set to
optimizer_replay_context system variable.
Later, when a user issues a query, the contents of the json context is
parsed and an in memory representation is built using the class
Optimizer_context_replay. This class is then used by the optimizer
to update and save original values of the tables, indexes, and range stats
in the methods "set_statistics_for_table()" of sql_statistics.cc, and
"check_quick_select()" of opt_range.cc.
After the query gets finished, the statistics that were updated
in the optimizer are restored back to the saved original values.
The entry point for parsing the json structure is in
"mysql_execute_command()" of sql_parse.cc, and similarly exit point
i.e. to restore the saved stats of the optimizer is at the end
of the same method.
Two new warning/error messages are introduced:
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_PARSE_FAILED, and
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_MATCH_FAILED
CREATE SERVER used to:
* support arbitrary options in backticks and not in backticks
* hard-coded historical options worked *only* without backticks
* PORT range was different when given as a number or a string
all that is fixed, unused keywords are removed
The "is it my own thread" check compared the login user name
(Security_context::user) and ignored the host, so u1@localhost could see
and kill threads of u1@'127.0.0.1'.
Compare the authenticated account instead:
* all comparisons are done in sctx->is_priv_user() now
* change user_matches() to priv_user_matches(), which uses is_priv_user()
* use it in KILL, KILL USER, SHOW PROCESSLIST, I_S.PROCESSLIST,
COM_PROCESS_INFO and SHOW EXPLAIN/ANALYZE FOR.
* all the remaining places use is_priv_user() directly instead
of doing strcmp: SHOW GRANTS, SHOW CREATE PROCEDURE, I_S.VIEWS,
the DEFINER clause, optimizer trace and change_security_context().
Assisted-by: Claude:claude-5-opus
Store all READ_ONLY sysvar values in the read_only_root
Even though READ_ONLY sysvars are protected, for string variables
it usually means that the pointer cannot be changed. The value it
points to - the string itself - still can be. Let's store all
values of string READ_ONLY sysvars in the read_only_root.
sysvars that point directly into argv are copied to read_only_root.
sysvars that have their values calculated and allocated now
must be explicitly marked with PREALLOCATED to let it know they
have to be free()-d.
Assisted-By: Claude:claude-4.8-opus
Protect all READ_ONLY sysvars from run-time changes.
Put them into a separate section and use my_virtual_mem_protect()
to make this section read-only before the server starts accepting
connections.
Verify that they're all protected in the sys_var constructor.
One exception: opt_noacl (--skip-grant-tables) can be changed
from 1 to 0 on FLUSH PRIVILEGES. Let's briefly drop the
protection for this 1->0 change. It can be needed only once
in a server lifetime and only if it was started with --skip-grant-tables
On shutdown the protection is removed, making variables writable
again because shutdown resets some of them during the cleanup
Assisted-By: Claude:claude-4.8-opus
WITH GRANT OPTION is not a privilege in the standard sense,
so whenever INFORMATION_SCHEMA visibility dictates "any privilege"
this does not include WITH GRANT OPTION, even though GRANT_ACL
is a privilege in MariaDB.
Assisted-By: Claude:claude-4.6-sonnet
1.
Only allow #mysql50# prefix on table names that
can not be decoded from a file name to a table name.
It is an error to put this prefix on a name that can be accessed
as a valid table name without a prefix. Use `t1` not `#mysql50#t1`,
use `a-b` not `#mysql50#a@002db`.
2.
Don't allow creation of new tables with the #mysql50# prefix.
Except when in SQLCOM_ALTER_DB_UPGRADE - main.upgrade test runs
ALTER DATABASE `#mysql50#mysqltest-1` UPGRADE DATA DIRECTORY NAME;
This command moves tables from the old db name to the new db name,
and some of these tables also have old #mysql50# names.
3.
Also don't allow to create such databases, views, partitions,
subpartitions, sequences, triggers.
Three problems:
1. Option value specified as `foo bar` was not quoted,
printed as foo bar, breaking CREATE TABLE.
2. Option value that included */ was printed as is, in unknown
options when the whole option was commented out, this broke the
comment: /* opt='val*/ue' */
3. Option names can include */ too, `opt*/ion`=1
Fix: simplify value printing, ignore the original quoting, always
print the value quoted as a string. Break comment markers with an
escape (as *\/) if inside a comment. As \-escapes don't work inside,
backticks, we cannot use `-quoting for option values at all.
For the same reason we cannot allow */ inside option names, thus
let's only allow keywords for option names and reject fancy names
even if IGNORE_BAD_TABLE_OPTIONS is in force.
gcc 15.1.15 gives the following error, possible from static struct
initialization:
/my/maria-11.8/sql/sql_show.cc: In function ‘void __static_initialization_and_destruction_0()’:
/my/maria-11.8/sql/sql_show.cc:11356:1: error: the frame size of 62880 bytes is larger than 16384 bytes [-Werror=frame-larger-than=]
Fixed by adding PRAGMA_DISABLE_CHECK_STACK_FRAME to sql_show.cc
This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.
Examples.
-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
(select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
from (select * from t1, t2 where t.1.a > t2.a) as DT;
-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- CTE
with aless100 as (select a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
Limitations:
- Only SELECT statements support implicit QB names. DML operations
(UPDATE, DELETE, INSERT) only support explicit QB names
Vector indexes are stored in a separate internal table (hlindex).
The main table's storage engine knows nothing about it, INDEX_LENGTH
was always reported as 0 for tables with vector indexes.
the fix: after calling info() on the main table handler in sql_show.cc,
check if the table has an hlindex, open it if needed, and add its data_file_length to
index_file_length before writing to the information schema record.
Problem:
UBSAN reports runtime errors in string comparision functions when
pointer arithmetic is done without checking NULL.
Fix:
- Add debug asserts in `strnncoll` function to catch NULL pointers.
- Define a new function `streq_safe` as a replacement for `streq`,
capable of handling NULL pointers. Replace `streq` with `streq_safe`
at multiple call sites, identified by the debug asserts.
- Add empty identifier checks in `is_infoschema_db` and
`is_perfschema_db` before calling the deep character-set based
comparision.
- Add IS_DEPRECATED and DEPRECATION_REPLACEMENT columns to the
SYSTEM_VARIABLES information schema table
- Add a mysql-test case to verify the new columns output correctly
gcc 15.1.15 gives the following error, possible from static struct
initialization:
/my/maria-11.8/sql/sql_show.cc: In function ‘void __static_initialization_and_destruction_0()’:
/my/maria-11.8/sql/sql_show.cc:11356:1: error: the frame size of 62880 bytes is larger than 16384 bytes [-Werror=frame-larger-than=]
Fixed by adding PRAGMA_DISABLE_CHECK_STACK_FRAME to sql_show.cc
paritioning has its own method for printing engine options,
it wasn't consistent with how table options were printed - didn't
use quotes, ignored sql_mode, so wasn't future and dump/import safe.
let's get rid of it and use the same method that prints per-table
engine options.
let's simplify the code by removing copying of part_elem->connect_string
to table->s->connect_string. Move CONNECTION support from he server into
engines, engines define TOPTION("CONNECTION") if they want to support it,
it's stored in the option_struct and handled by the option_struct fix.
Problems:
* Mroonga used CONNECTION for something but there were no tests for it.
Removed Mroonga support for CONNECTION as requested by the maintainer
in MDEV-38530
* DROP/ALTER SERVER command used to close all tables using the server
in question, avoiding the need for FLUSH TABLES. This functionality
never worked for partitioned tables anyway and is now removed. Will be
done properly in MDEV-37827.
