The sql_mode value supplied is obsolete and ignored in MySQL 5.7.4+, triggering ER_SQL_MODE_NO_EFFECT (Error 3027).
MySQL Error 3027 ER_SQL_MODE_NO_EFFECT appears when you set a deprecated sql_mode value. MySQL ignores that mode and asks you to switch to STRICT_ALL_TABLES or STRICT_TRANS_TABLES. Update your sql_mode and restart the session or server to fix the issue.
ER_SQL_MODE_NO_EFFECT
MySQL raises Error 3027 when you attempt to enable a sql_mode value that was removed in version 5.7.4 and later. The server prints the message '%s' mode no longer has any effect. Use STRICT_ALL_TABLES or STRICT_TRANS_TABLES instead.
The error is purely informational but signals that your current sql_mode configuration is outdated. Ignoring it can hide data-quality problems that strict SQL modes normally catch.
Error 3027 is triggered during connection or SET SESSION sql_mode when the mode list includes deprecated options like ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, or PAD_CHAR_TO_FULL_LENGTH.
From MySQL 5.7.4 forward, these options were removed or folded into STRICT modes. Any script, my.cnf file, or client session that still references them will hit ER_SQL_MODE_NO_EFFECT.
Remove obsolete flags and replace them with supported strict modes. The safest replacement is STRICT_ALL_TABLES because it enforces strict checks for both transactional and non-transactional tables.
Apply the change either at the global server level in my.cnf or dynamically in your session, then reconnect to verify the warning disappears.
Legacy applications upgraded from MySQL 5.6 often carry forward old sql_mode settings in configuration management templates. Update those templates to prevent repeated warnings.
CI pipelines that run SET GLOBAL sql_mode in migration scripts should add conditional checks for server version before executing outdated flags.
Standardize on STRICT_ALL_TABLES or STRICT_TRANS_TABLES across all environments. Version-control your my.cnf and review it during upgrades.
Leverage a modern SQL IDE like Galaxy to lint connection scripts. Galaxy surfaces deprecated sql_mode values inline, preventing commits that would trigger Error 3027.
Error 1067 invalid default value can appear after you switch to strict mode because invalid dates become disallowed. Fix the column defaults accordingly.
Error 1364 Field doesn't have a default value may arise once strict mode is active. Provide explicit column values or adjust schema defaults.
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, PAD_CHAR_TO_FULL_LENGTH and similar flags were removed in 5.7.4. Their presence triggers Error 3027.
Configuration files copied from older servers often include deprecated modes. Restarting MySQL with such a file causes the server to log ER_SQL_MODE_NO_EFFECT.
Deployment scripts that run SET SESSION sql_mode='...' without version checks may include obsolete modes, generating the warning at runtime.
Occurs when strict mode is enabled and a column default violates date or numeric constraints.
Raised in strict mode when an INSERT omits a NOT NULL column without default.
Appears under strict mode for invalid numeric conversions.
No. The server ignores the deprecated mode and continues processing. The warning highlights an outdated configuration that should be corrected.
STRICT_ALL_TABLES is safest because it enforces data integrity checks on all storage engines. STRICT_TRANS_TABLES limits enforcement to transactional tables.
Strict mode may surface hidden data issues. Test in staging first, fix invalid data or schema defaults, then deploy to production.
Galaxy flags deprecated sql_mode values during code review and provides AI suggestions to update them, preventing Error 3027 before deployment.
.avif)
.avif){kind=logo}
