You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by GitBox <gi...@apache.org> on 2021/09/02 14:42:39 UTC
[GitHub] [cloudstack] weizhouapache edited a comment on issue #5175: MySQL error when SELECT disk_offering_view / service_offering_view
weizhouapache edited a comment 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 ./client/conf/db.properties.in
```
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: commits-unsubscribe@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org