weizhouapache commented on issue #5175:
URL: https://github.com/apache/cloudstack/issues/5175#issuecomment-911757856
@nvazquez
I think it is not a bug in cloudstack. cloudstack has already set sql_mode
in utils/conf/db.properties
```
db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&prepStmtCacheSqlLimit=4096&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'&serverTimezone=UTC
```
to fix the issue , users have to change the mysql setting in mysql.conf , or
change mysql global setting by queries if mysql cannot be restarted.
here is an example
```
mysql> select * from service_offering_view;
ERROR 1055 (42000): Expression #34 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'cloud.vsphere_storage_policy.value' which is
not functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode
|
+-----------------------------------------------------------------------------------------------------------------------+
|
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
|
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode
|
+-----------------------------------------------------------------------------------------------------------------------+
|
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
|
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set global
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
mysql> set
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from service_offering_view limit 1;
+----+--------------------------------------+----------------+----------------+-------------------+---------------------+------+---------+-------------------+------------+-----------------+----------+----------+---------------+-----------------+---------------------+----------------------------+------------------+----------------------+-----------------------------+----------------+--------------------+---------------------------+-----------------+---------------------+----------------------------+------------+----------------+------+-------+----------+---------+---------+------------+---------------+----------+-------------+---------+----------+-------------+--------------------+-------------------------+------------------------+-----------+-------------+-------------+-------------+---------+-----------+-----------+---------+---------+------------+------------+
| id | uuid | name | display_text
| provisioning_type | created | tags | removed | use_local_storage
| system_use | customized_iops | min_iops | max_iops | hv_ss_reserve |
bytes_read_rate | bytes_read_rate_max | bytes_read_rate_max_length |
bytes_write_rate | bytes_write_rate_max | bytes_write_rate_max_length |
iops_read_rate | iops_read_rate_max | iops_read_rate_max_length |
iops_write_rate | iops_write_rate_max | iops_write_rate_max_length | cache_mode
| root_disk_size | cpu | speed | ram_size | nw_rate | mc_rate | ha_enabled |
limit_cpu_use | host_tag | default_use | vm_type | sort_key | is_volatile |
deployment_planner | dynamic_scaling_enabled | vsphere_storage_policy |
domain_id | domain_uuid | domain_name | domain_path | zone_id | zone_uuid |
zone_name | min_cpu | max_cpu | min_memory | max_memory |
+----+--------------------------------------+----------------+----------------+-------------------+---------------------+------+---------+-------------------+------------+-----------------+----------+----------+---------------+-----------------+---------------------+----------------------------+------------------+----------------------+-----------------------------+----------------+--------------------+---------------------------+-----------------+---------------------+----------------------------+------------+----------------+------+-------+----------+---------+---------+------------+---------------+----------+-------------+---------+----------+-------------+--------------------+-------------------------+------------------------+-----------+-------------+-------------+-------------+---------+-----------+-----------+---------+---------+------------+------------+
| 1 | 5a79c934-c955-4681-b479-6ae76e29d7bb | Small Instance | Small
Instance | thin | 2021-08-23 06:26:32 | NULL | NULL |
0 | 0 | NULL | NULL | NULL | NULL |
NULL | NULL | NULL |
NULL | NULL | NULL | NULL |
NULL | NULL | NULL |
NULL | NULL | NULL | 0 | 1 | 500
| 512 | NULL | NULL | 0 | 0 | NULL |
0 | NULL | 0 | 0 | NULL |
1 | NULL | NULL | NULL | NULL | NULL
| NULL | NULL | NULL | 1 | 1 | 512 | 512
|
+----+--------------------------------------+----------------+----------------+-------------------+---------------------+------+---------+-------------------+------------+-----------------+----------+----------+---------------+-----------------+---------------------+----------------------------+------------------+----------------------+-----------------------------+----------------+--------------------+---------------------------+-----------------+---------------------+----------------------------+------------+----------------+------+-------+----------+---------+---------+------------+---------------+----------+-------------+---------+----------+-------------+--------------------+-------------------------+------------------------+-----------+-------------+-------------+-------------+---------+-----------+-----------+---------+---------+------------+------------+
1 row in set (0.01 sec)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
