TRUNCATE TABLE only when the table exists: there is no IF EXISTS
ERROR 1146 (42S02): Table 'mydb.staging_events' doesn't existShort answer
MySQL has no TRUNCATE TABLE IF EXISTS. The statement takes only a table name, so a table that is not there raises error 1146, and writing IF EXISTS is a syntax error. Create the table first with CREATE TABLE IF NOT EXISTS, or wrap TRUNCATE in a stored procedure with a handler for SQLSTATE 42S02.
Fix it fast
1Find out which cause you have
Read the error number. 1064 near IF EXISTS means the statement was written with a clause that does not exist. 1146 means the syntax is fine and the table is absent from that database.
SELECT DATABASE(); SHOW TABLES LIKE 'staging_events';2Match what you see, then jump to its fix
- 1
ERROR 1064 near 'IF EXISTS'The statement was written as TRUNCATE TABLE IF EXISTSFix - 2
ERROR 1146 (42S02), and SHOW TABLES does not list the tableThe table was never created on this server or in this databaseFix - 3
The same script works on one server and stops with error 1146 on another, and you do not want to create the tableThe script has to run where the table may or may not existFix - 4
Statements after the failing TRUNCATE never run when you load the fileOne failed TRUNCATE stops the whole script run by the mysql clientFix
- 1
What does the error look like?
ERROR 1146 (42S02): Table 'mydb.staging_events' doesn't existTable '%s.%s' doesn't existERROR 1064 (42000): ... near 'IF EXISTS'
Cause 1 of 4
The statement was written as TRUNCATE TABLE IF EXISTS
1Check
The manual gives one form only, TRUNCATE [TABLE] tbl_name. Unlike DROP TABLE, it has no IF EXISTS clause, so the parser stops at those two words.
-- fails with a syntax error
TRUNCATE TABLE IF EXISTS staging_events;2Fix
Remove IF EXISTS. If the table might be missing, pick one of the patterns in the next causes.
TRUNCATE TABLE staging_events;3Verify
The statement runs without a syntax error. A missing table now gives 1146, not 1064.
TRUNCATE TABLE staging_events;Confirmed by dev.mysql.com
Cause 2 of 4
The table was never created on this server or in this database
1Check
The message names the database and the table the server looked for. A fresh environment, a restored dump that lacked the table, or a script run with a different default database all end this way.
SELECT DATABASE();
SHOW TABLES;2Fix
Create the table before you truncate it. CREATE TABLE IF NOT EXISTS makes that step safe to repeat, but the manual warns that it does not compare the structure of an existing table with your definition.
CREATE TABLE IF NOT EXISTS staging_events (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
payload JSON NOT NULL
);
TRUNCATE TABLE staging_events;3Verify
Count the rows after the truncate.
SELECT COUNT(*) FROM staging_events;Confirmed by dev.mysql.com
Cause 3 of 4
The script has to run where the table may or may not exist
1Check
Inside a stored program, an unhandled error ends the program at that statement. A CONTINUE handler for the SQLSTATE of 'table does not exist', which is 42S02 for error 1146, lets the program go on. TRUNCATE causes an implicit commit, which stored procedures permit.
SHOW TABLES LIKE 'staging_events';2Fix
Put the TRUNCATE in a stored procedure that carries a CONTINUE handler with an empty body, and call that procedure. Only conditions with that SQLSTATE are ignored; every other error still stops the procedure.
DELIMITER //
CREATE PROCEDURE truncate_staging_if_exists()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' BEGIN END;
TRUNCATE TABLE staging_events;
END //
DELIMITER ;
CALL truncate_staging_if_exists();3Verify
Call it on a server with the table and on one without: neither call returns an error.
CALL truncate_staging_if_exists();Confirmed by dev.mysql.com
Cause 4 of 4
One failed TRUNCATE stops the whole script run by the mysql client
1Check
By default the mysql client stops at the first SQL error in a file. Statements after it are skipped.
mysql -u root -p mydb < reset.sql2Fix
For a throwaway reset script, tell the client to go on after errors. It then also goes on after every other error, so read the output.
mysql --force -u root -p mydb < reset.sql3Verify
The error for the missing table is printed once, and the rest of the file ran.
mysql --force -u root -p mydb < reset.sqlConfirmed by dev.mysql.com
How do I stop it coming back?
- Create tables in migration files that always run first, and keep reset scripts for tables that migrations own.
- Name the database in the connection or with USE, so a script cannot run against the wrong default database.
- Do not use --force for anything that matters: it hides errors you did not expect.
- Remember what TRUNCATE is: a DDL statement that commits at once and cannot be rolled back, so keep it out of code paths that expect a transaction.
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
MySQL says Table 'db.table' doesn't exist but you can see it
ER_NO_SUCH_TABLE: Table '%s.%s' doesn't exist
Read the name in the message: it is exactly the database and table MySQL looked for.
2 causes, each with a fixDatabases
A script with DELIMITER fails with error 1064 near DELIMITER
%s near '%s' at line %d
DELIMITER is a command of the mysql client, not an SQL statement, so the server cannot parse it.
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 fix