MySQL says the table already exists when you create it
ERROR 1050 (42S01): Table 'users' already existsShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
SHOW 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
SHOW 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
CREATE 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
- 1
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
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
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 52Fix
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 again3Verify
The CREATE statement runs without error.
SHOW TABLES LIKE 't1';Confirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
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 fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix