A WITH common table expression fails with a syntax error in MySQL
ERROR 1064 (42000) on a statement that begins with WITHShort answer
Common table expressions arrived in MySQL 8.0. On 5.7 or older, a query that starts with WITH is a syntax error. Check SELECT VERSION(). Upgrade to 8.0 or later if you own the server, or rewrite each CTE as a derived table: a subquery in the FROM clause with an alias.
Fix it fast
1Find out which cause you have
Ask the server that rejects the query for its version. Anything below 8.0 cannot run CTEs.
SELECT VERSION();2Match what you see, then jump to its fix
- 1
VERSION() returns 5.x, and the error points at WITHThe server is older than MySQL 8.0Fix - 2
The platform is stuck on a 5.x releaseYou cannot change the server (judge, host or old client project)Fix - 3
A WITH query on MySQL 8.0 or later fails and the CTE refers to its own nameA recursive CTE is missing the RECURSIVE keywordFix
- 1
What does the error look like?
ERROR 1064 (42000) on a statement that begins with WITHThe same query works on your laptop but fails on an online SQL judge
Cause 1 of 3
The server is older than MySQL 8.0
1Check
The MySQL 8.0 release notes list common table expressions, both nonrecursive and recursive, as new in 8.0. Earlier versions have no WITH clause.
SELECT VERSION();2Fix
If you control the server, plan an upgrade to a supported release (8.0 or later). If you do not, use the rewrite in the next cause.
-- needs MySQL 8.0 or later
WITH recent AS (SELECT id, total FROM orders WHERE total > 100)
SELECT * FROM recent;3Verify
On the upgraded server the WITH query returns rows.
SELECT VERSION();Confirmed by dev.mysql.com
Cause 2 of 3
You cannot change the server (judge, host or old client project)
1Check
MySQL's WITH syntax is WITH [RECURSIVE] name AS (subquery). A non-recursive CTE can be replaced by a derived table, which is a subquery in the FROM clause; the alias is required.
SELECT VERSION();2Fix
Move the subquery into FROM and give it an alias. If the CTE is used twice, repeat the subquery, or save it as a view or temporary table.
SELECT recent.id, recent.total
FROM (SELECT id, total FROM orders WHERE total > 100) AS recent;3Verify
The rewritten query runs on the old server and returns the same rows.
SELECT COUNT(*) FROM (SELECT id FROM orders WHERE total > 100) AS recent;Confirmed by dev.mysql.com
Cause 3 of 3
A recursive CTE is missing the RECURSIVE keyword
1Check
The manual says the WITH clause must begin with WITH RECURSIVE if any CTE in it refers to itself. A recursive CTE has a nonrecursive SELECT part and a recursive SELECT part joined with UNION ALL or UNION DISTINCT.
SELECT VERSION();2Fix
Add the RECURSIVE keyword and join the two parts with UNION ALL.
WITH RECURSIVE cte (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;3Verify
The query returns the numbers 1 to 5.
SELECT 1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Run SELECT VERSION() on a new platform before writing queries with CTEs or window functions.
- Write portable SQL (derived tables) when the same query must run on servers you do not control.
- Test queries on the version you deploy to, not only on your laptop.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix