Unable to Invoke Service on MySqlThing: null using MySQL Relational Database Extension

[wspadola]

I'm trying to connect to a MySQL 5.2.39 database and am receiving an error when running my SQL (Query) service.

Unable to Invoke Service QueryMasterTableSQL on MySqlThing: null

• I have confirmed that I am able to connect using the proper credentials to the database as well as run the actual query I've created in my service manually using the MySQL Workbench.
• The connection validation string is "SELECT 1"
• Both the Thingworx and MySQL are installed on the same machine, port 3306 is open.
• I am also using the most recent download of the MySQL connector from the Thingworx Marketplace.

(For what it's worth, I have also tried MySQL 8 (with MySQL 5.x compatibility installed on another server) and also receive the same error)

I see no errors in other logs. Does anyone have any advice on what I can correct in order for Query Service to work?

[supandey]

Hi @wspadola what about the isConnected property for this database thing which you are connecting to mySQL? Is it showing as true while this service fails to invoke?

[supandey]

@wspadola Another quick thought, are you able to telnet on that port using the machine name / IP where MySQL DB is installed?

I attempted the same and then i figured MySQL doesn't allow remote connections unless the use is white listed; so after some looking around i fixed by starting MySQL CLI directly on the machine where MySQL DB is installed and added a specific user that's allowed to make remote connections :

> CREATE USER 'twadmin'@'%' IDENTIFIED BY '$omePassword873';

> GRANT ALL PRIVILEGES ON *.* TO 'twadmin'@'%' WITH GRANT OPTION; 

Though you might want to give this to exiting root user, instead of creating another user (like what's done above) with root like access and with open remote access - likely not recommended for the production environment.

[wspadola]

I'm afraid while the problem has been resolved, I have not found the root cause.

I did make sure that the credentials were correct, the firewall was open, and that the database allowed the connection, but it would continue to say "null".

If I understand correctly though, perhaps it takes a few moments to connect.  I did wait a few minutes, I've even restarted the Tomcat service.  These did not resolve the issue as it stood at the time.

During one of my debugging sessions, I uninstalled the extension, restarted Tomcat and tried to start from "square one": installing the extension "fresh".  In doing so, I must have changed some behaviour in my environment, as the extension connected successfully and started to return query resultsets as it normally should.

While I don't have access to the MySQL server I'm connecting to, I will try to hunt down potential reasons as the engagement progresses and update this thread with anything.

Thank you for your responses, @supandey.