One Star eli
One Star

Communications Exception has occurred

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.
13 REPLIES
One Star eli
One Star

Re: Communications Exception has occurred

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

Re: Communications Exception has occurred

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

Re: Communications Exception has occurred

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

Re: Communications Exception has occurred

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

Re: Communications Exception has occurred

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star eli
One Star

Re: Communications Exception has occurred

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
One Star eli
One Star

Re: Communications Exception has occurred

anyone there?
One Star

Re: Communications Exception has occurred

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
One Star eli
One Star

Re: Communications Exception has occurred

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
One Star

Re: Communications Exception has occurred

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
One Star eli
One Star

Re: Communications Exception has occurred

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?
One Star

Re: Communications Exception has occurred

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
One Star eli
One Star

Re: Communications Exception has occurred

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