search

Alanaktion / phproject

A high performance full-featured project management system
https://www.phproject.org
GNU General Public License v3.0
384 stars 106 forks source link

Support modern SQL modes #370

Open Alanaktion opened 5 years ago

Alanaktion commented 5 years ago

MySQL 5.7 and higher set this SQL mode by default: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

We should update our queries to support these defaults. In particular, some queries don't use GROUP BY correctly under these restrictions.

Alanaktion commented 5 years ago

Example: When a user is in a group and visits /backlog under MySQL 5.7 and higher, this error is triggered:

PDOStatement: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'user_group.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
PHP message: [vendor/bcosca/fatfree-core/db/sql/mapper.php:305] DB\SQL->exec()
PHP message: [vendor/bcosca/fatfree-core/db/sql/mapper.php:345] DB\SQL\Mapper->select()
PHP message: [app/model/user.php:168] DB\SQL\Mapper->find()
PHP message: [app/view/backlog/index.html:32] Model\User->getSharedGroupUserIds()
PHP message: [app/controller.php:73] Preview->render()
PHP message: [app/controller/backlog.php:124] Controller->_render()
PHP message: [index.php:142] Base->run()

This attempts to run this query:

SELECT `id`,`user_id`,`group_id`,`manager` FROM `user_group` WHERE group_id IN (?) GROUP BY `user_id`

The ORM itself is somewhat the problem here, but we can either add id to the grouping, or bypass the default SQL_MODE (which is not ideal), both of which should work fine.