Skip to main content
W
wspadola12-Amethyst
12-Amethyst
May 10, 2018
Question

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

  • May 10, 2018
  • 2 replies
  • 3775 views

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?

 

2 replies

S
supandey5-Regular Member
5-Regular Member
May 11, 2018

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?

S
supandey5-Regular Member
5-Regular Member
May 11, 2018

@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.

 

 

W
wspadola12-AmethystAuthor
12-Amethyst
May 22, 2018

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.

 

Terms & ConditionsCookie settingsAccessibility statement