🚨 MySQL Error Guide: 'Table already exists' — Fix ERROR 1050

Overview

ERROR 1050 is raised when CREATE TABLE targets a name that already exists in the schema:

ERROR 1050 (42S01): Table 'orders' already exists

A subtler variant appears when the table is not visible in SHOW TABLES but an orphaned tablespace or data-dictionary entry blocks recreation:

ERROR 1050 (42S01): Table 'orders' already exists
-- yet SELECT from it fails and it is absent from SHOW TABLES

Most 1050s are a migration replayed or a name collision; the orphaned-file variant is rarer but more confusing.

Symptoms

• A migration or bootstrap script fails on CREATE TABLE for a table that is already present.
• CREATE TABLE fails but the table is missing from SHOW TABLES (orphaned tablespace/dictionary mismatch).
• The error appears on a replica when a statement-based CREATE conflicts with an existing table.
• A rename or restore leaves a stale .ibd/.frm that blocks recreating the name.
• Case-sensitivity confusion between Orders and orders on a case-insensitive vs case-sensitive filesystem.

Common Root Causes

• Migration rerun — the same CREATE TABLE executed twice because migration bookkeeping was lost, reset, or run out of order.
• Non-idempotent DDL — CREATE TABLE without IF NOT EXISTS in a script expected to be re-runnable.
• Orphaned tablespace files — a leftover .ibd or a data-dictionary entry from a crash, botched restore, or manual file operation.
• Replication conflict — a CREATE replicated to a replica that already has the table from a prior manual change.
• Case-sensitivity / lower_case_table_names mismatch — the “same” name differs by case across environments.
• Two concurrent creators — parallel deploys racing to create the same table.

Diagnostic Workflow

First confirm whether the table truly exists and inspect its current definition:

SHOW TABLES LIKE 'orders';
SHOW CREATE TABLE orders\G
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_name = 'orders';

If it exists and matches what you intended, the fix may simply be to skip the create (the migration already ran). Check migration bookkeeping:

SELECT * FROM schema_migrations ORDER BY version DESC LIMIT 5;   -- tool-specific

For the orphaned case — CREATE fails but SHOW TABLES does not list it — look for a stale tablespace and mismatched data-dictionary state:

SELECT name, space FROM information_schema.innodb_tables WHERE name LIKE '%orders%';

ls -l /var/lib/mysql/<dbname>/ | grep -i orders   # stale .ibd / .frm on disk

Check case-sensitivity handling, which differs by platform:

SELECT @@lower_case_table_names;   -- 0 case-sensitive, 1 lowercased, 2 stored-as-given

Example Root Cause Analysis

A blue/green deploy failed applying a migration:

ERROR 1050 (42S01): Table 'audit_log' already exists

SHOW TABLES LIKE 'audit_log' confirmed the table was present, but schema_migrations did not list the migration version. The team had restored the database from a backup that included audit_log but restored the app before the schema_migrations rows were seeded, so the migration tool believed the change was still pending and tried to re-create it. The correct fix was to reconcile the migration ledger — mark that version as applied — rather than dropping the populated table. They then made the migration idempotent with CREATE TABLE IF NOT EXISTS and ensured restores always include the migration bookkeeping table.

Prevention Best Practices

• Make bootstrap DDL idempotent with CREATE TABLE IF NOT EXISTS, reserving bare CREATE for migrations that must run exactly once and are tracked.
• Rely on a migration tool with a durable ledger, and always restore that ledger together with the schema.
• Standardize lower_case_table_names across all environments to avoid case collisions between platforms.
• Serialize deploys so two runners cannot race to create the same table.
• On replicas, avoid manual schema changes that diverge from what replication will apply.
• Never delete .ibd/.frm files by hand; use DROP TABLE so the data dictionary and tablespace stay consistent.

Quick Command Reference

SHOW TABLES LIKE 'orders';                           -- does it really exist?
SHOW CREATE TABLE orders\G                            -- current definition
SELECT @@lower_case_table_names;                     -- case-sensitivity mode
CREATE TABLE IF NOT EXISTS orders (...);             -- idempotent create

Conclusion

ERROR 1050 almost always means a CREATE TABLE ran when the table already existed — usually a replayed or out-of-order migration. Confirm the table’s real state with SHOW TABLES and SHOW CREATE TABLE, reconcile the migration ledger rather than dropping populated tables, and make bootstrap DDL idempotent with IF NOT EXISTS. For the rare orphaned-tablespace variant, resolve the data-dictionary/file mismatch with proper DROP semantics instead of deleting files by hand.