[resolved] load data from amazon redshift to mysql

[Anonymous]

Hi all,
I can load 1 million data from tfileinputdelimited to tmysqloutput and also I can load data from tredshiftinput to tfileoutputdelimited via tmap transformation in talend.
But, I cannot able to load data from tredshiftinput to tmysqloutput via tmap in Talend. I am getting error as No operations allowed after statement closed.
I do not know why I am getting this error.
I am using windows 8.1 64 bit OS.
Mysql -Heidisql
Please tell me how to load data??
Please give me a solution.

[Anonymous]

Hi,
What's your build version? Could you please upload your job setting screenshots into forum which will be helpful for us to address your issue.
Please take a look at a related forum: https://community.talend.com/t5/Design-and-Development/Timeout-for-logging-database-MySQL/td-p/10731...
Best regards
Sabrina

[Anonymous]

Hi,
Can you remove your tMySQL output component and connect to tLogRow and check if it is working...
What is the commit size for output DB component? try changing it to smaller chunks..
Vaibhav

[Anonymous]

Hi Sabrina,
Talend big data-5.6.
Thank you. I Have increased the parameter values in mysql. but still Its showing same error like before.But ,the thing is after 15 minutes 100 rows will load to mysql and then it will show error as follows:
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
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.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:463)
at com.mysql.jdbc.PreparedStatement.clearBatch(PreparedStatement.java:1100)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1397)
at project_1.refr_ms_test_0_1.refr_ms_test.tRedshiftInput_1Process(refr_ms_test.java:1368)
at project_1.refr_ms_test_0_1.refr_ms_test.runJobInTOS(refr_ms_test.java:1858)
at project_1.refr_ms_test_0_1.refr_ms_test.main(refr_ms_test.java:1715)
disconnected
Job refr_ms_test ended at 10:17 03/12/2014.

 
Please help me out.
Vinuta

[Anonymous]

Hi,
I removed tmysqloutputcomponent and replaced with tlogrow. But,still It is not loading.
Commit size: 10000.
Regards,
Vinuta

[Anonymous]

Can you use tPreJob-->tMySQLCOnnection flow and enable auto commit in the tMySQLCOnnection  component?
- use existing connection in tmysqloutput component 
and try again
Vaibhav

[Anonymous]

Hi,
But how to use tprejob component and what about tredshiftinput???
Vinuta

[Anonymous · Accepted solution]

tPreJob-->OnComponentOk-->tMySQLCOnnection
- enable check box for use existing connection in tmysqloutput component
your job goes here
check example here
http://www.talendbyexample.com/talend-tprejob-tpostjob-components.html

Vaibhav

[Anonymous]

Hi,
I have done what you have told. The rows loaded successfully.Thank you very much for your help. It took 27 minutes to load. Is it possible to reduce the time and make it load fast???
And one more thing if I want to load approximately 7 to 10 Million rows at time is this the same method or do we have other method??

Vinuta

[Anonymous]

Hi,
Can you please tell me what was the problem before???
Regards,
Vinuta