Users are unable to create MySQL data source in Information Designer.

Users are unable to create MySQL data source in Information Designer.

This article talks about issues in creating a data source in the Information Designer for MySQL server (i.e. a JDBC connection).

Symptoms of Issue:

The following error occurs: Cannot connect to data source 'My Data source - mysql1', due to invalid configuration or that the data source is not running.

Database reported: The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server OR Access denied for user 'XXXX'@'XX.XX.XX.XXX' (using password: YES)

Cause:

There may be several reasons including firewall, network connectivity, or the user used for creating the connection is not defined correctly in MySQL database.

Resolution

Ensure the MySQL server and the port the MySQL database is running on are accessible from the TIBCO Spotfire Server. Run the following script (updated with the required username, IP address and password as needed) and then try to create the data source with the newly created user or update the privileges of the existing user.

CREATE USER 'username'@'Ip address of Spotfireserver' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'Ip address of Spotfireserver' IDENTIFIED BY 'password' WITH GRANT OPTION; OR CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;