Bug: "Analyze Database Structure" fails with MySQL error #1055
Description
When running "Analyze Database Structure", the database comparison fails with a Doctrine DBAL / MySQL error.
The feature appears to compare the current database schema against the definitions provided by the loaded extensions (ext_tables.sql). However, the SQL query used during the analysis is not compatible with MySQL's ONLY_FULL_GROUP_BY mode.
Error Message
#1055 Doctrine\DBAL\Exception\DriverException
SQLSTATE[42000]: Syntax error or access violation: 1055
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'naegler_hamburg.tx_aim_request_log.uid' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
Steps to Reproduce
- Open the backend.
- Open "Analyze Database Structure".
- Run the database structure analysis.
- The database comparison fails with the error above.
Expected Behavior
The database structure should be analyzed successfully and compared against the loaded extension definitions.
The analyzer should then display the required schema changes.
Actual Behavior
The database comparison fails before the analysis can be completed.
MySQL rejects the generated query because tx_aim_request_log.uid is included in the SELECT list but is neither included in the GROUP BY clause nor used within an aggregate function.
This occurs when MySQL is running with:
sql_mode=ONLY_FULL_GROUP_BY
Technical Details
Affected table:
tx_aim_request_log
Affected column:
uid
Exception location:
vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php line 102
The underlying query appears to contain a structure equivalent to:
SELECT uid, ...
FROM tx_aim_request_log
...
GROUP BY ...
where uid is not part of the GROUP BY clause.
Possible Fix
The generated SQL should be made compatible with MySQL's ONLY_FULL_GROUP_BY mode.
Depending on the purpose of the query, possible solutions include:
- adding
uid to the GROUP BY clause,
- using an appropriate aggregate function for
uid, or
- restructuring the query so that the
GROUP BY is no longer required.
Disabling ONLY_FULL_GROUP_BY would only work around the problem and should not be necessary for a correctly generated query.
Impact
Medium / High
The database structure analysis cannot be completed on a standard MySQL configuration with ONLY_FULL_GROUP_BY enabled.
Environment
- Database: MySQL
- SQL mode:
ONLY_FULL_GROUP_BY
- Database abstraction layer: Doctrine DBAL
- Affected table:
tx_aim_request_log
System Information
This is a short system overview. For advanced information please head to: Environment Module
- TYPO3 Version: 14.3.6
- Installation Method: Composer mode
- Web Server: nginx/1.31.3
- PHP Version: 8.5.8
- Debugger: xdebug 3.5.3
- Database (Default): MySQL 9.3.0
- Application Context: Production
Screenshots
The attached screenshots show:
- The "Analyze Database Structure" dialog with the detected schema changes.
- The resulting Doctrine/MySQL
#1055 exception.
Bug: "Analyze Database Structure" fails with MySQL error #1055
Description
When running "Analyze Database Structure", the database comparison fails with a Doctrine DBAL / MySQL error.
The feature appears to compare the current database schema against the definitions provided by the loaded extensions (
ext_tables.sql). However, the SQL query used during the analysis is not compatible with MySQL'sONLY_FULL_GROUP_BYmode.Error Message
#1055 Doctrine\DBAL\Exception\DriverException
SQLSTATE[42000]: Syntax error or access violation: 1055
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'naegler_hamburg.tx_aim_request_log.uid' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
Steps to Reproduce
Expected Behavior
The database structure should be analyzed successfully and compared against the loaded extension definitions.
The analyzer should then display the required schema changes.
Actual Behavior
The database comparison fails before the analysis can be completed.
MySQL rejects the generated query because
tx_aim_request_log.uidis included in theSELECTlist but is neither included in theGROUP BYclause nor used within an aggregate function.This occurs when MySQL is running with:
Technical Details
Affected table:
tx_aim_request_logAffected column:
uidException location:
vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.phpline 102The underlying query appears to contain a structure equivalent to:
where
uidis not part of theGROUP BYclause.Possible Fix
The generated SQL should be made compatible with MySQL's
ONLY_FULL_GROUP_BYmode.Depending on the purpose of the query, possible solutions include:
uidto theGROUP BYclause,uid, orGROUP BYis no longer required.Disabling
ONLY_FULL_GROUP_BYwould only work around the problem and should not be necessary for a correctly generated query.Impact
Medium / High
The database structure analysis cannot be completed on a standard MySQL configuration with
ONLY_FULL_GROUP_BYenabled.Environment
ONLY_FULL_GROUP_BYtx_aim_request_logSystem Information
This is a short system overview. For advanced information please head to: Environment Module
Screenshots
The attached screenshots show:
#1055exception.