How to Fix MySQL Error 4091 (ER_XA_TEMP_TABLE)
You see mysql error 4091 when an XA transaction tries to use a temporary table. You can fix it by removing temporary tables from XA work or changing your transaction rules. MySQL logs this issue under the symbol ER_XA_TEMP_TABLE and the state HY000. The engine blocks the query when the detach on prepare rule is turned on. This rule protects your data across two-phase commits, so it rejects local temporary tables. Understanding how distributed work handles session state helps your team prevent t
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 see mysql error 4091 when an XA transaction tries to use a temporary table. You can fix it by removing temporary tables from XA work or changing your transaction rules.
MySQL logs this issue under the symbol ER_XA_TEMP_TABLE and the state HY000. The engine blocks the query when the detach on prepare rule is turned on. This rule protects your data across two-phase commits, so it rejects local temporary tables. Understanding how distributed work handles session state helps your team prevent this failure.
Why mysql error 4091 happens in distributed work
Distributed transactions use the XA protocol to coordinate changes across two or more systems. A transaction manager prepares each branch first and then commits them all together. When the detach on prepare setting is active, the engine detaches the transaction from the current session. This step happens right after the prepare phase finishes.
Detaching the branch lets another session finish the commit or rollback later. It also frees up the client session so the app can reuse it right away. Still, temporary tables belong only to the specific session that created them. If the XA branch detaches from that session, the temporary table loses its link to the work.
Because another session cannot read that temporary table, the engine cannot keep the transaction consistent. To stop data loss or broken replication, MySQL blocks temporary table use inside the XA block. As soon as your app creates, reads, or writes to a temporary table, the engine returns error 4091.
How to find and fix the failure in production
First, check your query logs to find which app workflow starts the failing XA transaction. Look for reports or batch jobs that build temporary tables inside a distributed unit of work. Often, an object-relational mapper starts an XA block and calls a stored procedure that uses temporary tables.
The best fix is to rewrite the query so it does not need a temporary table. You can use common table expressions or derived tables inside a single query instead. These structures run in memory for that one statement and do not persist across the session. They give you the same result while following the rules for detached XA transactions.
MySQL Access Governance + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
If your app must stage data across multiple steps, use a standard base table instead. Add a column for the transaction ID or session ID to keep parallel runs separate. Have your app clean up its own rows before the XA transaction prepares. This approach works with detached branches and keeps replication safe.
Some teams consider turning off the detach on prepare setting for that session. While this allows temporary tables in some setups, it ties the XA branch to the open session. If the client drops before the commit phase, recovery gets much harder to manage. Rewriting the SQL logic is much safer than changing engine safety rules or granting admin rights.
Preventing transaction errors before deployment
Developers often test queries in local setups that use standard local transactions. A query that uses a temporary table will pass local tests without any warnings. The error only shows up in staging or production when the transaction manager wraps the call in XA.
Make sure your test systems match the transaction settings of your production database. Review database changes for temporary table commands whenever your app uses two-phase commit managers. Catching these statements during peer review keeps your production pipelines running without unexpected rollbacks.
Add wire guardrails and masking beside MySQL
Database grants and TLS setup decide who can reach MySQL. Even so, an open session can run broad updates or read raw personal data from allowed tables. In practice, hoop.dev sits inline in the wire protocol beside the database as a sidecar for your MySQL. On every production query an agent or engineer sends, hoop.dev decides to pass, mask, block, or escalate it for human review.
With hoop.dev MySQL support, guardrails refuse bad queries before they run or hold risky calls for approval, while data masking rewrites matched sensitive values in result rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for GDPR, HIPAA, and PCI DSS programs. Read the docs and source on GitHub.
Frequently asked questions
What does the ER_XA_TEMP_TABLE symbol mean in MySQL?
ER_XA_TEMP_TABLE is the official symbol for MySQL error code 4091 under SQLSTATE HY000. It means a query tried to use a temporary table inside an XA transaction while detach on prepare is on.
Why does detach on prepare block temporary tables?
Temporary tables belong to one session, while a detached XA transaction moves away from that session. MySQL blocks the action so the transaction does not lose its data when it detaches.
Can I use common table expressions instead of temporary tables?
Yes, common table expressions and subqueries work well inside XA transactions. They live only for a single statement and do not create session-bound temporary tables.
Related guides
In this post