skills.rest
mariadb-corporation
mariadb-corporationOFFICIAL
Explore all skills by this author →
WebsiteGitHub

mariadb-alter-table

Generates MariaDB-specific ALTER TABLE statements with online DDL, lock, and versioning clauses.

28|115|Updated Jan 28, 2025
Search Tags:#SQL#database#schema migration#mariadb#innodb#online ddl#alter table
One-click install
npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-alter-table-mariadb-corporation
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mariadb-alter-table
Source: https://github.com/mariadb-corporation/mariadb-docs/tree/main/agent-skills/granular/statements/mariadb-alter-table
Command: npx skills add https://github.com/mariadb-corporation/mariadb-docs --skill mariadb-alter-table-mariadb-corporation

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Standard SQL ALTER TABLE knowledge misses MariaDB-specific behavior, leading to blocking schema migrations, failed idempotent scripts, and replication lag. This Skill supplies the exact MariaDB syntax and semantics for safe, online schema changes. ## Core Features & Use Cases - Online DDL guidance: Covers ALGORITHM (DEFAULT/COPY/INPLACE/NOCOPY/INSTANT) and LOCK (NONE/SHARED/EXCLUSIVE) clauses, plus the ALTER ONLINE TABLE shorthand, so migrations avoid blocking concurrent reads and writes. - Idempotent migrations: Documents subclause-level IF EXISTS / IF NOT EXISTS and WAIT/NOWAIT lock timeouts for migration scripts that tolerate existing or missing columns and bounded metadata-lock waits. - Advanced operations: Details ADD/DROP SYSTEM VERSIONING, ADD PERIOD FOR, DROP CONSTRAINT, FORCE rebuilds, partition CONVERT/EXCHANGE, DISCARD/IMPORT TABLESPACE, atomic ALTER, and two-phase replication via binlog_alter_two_phase. - Use Case: When planning a schema migration on a MariaDB 11.8 production primary, use this Skill to combine multiple changes into one ALTER TABLE with LOCK=NONE and NOWAIT, avoiding table rebuilds and replica lag. ## Quick Start Ask the AI to write a MariaDB ALTER TABLE statement that adds a column and an index to an existing table online without blocking writes.

Frequently Asked Questions about mariadb-alter-table

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I run ALTER TABLE online in MariaDB without locking?▼

Use ALTER TABLE with LOCK=NONE, or the shorthand ALTER ONLINE TABLE, which errors if the operation cannot run lock-free. InnoDB defaults to online DDL where supported, and since 11.2 LOCK=NONE works for most COPY-algorithm operations too.

How to write idempotent migrations in MariaDB?▼

MariaDB supports IF EXISTS and IF NOT EXISTS on individual ALTER TABLE subclauses like ADD COLUMN, DROP COLUMN, and ADD INDEX. Unmet conditions emit a warning and skip that subclause instead of failing the whole statement.

Does MariaDB ALTER TABLE cause replication lag?▼

Long ALTER statements replicate only after finishing on the primary by default. Setting binlog_alter_two_phase=ON (since 10.8) lets replicas begin applying when the ALTER starts, eliminating most schema-change replication lag.

Why does ALTER TABLE fail on a system-versioned table in MariaDB?▼

Altering an already system-versioned table errors unless the session sets system_versioning_alter_history = KEEP. This preserves existing history rows under the new schema, though historical rows may not match new column definitions.

What is the difference between ALGORITHM=INPLACE and NOCOPY in MariaDB?▼

INPLACE performs an engine-specific in-place operation but may still rebuild the clustered index. NOCOPY is MariaDB-specific and errors out if the operation would require a clustered-index rebuild, guaranteeing a fast change or a fast failure.

Do I need gh-ost or pt-online-schema-change with MariaDB?▼

No. MariaDB's InnoDB ALTER TABLE defaults to ALGORITHM=DEFAULT, LOCK=DEFAULT, which performs online DDL where the operation supports it. External schema-change tools are generally unnecessary for InnoDB tables on MariaDB.

Related Skills

Explore More Software Engineering Skills→
HarriesHarries
1

dev-ddl

Generate SQL DDL statements and schema alterations from design documents.

Community
Advanced
rawhit-rrawhit-r

db-migrate

Generate and validate database migration SQL against production schemas.

Community
Advanced
Impertio-StudioImpertio-Studio
2

mariadb-syntax-system-versioned-tables

Create MariaDB system-versioned tables for row-level history tracking.

Official
Advanced
iceflowericeflower

data-migration

Plan zero-downtime MySQL and PostgreSQL migrations using Expand-and-Contract, Dual-Write, and Shadow Table patterns.

Community
Advanced