<p>MySQL error 1766 occurs when you try to change a protected system variable while a transaction is still open.</p>
<p>MySQL Error 1766: ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION means the server blocks SET statements on certain system variables during an active transaction. Commit or roll back the transaction, then issue the SET, or move the change to session start to resolve the problem.</p>
The system variable %s cannot be set when there is an
MySQL raises error 1766 when a client issues SET GLOBAL or SET SESSION against a system variable that the engine forbids modifying while a transaction is open. The message returned is: The system variable %s cannot be set when there is an ongoing transaction.
The restriction exists to protect transactional consistency. Changing critical parameters such as autocommit, foreign_key_checks, or sql_mode mid-transaction could yield unpredictable results, so the server rejects the request.
The error fires whenever a protected variable is targeted by a SET command after START TRANSACTION or when autocommit is OFF. It also appears if your client library implicitly begins a transaction, for example by selecting from InnoDB with autocommit disabled.
Variables most often involved include autocommit, tx_isolation, transaction_read_only, and other engine-level flags that impact transaction semantics.
First, end the current transaction with COMMIT or ROLLBACK, then run the SET statement. Alternatively, move the SET command to the very start of the session, before any data-modifying statement, or apply the change globally outside peak hours.
If the variable must change automatically, wrap your logic in stored procedures that explicitly commit before issuing SET commands, ensuring no active transaction exists.
Application starts a session, disables autocommit, runs multiple inserts, then attempts SET autocommit=1 mid-batch. Solution: commit first, or reorder the SET to the beginning.
DBA script loops through databases, sets sql_mode, and forgets an earlier START TRANSACTION. Add an explicit COMMIT before the loop or remove unnecessary START TRANSACTION.
Always commit or roll back promptly. Avoid changing transaction-sensitive variables mid-session. Place all session-level SET statements immediately after connection creation. Monitor long-running transactions with performance_schema tables and kill idle ones.
MySQL error 1178 (SQLSTATE HY000) disallows changing foreign_key_checks within a transaction. Error 1568 blocks ALTER TABLE on temporary tables in transactions. Solutions are similar: end the transaction first.
SET autocommit=1 or SET autocommit=0 after START TRANSACTION triggers the error.
SET SESSION transaction_isolation='READ COMMITTED' while a transaction is active is blocked.
SET foreign_key_checks=0 fails if the session already opened a transaction.
Some drivers start transactions automatically when autocommit is disabled, causing unexpected 1766 errors.
Occurs when toggling foreign_key_checks during an open transaction. Solution is identical: commit first.
Blocking ALTER TABLE actions on temp tables while a transaction is active.
Long-running uncommitted transactions may also lead to lock timeouts; committing sooner avoids both issues.
No. Ignoring it leaves the variable unchanged, which might break application logic. Always correct your transaction flow.
MySQL 5.7, 8.0, and MariaDB 10.2+ enforce this rule.
Yes, if the session that issues SET GLOBAL has an active transaction.
Galaxy highlights open transactions and offers AI-generated fixes, reminding you to commit before issuing SET statements.
.avif)
.avif){kind=logo}
