(opens in a new tab)

MySQL Error 1050: Table Already Exists

November 2, 2026 by Chat2DB Team

MySQL raises this error when a statement wants to create a table or view under a name that is already taken in the database:

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

The obvious case is running the same CREATE TABLE twice. The less obvious ones are a migration that failed halfway and is being re-run, a RENAME TABLE whose target name is taken, a view that shares the name, and a table you cannot see because only its data file is left on disk. This guide goes through each with output from MySQL 8.4.11, and explains why the popular IF NOT EXISTS fix can hide schema drift.

Reproducing the Error

CREATE TABLE orders (id INT PRIMARY KEY, total DECIMAL(10,2));
CREATE TABLE orders (id INT PRIMARY KEY, total DECIMAL(10,2));
-- ERROR 1050 (42S01): Table 'orders' already exists

The same error comes from every statement that introduces a table name:

CREATE TABLE orders_new LIKE orders;
 
RENAME TABLE orders_new TO orders;
-- ERROR 1050 (42S01): Table 'orders' already exists
 
ALTER TABLE orders_new RENAME TO orders;
-- ERROR 1050 (42S01): Table 'orders' already exists
 
CREATE VIEW orders AS SELECT 1;
-- ERROR 1050 (42S01): Table 'orders' already exists

Tables and views share one namespace in a database. You cannot create a table named like an existing view either: with a view v_orders, CREATE TABLE v_orders (id INT) fails with Table 'v_orders' already exists.

Step 1: Find Out What Owns the Name

Before changing the statement, look at the object that is already there. It might be the table you wanted, an older version of it, or a view:

SELECT table_name, table_type, create_time
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name IN ('orders', 'v_orders');

+------------+------------+---------------------+
| TABLE_NAME | TABLE_TYPE | CREATE_TIME         |
+------------+------------+---------------------+
| orders     | BASE TABLE | ...                 |
| v_orders   | VIEW       | ...                 |
+------------+------------+---------------------+

Then compare its structure with what your script expects:

SHOW CREATE TABLE orders;

Also check that you are in the database you think you are. SELECT DATABASE(); shows the current default schema, and a script that forgot its USE statement often runs against the wrong one.

Fix 1: CREATE TABLE IF NOT EXISTS, With Care

IF NOT EXISTS turns the error into a note and does nothing:

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT PRIMARY KEY, total DECIMAL(12,2), note TEXT);
-- Query OK, 0 rows affected, 1 warning
-- Note 1050: Table 'orders' already exists

That makes scripts safe to re-run, but notice what did not happen. The definition above differs from the existing table (BIGINT instead of INT, a wider DECIMAL, an extra note column), and MySQL silently kept the old table:

CREATE TABLE `orders` (
  `id` int NOT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

IF NOT EXISTS only checks the name, never the structure. If the existing table is an older version, the script now "succeeds" and the application fails later with Unknown column 'note'. Use it for bootstrap scripts where the table can only have one shape, and use explicit ALTER TABLE steps (or a migration tool) for changes.

The same applies to CREATE TABLE IF NOT EXISTS ... SELECT. When the table exists, MySQL 8.4 skips the whole statement, including the insert:

CREATE TABLE IF NOT EXISTS orders AS SELECT 1 AS id, 2.00 AS total;
-- Note 1050: Table 'orders' already exists
SELECT COUNT(*) FROM orders;   -- 0: no row was inserted

Fix 2: Drop and Re-create, Only for Disposable Tables

For staging tables, scratch tables and test fixtures that are rebuilt every time:

DROP TABLE IF EXISTS import_staging;
CREATE TABLE import_staging (id INT PRIMARY KEY, payload JSON);

Never use this pattern on a table that holds data you need. DROP TABLE commits immediately and cannot be rolled back. If you are unsure, rename the old table out of the way instead (next section) and drop it once you have checked the result.

Fix 3: Swap Tables Safely With RENAME TABLE

A common pattern builds a new table and swaps it in. Doing it in two separate renames leaves a moment without an orders table, and the second rename fails if a leftover from a previous run still exists:

CREATE TABLE orders_old LIKE orders;      -- leftover from an earlier attempt
 
RENAME TABLE orders TO orders_old, orders_new TO orders;
-- ERROR 1050 (42S01): Table 'orders_old' already exists

RENAME TABLE with several pairs is atomic. It either renames everything or nothing, so after this error orders and orders_new are unchanged. Clear the leftover name and run it again:

DROP TABLE orders_old;   -- after confirming it is really a leftover
RENAME TABLE orders TO orders_old, orders_new TO orders;
-- Query OK

Use a single multi-table RENAME TABLE for swaps, never two statements, and give the backup a dated name (orders_old_20261102) so leftovers never collide.

Fix 4: Re-running a Migration That Failed Halfway

This is the most common real-world source of the error. In MySQL, every CREATE TABLE commits immediately, so a migration file with several statements cannot be rolled back as a whole:

CREATE TABLE invoices (id INT PRIMARY KEY, order_id INT);
CREATE TABLE invoice_lines (
  id INT PRIMARY KEY, invoice_id INT,
  amount DECIMAL(10,2) NOT NUL);           -- typo
-- ERROR 1064 (42000): ... near 'NUL)'

The first table was created. After fixing the typo, re-running the file stops at the first statement:

CREATE TABLE invoices (id INT PRIMARY KEY, order_id INT);
-- ERROR 1050 (42S01): Table 'invoices' already exists

Migration tools such as Flyway and Liquibase record the failed migration, so the tool and the schema now disagree. To recover:

1. Check which statements of the migration actually ran, using SHOW TABLES and SHOW CREATE TABLE.
2. Either drop the objects that were created (if they hold no data) and re-run the migration, or finish the remaining statements by hand and mark the migration as applied.
3. Clear the failed entry in the tool's history table (Flyway has a repair command for this) before running it again.

To prevent it, keep one DDL statement per migration where possible, and test migrations against a copy of the production schema first.

Single statements, on the other hand, are atomic since MySQL 8.0. A CREATE TABLE ... SELECT that fails while copying rows does not leave a half-built table behind:

CREATE TABLE dup_ids (id INT PRIMARY KEY) SELECT 1 AS id UNION ALL SELECT 1;
-- ERROR 1062 (23000): Duplicate entry '1' for key 'dup_ids.PRIMARY'
SHOW TABLES LIKE 'dup_ids';   -- empty set

Views: CREATE OR REPLACE Only Replaces Views

CREATE OR REPLACE VIEW is the usual way to make view scripts re-runnable. It does not help when the name belongs to a table:

CREATE OR REPLACE VIEW orders AS SELECT 1;
-- ERROR 1347 (HY000): 'e1050.orders' is not VIEW

This happens when a table is being replaced by a view with the same name, for example during a refactoring. Rename the table first (RENAME TABLE orders TO orders_base), then create the view.

Temporary Tables Can Hide the Real One

A temporary table may have the same name as a permanent one. MySQL allows it, and the temporary table hides the permanent table for the rest of the session:

CREATE TEMPORARY TABLE orders (id INT, note VARCHAR(10));   -- OK
SELECT * FROM orders;           -- reads the empty temporary table
SHOW TABLES;                    -- lists only the permanent orders

Creating a second temporary table with the same name in the same session fails with 1050. Watch out for the reverse effect too: DROP TABLE orders drops the temporary table first. Use DROP TEMPORARY TABLE so that a mistake cannot remove the permanent table. Temporary tables are per connection, so a pooled connection that never closes can keep one alive and cause 1050 for the next request that reuses the connection.

Upper and Lower Case Names

Whether Orders and orders are the same name depends on lower_case_table_names:

SELECT @@lower_case_table_names;

On Linux the default is 0. There, CREATE TABLE Orders next to an existing orders succeeds and you end up with two tables that differ only in case, which is confusing and breaks when the schema is moved to Windows or macOS. With 1 (the Windows default), names are stored in lowercase, so the same statement reports that the table already exists. Pick one naming convention, ideally all lowercase, and stick to it.

Related: ERROR 1813, Tablespace Exists

Sometimes CREATE TABLE fails although SHOW TABLES does not list the table:

CREATE TABLE payments (id INT PRIMARY KEY, amount DECIMAL(10,2));
-- ERROR 1813 (HY000): Tablespace '`e1050`.`payments`' exists.
 
SHOW TABLES LIKE 'payments';      -- empty set
DROP TABLE IF EXISTS payments;    -- Note 1051: Unknown table 'e1050.payments'

The data directory contains a payments.ibd file that does not belong to any table in the data dictionary, typically left by a manual file copy, an interrupted restore or a crash during an ALTER TABLE. DROP TABLE cannot help because, as far as MySQL is concerned, there is no table. With the server's file system in front of you, move the stray file out of the database directory (keep it until you know it is not needed), and the CREATE TABLE succeeds. On a managed service without file access, contact the provider's support.

Inspecting Before You Run DDL

Most 1050 errors are cheap to avoid by checking the schema before running a script, especially on a shared or production database. In Chat2DB (opens in a new tab) the schema tree shows tables and views side by side, and opening a table shows its current DDL, so you can compare it with the script before you decide between IF NOT EXISTS, an ALTER TABLE or a rename. If you work with PostgreSQL as well, its version of the error is covered in Postgres: relation already exists.

Summary

• ERROR 1050 means the name is already used by a table or view in the current database. Check information_schema.tables and SHOW CREATE TABLE first.
• CREATE TABLE IF NOT EXISTS only checks the name. It silently keeps an older structure and, with ... SELECT, inserts nothing.
• Use DROP TABLE IF EXISTS only for disposable tables, and one atomic multi-table RENAME TABLE for swaps.
• Migrations with several DDL statements can fail halfway because each CREATE TABLE commits. Inspect, clean up, then repair the tool's history.
• CREATE OR REPLACE VIEW cannot replace a table (ERROR 1347), temporary tables can shadow permanent ones, and an orphaned .ibd file causes ERROR 1813.