How to Fix MySQL Error 3154 (ER_JSON_BAD_ONE_OR_ALL_ARG)

Published
Length
3 min read

Luis Felipe Dias

All posts

You trigger mysql error 3154 when a JSON function receives an invalid search scope argument. To fix it, pass only the exact string one or all into that function position. The database engine maps this issue to the symbol ER_JSON_BAD_ONE_OR_ALL_ARG and SQLSTATE 42000. When a query fails this check, the server stops execution and returns the message. Why mysql error 3154 happens in your database MySQL includes built-in functions that search inside JSON documents for specific values or paths. 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 trigger mysql error 3154 when a JSON function receives an invalid search scope argument. To fix it, pass only the exact string one or all into that function position.

The database engine maps this issue to the symbol ER_JSON_BAD_ONE_OR_ALL_ARG and SQLSTATE 42000. When a query fails this check, the server stops execution and returns the message.

Why mysql error 3154 happens in your database

MySQL includes built-in functions that search inside JSON documents for specific values or paths. These functions ask you to define how wide the search should be.

The engine refers to this specific setting as the oneOrAll argument in its official message. The rule is strict and accepts only the words one or all as valid input.

Passing the word one tells the engine to stop after the first match it finds. Passing the word all tells the engine to return every matching path in the document.

Developers often cause mysql error 3154 by putting arguments in the wrong order during a query. If you swap the search string and the scope word, the parser rejects the query.

Dynamic query builders in an app can also send an empty string or a number. Even a small typo or extra space inside the quotes will trigger this syntax failure.

Because SQLSTATE 42000 marks a syntax or access rule violation, the query stops right away. The database will not read or change any rows until you send a valid word.

How to find and fix the bad argument in production

Start by checking your app error logs to find the exact query that failed. The error message names the specific JSON function that received the bad input value.

JSON Web Tokens (JWT) + MySQL Access Governance: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Look at the second argument passed into that function in your code or query builder. Check whether a variable holds a null value, a boolean, or a misspelled word.

If your app lets users pick search options, validate that input before building the query. Restrict the variable in your code so it can only hold one or all.

When you inspect live data to test a fix, follow safe access rules in production. Do not give developers broad database admin roles just to debug a broken JSON query.

Run your test queries through a controlled workflow that logs every action and query. Data masking can rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in results.

This approach lets engineers check JSON structures and test query fixes without exposing private data. Once the test query works, deploy the updated code through your normal release pipeline.

How to prevent JSON argument errors in app code

You can stop this error early by using constants for your JSON search modes. Define fixed constants for the words one and all in your shared data layer.

Avoid building raw query strings by joining user input directly into the function call. Use prepared statements and bound parameters so the database receives clean values every time.

Add unit tests that check both single match and full match paths in your app. Good test coverage catches argument order mistakes before the code ever reaches your production database.

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 SQLSTATE 42000 mean for MySQL error 3154?

SQLSTATE 42000 is a standard code for syntax errors or access rule violations. Here, it means the JSON function rejected an argument that broke its input rules.

Does the one or all argument care about letter case?

MySQL accepts the words one and all in either uppercase or lowercase letters. Still, adding extra spaces or any other characters inside the quotes will cause the error.

Can a null value trigger ER_JSON_BAD_ONE_OR_ALL_ARG?

Passing an actual SQL null value usually makes the JSON function return a null result. Passing the word null inside quotes as a string will trigger error 3154 right away.

The query runs. The emails stay hidden.