Error: rlm_python:EXCEPT:<class '_mysql_exceptions.OperationalError'>: (2006, 'MySQL server has gone away')

 db = MySQLdb.connect(host="10.0.1.1",user="111",passwd="111",db="111") cursor = db.cursor() def mac_search(*args): serid=[465,641,465,641,638,637,644,645,648,709,1022,1120,1122,1124] sql = """SELECT sl2.service_id FROM ip_groups AS ig \ JOIN iptraffic_service_links AS isl ON ig.ip_group_id=isl.ip_group_id \ JOIN service_links AS sl ON isl.id=sl.id \ JOIN accounts AS ac ON sl.account_id=ac.id \ JOIN service_links AS sl2 ON ac.id=sl2.account_id \ WHERE ig.mac='%s' \ AND ig.is_deleted=0 \ AND isl.is_deleted=0 \ AND sl.is_deleted=0 \ AND ac.is_deleted=0 \ AND sl2.is_deleted=0;""" %args[0].split()[0] cursor.execute(sql) for j1 in cursor.fetchall(): if j1[0] in serid: cursor.execute("SELECT * FROM s_multicast WHERE service_id=%s AND %s>=ip_start AND %s<=ip_end;" %(j1[0],args[0].split()[1],args[0].split()[1])) return cursor.fetchone() def authorize(p): mac = "{0}:{1}:{2}:{3}:{4}:{5}".format(p[0][1][1:-1][:2],p[0][1][1:-1][2:4],p[0][1][1:-1][4:6],p[0][1][1:-1][6:8],p[0][1][1:-1][8:10],p[0][1][1:-1][10:12]).lower() ipm = "{0}".format(struct.unpack("!I", socket.inet_aton(p[8][1]))[0]) data = "{0} {1}".format(mac,ipm) start = time.time() g = mac_search(data) print "!!!Elapsed Time: %.4f" %(time.time() - start) if g: return radiusd.RLM_MODULE_OK else: accounting(p) return radiusd.RLM_MODULE_REJECT def accounting(p): if p[8][0] == 'Acct-Status-Type': sql = """INSERT INTO s_multicast_log (mac,sw_ip,port,channel,date,status) VALUES ('%s','%s',%d,'%s',UNIX_TIMESTAMP(),'2')""" %(p[0][1][1:-1],p[1][1],int(p[6][1]),p[7][1]) else: sql = """INSERT INTO s_multicast_log (mac,sw_ip,port,channel,date,status) VALUES ('%s','%s',%d,'%s',UNIX_TIMESTAMP(),'0')""" %(p[0][1][1:-1],p[2][1],int(p[7][1]),p[8][1]) try: cursor.execute(sql) db.commit() return radiusd.RLM_MODULE_OK except: db.rollback() return radiusd.RLM_MODULE_FAIL def post_auth(p): return radiusd.RLM_MODULE_NOOP

What can cause this error?

  • most likely the server is off or unavailable. - KoVadim
  • Just about, it is written that the server went for a walk: 'MySQL server has gone away' - metazet
  • I see what is written, the base is working combat. - avdoshkin
  • Not. Python does not write like that because there is no server :)) Some kind of error in the request. But since the author did not indicate which particular query falls, and what is the structure of his tables, let him guess himself. - Ali
  • one
    "Server has gone away" means that the client is trying to make requests on a closed connection. Switch the logging of everything and everything from the MySQL server (from queries to disconnect connections), and see what happens. And in general, this is a bad approach. One connection to the whole RADIUS, plus, once the network hiccupped, the connection broke and that's it. The correct architecture here is a pool of connections, etc. And more. Someone make me get it: mac = "{0}:{1}:{2}:{3}:{4}:{5}".format(p[0][1][1:-1][:2],p[0][1][1:-1][2:4],p[0][1][1:-1][4:6],p[0][1][1:-1][6:8],p[0][1][1:-1][8:10],p[0][1][1:-1][10:12]).lower() - drdaeman

1 answer 1

“Server has gone away” means that the client is trying to make requests on a closed / dead connection. Switch the logging of everything and everything from the MySQL server (from queries to disconnect connections), and see what happens.

Although, in general, everything written is a bad approach, which, it is not surprising that it falls. That is, I have a strong suspicion that everything falls from the client (FreeRADIUS) side, because of problems with threads, the benefit is that symptoms (one test client works, battle traffic dies) are characteristic. There’s not even one connection for the entire RADIUS server (for any decent load this is not enough!), But one cursor. That, in general, is not thread-safe. Two requests came in parallel and that's it. And in the documentation on MySQLdb even more is written:

The same protocol connection at once. It is not terribly hard to achieve using the standard cursor class (which uses the mysql_store_result ()), it is not complicated by the SSCursor (which uses the mysql_use_result (); with the latter It is also a process to make it possible to ensure that it can be executed. It is not quite worth it.

Do not share connections between threads. It’s really not worth the effort, since it’s my life You can see what's going on a time . The MySQL client library will probably upchuck and die . You have been warned.

For threaded applications, try using a connection pool. This can be done using the Pool module.

(Selection is mine, the point is not to separate the connections between threads. Put connections into the pool and give threads one at a time. Otherwise, the MySQL client library will choke and die. The rest is lazy, sorry.)

The correct architecture here is a pool of connections, which, first, keeps several (some fixed number, or adaptively changing the size under load - depends on the implementation) of open sessions, and on request takes a free one from the pool. Cursors create already in functions. Or to take Pool from MySQLdb If you do not want to reinvent your own bike - the same pool of connections from SQLAlchemy to help or PySQLPool , as an option.

  • Thanks for the clarification, it seems everything turned out! - avdoshkin