Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL says the table already exists when you create it

ErrorERROR 1050 (42S01): Table 'users' already exists

Short answer

Error 1050 means a table with that name is already in the schema. Check with SHOW TABLES first, mind letter case on Linux, and remember that IF NOT EXISTS only skips the error, it never compares structure. If the table is not usable yet the name is blocked, drop the stale entry.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    List tables with a name pattern to see exactly what exists, with the exact letter case.

    SHOW TABLES LIKE 'users';
    SELECT @@lower_case_table_names;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW TABLES returns the name. A script that used CREATE TABLE IF NOT EXISTS did not fail, but queries then say a column is unknown.The table is really there, maybe with different columnsFix
    2. 2SHOW TABLES shows Users while you create users, or the same script works on one machine and fails on another.The same name exists in another letter caseFix
    3. 3CREATE says the table exists while the table cannot be used, and the error log shows a message that an .ibd file could not be opened.A half-dropped InnoDB table leaves a stale entryFix

What does the error look like?

  • ERROR 1050 (42S01): Table 'users' already exists

Cause 1 of 3

The table is really there, maybe with different columns

1Check

IF NOT EXISTS prevents the error, but the manual says nothing verifies that the existing table has the structure in your statement. Compare the real definition.

SHOW CREATE TABLE users;

2Fix

If the old table is disposable, drop it and create it again. If it holds data, change it with ALTER TABLE to match the new definition.

-- disposable table only
DROP TABLE IF EXISTS users;
CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) NOT NULL);

-- table with data
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT '';

3Verify

The definition now matches the script.

SHOW CREATE TABLE users;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The same name exists in another letter case

1Check

On Linux table names are case-sensitive by default (lower_case_table_names is 0); on Windows the default is 1 and on macOS it is 2. A script moved between systems meets a different rule.

SELECT @@lower_case_table_names;

2Fix

Use one lower-case name everywhere. The setting cannot be changed after the server has been initialised in MySQL 8.4, so fix the names in your scripts, not the setting.

RENAME TABLE Users TO users;

3Verify

Only the lower-case name is listed.

SHOW TABLES LIKE 'users';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A half-dropped InnoDB table leaves a stale entry

1Check

InnoDB logs 'Cannot open datafile' for a file-per-table tablespace whose .ibd file is gone, for example after a manual file delete or an interrupted restore.

grep -i "ibd" /var/lib/mysql/*.err | tail -n 5

2Fix

The manual says to remove the dictionary entry with DROP TABLE, then create the table again. If the server will not start because of the damage, use the forced recovery steps in the manual.

DROP TABLE test.t1;
-- then run your CREATE TABLE again

3Verify

The CREATE statement runs without error.

SHOW TABLES LIKE 't1';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use plain CREATE TABLE in versioned migrations so a mismatch fails loudly instead of being skipped.
  • Write every table and schema name in lower case.
  • Never delete .ibd files by hand; use DROP TABLE.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

A query fails with No database selected

ERROR 1046 (3D000): No database selected

Error 1046 means the connection has no default database and the statement used a bare table name.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.