A failed ALTER TABLE ... RENAME INDEX ... ALGORITHM=INSTANT changed the
outcome of a subsequent, unrelated ALTER TABLE ... ADD FOREIGN KEY. A
failed statement must have no side effects, but here the second statement
wrongly succeeded where on a fresh table it correctly fails with
ER_DUP_KEYNAME.
Root cause: while rebuilding the key list, mysql_prepare_alter_table()
handled a RENAME INDEX request by clearing HA_GENERATED_KEY in place on
key_info->flags. key_info points into the (possibly cached) TABLE object
that is reused across statements. The ALGORITHM=INSTANT incompatibility is
only detected later, after mysql_prepare_alter_table() has returned, so the
statement fails with ER_ALTER_OPERATION_NOT_SUPPORTED with the cleared flag
never restored. The cached generated FK-support index (fk1) was thus left
permanently marked as user-defined.
That corrupted flag flips the de-duplication tie-break in the next ALTER:
adding FOREIGN KEY ind1 (b) creates a generated support index on column b
that prefix-matches fk1(b). Normally fk1 (generated) is dropped and the new
ind1 survives, colliding by name with the existing user index ind1(a) and
raising ER_DUP_KEYNAME. With fk1 no longer marked generated, the new ind1
is dropped instead, so no name collision is reached and the ADD FOREIGN KEY
silently succeeds.
Fix: do not mutate the cached key_info->flags. Track the "renamed => no
longer generated" decision in a per-key local variable (generated_key),
initialised from the flag, set to false on rename, and passed to the Key
constructor. This preserves the in-statement behaviour while leaving the
cached TABLE metadata untouched, so a failed ALTER has no lingering effect.
All new code of the whole pull request, including one or several files that
are either new files or modified ones, are contributed under the BSD-new
license. I am contributing on behalf of my employer Amazon Web Services, Inc.
Currently, ALTER TABLE ... AUTO_INCREMENT = N silently fails when using
values lower than the next AUTO_INCREMENT value in the column.
Add a note when attempting to set AUTO_INCREMENT to a lower value than
the next AUTO_INCREMENT value, and maintain the higher value.
Update existing test suites and add a new test to the maria suite for
the newly added note.
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.
Force REPEATABLE READ isolation for the duration of mysql_alter_table,
so HA_CHECK_UNIQUE_AFTER_WRITE never triggers during ALTER's exclusive table copy.
(cherry-pick of 46ee272a10 - online ALTER TABLE (MDEV-32100))
Field CHECK constraints always have the name of the corresponding field.
Table-level CHECK constraint can be arbitrary named and can have
the name of the field with a CHECK constraint.
Detect this and throw ER_DUP_CONSTRAINT_NAME.
Special treatment for auto-generated constraint names
(CONSTRAINT_1, etc) they can match a field name too - this is not
an error, just auto-generate a different name.
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.
Problem:
InnoDB index corruption occurs when an index is renamed to a name that
differs only in case (e.g., 'b' to 'B'). The SQL layer uses
case-insensitive comparison and fails to recognize the change.
Fix:
Use case-sensitive comparison when matching index names during
ALTER TABLE to correctly identify and handle case changes.
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:
========
The issue occurs when ALTER TABLE contains duplicate DROP FOREIGN KEY
operations (e.g., "DROP FOREIGN KEY f1, DROP FOREIGN KEY f1").
In fk_prepare_copy_alter_table(), Removes from the list all foreign
keys which are to be dropped. But iterator continues to accessing the
removed entry when it encounters duplicate foreign key.
Solution:
=========
mysql_prepare_alter_table(): Add duplicate detection logic to handle
cases where the same foreign key is specified multiple times in a
single ALTER TABLE statement's DROP statement.
fk_prepare_copy_alter_table(): fk_parent_key_list and fk_child_key_list
contain all existing foreign keys where the table acts as parent or child
respectively, and they are populated through storage engine calls.
After this phase, the code iterates through these collections comparing
each foreign key against alter_info->drop_list using case-insensitive
string matching for constraint names and database/table names.
- When matches are found, instead of removing them immediately,
the matching FOREIGN_KEY_INFO objects are added to the
keys_to_remove collection, which acts as a temporary holding area.
Once all matching keys are collected in keys_to_remove, use it to
remove the key entries from original lists.
Problem:
=========
An assertion failure occurs in InnoDB during consecutive
ALTER TABLE operations using the COPY algorithm. The crash
happens during the table rename phase because the
source table (dict_table_t::n_ref_count) is non-zero, despite
the thread holding an exclusive Metadata Lock (MDL).
Reason:
========
When ALTER IGNORE TABLE is executed via the COPY algorithm,
it generates undo logs for every row inserted into the
intermediate table (e.g., #sql-alter-...). The background Purge
Thread, responsible for cleaning up these undo logs, attempts
to take an MDL on the table to prevent the table from
being dropped while in use.
Race condition:
==================
First ALTER: Creates #sql-alter-, copies data, and renames it to t1.
Purge Activation: The Purge thread picks up the undo logs from step 1.
It takes an MDL on the temporary name (#sql-alter-) and increments
the table's n_ref_count.
Identity Shift: InnoDB renames the physical table object to t1, but
the Purge thread still holds a reference to this object.
Second ALTER: Starts a new copy process. When it attempts to rename
the "new" t1 to a backup name, it checks if n_ref_count == 0.
Because the Purge thread is still "pinning" the object to
clean up logs from the first ALTER, the count is > 0,
triggering the assertion failure.
Solution:
========
ALTER IGNORE TABLE needs row-level undo logging to easily
roll back the last inserted row in case of duplicate key errors.
By discarding the last undo log record after inserting each row,
purge will not process any log records generated by
ALTER IGNORE TABLE, preventing unexpected access from the purge
subsystem during subsequent DDL operations.
Make skip_alter_undo (1-bit) to (2-bit enum)
to support different ALTER operation modes:
- NO_UNDO (0): Normal mode with standard undo logging
- SKIP_UNDO (1): ALTER mode that skips undo logging
- IGNORE_UNDO (2): ALTER IGNORE mode that rewrites undo blocks
trx_undo_report_row_operation(): Add ALTER IGNORE undo
rewriting logic. Store old undo record info before writing
new records for IGNORE_UNDO mode. Reset undo top_offset
to maintain only latest insert undo
row_ins_clust_index_entry_low(): Prevent ALTER IGNORE from
using bulk insert optimization. since it requires to write
undo log for each row operation that's incompatible with
existing bulk operation.
- This issue shares the same root cause of MDEV-38667. This
patch fixes the remaining caller of HA_EXTRA_END_ALTER_COPY by
temporarily setting abort_on_warning to false that prevents
the warning to error escalation.
Fix rpl suite tests added by MDEV-25039.
rpl_foreign_key_lock_table_insert.test is removed altogether because it
is unclear what the purpose of the test is. The changes of the patch
were done on the slave, yet all operations in the test were done on the
master. Nothing different could happen on the slave because it is
configured to be serial, so all transactions would run sequentially
anyway, and no validations were performed.
rpl_foreign_key_ddl_insert.test was renamed to
rpl_row_foreign_key_mdl.test and the test itself was re-written to be
a minimal test case to ensure that MDL locking behavior is different
pre- and post- patch. A few problems with the original test:
* No foreign-key locking was done on the slave because the table
engine was not InnoDB.
* rpl_fk_ddl.inc had inconsistent validation checking. I.e., the child
query validation checks were done on the master (which is incorrect)
and because the slave was configured to be serial, the two
transactions could not run concurrently on the slave anyway.
Issue:
On galera write node INSERT statements does not acquire MDL locks on it's all child
tables and thereby wsrep certification keys are also added for limited tables, but
on applier nodes it does acquire MDL locks for all child tables. This can result
into MDL BF-BF conflict on applier node when transactions referring to parent and
child tables are executed concurrently. For example:
Tables with foreign keys: t1<-t2<-t3<-t4
Conflicting transactions: INSERT t1 and DROP TABLE t4
Wsrep certification keys taken on write node:
- for INSERT t1: t1 and t2
- for DROP TABLE t4: t4
On applier node MDL BF-BF conflict happened between two transaction because
MDL locks on t1, t2, t3 and t4 were taken for INSERT t1, which conflicted
with MDL lock on t4 taken by DROP TABLE t4.
The Wsrep certification keys helps in resolving this MDL BF-BF conflict by
prioritizing and scheduling concurrent transactions. But to generate Wsrep
certification keys it needs to open and take MDL locks on all the child tables.
On applier nodes Write_rows event is implicitly a REPLACE, deleting all conflicting
rows which can cause cascading FK actions and locks on foreign key children tables.
Solution:
For Galera applier nodes the Write_rows event is considered pure INSERT
which will never cause cascading FK actions and locks on foreign key children tables.
Run-time has semantics duplication in unireg_check, default_value and
flags, so all three must be in sync before FRM creation. Special
unireg_check values for temporal field types was introduced by
32b28f9298 WL#1266 "Separate auto-set logic from TIMESTAMP type."
Rename cloning methods of class Item and its descendants
in the following way:
(from) (to)
do_build_clone -> deep_copy
build_clone -> deep_copy_with_checks
do_get_copy -> shallow_copy
get_copy -> shallow_copy_with_checks
to better reflect their functionality.
Also make Item::deep_copy() and shallow_copy() protected.
Outside users should call deep_copy_with_checks()
and shallow_copy_with_checks().
...just like the when deleting the .frm file in the same function.
This improves consistency: internal duplicate deletion of .par file
and .frm file both fail silently.
Problem was that thd->lex->m_sql_cmd is not always set
especially when user has not provided ENGINE=xxx so
requesting option_storage_engine_name from there is not
safe.
Fixed by accessing thd->lex->m_sql_cmd only when user
has used ENGINE= and if not using ha_default_handlerton
and requesting engine name after it.
- Removed duplicate words, like "the the" and "to to"
- Removed duplicate lines (one double sort line found in mysql.cc)
- Fixed some typos found while searching for duplicate words.
Command used to find duplicate words:
egrep -rI "\s([a-zA-Z]+)\s+\1\s" | grep -v param
Thanks to Artjoms Rimdjonoks for the command and pointing out the
spelling errors.
Ensure that Annotate_rows is always written direct after GTID information,
before any table_map events.
Before this patch, the following problems existed when mixing
transactional and not transactional tables in the same statement:
- Annotate rows could be written after row events or in the next GTID
event.
- See rpl_row_mixing_engines
- Annotate_rows was not always written to binary log in case of error
with a transactional table (rolled back) but a not transactional
table was updated.
- See sp_trans_log, binlog_row_mix_innodb_myisam
Fixed by writing the Annotate_rows event into the non transactional
cache if there are not transactional tables used. If not, write the
event into the transactional cache.
See also MDEV-30046.
Idempotent write_row works same as REPLACE: if there is a duplicating
record in the table, then it will be deleted and re-inserted, with the
same update optimization.
The code in Rows:log_event::write_row was basically copy-pasted from
write_record.
What's done:
REPLACE operation was unified across replication and sql. It is now
representred as a Write_record class, that holds the whole state, and allows
re-using some resources in between the row writes.
Replace, IODKU and single insert implementations are split across different
methods, reluting in a much cleaner code.
The entry point is preserved as a single Write_record::write_record() call.
The implementation to call is chosen on the constructor stage.
This allowed several optimizations to be done:
1. The table key list is not iterated for every row. We find last unique key in
the order of checking once and preserve it across the rows. See last_uniq_key().
2. ib_handler::referenced_by_foreign_key acquires a global lock. This call was
done per row as well. Not all the table config that allows optimized replace is
folded into a single boolean field can_optimize. All the fields to check are
even stored in a single register on a 64-bit platform.
3. DUP_REPLACE and DUP_UPDATE cases now have one less level of indirection
4. modified_non_trans_tables is checked and set only when it's really needed.
5. Obsolete bitmap manipulations are removed.
Also:
* Unify replace initialization step across implementations:
add prepare_for_replace and finalize_replace
* alloca is removed in favor of mem_root allocation. This memory is reused
across the rows.
* An rpl-related callback is added to the replace branch, meaning that an extra
check is made per row replace even for the common case. It can be avoided with
templates if considered a problem.
MDEV-33658 part 1’s refactoring ecaedbe299
introduced a new function init_key_info which (in part) aims to
calculate the total key length; however, it doesn’t account for the
key already having been initialized (as happens when called via
ALTER TABLE .. CONVERT PARTITION .. TO TABLE). This leads to crashes
when this key is later iterated over, because the iterator will try
to iterate over additional key parts which don’t exist because the
length reports as longer than the actual memory owned. The crash
reported by MDEV-36906 highlights this in function key_copy.
To explain how the keys already have been initialized, init_key_info
is called multiple times. That is, init_key_info is called from
mysql_prepare_create_table, which prepares a table and its key
structures for table creation, which is in turn called by
mysql_write_frm when using flags MFRM_WRITE_SHADOW and
MFRM_WRITE_CONVERTED_TO. The
ALTER TABLE .. CONVERT PARTITION .. TO TABLE use case (see function
fast_alter_partition_table), calls mysql_write_frm multiple times with
both of these flags set (first with MFRM_WRITE_CONVERTED_TO and then
with MFRM_WRITE_SHADOW).
Raising it up a level, mysql_prepare_create_table doesn't need to be
called again after it has already been invoked when just writing frms.
Init_key_info is the only place in that function which leads to side
effects, but the rest is redundant and can be skipped on the second
call (i.e. when writing the shadow).
The patch fixes this by skipping the call to mysql_prepare_create_table
in mysql_write_frm in the MFRM_WRITE_SHADOW block when it has already
been called previously. To track whether or not it has been previously
called, we add a new flag for the mysql_write_frm function,
MFRM_ALTER_INFO_PREPARED, which is hard-coded into the function call on
the later invocation.
Test case based on work by Elena Stepanova <elenst@mariadb.com>
Reviewed By:
============
Sergei Golubchik <serg@mariadb.org>
Nikita Malyavin <nikita.malyavin@mariadb.com>
In locked_tables_mode when table is opened without
MYSQL_OPEN_GET_NEW_TABLE flag it is taken from pre-opened and locked
tables. In that case we upgrade its MDL ticket to MDL_EXCLUSIVE before
the operation and downgrade after operation.
Lots of different cases, SELECT, SELECT DEFAULT(),
UPDATE t SET x=DEFAULT, prepares statements,
opening of a table for the I_S, prelocking (so TL_WRITE),
insert with subquery (so SQLCOM_SELECT), etc.
Don't check NEXTVAL privileges in fix_fields() anymore, it cannot
possibly handle all the cases correctly. Make a special method
Item_func_nextval::check_access() for that and invoke it from
* fix_fields on explicit SELECT NEXTVAL()
(but not if NEXTVAL() is used in a DEFAULT clause)
* when DEFAULT bareword in used in, say, UPDATE t SET x=DEFAULT
(but not if DEFAULT() itself is used in a DEFAULT clause)
* in CREATE TABLE
* in ALTER TABLE ALGORITHM=INPLACE (that doesn't go CREATE TABLE path)
* on INSERT
helpers
* Virtual_column_info::check_access() to walk the item tree and invoke
Item::check_access()
* TABLE::check_sequence_privileges() to iterate default expressions
and invoke Virtual_column_info::check_access()
also, single-table UPDATE in prepared statements now associates
value items with fields just as multi-update already did, fixes the
case of PREPARE s "UPDATE t SET x=?"; EXECUTE s USING DEFAULT.
@@enforce_storage_engine is local setting and there is no
knowledge how other nodes are configured. Statement
CREATE TABLE xxx ENGINE=yyy is replicated as it is and
if required engine != enforced engine it could lead
inconsistent used storage engine in the cluster.
Fix is to return error and a warning if required engine is not
same as enforced engine.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>
Check stmt_da::is_error before calling stmt_da::sql_errno(which is what
the assertion ensures)
The bug did not have any negative effects in optimized builds.
Log tables cannot work with transactional InnoDB or Aria, that is
checked by ALTER TABLE for ER_UNSUPORTED_LOG_ENGINE. But it was
possible to circumvent this check with CREATE TABLE. The patch makes
the check of supported engine common for ALTER TABLE and CREATE TABLE.
