When upgrading from Retain 2.6.2 to Retain 3.5.1.1, the migration seems to stall

When the migrator goes to insert into the t_message_recipients on resume, it fails to do an insert. Here was the customer's configuration:

Retain 2.6.2 140 GB memory 16 CPU cores 2.6TB storage including 15GB Exalead index MySQL 5.5.28 for Linxu with 190GB RAM - 502GB free disk 1TB db SUSE Linux Enterprise Server 11.2 Part of the problem is that it's not sending the query. The last query it does before it chokes as shown in the migrate3[nnnnnnnn].log:

Select t_recipients.email_id, t_recipients.recipientMail, t_recipients.recipientDisplay, t_recp_properties.value, t_recipients.f_type, t_recipients.recipient_id from t_recipients left join t_recp_properties on t_recipients.recipient_id = t_recp_properties.recip_id where t_recipients.email_id > 5001 and t_recipients.email_id<10001 and t_recipients.f_type != 200 and t_recipients.f_type != 100 and t_recipients.f_type != 101 and t_recipients.f_type != 102 order by t_recipients.recipient_id asc The log also showed that the query was taking 7 seconds:

2016-12-18 05:49:49,103 JDBCExceptionReporter - The last packet successfully received from the server was 42,883,107 milliseconds ago. The last packet sent successfully to the server was 42,883,107 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. 2016-12-18 05:49:49,104 DatabaseMigration - Error occurred on JDBC prepared statement org.hibernate.exception.JDBCConnectionException: could not execute query

As a workaround, execute these two lines on the mysql db, to increase the timeout:

SET GLOBAL interactive_timeout = 172800;
SET GLOBAL wait_timeout = 172800;

It isn't known whether the migraiton tool is keeping a session open that long or if it's an issue with hibernate or something else, but to increase the timeout values on the db is hopefully effective in resolving this.  It worked for this particular customer.

This article was originally published in the GWAVA knowledgebase as article ID 2905.