Python MySQL-Connector failing to connect intermittently on Windows VPS

user_4688190 0
Viewed 366

I am scraping data using a Windows VPS. I am using Python MySQL-Connector to upload this scraped data to a Linux dedicated server.

import mysql.connector

    mydb = mysql.connector.connect(
      host="...",
      user="...",
      password="...",
      database="...",
      connection_timeout=60
    )
    mycursor = mydb.cursor()

About 25% of the time this fails, or takes more than twenty seconds. When it fails I get a 10060 error. Is the problem more likely to be with my Linux server or more likely my Windows VPS? Help sincerely appreciated. Here is the error message:

2055: Lost connection to MySQL server at '[IP]:3306', system error: 10060 A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
server mysql-connector linux mysql python
2 Answers

This error is likely ralated to slow mysql database server, so you could do following:

  1. Increase your connection_timeout to higher value i.e. 600.
  2. Configure settings that are responsible for database server perormance (i.e. see this presentation - https://www.oracle.com/technetwork/community/developer-day/mysql-performance-tuning-403029.pdf) especially set mysql RAM usage server (1/2 of total system RAM - variable innodb_buffer_pool_size (set it in my.cnf))
  3. Ensure that your closes cursor after your finished to use it, call mycursor.close.
  4. Increase if possible resources (CPU, RAM, more faster disk drive) of a system with your database server.
user_119358090

Looking at the error message, I suspect this is caused by a port being blocked (the default TCP/IP port of the MySQL server is 3306).

Check if the port 3306 is busy:

netstat -tulpn | grep 3306

then try to free that port before running your app again.

user_95609860
Related