EXCHANGE PARTITION fails with ERROR 1736 (Tables have different definitions)
when tables contain generated columns with AND/OR conditions, even when the
expressions are logically equivalent. This occurs because when expressions are
re-parsed (e.g., via CREATE TABLE ... LIKE), the order of arguments in AND/OR
conditions may change, but the comparison was order-sensitive.
The Item_cond::eq() method was implemented to perform set-based comparison
for commutative AND/OR operations. The set-based comparison algorithm ensures
that two Item_cond expressions are considered equal if they contain the same set
of equivalent arguments, regardless of order.
Added comprehensive test case covering:
- Generated columns with OR conditions
- Generated columns with AND conditions
- Multiple generated columns with different AND/OR combinations
- Nested AND/OR conditions
The fix allows EXCHANGE PARTITION to succeed when expressions are logically
equivalent but have different argument ordering, which is correct behavior
since AND/OR operations are commutative.
MDEV-38757 Limit unordered vcol condition comparison to EXCHANGE PARTITION
Review follow-up to the previous commit, which made Item_cond::eq()
compare AND/OR argument lists as sets for every caller. That changed
equality semantics globally and broke main.derived_cond_pushdown, where
conditions that eq() started reporting as equal were dropped from
attached_condition. Reordering AND/OR operands also changes evaluation
order, which is observable when operands are functions, so the relaxed
comparison must not be the default.
Item::Eq_config gains an unordered_conditions flag, defaulting to false,
next to the existing binary_cmp and omit_table_names flags.
Item_cond::eq() compares its argument lists as sets only when that flag
is set, and otherwise reports two distinct Item_cond objects as unequal,
as it did before this patch series. The flag is threaded through
Virtual_column_info::is_equal() and a new mysql_compare_tables()
parameter, and only Sql_cmd_alter_table_exchange_partition passes it as
true, so the relaxed comparison stays confined to the EXCHANGE PARTITION
metadata check.
The set comparison tests containment in both directions rather than
comparing element counts, so an expression also matches a form that
repeats one of its terms, for example
col1 > 10 and col2 < 100 or col3 > 50
col3 > 50 or col1 > 10 and col2 < 100 or col3 > 50
Test 5 of parts.partition_exchange_generated_columns covers that case.
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.
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>
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.
Two new error codes ER_SEQUENCE_TABLE_HAS_TOO_FEW_ROWS and
ER_SEQUENCE_TABLE_HAS_TOO_MANY_ROWS were introduced in MDEV-36032 in
both 10.11 and, as part of MDEV-22491, 12.0. Here we remove them from
10.11, but they should remain in 12.0.
Ensure that backup_reset_alter_copy_lock() is called in case of rollback
or error in mysql_inplace_alter_table() or copy_data_between_tables().
Other things:
- Improved error from mariabackup when unexpected DDL operation is
encountered.
- Added assert if backup_ddl_log() is called in the wrong context.
To check the rows, the table needs to be opened. To that end, and like
MDEV-36038, we force COPY algorithm on ALTER TABLE ... SEQUENCE=1.
This also results in checking the sequence state / metadata.
The table structure was already validated before this patch.
Problem:
========
- After commit cc8eefb0dc (MDEV-33087),
InnoDB does use bulk insert operation for ALTER TABLE.. ALGORITHM=COPY
and CREATE TABLE..SELECT as well. InnoDB fails to clear the bulk
buffer when it encounters error during CREATE..SELECT. Problem
is that while transaction cleanup, InnoDB fails to identify
the bulk insert for DDL operation.
Fix:
====
- Represent bulk_insert in trx by 2 bits. By doing that, InnoDB
can distinguish between TRX_DML_BULK, TRX_DDL_BULK. During DDL,
set bulk insert value for transaction to TRX_DDL_BULK.
- Introduce a parameter HA_EXTRA_ABORT_ALTER_COPY which rollbacks
only TRX_DDL_BULK transaction.
- bulk_insert_apply() happens for TRX_DDL_BULK transaction happens
only during HA_EXTRA_END_ALTER_COPY extra() call.
ha_innobase::extra(): Conditionally avoid a log write that had been
added in commit e5b9dc1536 (MDEV-25910)
because it may be invoked as part of select_insert::prepare_eof()
and not only during DDL operations.
Reviewed by: Sergei Golubchik
mysql_alter_table(): Consider ha_sequence::storage_ht() when determining
if the storage engine changed.
ha_sequence::check_if_supported_inplace_alter(): A new function, to
ensure that ha_innobase::check_if_supported_inplace_alter() will be
called on ALTER TABLE name_of_sequence SEQUENCE=0.
ha_innobase::check_if_supported_inplace_alter(): For any change of
the SEQUENCE attribute, always return HA_ALTER_INPLACE_NOT_SUPPORTED,
forcing ALGORITHM=COPY.
