Ran DELETE or UPDATE without WHERE? How to recover the rows, and what MySQL Error 1175 means

VERICTO-001 Critical In CI (offline CLI): BLOCKED

A DELETE or UPDATE without a WHERE clause applies to every row of the table. If the transaction is still open, run ROLLBACK now and nothing is lost. If it was committed (psql, most drivers and MySQL Workbench autocommit each statement), SQL has no undo: the rows come back only from a backup, point-in-time recovery or, on MySQL, the binary log. Stop the writes to that table first, so what you restore is not overwritten again.

How to recover the data

  • Still in a transaction? ROLLBACK; in the same session undoes it (psql shows an asterisk in the prompt, mydb=*#, while a transaction is open). On a committed statement it changes nothing.
  • PostgreSQL: restore a base backup with WAL archiving to a separate instance, with recovery_target_time set just before the statement, then copy the rows back (pg_dump --data-only -t table from the restored instance). Managed services such as Amazon RDS and Google Cloud SQL do point-in-time restore into a new instance.
  • MySQL: restore the last full backup to a separate server and replay the binary log with mysqlbinlog --stop-datetime (or --stop-position) up to just before the statement. With row-based logging (binlog_format=ROW, the default since MySQL 5.7.7) the binary log also holds the changed rows.
  • No backup at all? On PostgreSQL, deleted rows stay on disk until VACUUM reclaims them; extensions such as pg_dirtyread can read them as a last resort, so do not vacuum that table until you have decided.

Why it happens

The statement had no row filter. It is usually one of: a script whose WHERE was commented out or lost in a copy-paste, a query built from a template where the condition came out empty, an ORM call such as Model.delete() or update_all on an unfiltered scope, or SQL written by an AI agent that took "clean up the old rows" literally. An always-true condition (WHERE 1=1) has the same effect, and is its own rule.

How to fix it

Write the filter first, check how many rows it selects, and run the change inside a transaction you only commit when the count is right:

BEGIN;
SELECT count(*) FROM users WHERE last_login < NOW() - INTERVAL '2 years';
DELETE FROM users WHERE last_login < NOW() - INTERVAL '2 years';
-- the DELETE reports the same count? COMMIT; otherwise ROLLBACK;
COMMIT;

For large deletes, delete in batches by primary key. On MySQL, keep sql_safe_updates on for interactive sessions (below). And give the application and any AI agent a database role that only has the privileges it needs.

MySQL Error 1175: safe update mode

MySQL Workbench enables safe update mode by default, so an unfiltered statement fails with:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

That error is the protection working: the statement would have touched every row. The fix is to name the rows by a key column (WHERE id = 42, or WHERE id IN (…)), not to switch the mode off. Safe update mode also accepts a LIMIT, which bounds the damage but does not choose the right rows. It only exists in MySQL sessions that turn it on (SET sql_safe_updates = 1, or mysql --safe-updates); PostgreSQL has no equivalent, and neither applies to the application's or an agent's own connection unless it sets it.

UPDATE without WHERE

An UPDATE products SET price = 0 without WHERE rewrites every row, and recovery is the same as for a DELETE: ROLLBACK before the commit, a backup or point-in-time recovery after it. The rows are still there, so the damage is easy to miss until someone reads them. Vericto reports it as VERICTO-030 (and with the same predicate as VERICTO-042); the CLI output below is for the DELETE, and the 030 page has the UPDATE.

How to stop it from happening again

VERICTO-001 · DELETE without WHERE clause · Severity: Critical · AST node: DeleteStmt > WhereClause = NULL

The statement the rule reports

-- cleanup.sql
DELETE FROM users;

Real CLI output

$ npx @vericto/vericto-cli@2.3.0 check --offline cleanup.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
✖ cleanup.sql  BLOCKED  VERICTO-001 critical
    DeleteStmt > WhereClause = NULL
    fix: DELETE FROM users WHERE id = $1
1 blocked · 0 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

✖ Vericto blocked 1 statement  (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
  ✖ cleanup.sql  BLOCKED  VERICTO-001 critical
      DeleteStmt > WhereClause = NULL
      fix: DELETE FROM users WHERE id = $1

This is Vericto enforcing a rule — the exit code is intentional, not a crash.
To allow a finding on purpose, add an inline comment on the statement:
    -- vericto:ignore[VERICTO-001] <reason>   (a reason is required)

The CLI blocks it and exits with code 1, so the CI job fails before the merge.

The corrected version

-- cleanup.sql
DELETE FROM users WHERE id = 42;
$ npx @vericto/vericto-cli@2.3.0 check --offline cleanup.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored   (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))

Try it free, no account

The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).

npx @vericto/vericto-cli@2.3.0 check --offline migrations/

On every pull request

The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:

on: pull_request
jobs:
  vericto:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v7
        with: { fetch-depth: 0 }
      - uses: vericto/check-sql@v1
        with: { offline: true, upload-sarif: false }

At runtime, for AI agents

The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.

The AST condition in the engine

The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:

"VERICTO-001" => find(stmts, |s| {
    s.kind == StatementKind::Delete && s.where_presence == WherePresence::Absent
})
.map(|s| violation(rule, s, suggest_delete(s))),

Frequently asked questions