How to Avoid MySQL Connection Timeouts in Python?

How to Configure MySQL Connection Timeout for Python

A common challenge encountered when connecting to MySQL databases through Python is the default connection timeout. This can lead to errors, especially for long-running operations. In this article, we will explore how to modify the default timeout settings and mitigate such issues.

Determining the Default Timeout Settings

The default timeout settings for MySQL can vary depending on the server configuration. To check the current values, connect to the database using the MySQL command-line client (e.g., mysql). Run the following command:

SHOW VARIABLES LIKE '%timeout%';

This will display the timeout settings, including:

• connect_timeout
• delayed_insert_timeout
• innodb_lock_wait_timeout
• innodb_rollback_on_timeout
• interactive_timeout
• lock_wait_timeout
• net_read_timeout
• net_write_timeout
• slave_net_timeout
• wait_timeout

Changing the Default Timeout

To modify the timeout settings, connect to the database using the Python client (_mysql). Execute the following queries:

con.query('SET GLOBAL connect_timeout=28800')
con.query('SET GLOBAL interactive_timeout=28800')
con.query('SET GLOBAL wait_timeout=28800')

These settings control:

• connect_timeout: Time allowed for establishing a connection (8 hours here)
• interactive_timeout: Maximum inactivity period for interactive connections (8 hours)
• wait_timeout: Maximum inactivity period for all connections (8 hours)

Recommendation

For a 10-hour execution time, it is advisable to set higher timeout values (e.g., 32400 seconds or 9 hours). This prevents connection interruptions due to exceeded timeouts and ensures uninterrupted operation of your Python program.

The above is the detailed content of How to Avoid MySQL Connection Timeouts in Python?. For more information, please follow other related articles on the PHP Chinese website!