Project

General

Profile

Actions

Anomalie #1667

closed

SQL error on getChartContribsAllTime() with MySQL

Added by Guillaume AGNIERAY about 1 year ago. Updated 7 months ago.

Status:
Fermé
Priority:
Normal
Category:
Core
Target version:
-
Start date:
02/23/2023
Due date:
% Done:

100%

Estimated time:
Version utilisée:

Description

MySQL has a default SQL mode enabled (only_full_group_by) which causes the error below on the charts page.
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
source:/galette/lib/Galette/IO/Charts.php@develop#L376


Détails
Type : PDOException

Code : 42000
Message : SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'galette-dev.galette_cotisations.date_enreg' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Fichier : C:\laragon\www\galette-dev\galette\vendor\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php

Ligne : 376

Trace
#0 C:\laragon\www\galette-dev\galette\vendor\laminas\laminas-db\src\Adapter\Driver\Pdo\Connection.php(376): PDO->query('SELECT date_for...')
#1 C:\laragon\www\galette-dev\galette\vendor\laminas\laminas-db\src\Adapter\Adapter.php(194): Laminas\Db\Adapter\Driver\Pdo\Connection->execute('SELECT date_for...')
#2 C:\laragon\www\galette-dev\galette\lib\Galette\Core\Db.php(788): Laminas\Db\Adapter\Adapter->query('SELECT date_for...', 'execute')
#3 C:\laragon\www\galette-dev\galette\lib\Galette\IO\Charts.php(383): Galette\Core\Db->execute(Object(Laminas\Db\Sql\Select))
#4 C:\laragon\www\galette-dev\galette\lib\Galette\IO\Charts.php(100): Galette\IO\Charts->getChartContribsAllTime() 
[...]

Related issues 1 (0 open1 closed)

Is duplicate of Galette - Anomalie #1219: Erreur MySQL quand on se rend dans le menu GraphiqueFerméJohan Cwiklinski12/07/2018

Actions
Actions #1

Updated by Johan Cwiklinski about 1 year ago

  • Is duplicate of Anomalie #1219: Erreur MySQL quand on se rend dans le menu Graphique added
Actions #2

Updated by Johan Cwiklinski about 1 year ago

I cannot test right now, but would it be possible replacing the group clause with the selected column solves the issue?

$cols['date'] = new Expression('date_format(date_enreg, \'%Y-%m\')');
$groupby = new Expression('date_format(date_enreg, \'%Y-%m\')');

I cannot remember why those are same for postgres but not for mysql.

Actions #3

Updated by Guillaume AGNIERAY about 1 year ago

Johan Cwiklinski a écrit (#note-2):

would it be possible replacing the group clause with the selected column solves the issue?

Yes it does ! :)

Actions #4

Updated by Anonymous about 1 year ago

  • Status changed from Nouveau to Résolu
  • % Done changed from 0 to 100
Actions #5

Updated by Johan Cwiklinski 7 months ago

  • Status changed from Résolu to Fermé
Actions

Also available in: Atom PDF