Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

TRUNCATE TABLE only when the table exists: there is no IF EXISTS

ErrorERROR 1146 (42S02): Table 'mydb.staging_events' doesn't exist

Short 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.

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

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 1064 near 'IF EXISTS'The statement was written as TRUNCATE TABLE IF EXISTSFix
    2. 2ERROR 1146 (42S02), and SHOW TABLES does not list the tableThe table was never created on this server or in this databaseFix
    3. 3The 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. 4Statements after the failing TRUNCATE never run when you load the fileOne failed TRUNCATE stops the whole script run by the mysql clientFix

What does the error look like?

  • ERROR 1146 (42S02): Table 'mydb.staging_events' doesn't exist
  • Table '%s.%s' doesn't exist
  • ERROR 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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.sql

2Fix

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.sql

3Verify

The error for the missing table is printed once, and the rest of the file ran.

mysql --force -u root -p mydb < reset.sql

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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