Syntax error or access violation: 1055 'k0_.number' isn't in GROUP BY

[xge]

Describe the bug
Adding a customer and then clicking on Administration > Customer in the sidebar crashes the application with a database exception. Customer is however persisted to the database.

To Reproduce
Steps to reproduce the behavior:

1. Install Kimai, as described in Recommended Setup
2. Add first customer, as described in Initial Setup
3. Navigate to Administration > Customer and see exception

Logfile

[2020-06-06 01:18:16] request.CRITICAL: Uncaught PHP Exception Twig\Error\RuntimeError: "An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT k0_.id AS id_0, k0_.name AS name_1, k0_.number AS number_2, k0_.comment AS comment_3, k0_.visible AS visible_4, k0_.company AS company_5, k0_.vat_id AS vat_id_6, k0_.contact AS contact_7, k0_.address AS address_8, k0_.country AS country_9, k0_.currency AS currency_10, k0_.phone AS phone_11, k0_.fax AS fax_12, k0_.mobile AS mobile_13, k0_.email AS email_14, k0_.homepage AS homepage_15, k0_.timezone AS timezone_16, k0_.color AS color_17, k0_.budget AS budget_18, k0_.time_budget AS time_budget_19 FROM kimai2_customers k0_ WHERE k0_.visible = ? GROUP BY k0_.id, k0_.name ORDER BY k0_.name ASC LIMIT 50' with params [true]:  SQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY")." at /var/www/kimai2/templates/customer/index.html.twig line 51 {"exception":"[object] (Twig\\Error\\RuntimeError(code: 0): An exception has been thrown during the rendering of a template (\"An exception occurred while executing 'SELECT k0_.id AS id_0, k0_.name AS name_1, k0_.number AS number_2, k0_.comment AS comment_3, k0_.visible AS visible_4, k0_.company AS company_5, k0_.vat_id AS vat_id_6, k0_.contact AS contact_7, k0_.address AS address_8, k0_.country AS country_9, k0_.currency AS currency_10, k0_.phone AS phone_11, k0_.fax AS fax_12, k0_.mobile AS mobile_13, k0_.email AS email_14, k0_.homepage AS homepage_15, k0_.timezone AS timezone_16, k0_.color AS color_17, k0_.budget AS budget_18, k0_.time_budget AS time_budget_19 FROM kimai2_customers k0_ WHERE k0_.visible = ? GROUP BY k0_.id, k0_.name ORDER BY k0_.name ASC LIMIT 50' with params [true]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY\"). at /var/www/kimai2/templates/customer/index.html.twig:51, Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'SELECT k0_.id AS id_0, k0_.name AS name_1, k0_.number AS number_2, k0_.comment AS comment_3, k0_.visible AS visible_4, k0_.company AS company_5, k0_.vat_id AS vat_id_6, k0_.contact AS contact_7, k0_.address AS address_8, k0_.country AS country_9, k0_.currency AS currency_10, k0_.phone AS phone_11, k0_.fax AS fax_12, k0_.mobile AS mobile_13, k0_.email AS email_14, k0_.homepage AS homepage_15, k0_.timezone AS timezone_16, k0_.color AS color_17, k0_.budget AS budget_18, k0_.time_budget AS time_budget_19 FROM kimai2_customers k0_ WHERE k0_.visible = ? GROUP BY k0_.id, k0_.name ORDER BY k0_.name ASC LIMIT 50' with params [true]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY at /var/www/kimai2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106, Doctrine\\DBAL\\Driver\\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY at /var/www/kimai2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:123, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY at /var/www/kimai2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121)"} []

To increase readability, I copied the executed query and the exception from the logfile:

Query

 SELECT k0_.id          AS id_0,
       k0_.name        AS name_1,
       k0_.number      AS number_2,
       k0_.comment     AS comment_3,
       k0_.visible     AS visible_4,
       k0_.company     AS company_5,
       k0_.vat_id      AS vat_id_6,
       k0_.contact     AS contact_7,
       k0_.address     AS address_8,
       k0_.country     AS country_9,
       k0_.currency    AS currency_10,
       k0_.phone       AS phone_11,
       k0_.fax         AS fax_12,
       k0_.mobile      AS mobile_13,
       k0_.email       AS email_14,
       k0_.homepage    AS homepage_15,
       k0_.timezone    AS timezone_16,
       k0_.color       AS color_17,
       k0_.budget      AS budget_18,
       k0_.time_budget AS time_budget_19
FROM   kimai2_customers k0_
WHERE  k0_.visible = ?
GROUP  BY k0_.id,
          k0_.name
ORDER  BY k0_.name ASC
LIMIT  50  

Exception

PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 'database_name.k0_.number' isn't in GROUP BY at /var/www/kimai2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:121

Additional context
Add any other context about the problem here.

• Kimai version: 1.9
• PHP version: 7.4.6
• MariaDB version: 10.4.13

[kevinpapst]

This query is not intentional, thanks for mentioning!

You could work around this by executing:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But please read about its consequences before.

[kevinpapst]

Ok, activated ONLY_FULL_GROUP_BY on my dev environment and found the error for 4 queries.
Will be fixed in master soon.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.