Error when connecting with TIBCO Spotfire Connector for Oracle MySQL due to unknown error (incompatible sql_mode)

Error when connecting with TIBCO Spotfire Connector for Oracle MySQL due to unknown error (incompatible sql_mode)

When adding a data table via the TIBCO Spotfire Connector for Oracle MySQL (Add Data Tables > Add > Connection To > Oracle MySQL), the connection fails with the following error 'Could not connect due to unknown error'

In the TIBCO Spotfire installed client logs, the following error is seen

MySqlException (0x80004005): SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This error is caused by an unsupported SQL mode set in the my.ini In MySQL 5.7 and above SQL mode is switched on by default. Add the following setting in my.ini:

sql-mode=" "

The my.ini can be located by checking the properties here:

1. In Windows Services, right click on Mysql57 and select Properties
2. Link under 'Path to executable' : "XXXX" --defaults-file= "Path-to-my.ini"

Navigate to that path and edit my.ini to add the specified setting. Save the file and restart MySQL.