MySQL raises warning 3123 when an optimizer hint is misspelled, misplaced, or uses unsupported syntax.
ER_WARN_OPTIMIZER_HINT_SYNTAX_ERROR (MySQL error 3123) means your optimizer hint is malformed or unsupported. Correct the hint keyword, placement, or remove it entirely to clear the warning and let the query run with the intended execution plan.
ER_WARN_OPTIMIZER_HINT_SYNTAX_ERROR
Error 3123 is a non-fatal warning thrown by MySQL when the parser encounters an optimizer hint that it cannot understand. MySQL continues executing the statement but ignores the problematic hint, which may lead to a sub-optimal execution plan.
The error was introduced in MySQL 5.7.7 alongside the initial optimizer hint feature set. It still appears in current 8.x releases whenever hint syntax deviates from documented rules.
The warning surfaces when a hint keyword is misspelled, contains wrong casing, or is not supported in the running server version. It also triggers if the hint is placed outside the valid comment form /*+ ... */ or when its inner parameters are malformed.
Using multiple hints separated by commas without proper spacing, nesting hints incorrectly, or passing an incorrect schema.table reference will also cause the parser to flag the hint.
Locate the faulty hint, cross-check the syntax against the official manual for your server version, and correct the spelling, parameter list, and placement. If the hint is unnecessary, simply remove it to silence the warning.
After editing, run EXPLAIN to verify that the modified hint is accepted and that the desired plan is chosen.
Developers often copy hints written for Oracle or older MySQL versions, leading to unsupported keywords. Another scenario is automated query generators injecting hints that break when table aliases change. Refactoring queries to use proper aliases resolves this quickly.
In migration projects, temporarily disable all hints, benchmark the native plan, then add only the minimal set of validated hints back.
Keep documentation for accepted hints handy, test each new hint with EXPLAIN before committing, and gate database upgrades with integration tests that include hinted queries. In Galaxy, you can save validated hint templates in a shared Collection to prevent team-wide syntax drift.
Warnings 3163 (ER_WARN_HINT_UNSUPPORTED_LEVEL) and 3170 (ER_DUP_SIGNED_KEY) often appear with badly placed hints. Correcting the hint block or downgrading to a version that supports the hint clears them.
Typos like IDX_MERGE instead of INDEX_MERGE or using hints introduced in later MySQL versions will trigger the warning.
Hints must live inside a standard comment that starts with /*+ and ends with */. Any deviation, such as extra dashes, breaks parsing.
Passing invalid table aliases, column names, or forgetting parentheses inside the hint results in error 3123.
Appears when the scope level of a hint is not recognized. Use session or statement scope only.
Occurs when the sql_mode or server option disallows a hinted feature. Adjust configuration or drop the hint.
A generic syntax error that can be thrown if the hint breaks the overall statement structure, not just the hint area.
No - MySQL treats it as a warning, ignores the faulty hint, and executes the statement.
You cannot turn this warning into a fatal error, but you can check it in application code using SHOW WARNINGS and abort if present.
The hint is skipped, so performance may degrade if the optimizer chooses a slower plan than intended.
Galaxy’s AI copilot autocompletes valid hint keywords and flags syntax errors in real time, reducing the chance of pushing broken hints.
.avif)
.avif){kind=logo}
