USM portal error Expression #2 column 'ca_uim.meci.master

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

If you open a USM Unified Service Manager and a popup errors appears with this message:

com.firehunter.ump.exceptions.DataFactoryException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ca_uim.meci.master_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Please check the log for more information.
Stack Trace:
(1) error, com.firehunter.ump.exceptions.DataFactoryException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ca_uim.meci.master_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ca_uim.meci.master_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    at com.firehunter.usm.alarms.NisDbAlarmProvider.getAlarmSummary(NisDbAlarmProvider.java:670)
    at com.firehunter.usm.alarms.NisDbAlarmProvider.getAlarmSummary(NisDbAlarmProvider.java:642)
    at com.firehunter.usm.AlarmUtils.getAlarmSummary(AlarmUtils.java:970)
    at com.firehunter.usm.DataFactory.getRoot(DataFactory.java:4227)
    at com.firehunter.usm.DataFactory.getCacheEntry(DataFactory.java:3649)
    at com.firehunter.usm.DataFactory.getGroups(DataFactory.java:3353)
    at com.firehunter.usm.DataFactory.getGroups(DataFactory.java:2937)
    at com.firehunter.usm.DataFactory.getGroups(DataFactory.java:2928)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)
    at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
    at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)
    at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)
    at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)

Environment

UIM with MySQL database

Cause

The portal don't have access to the MySQL database information.

Resolution

This error can be few things

The hub licenses had expired and the data engine is not able to connect to the database.
wasp data engine address are case sensitive
wrong address on any probe that is listed under ump_common on wasp portal probe (maintenance_mode, ems, nas, ace, automated_deploy_engine, discovery_server, mpse, sla_engine and udm_manager).
my.cnf file has the entry "ONLY_FULL_GROUP_BY"

Resolutions:

Item 1 put the new licenses on the primary hub, restart the hub, on the portal wasp open the raw configure, remove the value for connection and restart the wasp probe.

Item 2 after you configure the right data_engine address on the wasp, go to raw configure, remove the value for connection and restart the wasp probe.

Item 3 after you configure the right address for the probes (maintenance_mode, ems, nas, ace, automated_deploy_engine, discovery_server, mpse, sla_engine and udm_manager), go to raw configure, remove the value for connection and restart the wasp probe

Item 4 check the my.cnf to make sure this is not set in the defaults. You might see something like this in the my.cnf:

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

if you see "ONLY_FULL_GROUP_BY" on this list, remove it

Additional Information

https://stackoverflow.com/questions/23921117/disable-only-full-group-by

For item #4: The ONLY_FULL_GROUP_BY sql_mode may be automatically set in the mysql server even if it is not configured in the my.cnf file. You can check this by executing the following SQL query from the mysql utility:

select @@sql_mode;

If the ONLY_FULL_GROUP_BY option is set in the sql_mode variable, then to remove it, add the sql_mode key to the my.cnf file with all of the other options but do not include the ONLY_FULL_GROUP_BY option:

ex:

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Save the changes, then stop and start the mysqld service:

service mysqld stop
service mysqld start