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