MySQL error when SELECT disk_offering_view / service_offering_view

[GabrielBrascher]

ISSUE TYPE

• Enhancement Request

COMPONENT NAME

DB

CLOUDSTACK VERSION

4.15.0.0, 4.15.1.0-RC3, 4.16

CONFIGURATION

OS / ENVIRONMENT

mysql --version
mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64)

SUMMARY

Seems to be related to MySQL only; MariaDB looks to be working fine. After some MySQL configurations to "disable" only_full_group_by then the select works.

This might be more a documentation than a cloudstack issue, but it is important to raise and track alternatives to solve/mitigate it.
This could bring problems to unaware users.

Bear in mind that the CloudStack mgmt/UI seems to be all good, even with the selects that failed; thus this is a minor issue.

STEPS TO REPRODUCE

Run a MySQL 'select' query on disk_offering_view or service_offering_view

EXPECTED RESULTS

Select offerings views in MySQL

ACTUAL RESULTS

mysql> select * from disk_offering_view limit 1;
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 * from service_offering_view limit 1;
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

Might be related to schema-41400to41500.sql#L423.

[nvazquez]

@harikrishna-patnala seems like vsphere_storage_policy may be conflicting here, any input?

[nvazquez]

@harikrishna-patnala any ideas?

[yadvr]

Ping @harikrishna-patnala @nvazquez

[nvazquez]

@harikrishna-patnala please provide your input after your investigation, thanks

[weizhouapache]

@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&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)

[harikrishna-patnala]

Thanks @weizhouapache .
I've created doc PR as a short note to change the SQL mode.
Let me know if this is fine apache/cloudstack-documentation#239

[yadvr]

Closing this as we've the doc PR now apache/cloudstack-documentation#239