My Yii framework website was working fine until I decided to upgrade the PHP. It was 5+ version.
My current environment:
1. ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
2. mysql Ver 8.0.33-0
3. PHP version: 8.1.21
4. PHPMyAdmin: 5.1.1deb5ubuntu1
After I updated the PHP I am getting these errors
1/
[error][yii\db\Exception] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()
#15 {main}
2/
Next yii\db\Exception: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT `userDeviceLink`.* FROM `userDeviceLink` LEFT JOIN `deviceSyscodata` ON `userDeviceLink`.`deviceSerial` = `deviceSyscodata`.`serial` LEFT JOIN `deviceNames` ON `deviceSyscodata`.`type` = `deviceNames`.`type` WHERE `userId` IN (8935, 39, 42, 47, 48, 8906, 8911, 8912, 8913, 8914, 8915, 8916, 8917, 8918, 8921, 8922, 8923, 8924, 8926, 8927, 8928, 8929, 8930, 8931, 8932, 8933, 8934, 8935, 8940, 8941, 8946, 8948, 8950, 8951, 8952, 8953, 8954, 8956, 8958, 8959, 8960, 8961, 8962, 8963, 8964, 8965,...) GROUP BY `deviceSerial` ORDER BY `timestamp` DESC in /var/www/html/connect/vendor/yiisoft/yii2/db/Schema.php:678
Stack trace:
#0 /var/www/html/connect/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException()
#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()
#15 {main}
Additional Information:
Array
(
[0] => 42000
[1] => 1055
[2] => Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'connect.userDeviceLink.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
)
I understand these two errors occurs because:
If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
I needed a quick fix because the site is really big. if I fix this soemwhere another file will have the same issue. So I added this line to my /etc/mysql/my.cnf
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
This is my full my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
This is my sql folder (that confuses me a little) screenshot I am not sure .cnf file where I added is correct file or not. After I added that , and restarted the mysql, the page load took a longer time but still the page is blank.
This method can give incorrect queries sometimes also it makes the site slow. So I tried to fix the query code.
as per the stacktrace I have to update these files and lines to fix the first error:
#5 /var/www/html/connect/models/UserDeviceLink.php(393): yii\db\ActiveQuery->all()
#6 /var/www/html/connect/models/UserDeviceLink.php(557): app\models\UserDeviceLink->findAllDevicesByUser()
#7 /var/www/html/connect/controllers/PlatformController.php(767): app\models\UserDeviceLink->getDeviceDataByUser()
Line 393 is ->all())) { from this method:
public function findAllDevicesByUser($userId, $order = 'claimedAt')
{
$userId = !($userId) ? Yii::$app->user->id : $userId;
$userIds = [];
array_push($userIds, $userId);
$myIds = User::instance()->getMyUsersIds($userId);
#return $myIds;
if ($myIds) {
foreach ($myIds as $key => $value) {
array_push($userIds, $value);
}
array_unique($userIds);
}
if (!empty($devices = self::find()
->joinWith('device')
->orderBy($order . ' DESC')
->where(["userId" => $userIds])
->groupBy('deviceSerial')
->asArray()
->all())) {
return $devices;
}
return null;
}
So I updated it to
public function findAllDevicesByUser($userId, $order = 'claimedAt')
{
$userId = !($userId) ? Yii::$app->user->id : $userId;
$userIds = [];
array_push($userIds, $userId);
$myIds = User::instance()->getMyUsersIds($userId);
if ($myIds) {
foreach ($myIds as $key => $value) {
array_push($userIds, $value);
}
array_unique($userIds);
}
$devices = self::find()
->select(['deviceSerial', 'MAX(id) AS id']) // Add the non-aggregated column 'id' to the SELECT list
->joinWith('device')
->orderBy($order . ' DESC')
->where(["userId" => $userIds])
->groupBy('deviceSerial') // Group by 'deviceSerial' instead of 'id'
->asArray()
->all();
if (!empty($devices)) {
return $devices;
}
return null;
}
I think my updated code is not the correct SELECT way to do this and it does not work.
Questions:
- Is it ok now to update the .cnf file? if ok, why it is not working me after making the change?
- How do I have to update the above methods to comply with the syntax?
