Rishi Ajmera Rishi Ajmera - 2 years ago 325
MySQL Question

MySQL server has gone away python MySQLdb

In my python script, I've subscribed to a web socket. Whenever the data is received, I'm inserting this data into MySQL db. Every second there are about 100-200 queries. The problem is it works for some time, and then it gives the error "error 2006: MySQL server has gone away"
I've increased Max_allowed_packets up to 512M. but it didn't work.

Here's my code.

def db_entry(threadName, _data):
_time = time.strftime('%Y-%m-%d %H:%M:%S')


#print ("starting new thread...")

for data in _data:
#print (data)
sql = "INSERT INTO %s (Script_Name, Lot_Size, Date, Time, Last_Price, Price_Change, Open,High, Low, Close, Volume, Buy_Quantity, Sell_Quantity) VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" %("_" + str(data['instrument_token']), data['instrument_token'], 1, datetime.datetime.today().strftime("%Y-%m-%d"), _time, data['last_price'], data['change'], data['ohlc']['open'], data['ohlc']['high'], data['ohlc']['low'], data['ohlc']['close'], data['volume'], data['buy_quantity'], data['sell_quantity'])


cursor.execute(sql)
# Commit your changes in the database
db.commit()

def on_tick(tick, ws):
thread_name = "Thread" + str(thread_count + 1)
try:
_thread.start_new_thread(db_entry,(thread_name,tick, ))
except exception as e:
print (e)
raise

def on_connect(ws):
# Subscribe to a list of instrument_tokens (RELIANCE and ACC here).
ws.subscribe(instrument_token)

# Set RELIANCE to tick in `full` mode.
ws.set_mode(ws.MODE_FULL,instrument_token)

# Assign the callbacks.
kws.on_tick = on_tick
kws.on_connect = on_connect


kws.enable_reconnect(reconnect_interval=5, reconnect_tries=50)

# Infinite loop on the main thread. Nothing after this will run.
# You have to use the pre-defined callbacks to manage subscriptions.
kws.connect()


Thanks in advance. :)

Answer Source

The documentation provided by the MySQL developer docs are very clear on this point. Odds are, some of those MySQL queries are running slower than others because they're waiting for their turn to insert data. If they wait too long, MySQL will just close their connection. By default, MySQL's wait_timeout is eight hours (28800s). Has the MySQL configuration been tweaked? How much hardware is allocated to MySQL?

Generally, look at all the timeout configurations. Read them and understand them. Do not simply copy and paste all the performance tweaks bloggers like blogging about.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download