How to fix Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
Finding Root Cause:
When using MySQL database and .NET /Connector with a thousands of rows to do an INSERT IGNORE command results in timeout expired exception. Usually we tend to suspect on the connection time out or command timeout variable.
But in this case, the problem is with
net_read_timeout, because the command needs to read thousands of rows before to do the task, the MySQL database net_read_timeout variable default value might be less than the expected value.The best way to fix it, is to increase the
net_read_timeout (by default is only 30 seconds see mysql website) so, you allow the command sufficient seconds to read. If not, the connection will close, because net_read takes more time than the timeout settled.Before:
Solution:
You need to have administration permissions to change global variables:
- Open MySQL query browser or MySQL console
- Run this command SET GLOBAL net_read_timeout = 100;
After:
Arjun's Blog 