Showing content from https://mariadb.com/docs/server/reference/sql-statements/data-definition/atomic-ddl below:
Atomic DDL | MariaDB Documentation
Atomic DDL | MariaDB Documentation
- MariaDB Server Documentation
- Quickstart Guides
- Server Usage
- Server Management
- Security
- Architecture
- Clients & Utilities
- HA & Performance
- Reference
- SQL Structure
- SQL Statements
- SQL Functions
- Data Types
- Plugins
- System Tables
- Error Codes
- MariaDB Error Code Reference
- Operating System Error Codes
- MariaDB Error Codes 1000 to 1099
- MariaDB Error Codes 1100 to 1199
- MariaDB Error Codes 1200 to 1299
- MariaDB Error Codes 1300 to 1399
- MariaDB Error Codes 1400 to 1499
- Error 1400: XAER_OUTSIDE: Some work is done outside global transaction
- Error 1401: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consist
- Error 1402: XA_RBROLLBACK: Transaction branch was rolled back
- Error 1403: There is no such grant defined for user on host on routine
- Error 1404: Failed to grant EXECUTE and ALTER ROUTINE privileges
- Error 1405: Failed to revoke all privileges to dropped routine
- Error 1406: Data too long for column at row
- Error 1407: Bad SQLSTATE
- Error 1408: ready for connections. Version: socket: port:
- Error 1409: Can't load value from file with fixed size rows to variable
- Error 1410: You are not allowed to create a user with GRANT
- Error 1411: Incorrect value for function
- Error 1412: Table definition has changed, please retry transaction
- Error 1413: Duplicate handler declared in the same block
- Error 1414: OUT or INOUT argument for routine is not a variable or NEW pseudo-variable in BEFORE tri
- Error 1415: Not allowed to return a result set from
- Error 1416: Cannot get geometry object from data you send to the GEOMETRY field
- Error 1417: A routine failed and has neither NO SQL nor READS SQL DATA in its declaration
- Error 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration an
- Error 1419: You do not have the SUPER privilege and binary logging is enabled
- Error 1420: You can't execute a prepared statement which has an open cursor associated with it
- Error 1421: The statement has no open cursor
- Error 1422: Explicit or implicit commit is not allowed in stored function or trigger
- Error 1423: Field of view underlying table doesn't have a default value
- Error 1424: Recursive stored functions and triggers are not allowed.
- Error 1425: Too big scale specified for column
- Error 1426: Too big precision specified for column
- Error 1427: For float(M,D, double(M,D or decimal(M,D, M must be >= D
- Error 1428: You can't combine write-locking of system tables with other tables or lock types
- Error 1429: Unable to connect to foreign data source
- Error 1430: There was a problem processing the query on the foreign data source
- Error 1431: The foreign data source you are trying to reference does not exist
- Error 1432: Can't create federated table. The data source connection string is not in the correct fo
- Error 1433: The data source connection string is not in the correct format
- Error 1434: Can't create federated table. Foreign data src error
- Error 1435: Trigger in wrong schema
- Error 1436: Thread stack overrun
- Error 1437: Routine body is too long
- Error 1438: Cannot drop default keycache
- Error 1439: Display width out of range for column
- Error 1440: XAER_DUPID: The XID already exists
- Error 1441: Datetime function: field overflow
- Error 1442: Can't update table in stored function/trigger because it is already used by statement wh
- Error 1443: The definition of table prevents operation on table
- Error 1444: The prepared statement contains a stored routine call that refers to that same statement
- Error 1445: Not allowed to set autocommit from a stored function or trigger
- Error 1446: Definer is not fully qualified
- Error 1447: View has no definer information (old table format)
- Error 1448: You need the SUPER privilege for creation view with definer
- Error 1449: The user specified as a definer does not exist
- Error 1450: Changing schema from is not allowed.
- Error 1451: Cannot delete or update a parent row: a foreign key constraint fails
- Error 1452: Cannot add or update a child row: a foreign key constraint fails
- Error 1453: Variable must be quoted with ..., or renamed
- Error 1454: No definer attribute for trigger
- Error 1455: has an old format, you should re-create the object(s)
- Error 1456: Recursive limit (as set by the max_sp_recursion_depth variable) was exceeded for routine
- Error 1457: Failed to load routine
- Error 1458: Incorrect routine name
- Error 1459: Table upgrade required. Please do "REPAIR TABLE" or dump/reload to fix it!
- Error 1460: AGGREGATE is not supported for stored functions
- Error 1461: Can't create more than max_prepared_stmt_count statements
- Error 1462: contains view recursion
- Error 1463: Non-grouping field is used in clause
- Error 1464: The used table type doesn't support SPATIAL indexes
- Error 1465: Triggers can not be created on system tables
- Error 1466: Leading spaces are removed from name
- Error 1467: Failed to read auto-increment value from storage engine
- Error 1468: user name
- Error 1469: host name
- Error 1470: String is too long for (should be no longer than )
- Error 1471: The target table of the is not insertable-into
- Error 1472: Table is differently defined or of non-MyISAM type or doesn't exist
- Error 1473: Too high level of nesting for select
- Error 1474: Name has become ''
- Error 1475: First character of the FIELDS TERMINATED string is ambiguous
- Error 1476: The foreign server you are trying to create already exists
- Error 1477: The foreign server name you are trying to reference does not exist. Data source error
- Error 1478: Table storage engine does not support the create option
- Error 1479: Syntax error: PARTITIONING requires definition of VALUES for each partition
- Error 1480: Only PARTITIONING can use VALUES in partition definition
- Error 1481: MAXVALUE can only be used in last partition definition
- Error 1482: Subpartitions can only be hash partitions and by key
- Error 1483: Must define subpartitions on all partitions if on one partition
- Error 1484: Wrong number of partitions defined, mismatch with previous setting
- Error 1485: Wrong number of subpartitions defined, mismatch with previous setting
- Error 1486: Constant/Random expression in (sub)partitioning function is not allowed
- Error 1487: Expression in RANGE/LIST VALUES must be constant
- Error 1488: Field in list of fields for partition function not found in table
- Error 1489: List of fields is only allowed in KEY partitions
- Error 1490: The partition info in the frm file is not consistent with what can be written into the f
- Error 1491: The function returns the wrong type
- Error 1492: For partitions each partition must be defined
- Error 1493: VALUES LESS THAN value must be strictly increasing for each partition
- Error 1494: VALUES value must be of same type as partition function
- Error 1495: Multiple definition of same constant in list partitioning
- Error 1496: Partitioning can not be used stand-alone in query
- Error 1497: The mix of handlers in the partitions is not allowed in this version of MariaDB
- Error 1498: For the partitioned engine it is necessary to define all
- Error 1499: Too many partitions (including subpartitions) were defined
- MariaDB Error Codes 1500 to 1599
- MariaDB Error Codes 1600 to 1699
- MariaDB Error Codes 1700 to 1799
- MariaDB Error Codes 1800 to 1899
- MariaDB Error Codes 1900 to 1999
- MariaDB Error Codes 3000 to 3099
- MariaDB Error Codes 4000 to 4099
- MariaDB Error Codes 4100 to 4199
- Error 4100: Field reference can't be used in table value constructor
- Error 4101: Numeric datatype is required for function
- Error 4102: Argument to the function is not a constant for a partition
- Error 4103: Argument to the function does not belong to the range [0,1]
- Error 4104: function only accepts arguments that can be converted to numerical types
- Error 4105: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
- Error 4106: Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function
- Error 4107: Limit only accepts integer values
- Error 4108: Invisible column must have a default value
- Error 4109: Rows matched: Changed: Inserted: Warnings:
- Error 4110: must be of type for system-versioned table
- Error 4111: Transaction-precise system versioning for is not supported
- Error 4112: You should never see it
- Error 4113: Wrong partitioning type, expected type
- Error 4114: Versioned table last HISTORY partition is out of, need more HISTORY partitions
- Error 4115: Maybe missing parameters
- Error 4116: Can only drop oldest partitions when rotating by INTERVAL
- Error 4117: You should never see it
- Error 4118: Partition contains non-historical data
- Error 4119: Not allowed for system-versioned. Change @@system_versioning_alter_history to proceed wi
- Error 4120: Not allowed for system-versioned. Change to/from native system versioning engine is not
- Error 4121: SYSTEM_TIME range selector is not allowed
- Error 4122: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
- Error 4123: Table must have at least one versioned column
- Error 4124: Table is not system-versioned
- Error 4125: Wrong parameters for: missing
- Error 4126: PERIOD FOR SYSTEM_TIME must use columns
- Error 4127: Wrong parameters for partitioned: wrong value for
- Error 4128: Wrong partitions for: must have at least one HISTORY and exactly one last CURRENT
- Error 4129: TRX_ID not found in mysql.transaction_registry
- Error 4130: Can not change system versioning field
- Error 4131: Can not DROP SYSTEM VERSIONING for table partitioned BY SYSTEM_TIME
- Error 4132: System-versioned tables in the database are not supported
- Error 4133: Transaction registry is disabled
- Error 4134: Duplicate ROW column
- Error 4135: Table is already system-versioned
- Error 4136: You should never see it
- Error 4137: System-versioned tables do not support
- Error 4138: Transaction-precise system-versioned tables do not support partitioning by ROW START or
- Error 4139: The index file for table is full
- Error 4140: The column cannot be changed more than once in a single UPDATE statement
- Error 4141: Row with no elements is not allowed in table value constructor in this context
- Error 4142: SYSTEM_TIME partitions in table does not support historical query
- Error 4143: index does not support this operation
- Error 4144: Changing table options requires the table to be rebuilt
- Error 4145: Can't execute the command as you have a BACKUP STAGE active
- Error 4146: You must start backup with "BACKUP STAGE START"
- Error 4147: Backup stage is same or before current backup stage
- Error 4148: Backup stage failed
- Error 4149: Unknown backup stage: Stage should be one of START, FLUSH, BLOCK_DDL, BLOCK_COMMIT or EN
- Error 4150: User is blocked because of too many credential errors; unblock with 'FLUSH PRIVILEGES'
- Error 4151: Access denied, this account is locked
- Error 4152: Application-time period table cannot be temporary
- Error 4153: Fields of PERIOD FOR have different types
- Error 4154: Cannot specify more than one application-time period
- Error 4155: Period field cannot be
- Error 4156: Period is not found in table
- Error 4157: Column used in period specified in update SET list
- Error 4158: Can't DROP CONSTRAINT. Use DROP PERIOD for this
- Error 4159: Specified key part was too long; max key part length is
- Error 4160: Comment for database is too long
- Error 4161: Unknown data type
- Error 4162: Operator does not exists
- Error 4163: Table history row start is later than row end
- Error 4164: STARTS is later than query time, first history partition may exceed INTERVAL value
- Error 4165: DDL-statement is forbidden as table storage engine does not support Galera replication
- Error 4166: The used command is not allowed because the MariaDB server or client has disabled the lo
- Error 4167: No secure transports are configured, unable to set --require_secure_transport=ON
- Error 4168: Slave SQL thread ignored the '%s' because table is shared
- Error 4169: AUTO_INCREMENT column cannot be used in the UNIQUE index
- Error 4170: Key cannot explicitly include column
- Error 4171: Key cannot have WITHOUT OVERLAPS
- Error 4172: is not allowed in this context
- Error 4173: Engine does not support rollback. Changes where commited during rollback call
- Error 4174: A primary key cannot be marked as IGNORE
- Error 4175: SKIP LOCKED makes this statement unsafe
- Error 4176: Field can't be set for JSON_TABLE
- Error 4177: Every table function must have an alias
- Error 4178: Can't store an array or an object in the scalar column of JSON_TABLE
- Error 4179: Can't store multiple matches of the path in the column of JSON_TABLE
- Error 4180: FETCH ... WITH TIES requires ORDER BY clause to be present
- Error 4181: Dropped orphan trigger, originally created for table
- Error 4182: Storage engine is disabled
- Error 4183: SFORMAT error
- Error 4184: Convert partition is not supported for subpartitioned table
- Error 4185: MariaDB tried to use the %s, but its provider plugin is not loaded
- Error 4186: Failed to parse histogram for table at offset
- Error 4187: OUT or INOUT argument for function is not allowed here
- Error 4188: Replicated query table can not be temporary
- Error 4189: Versioned table: adding HISTORY partition(s) failed
- Error 4190: is implicitly changing the value of from to
- Error 4191: CHANGE MASTER TO option is missing requirement
- Error 4192: Slave log event execution was interrupted (slave_max_statement_time exceeded)
- Error 4193: Invalid value for keyword
- Error 4194: keyword is not supported
- Error 4195: Variable schema is not supported
- Error 4196: Pseudo thread id should not be modified by the client as it will be overwritten
- Error 4197: Wrong number of columns
- Error 4198: Sequence tables cannot have any keys
- Error 4199: Sequence tables cannot have any constraints
- MariaDB Error Codes 4200 to 4299
- Client/Server Protocol
- Reference
- SQL Statements
- Data Definition (DDL)
Atomic DDL
We improved readability for DDL (Data Definition Language) operations to make most of them atomic, and the rest crash-safe, even if the server crashes in the middle of an operation.
The design of Atomic/Crash-safe DDL (MDEV-17567 ) allows it to work with all storage engines.
-
Atomic means that either the operation succeeds (and is logged to the binary log or is completely reversed.
-
Crash-safe means that in case of a crash, after the server has restarted, all tables are consistent, there are no temporary files or tables on disk and the binary log matches the status of the server.
-
DDL Data definition language.
-
DML Data manipulation language.
-
'DDL recovery log' or 'DDL log' for short, is the new log file, ddl_recovery.log by default, that stores all DDL operations in progress. This is used to recover the state of the server in case of sudden crash.
Before 10.6, in case of a crash, there was a small possibility that one of the following things could happen:
-
There could be temporary tables starting with #sql-alter or #sql-shadow or temporary files ending with '' left.
-
The table in the storage engine and the table's .frm file could be out of sync.
-
During a multi-table rename, only some of the tables were renamed.
Which DDL Operations are Now Atomic
Which DDL Operations are Now Crash Safe DROP TABLE of Multiple Tables.
DROP TABLE over multiple tables is treated as if every DROP is a separate, atomic operation. This means that after a crash, all fully, or partly, dropped tables will be dropped and logged to the binary log. The undropped tables will be left untouched.
CREATE OR REPLACE TABLE foo is implemented as:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo ...
This means that if there is a crash during CREATE TABLE then the original table 'foo' will be dropped even if the new table was not created. If the table was not re-created, the binary log will contain theDROP TABLE.
DROP DATABASE is implemented as:
DROP TABLE table
LOOP OVER ALL tables
Each DROP TABLE is atomic, but in case of a crash, things will work the same way as DROP TABLE with multiple tables.
Atomic with Different Storage Engines
Atomic/Crash-safe DDL works with all storage engines that either have atomic DDLs internally or are able to re-execute DROP or RENAME in case of failure.
This should be true for most storage engines. The ones that still need some work are:
The DDL Log Recovery File
The new startup option --log-ddl-recovery=path (ddl_recovery.log by default) can be used to specify the place for the DDL log file. This is mainly useful in the case when one has a filesystem on persistent memory, as there is a lot of sync on this file during DDL operations.
This file contains all DDL operations that are in progress.
At MariaDB server startup, the DDL log file is copied to a file with the same base name but with a backup.log suffix. This is mainly done to be able to find out what went wrong if recovery fails.
If the server crashes during recovery (unlikely but possible), the recovery will continue where it was before. The recovery will retry each entry up to 3 times before giving up and proceeding with the next entry.
-
We believe that a clean separation of layers leads to an easier-to-maintain solution. The Atomic DDL implementation in MariaDB 10.6 introduced minimal changes to the storage engine API, mainly for native ALTER TABLE.
-
In our InnoDB implementation, no file format changes were needed on top of the RENAME undo log that was introduced in MariaDB 10.2.19 for a backup-safe TRUNCATE re-implementation. Correct use of sound design principles (write-ahead logging and transactions; also file creation now follows the ARIES protocol) is sufficient. We removed the hacks (at most one CREATE or DROP per transaction) and correctly implemented rollback and purge triggers for the InnoDB SYS_INDEXES table.
-
Numerous DDL recovery bugs in InnoDB were found and fixed quickly thanks to rr-project.org . We are still working on one: data files must not be deleted before the DDL transaction is committed.
Thanks to Atomic/Crash-safe DDL, the MariaDB server is now much more stable and reliable in unstable environments. There is still ongoing work to fix the few remaining issues mentioned above to make all DDL operations Atomic.
This page is licensed: CC BY-SA / Gnu FDL
RetroSearch is an open source project built by @garambo
| Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4