MySQL raises error 3059 when a REPLACE statement tries to delete rows that are not accessible through the target view or table.
ER_REPLACE_INACCESSIBLE_ROWS (MySQL error 3059) occurs when a REPLACE INTO must delete rows that the session cannot access - usually because the target is an updatable view missing primary keys or the user lacks DELETE rights. Add the key column or grant DELETE privilege to resolve.
ER_REPLACE_INACCESSIBLE_ROWS
MySQL throws error 3059, ER_REPLACE_INACCESSIBLE_ROWS, when a REPLACE statement tries to remove rows that are not visible or updatable in the target view or table. The server halts execution because the delete portion of REPLACE would affect inaccessible data.
The error was introduced in MySQL 5.7.5 to protect data consistency. It commonly appears when developers run REPLACE on an updatable view that hides the primary key columns needed to locate conflicting rows.
The error surfaces chiefly on updatable views that omit primary key columns. Without the key, MySQL cannot match and delete existing rows.
Another trigger is a REPLACE that references a table where the user lacks DELETE privilege, even though INSERT is allowed.
The error can also appear when sql_safe_updates or read_only mode is active, or when triggers or foreign keys block the needed delete action.
First verify whether the target is a view. If so, amend the view to include primary key columns and mark it WITH CASCADED CHECK OPTION so MySQL can delete safely.
If privileges cause the issue, grant DELETE on the base table or execute the statement as a definer with full rights.
When safe update mode blocks deletion, run SET SQL_SAFE_UPDATES=0 or disable read_only for the session, then rerun the REPLACE.
Using REPLACE on a summary view that omits the id column will fail. Alter the view to expose the id column or perform an explicit DELETE followed by INSERT.
Running REPLACE on a replica set to read_only also triggers error 3059. Temporarily disable read_only before executing the statement.
BEFORE DELETE triggers that raise errors can likewise break REPLACE. Modify or disable the trigger during maintenance.
Always include primary key columns in updatable views when REPLACE might be used.
Prefer INSERT ... ON DUPLICATE KEY UPDATE over REPLACE for idempotent writes because it does not require a hidden delete.
Grant consistent INSERT and DELETE privileges to accounts expected to run REPLACE, and keep SQL_SAFE_UPDATES off in automation scripts.
Error 1393 update target not updatable - arises when UPDATE touches a non-updatable view. Include primary keys or update the base table.
Error 1288 target table for DELETE is not updatable - similar cause; adjust the view definition or privileges.
Error 1175 You are using safe update - disable SQL_SAFE_UPDATES or supply a WHERE clause with key columns.
REPLACE needs to match rows by primary key. If the view omits the key, MySQL cannot locate rows to delete and raises 3059.
The executing user has INSERT permission but lacks DELETE on the base table, making the delete portion of REPLACE impossible.
SQL_SAFE_UPDATES=1 or READ ONLY mode blocks deletes, so REPLACE fails with ER_REPLACE_INACCESSIBLE_ROWS.
BEFORE DELETE triggers or foreign key constraints can prevent the required delete, leading MySQL to abort with error 3059.
Raised when UPDATE fails on a non-updatable view. Fix by exposing key columns or updating the base table.
Occurs on DELETE through a view lacking key columns. Similar remediation to error 3059.
Triggered when updates or deletes lack key constraints while SQL_SAFE_UPDATES is on. Disable the mode or tighten the WHERE clause.
No MySQL hint overrides ER_REPLACE_INACCESSIBLE_ROWS. You must adjust the view or rewrite the statement.
Yes if the executing user lacks DELETE privilege or if the table is read only. Otherwise REPLACE works on tables.
Yes because it updates existing rows without deleting them, avoiding privilege and trigger constraints that block REPLACE.
Galaxy highlights privilege mismatches and suggests ON DUPLICATE KEY UPDATE when it detects REPLACE on a non-updatable view, preventing runtime failures.
.avif)
.avif){kind=logo}
