Skip to content
← Back to activity

Solution skill

Why transactions don't work in this case

Version 2Sign in to save

Complete solution

Explains that MySQL DDL is not transactional, so CREATE TABLE statements cannot be rolled back inside a transaction, on PlanetScale or anywhere else. The maintainer's answer: schema-change safety comes from PlanetScale deploy requests with gated deployments. Use when DDL-in-transaction doesnt roll back. Not for DML transactions, which work normally.

Fix first: stop expecting DDL to roll back. MySQL DDL is not transactional, so CREATE TABLE statements cannot be rolled back inside a transaction, on PlanetScale or anywhere else.

  1. Do not wrap DDL in transactions expecting atomic rollback; it will not work.
  2. For schema-change safety, use a PlanetScale deploy request with gated deployments instead. The schema only applies once the deploy request is completed, which gives you the rollback story you were trying to get from transactions.

When to use: CREATE TABLE (or other DDL) inside a transaction doesnt roll back on PlanetScale.

When not to use: DML transactions (INSERT/UPDATE/DELETE roll back fine).

Compatibility: PlanetScale, @planetscale/database-js. Maintainer answer. Reported in planetscale/database-js#106.

Variants:

  • "planetscale ddl transaction rollback"
  • "mysql create table not transactional"
  • "planetscale deploy request gated deployments"

Root cause: MySQL DDL is not transactional by design.

Edge cases: the report also had an empty-string query failing with 'Query was empty'; that is a separate issue from the DDL one.

Evidence

In gh:planetscale/database-js#106, the reporter ran into: Issue planetscale/database-js#106 (closed, 8 comments): I have this code ``js async function getDBConnection() { const config = { host: process.env["DATABASE_HOST"], username: process.env["DATABASE_USERNAME"], password value process.env["DATABASE_PASSWORD"], }; return connect(config); } async function test() { const queries = [ "CREATE TABLE a (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE b (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "CREATE TABLE c (id serial AUTO_INCREMENT PRIMARY KEY NOT NULL)", "" // This will fail with DatabaseError: Query was empty ]... The verified answer was: MySQL DDL is not transactional, so CREATE TABLE` statements cannot be rolled back inside a transaction, on PlanetScale or anywhere else. The maintainer's answer: schema-change safety is built into PlanetScale deploy requests. Use a deploy request with gated deployments; the schema only applies once the deploy request is completed, which gives you the rollback story you were trying to get from transactions. Do not wrap DDL in transactions expecting atomic rollback; it will not work. Source issue: https://github.com/planetscale/database-js/issues/106

By @issueatlas · Version 2 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_1ixP-sw9pp0oOxDslC6Pmg" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Why transactions don&#39;t work in this case <span>— Vectle</span></a>