One Star

Timeout for logging database (MySQL)

Hello,
I have a Talend job (many other jobs work just fine) that takes just over 1/2 hour to pull data from one MySQL database to another (it has over 20 mil rows). In this job, a timeout occurs from the Logging database (set up based on Talend best practices). The start of the job is recorded. However, looks like once the data is written, the connection to the logging database is lost and the job cannot record the success. So, I get the following error message:
"Communications link failure
The last packet successfully received from the server was 2,537,661 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago. Exception in component talendStats_DB
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed."
Here are the values set on MySQL for timeouts currently:
connect_timeout 10
delayed_insert_timeout 300
innodb_lock_wait_timeout 300
innodb_rollback_on_timeout OFF
interactive_timeout 14400
lock_wait_timeout 31536000
net_read_timeout 30
net_write_timeout 60
slave_net_timeout 3600
thread_pool_idle_timeout 60
wait_timeout 14400
Is there one I can modify that causes this? I thought the 'wait_timeout' was the one that controlled this.
Thanks!
Polina
3 REPLIES
Seventeen Stars

Re: Timeout for logging database (MySQL)

No this is a problem with long running statements and MySQL. I guess you mean the AMC database. Yes unfortunately I know such problems and we have decided to switch off this feature because of such problems.
You could try to increase the inactivity timeout. I do not think it is a lock issue, it is more an issue the database closes the connection for it self because of suspected inactivity.
Moderator

Re: Timeout for logging database (MySQL)

Hi,
Could you please try to investigate the MySQL server variable "wait_timeout" and increase it to a high value, rather than the default of 8 hours, open the configuration file mysq.ini/mysql.cnv and modify the following two parameters or add the following parameters if they do not exist.
wait_timeout=31536000
interactive_timeout=31536000
Let me know if it is OK with you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Timeout for logging database (MySQL)

This is an old question but I'm facing with the same issue at the moment.
Did you make any enhancements in Talend in the meantime to make it work without increasing the wait_timeout and interactive_timeout parameters on the server? 
Is that possible to change it from Talend only for a certain session or maybe by passing an extra JDBC parameter?