MySQL Error 3128: ER_UNRESOLVED_HINT_NAME - How to Fix and Prevent

What is MySQL error 3128 ER_UNRESOLVED_HINT_NAME?

Error 3128 appears when MySQL fails to map the name inside an optimizer hint comment to a supported hint. Starting in 5.7.7, MySQL introduced parser validation for hints and raises ER_UNRESOLVED_HINT_NAME if the name is misspelled, unsupported, or used in the wrong context.

The server stops parsing hints at the first unresolved name, skips the remaining hints, and continues executing the statement with default optimization rules. Although the query still runs, the intended tuning directive is ignored, so performance may differ from expectations.

What Causes This Error?

Misspelled hint names are the top reason: a typo like INDEXES instead of INDEX can trigger the error immediately. Version mismatch also matters - a hint introduced in MySQL 8.0 will not be recognized by 5.7.x and will raise ER_UNRESOLVED_HINT_NAME.

Using a clause-specific hint in the wrong place, such as JOIN_ORDER inside a SELECT list rather than the SELECT hint list, produces the same error. Finally, forgetting to qualify table aliases inside hints can leave MySQL unable to resolve the identifier.

How to Fix ER_UNRESOLVED_HINT_NAME

Start by checking the exact spelling of every hint in the /*+ */ block. Cross-reference with the MySQL manual for your server version to ensure the hint is valid and supported.

If the hint is new to a later release, remove it or upgrade MySQL. When table aliases are involved, prefix the hint with the correct alias, for example INDEX(t1 idx_user_id) instead of INDEX(idx_user_id).

Common Scenarios and Solutions

Developers migrating queries from MySQL 8.0 to 5.7 often leave in BKA or NO_ICP hints that 5.7 cannot parse. Removing or replacing them with equivalent optimizer switches resolves the error quickly.

Automated query generators may output empty hint slots such as MAX_EXECUTION_TIME() if no value is supplied. Adding the required argument or deleting the hint prevents ER_UNRESOLVED_HINT_NAME.

Best Practices to Avoid This Error

Validate hints in staging before deploying to production. Pair continuous-integration checks with the SELECT /*+ SET_VAR(optimizer_switch='derived_merge=off') */ 1 technique to make sure hints compile.

Use Galaxy’s schema-aware autocomplete, which lists only hints supported by the connected MySQL version. This minimizes typos and version drift.

Related Errors and Solutions

ER_UNSUPPORTED_HINT (3130) surfaces when the hint syntax is recognized but not allowed for the statement type. ER_WARN_UNSUPPORTED_HINT (3129) logs a warning rather than an error for non-critical mismatches. Both can be fixed by adjusting or deleting the offending hint.