The Intuition: Asking an Ambiguous Question
COUNT, SUM, or MAX), MySQL simply grabbed the value from whichever physical row it encountered first on disk. Because the answer looked plausible on the screen, developers wrote their code around this indeterminate behavior for years.
MySQL 8.0 refuses to answer questions structured like that. Under modern standards, if you do not tell the engine how to choose among multiple possible answers for a row, it halts the query immediately and throws an error rather than returning arbitrary data.
The Real Mechanism: Understanding SQL Modes
sql_mode. In modern releases, this variable contains a comma-separated list of flags that dictate how aggressively the parser validates incoming SQL statements and incoming data.
While legacy installations often ran with an empty or very permissive sql_mode, MySQL 8.0 enables several strict flags by default. Two of them cause nearly all legacy upgrade crashes:
First, ONLY_FULL_GROUP_BY. When this mode is active, the SQL standard rule of functional dependency is enforced. If a query includes a GROUP BY clause, any column appearing in the SELECT list, the HAVING condition, or the ORDER BY clause must meet one of two criteria: it must either be listed directly in the GROUP BY clause itself, or it must be wrapped inside an aggregate function such as MIN(), MAX(), SUM(), AVG(), or COUNT(). If an unaggregated column is not uniquely determined by the columns in the GROUP BY list, MySQL 8.0 stops execution and reports error 1055.
Second, STRICT_TRANS_TABLES. In older engines, if an application tried to insert a string that was 50 characters long into a VARCHAR(30) column, or attempted to insert an invalid date string like 0000-00-00, the server would silently truncate the text or substitute a default zero value while emitting a quiet warning. Under STRICT_TRANS_TABLES, MySQL treats data loss as a fatal failure. The insert or update statement fails completely, and transaction changes roll back. Older content management systems or home-grown billing scripts that relied on the database to silently trim oversized form inputs or accept blank dates will immediately throw unhandled exceptions.
Combined with related defaults like NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO, MySQL 8.0 shifts the database contract. The database is no longer a permissive storage bin that cleans up after sloppy code; it is a strict engine enforcing relational integrity.
Refactoring Non-Compliant Queries
ONLY_FULL_GROUP_BY, you have two structural ways to fix it depending on what the business logic actually requires.
Consider an e-commerce order table where an older script fetches the latest order for each customer using a query like SELECT customer_id, order_id, order_date FROM orders GROUP BY customer_id. In MySQL 5.5, this executed without error, but the returned order_id and order_date were not guaranteed to match or represent the actual latest order—they were simply whatever row the storage engine hit first.
If your application genuinely does not care which value is selected—for instance, if you only need any sample value from that group—you can make that intent explicit using the ANY_VALUE() function. If the application actually needs specific data, like the highest or most recent value, you should use standard aggregate functions or join against a subquery.
Here is how those approaches look when transforming an ambiguous query into valid SQL:
SQL
-- Non-compliant query (fails with Error 1055 in MySQL 8.0):
-- SELECT customer_id, order_id, order_date FROM orders GROUP BY customer_id;
-- Option A: Explicitly state that any arbitrary order_id is acceptable
SELECT customer_id, ANY_VALUE(order_id), MAX(order_date) AS latest_order
FROM orders
GROUP BY customer_id;
-- Option B: Use a correlated subquery or window function to get exact row data
SELECT customer_id, order_id, order_date
FROM orders
WHERE (customer_id, order_date) IN (
SELECT customer_id, MAX(order_date)
FROM orders
GROUP BY customer_id
);ANY_VALUE() preserves the fast performance of the original legacy query while documenting for future developers that the selected column is intentionally arbitrary. Using window functions or subqueries provides deterministic, mathematically sound results.
Common Mistakes During Server Upgrades
/etc/my.cnf, clear the sql_mode variable, and restart the service.
While clearing sql_mode makes the errors disappear immediately, it introduces significant technical debt and data risk. Disabling strict modes globally allows silent data truncation to resume across all databases on that instance. If modern applications or newer CMS components are running alongside legacy scripts, they may depend on strict mode to prevent corrupted or truncated records from being written to production tables.
Another common pitfall is changing sql_mode inside the web application at the session level without testing edge cases. Developers sometimes add SET SESSION sql_mode = '' immediately after establishing the database connection. While this confines the permissive behavior to the legacy application and keeps the rest of the server safe, it masks ongoing data hygiene problems. Zero-dates (0000-00-00) will continue to populate tables, which causes major failures later if you ever need to dump and restore the database using standard tools or replicate to a managed cloud database where strict mode cannot be bypassed.
A third issue is assuming that primary keys always satisfy functional dependency. If your GROUP BY clause groups by a non-unique column (such as a category name or a status slug) rather than the table's primary key, MySQL 8.0 will still flag every other column in the SELECT list as indeterminate. Grouping by the unique primary key tells the optimizer that all other columns in that row are functionally dependent on that key, which allows MySQL to accept those columns without requiring explicit aggregate wrappers.
Preparing Legacy Systems for Migration
ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES on a staging copy running an older MySQL version to see what fails while production remains untouched.
Modern strict modes exist to prevent subtle, invisible data corruption and inconsistent report outputs. Treating error 1055 as a signal to fix ambiguous SQL logic—rather than an obstacle to disable—ensures your database remains reliable, predictable, and maintainable long after the upgrade is finished.