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