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)

book

Article ID: KB0082446

calendar_today

Updated On:

Products Versions
Spotfire Analyst 7.9 and higher

Description

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

Issue/Introduction

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

Resolution

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.
Powered by Wolken Software