<p>MySQL throws error 1792 when a data-modifying statement is issued inside a READ ONLY transaction or on a read-only replica.</p>
<p>MySQL Error 1792: ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION means the server blocked a write because the current transaction or replica is read only. End the read only transaction, switch to READ WRITE, or run the statement on the primary node to resolve the issue.</p>
Cannot execute statement in a READ ONLY transaction.
MySQL returns this error when a statement that modifies data or schema is executed while the current transaction is marked READ ONLY or the session is connected to a read only replica. The server protects data integrity by forbidding any write during such transactions.
The storage engine checks transaction characteristics at execution time. If START TRANSACTION READ ONLY or SET SESSION TRANSACTION READ ONLY was issued, any INSERT, UPDATE, DELETE, REPLACE, CREATE, ALTER or DROP fails with error 1792. The same happens on replicas running with super_read_only or read_only flags.
Convert the transaction to READ WRITE, end the current read only transaction, or route the query to a writable primary. On replicas, temporarily disable super_read_only or read_only if safe and permitted.
Application pools often default to read only for reporting but later reuse the same connection for writes. Explicitly issue SET SESSION TRANSACTION READ WRITE before the first insert, or open a fresh primary connection. In stored procedures, check @@tx_read_only and raise custom messages to avoid silent failure.
Separate read and write workloads using dedicated connection pools. Close transactions quickly to reduce state leakage. Use Galaxy connection labels to mark queries as read or write, ensuring that modifying statements are never routed to a replica.
START TRANSACTION READ ONLY or SET SESSION TRANSACTION READ ONLY sets the transaction to read only, causing subsequent writes to fail.
A client connected to a replica with read_only=ON or super_read_only=ON triggers the error for any write.
Applications may reuse a read only connection for a write without resetting the session, leading to unexpected failures.
Occurs when attempting writes on a server started in full read only mode.
Long running transactions on replicas can also trigger lock wait issues.
Appears when disabling super_read_only without the required SUPER privilege.
No. You must commit or roll back the transaction and start a new one with READ WRITE.
No. Pure SELECT statements are allowed in read only transactions.
Aurora replicas are inherently read only. Route all writes to the writer endpoint.
Galaxy tags connections as read or write and blocks modifying queries from being executed on read only replicas, preventing error 1792.
.avif)
.avif){kind=logo}
