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

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)

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

This error can be few things

1. The hub licenses had expired and the data engine is not able to connect to the database.
2. wasp data engine address are case sensitive
3. 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).
4. 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

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