Today's reminder that MySQL will happily let you believe you're doing everything "right" right up until it doesn't.
I was wiring up a perfectly ordinary paginated query in a Next.js service. Nothing exotic. Prepared statements, placeholders, clean parameter handling. The kind of code you write on autopilot because you've written it a hundred times before.
And then MySQL reminded me again that it is not PostgreSQL.
The offending query looked harmless:
SELECT *
FROM writings
ORDER BY created_at DESC
LIMIT ? OFFSET ?
The parameters were validated. Integers only. Floored. Bounded. Safe. Sensible.
And still: runtime error.
The cause, as always, is not obvious until you already know it:
MySQL does not support placeholders forLIMIT and OFFSET in prepared statements.
Not partially. Not "sometimes." Just no.
This is one of those behaviors that lives in the uncanny valley between "documented" and "constantly forgotten." Every time I switch contexts PostgreSQL to MySQL, ORM generated queries to raw SQL I relearn this the hard way.
The fix is simple, but mildly infuriating.
Instead of placeholders, you must interpolate the integers directly:
const limitInt = Math.floor(limitNum);
const offsetInt = Math.floor(offset);
const dataQuery = `
SELECT *
FROM writings
${whereClause}
ORDER BY created_at DESC
LIMIT ${limitInt} OFFSET ${offsetInt}
`;
And yes, this is safe because:
limitNum andoffsetare validated
Values are clamped and floored
They cannot contain user controlled strings
Only WHERE clause values remain parameterized
The resulting query is exactly what MySQL expects:
text
SELECT *
FROM writings
ORDER BY created_at DESC
LIMIT 6 OFFSET 0
Instead of the version MySQL silently refuses to support:
SELECT *
FROM writings
ORDER BY created_at DESC
LIMIT ? OFFSET ?
This is not a Next.js problem.
It is not a driver bug.
It is not a configuration issue.
It is simply one of those MySQL constraints that leaks through abstraction layers and reminds you that SQL dialects are not interchangeable, no matter how much tooling tries to pretend they are.
The frustrating part isn't the workaround.
The frustrating part is that this failure happens after you've already done the "right" things:
- Prepared statements
- Parameterized queries
- Defensive validation
- Boring, careful code
And yet, you still have to break the pattern carefully, deliberately to satisfy the database.
The takeaway is not "don't use MySQL."
The takeaway is simpler, and more annoying:
Always remember which database you are talking to.
Because the moment you assume uniform behavior across SQL engines, reality will correct you usually in production.
Fixed.
Again.