Setting up a database connection to a CentOS server remoted

One Star

Setting up a database connection to a CentOS server remoted

Hello Friends,
I am new to this and I have been looking for a tool that will help me with data integration.
I have found this WONDERFUL TOOL that I am willing to work with and understand it at the top and make it my personal DB tool Smiley Happy
I want to understand it better and I have a project that I want to be able to accomplish with this tool.
The idea is to be able to take tables from one server - IP 10.0.0.214 and Store then (Upgrade DB) on a newer server 10.0.0.100
I am working from Windows Desktop and I am willing to work with TWO CentOS Linux Servers.
I am trying to connect to the Server using root and password - I am getting error since it changes the root to root@10.0.0.197 and not the one I am using on the Remote server.
I can NOT use localhost since it will add the same as well.
I have tried using the Server IP and I am getting errors all the time.
So I have created a user on the remote Linux server and called it arye@10.0.0.197 and gave it all the privileges using PhpMyAdmin.
When I try to connect I am getting Errors that user can NOT connect with Or without password - Every time when I check the connection I am getting Errors Smiley Sad
Can you please let me know why? and How do I connect to a REMOTE LINUX server to have a job that will update data from Old server to new server.
I really think that this would be the tool that I will work with from now on and I have seen tutorials and realized that this is the tool for me Smiley Happy - Also, In all of the tutorials I have seen that CONNECTION were created with root and localhost - Assuming that th MySQL server is resident on the same PC where the Talend solution is installed.
I would be happy to make two connections to the databases on both of my servers and upgrade from Old to new with one job - that would be great!!!!
Please assist me with HOW to make that happened.
Thank you so much in advance.
Arye 
One Star

Re: Setting up a database connection to a CentOS server remoted

Trying with user from my local PC arye@10.0.0.197 and I am getting Errors:
Connection failure. You must change the Database Settings.
java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'arye@10.0.0.197'@'10.0.0.197' (using password: YES)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:164)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1078)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:313)
at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:272)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:2513)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:1)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Access denied for user 'arye@10.0.0.197'@'10.0.0.197' (using password: YES)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)
at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:155)
... 9 more

Please assist.
Thanks
Moderator

Re: Setting up a database connection to a CentOS server remoted

Hi arye,
Thanks for posting your issue here.
Do you want to set up DB connection between 2 servers?
Have you already checked online document about:TalendHelpCenterSmiley Frustratedetting up a database connection?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Setting up a database connection to a CentOS server remoted

Thanks Sabrina,
I have done that already and still getting errors:
I have used IP 10.0.0.100 and user root with the server password and I am getting the following Error
Connection failure. You must change the Database Settings.
java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'root'@'10.0.0.197' (using password: YES)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:164)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1078)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:313)
at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:272)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:2513)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:1)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Access denied for user 'root'@'10.0.0.197' (using password: YES)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)
at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:155)
... 9 more

As you can see
Caused by: java.sql.SQLException: Access denied for user 'root'@'10.0.0.197' (using password: YES)
I want to login with root privileges of the Server that I am trying to login remotely and not my PC.

The IP user is My PC IP and not the Linux Server IP - Why?

When I tried using arye@10.0.0.97 I got
Caused by: java.sql.SQLException: Access denied for user 'arye@10.0.0.197'@'10.0.0.197' (using password: YES)
It gave me @10.0.0.197 TWICE.

The idea is to be able to connect to a database on Linux server 10.0.0.100 using its own root admin privileges from a remote computer using IP 10.0.0.X (in my case X=197) and that to be done without any difficulties.
Please assist
Thank You.
Arye
One Star

Re: Setting up a database connection to a CentOS server remoted

Here is a screen of what I am trying to do:
Moderator

Re: Setting up a database connection to a CentOS server remoted

Hi,
Can you ping your server successfully with 10.0.0.197? If it is your local machine, have you tried to use "localhost"?
The error message indicates your DB parameter is not correct. Could you please check it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Setting up a database connection to a CentOS server remoted

Thank you Sabrina! Smiley Happy
Yes I can Ping it Successfully
Pinging 10.0.0.100 with 32 bytes of data:
Reply from 10.0.0.100: bytes=32 time=1ms TTL=64
Reply from 10.0.0.100: bytes=32 time<1ms TTL=64
Reply from 10.0.0.100: bytes=32 time<1ms TTL=64
Reply from 10.0.0.100: bytes=32 time<1ms TTL=64
Ping statistics for 10.0.0.100:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 1ms, Average = 0ms

No problem accessing 10.0.0.100 from 10.0.0.197.
When using localhost it will be like I am trying to connect MY PC (10.0.0.197) and Not a remote PC.
I have made the change just in case you will be able to see something in the result I got:
Connection failure. You must change the Database Settings.
java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:164)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1078)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:313)
at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:272)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:2513)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$30.runWithCancel(DatabaseForm.java:1)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:356)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2502)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.GeneratedConstructorAccessor26.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344)
at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:155)
... 9 more
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:315)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:306)
... 22 more

I do not have that database on my PC and I want to connect a remote Linux PC - that is whay I think setting "localhost" will not serve the propose.
Please assist,
Thank You.
One Star

Re: Setting up a database connection to a CentOS server remoted

Hello, Do anyone have an answer for my issue?
I was hopping that I have found my SQL solution and I was also thinking on purchasing it as well. 
I need to be able to connect to my servers first and that I can NOT do.
Please assist so i will be able to investigate it further more.
Thanks for your professional support.
Moderator

Re: Setting up a database connection to a CentOS server remoted

Hi,
We cannot repro your issue on our side. It is OK with us to set up a MySql database connecting to a remote server.
What's the build version you are using?
Have you already checked firewall or timeout parameter? Are you using proxy enabled network?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Setting up a database connection to a CentOS server remoted

Thanks Sabrina - But the issue is not Firewall since I have put out iptables at all.
The issue is with the Syntax that is auto generated by the software.
It create this:
root'@'10.0.0.197
And I want it to connect using root'@'10.0.0.100
That is automatically added to the connection string when I try to connect using ONLY root
As you can see here:
Access denied for user 'arye@10.0.0.197'@'10.0.0.197' (using password: YES)
It add it twice: root@10.0.0.197'@'10.0.0.197
Why?
I am using TOS version 5.6

Thanks
One Star

Re: Setting up a database connection to a CentOS server remoted

Exact info and build:
Version: 5.6.2
Build id: V5.6.2_20150508_1414
Moderator

Re: Setting up a database connection to a CentOS server remoted

Hi,
Does this issue repro on your other studios? We cannot get this problem from your previous screenshot.
Could you please take a testing on other Talend Studio to see if this issue repro? Is everything OK with you after restarting this studio?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Setting up a database connection to a CentOS server remoted

Thanks Sabrina,
Do you want me to try install an earlier version of Studio and check?
Is not this version 
Version: 5.6.2
Build id: V5.6.2_20150508_1414

Stable?
Yeas I have restarted the SW and the PC an it stay the same.
Thanks