topic Re: Communications Exception has occurred in Talend Studio

Communications Exception has occurred

Anonymous — Thu, 09 Aug 2012 10:02:00 GMT

I'm running a job which has a sub job. It just process 15000 of rows first into the child table. Child table's primary key is a foreign key of master table. So I have to insert data into master table after the data is available in child table.
This exception occurs when the job starts processing the master table after 1000 rows.... don't know what is that...
---------------------------------------------------------------------------------------------------------------------------------
Exception in component tMysqlInput_4
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:1118)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2308)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2122)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:774)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
at sun.reflect.GeneratedConstructorAccessor5.newInstance(Unknown Source)
disconnected
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:375)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:289)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at talentbase.dw_job_test_0_1.DW_Job_Test.tMysqlInput_4Process(DW_Job_Test.java:3495)
at talentbase.dw_job_test_0_1.DW_Job_Test.tMysqlInput_3Process(DW_Job_Test.java:2500)
at talentbase.dw_job_test_0_1.DW_Job_Test.tMysqlInput_1Process(DW_Job_Test.java:1707)
at talentbase.dw_job_test_0_1.DW_Job_Test.runJobInTOS(DW_Job_Test.java:3805)
at talentbase.dw_job_test_0_1.DW_Job_Test.main(DW_Job_Test.java:3663)
Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
at java.net.DualStackPlainSocketImpl.connect0(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 java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
... 17 more
Job DW_Job_Test ended at 10:49 09/08/2012.

Re: Communications Exception has occurred

Anonymous — Thu, 09 Aug 2012 14:22:29 GMT

please see the screenshot of job for more details...

Re: Communications Exception has occurred

janhess — Thu, 09 Aug 2012 15:41:19 GMT

It's a permissions problem. You probably don't have insert/update permission on the table and it's failing at the first commit.

Re: Communications Exception has occurred

Anonymous — Thu, 09 Aug 2012 15:50:40 GMT

but if i run the lower subjob separately, it works just fine without any problems and i can see the insertions in the table.

Re: Communications Exception has occurred

janhess — Thu, 09 Aug 2012 16:15:56 GMT

Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): connect
Missed that. I think you need to reuse your connections.

Re: Communications Exception has occurred

Anonymous — Fri, 10 Aug 2012 02:52:14 GMT

Hi
Do you create DB connection on each DB component or use an existing connection? About the load module on tMap, load once or reload each row?
Best regards
Shong

Re: Communications Exception has occurred

Anonymous — Fri, 10 Aug 2012 07:47:47 GMT

Hi,
I'm using only input components which have their own DB settings and that's it, no other connection is there.
tMap settings have "Relaod at each row", because in near future i would have to deal with millions of rows for lookup. that's why using that.
check out the screenshot

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 08:19:50 GMT

anyone there?

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 09:01:12 GMT

In your tMap use following setting and try, hopefully your requirements are straight forward and this should be fine for you.
---------
Lookup Model : Load Once
Match Model : All rows
Join Model : Inner Join
If that doesn't work, try to use tMysqlConnection for your all your table input output objects.
--
Regards,
Vinod

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 09:04:46 GMT

Thanks.
I'm using Global Variable within tMap, which is being used to make the query and cannot be available if tMap is set to Load Once

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 09:48:05 GMT

Can you please check if you are using tMysqlInput/tMysqlOutput connection instead of tMysqlConnection?
Reason being when you open connection with tMysqlInput/tMysqlOutput in that case each time that component is called the connection will be opened and will be closed later. So from your job you are looking up a table on each row that means for each main flow input row your lookup table connection will be made and your entire table will be loaded and later connection will be closed(I presume for connection close part). So I would suggest you to drag an tMysqlConnection component on the job and initialize all the connections with tMysqlConnection.
--
Regards,
Vinod

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 10:25:07 GMT

Thank you very much for the good explaination.
Have just added tMySqlConnection.. but still getting the same error message. Please check the screenshot if that's how you meant the connection should be placed?

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 10:50:49 GMT

Yes that is how I meant it to be.
Inside "dim_pageview" can you check if it is reusing that connection object or not?
If not then set "Use existing connection" for all the database components.
--
Regards,
Vinod

Re: Communications Exception has occurred

Anonymous — Mon, 13 Aug 2012 12:27:38 GMT

same thing has happened again... can i send you the job?