How to Fix MySQL Error 1766 (ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION)
You trigger mysql error 1766 when you change a protected system variable during an open transaction. To fix it, finish the current transaction with a commit or rollback before setting the variable. The database engine uses the symbol ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION and SQLSTATE HY000 for this failure. It tells you that the system variable cannot be set when there is an ongoing transaction. Database rules protect data state and replication logs from mid-transaction shifts. What causes
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You trigger mysql error 1766 when you change a protected system variable during an open transaction. To fix it, finish the current transaction with a commit or rollback before setting the variable.
The database engine uses the symbol ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION and SQLSTATE HY000 for this failure. It tells you that the system variable cannot be set when there is an ongoing transaction. Database rules protect data state and replication logs from mid-transaction shifts.
What causes mysql error 1766 in your database
MySQL locks specific session and global variables as soon as a transaction begins. Changing these settings mid-flight could corrupt the binary log or break ACID rules. When an app or engineer tries to alter one of these settings, the engine stops the command.
Many teams hit this wall without knowing a transaction is even open. Database drivers and ORMs often turn off auto-commit mode behind the scenes. When auto-commit is off, your very first read or write query starts a new transaction. That transaction keeps the session active until your code sends an explicit commit or rollback.
Session pooling tools can also cause this error across shared database sessions. One app thread might run a query and leave the transaction open by mistake. When the next workflow grabs that pooled session and updates a variable, the command fails. Admin scripts that mix schema setup, variable updates, and data changes often trigger this same trap.
How to diagnose and fix the failure in production
First, find the exact place where your workflow sets the system variable. Check your query logs to see which queries ran right before the failure. Look for explicit transaction start commands or earlier queries running with auto-commit turned off.
Once you find the open work, end the transaction before you update the variable. Issue a commit if the earlier changes are ready to save to disk. Issue a rollback if you need to discard those uncommitted steps and start fresh. After the transaction closes, your variable update will run without any issue.
Just-in-Time Access + MySQL Access Governance: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
In app code, move session setup steps to the start of the database lifecycle. Configure your session variables right when the client opens a new session. Never change transaction isolation or replication variables in the middle of business logic. If you use a session pool, tell the pool to reset session state before handing out connections.
How to prevent transaction state conflicts
Good database hygiene stops this error from coming back during future deploys. Keep your variable changes separate from your data modification scripts at all times. When engineers run manual fixes, they should check their client auto-commit status first. Many desktop database tools disable auto-commit to save users from bad deletes.
You can also enforce strict query review rules for production database sessions. Reviewing admin scripts before they run catches mixed transaction and setup commands early. Limiting direct manual variable changes in production keeps your database state predictable and safe. You do not need to grant broad superuser rights to fix a simple ordering mistake.
Check MySQL queries and results on the wire
MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL, inline in the MySQL wire protocol. On every query an AI agent or human runs against production MySQL, RDS, or Aurora, hoop.dev decides to pass, mask, block, or hold it for review.
Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails, which refuse banned queries with a native MySQL error or hold risky ones for approval. On the response path, hoop.dev data masking rewrites string values in returned rows in memory, and every decision is logged. See the MIT-licensed project on GitHub.
Frequently asked questions
What does ER_VARIABLE_NOT_SETTABLE_IN_TRANSACTION mean in MySQL?
This symbol means you tried to change a restricted system variable while a transaction was active. MySQL blocks the change so your transaction logs and data remain consistent.
Why do I get mysql error 1766 when I did not start a transaction?
Your database client or ORM likely turned off auto-commit mode for your session. With auto-commit off, any standard query starts an open transaction in the background.
Can I bypass this error by granting higher user privileges?
No, extra user roles or admin rights will not bypass this engine rule. You must commit or roll back the active transaction before you set the variable.
Related guides
In this post