MySQL

MySQL, MariaDB: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Leave a comment

Hi,

I use a powershell script to write some data to a MySQL Database table.

The time the script is running increases every day and the point is reached were it runs in an timeout 🙁

To increase the “connect timeout”:

  • Add the timeout=3600 directive to the connection string
    for example:  server=MySQLHost;port=3306;uid=MySQLUserName;pwd=mySecurePW;database=my_DB;connect timeout=3600
  • Increase the net_write_timeout and net_read_timeout Parameter by set it in your connection as soon as the connection is established
    @@global.net_write_timeout;
    @@global.net_read_timeout;
    Check current values

    1
    2
    3
    4
    5
    6
    7
    mysql> select @@global.net_write_timeout,@@global.net_read_timeout;
    +----------------------------+---------------------------+
    | @@global.net_write_timeout | @@global.net_read_timeout |
    +----------------------------+---------------------------+
    |                         60 |                        30 |
    +----------------------------+---------------------------+
    1 row in set (0.00 sec)

    Set adjusted values

    1
    2
    3
    4
    set @@global.net_write_timeout = 3600;
    Query OK, 0 rows affected (0.00 sec)
    set @@global.net_read_timeout = 3600;
    Query OK, 0 rows affected (0.00 sec)
  • Or set it as default in your MySQL Database instance. Add it to the mysqld section of the my.ini.

    1
    2
    3
    4
    5
    6
    [mysqld]
    ...
    # .Net Timeout
    net_write_timeout = 3600
    net_read_timeout = 3600
    ...
  • Increase command timeout. Here a Powershell example

    1
    2
    3
    4
    5
    $oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
    $oMYSQLCommand.Connection=$oConn
    $oMYSQLCommand.CommandText='`myStoredProcedure`'
    $oMYSQLCommand.CommandType = [System.Data.CommandType]::StoredProcedure
    $oMYSQLCommand.CommandTimeout=7200

Michael

Leave a Reply