Executing a job on Linux server - trouble with DB connection in tDBInput component

[Metikosh]

Hi,

I have developed a simple job on my Windows 10 laptop - it consists of three components: tDBInput, tAggregateRow and tDBOutput. In the tDBInput I am fetching data from a MariaDB table on a Linux server (the same one that I am trying to execute the job on), then performing a count with a group by and finally writing the result to a different MariaDB table on the same server.

When executing this job from my Windows laptop, I am giving the IP address of the Linux server (as host) to the tDBInput and tDBOutput components, with the username and password that I am using to connect (via RDBMS) to the MariaDB on the Linux server. When I deploy and run this job on the Linux server with the same configuration - it works on the Linux server. What I am saying is that I have successfully executed the job on the Linux server already.

The thing that bothers me is that If I configure the tDBInput and tDBOutput components with the following settings: host-localhost(the Linux server, which will be the localhost when I place and run the job on it), username-root(the user that I use to access the MariaDB server on the Linux machine), password-xxx(the password that I use to access the MariaDB server on the Linux machine) - then I get an error when executing the .sh file...The error is the following:

Exception in component tDBInput_1 (testAggregateStagingLocalhost3000000)

java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=localhost)(port=3306)(type=master) : Socket fail to connect to host:localhost, port:3306. Connection refused

    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:241)

    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1241)

    at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:610)

    at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:142)

    at org.mariadb.jdbc.Driver.connect(Driver.java:86)

    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)

    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:229)

    at local_project.testaggregatestaginglocalhost3000000_0_1.testAggregateStagingLocalhost3000000.tDBInput_1Process(testAggregateStagingLocalhost3000000.java:1504)

    at local_project.testaggregatestaginglocalhost3000000_0_1.testAggregateStagingLocalhost3000000.runJobInTOS(testAggregateStagingLocalhost3000000.java:2866)

    at local_project.testaggregatestaginglocalhost3000000_0_1.testAggregateStagingLocalhost3000000.main(testAggregateStagingLocalhost3000000.java:2707)

Caused by: java.sql.SQLNonTransientConnectionException: Socket fail to connect to host:localhost, port:3306. Connection refused

    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:241)

    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.connException(ExceptionMapper.java:87)

    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createSocket(AbstractConnectProtocol.java:218)

    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:474)

    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1236)

    ... 8 more

Caused by: java.net.ConnectException: Connection refused

    at java.base/sun.nio.ch.Net.pollConnect(Native Method)

    at java.base/sun.nio.ch.Net.pollConnectNow(Net.java:672)

    at java.base/sun.nio.ch.NioSocketImpl.timedFinishConnect(NioSocketImpl.java:542)

    at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:597)

    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327)

    at java.base/java.net.Socket.connect(Socket.java:633)

    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createSocket(AbstractConnectProtocol.java:213)

    ... 10 more

So why does it work if I give the IP address and it does not work for the localhost configuration?

Thanks!

[Anonymous]

This is your configuration of the user on MariaDB. The same thing occurs with MySQL as well. This should help you out.....

https://mariadb.com/kb/en/troubleshooting-connection-issues/

You are looking for this text....

"Authentication is granted to a particular username/host combination. 

user1'@'localhost'

, for example, is not the same as 

user1'@'166.78.144.191'

. See the GRANT article for details on granting permissions."

It looks like your user has been granted access from your Windows machine, but not from localhost. This can only be fixed within the DB.

[Metikosh]

Thank you for the answer rhall.

I am actually using a different user/host combination for the different component settings.

When using the localhost settings (in the tDBInput and tDBOutput components) I am using the root user (of the Linux machine) credentials (user: root, host: localhost), otherwise (when giving the IP address - either when executing on the Windows or the Linux machine) I am using a different user (user: exampleuser, host: 166.78.144.191) which was created so I can access the DB remotely (this configuration works when I execute it on the Linux machine). The root user also has access to the MariaDB server and its databases - I can access them manually.

So I would expect that the root/localhost username/host combination should work.

[Metikosh]

To clarify:

This setting works when executing on Windows and on Linux.

And I expect this setting to work when executing the job on the Linux server only.

[Anonymous · Accepted solution]

As I said, this is about your user permissions configured on MariaDB. You assign permissions not just to the user, but to the location they log in from as well. It is the same with MySQL. You need to speak to whoever is the admin for your DB and explain this issue to them. Maybe show them the page I linked to. What they need to do is grant permissions to your user to access the DB from "localhost".

You could also try accessing from 127.0.0.1 instead of localhost. That may help you. It all depends on your DB configuration.

I guarantee that this isn't an issue with Talend or how your job is configured (since you have managed to get access from another machine). This is almost certainly down to the DB config.