Troubleshooting of MySQL disconnection by MySQLdb and torndb modules in Python

This article mainly introduces how the MySQLdb and torndb modules in Python handle MySQL disconnection issues. torndb is more concise to use, for more information about how to use python to refine the wordpress tag code, see the error when calling the MySQLdb module, after checking the code for a long time, no problem was found in the code. Later, I asked the master, and I was told that MySQLdb had a broken connection hole and the database had to be reconnected.

I. Error code and prompt

The error code is as follows:

import MySQLdbdef getTerm(db,tag):    cursor = db.cursor()    query = "SELECT term_id FROM wp_terms where name=%s "    count = cursor.execute(query,tag)    rows = cursor.fetchall()    db.commit()    #db.close()    if count:        term_id = [int(rows[id][0]) for id in range(count)]        return term_id    else:return Nonedef addTerm(db,tag):    cursor = db.cursor()    query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"    data = (tag,tag)    cursor.execute(query,data)    db.commit()    term_id = cursor.lastrowid    sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "    value = (term_id,tag)    cursor.execute(sql,value)    db.commit()    db.close()    return int(term_id)dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']tagids = []for tag in tags:    termid = getTerm(dbconn,tag)    if termid:        print tag, 'tag id is ',termid        tagids.extend(termid)    else:        termid = addTerm(dbconn,tag)        print 'add tag',tag,'id is ' ,termid        tagids.append(termid)print 'tag id is ',tagids

It can be executed directly. when the getTerm function is called for the second time in the for loop, the following error is returned:

Traceback (most recent call last): File "a.py", line 40, in 
 
    termid = getTerm(dbconn,tag) File "a.py", line 11, in getTerm  count = cursor.execute(query,tag) File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 154, in execute  charset = db.character_set_name()_mysql_exceptions.InterfaceError: (0, '')
 

II. Solution

Initially, I thought it was an encoding problem. I checked it several times and found no encoding problem. I did not find any exception in the python code. After asking the master, the master gave a prompt:

It only depends on the code usage. the timeout time of mysql is adjusted for a long time or an exception is caught from the Slave node. The reason is:
Cursor. connection is not closed
However, the socket has been disconnected.
Cursor does not create another socket
Execute MysqlDB. connect () again ()
I was a little confused. I first checked all timeout-related variables from mysql.

mysql> show GLOBAL VARIABLES like "%timeout%";

+----------------------------+-------+| Variable_name       | Value |+----------------------------+-------+| connect_timeout      | 10  || delayed_insert_timeout   | 300  || innodb_lock_wait_timeout  | 50  || innodb_rollback_on_timeout | OFF  || interactive_timeout    | 28800 || net_read_timeout      | 30  || net_write_timeout     | 60  || slave_net_timeout     | 3600 || table_lock_wait_timeout  | 50  || wait_timeout        | 28800 |+----------------------------+-------+10 rows in set (0.00 sec)

It is found that the minimum timeout time is 10 s, and the execution of my program is obviously not 10 s. Because I have checked the related error, it is estimated that this is probably another error: 2006, MySQL server has gone away. Now it should be okay with the time-out period. then, try to test through MySQLdb ping. if an exception is caught, reconnect again. The modified code is as follows:

#!/usr/bin/python#coding=utf-8import MySQLdbdef getTerm(db,tag): cursor = db.cursor() query = "SELECT term_id FROM wp_terms where name=%s " count = cursor.execute(query,tag) rows = cursor.fetchall() db.commit() #db.close() if count: term_id = [int(rows[id][0]) for id in range(count)] print term_id return term_id else:return Nonedef addTerm(db,tag): cursor = db.cursor() query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)" data = (tag,tag) cursor.execute(query,data) db.commit() term_id = cursor.lastrowid sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) " value = (term_id,tag) cursor.execute(sql,value) db.commit() db.close() return int(term_id)dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']if __name__ == "__main__": tagids = [] for tag in tags: try:   dbconn.ping() except:  print 'mysql connect have been close'   dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8') termid = getTerm(dbconn,tag) if termid:  print tag, 'tag id is ',termid  tagids.extend(termid) else:  termid = addTerm(dbconn,tag)  print 'add tag',tag,'id is ' ,termid  tagids.append(termid) print 'All tags id is ',tagids

The execution result shows that it is OK, and the result shows that the 'MySQL connect have been close' is printed every 1-2 getTerm or addTerm function calls '.

III. use torndb module to solve mysql disconnection
1. sample code comparison between MySQLdb and torndb
Torndb is an open-source mysql module based on MySQLdb. The new module is small and is a py file with only 2 hundred lines of code. Although the code is short, the function is indeed much easier than MySQLdb, and The reconnect method and max_idel_time parameter are added in this module to solve the problem of mysql disconnection. Compare the code using native MySQLdb module and torndb module:
Code for using the MySQLdb module

import MySQLdbdef getTerm(db,tag):    cursor = db.cursor()    query = "SELECT term_id FROM wp_terms where name=%s "    count = cursor.execute(query,tag)    rows = cursor.fetchall()    db.commit()    #db.close()    if count:        term_id = [int(rows[id][0]) for id in range(count)]        return term_id    else:return Nonedef addTerm(db,tag):    cursor = db.cursor()    query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"    data = (tag,tag)    cursor.execute(query,data)    db.commit()    term_id = cursor.lastrowid    sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "    value = (term_id,tag)    cursor.execute(sql,value)    db.commit()    db.close()    return int(term_id)def addCTag(db,data):    cursor = db.cursor()    query = '''INSERT INTO `wp_term_relationships` (      `object_id` ,      `term_taxonomy_id`      )      VALUES (      %s, %s) '''    cursor.executemany(query,data)    db.commit()    db.close()dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']tagids = []for tag in tags:    if termid:        try:         dbconn.ping()        except:         dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')         print tag, 'tag id is ',termid        termid = getTerm(dbconn,tag)        tagids.extend(termid)    else:        try:         dbconn.ping()        except:         dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')        termid = addTerm(dbconn,tag)        print 'add tag',tag,'id is ' ,termid        tagids.append(termid)print 'tag id is ',tagidspostid = '35'tagids = list(set(tagids))ctagdata = []for tagid in tagids:  ctagdata.append((postid,tagid))try:  dbconn.ping()except:  dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8')  addCTag(dbconn,ctagdata)

Torndb code

#!/usr/bin/python#coding=utf-8import torndbdef getTerm(db,tag):    query = "SELECT term_id FROM wp_terms where name=%s "    rows = db.query(query,tag)    termid = []    for row in rows:      termid.extend(row.values())    return termiddef addTerm(db,tag):    query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)"    term_id = db.execute_lastrowid(query,tag,tag)    sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) "    db.execute(sql,term_id,tag)    return term_iddef addCTag(db,data):    query = "INSERT INTO wp_term_relationships (object_id,term_taxonomy_id) VALUES (%s, %s) "    db.executemany(query,data)dbconn = torndb.Connection('localhost:3306','361way',user='root',password='123456')tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java']tagids = []for tag in tags:  termid = getTerm(dbconn,tag)  if termid:    print tag, 'tag id is ',termid    tagids.extend(termid)  else:    termid = addTerm(dbconn,tag)    print 'add tag',tag,'id is ' ,termid    tagids.append(termid)print 'All tags id is ',tagidspostid = '35'tagids = list(set(tagids))ctagdata = []for tagid in tagids:  ctagdata.append((postid,tagid))addCTag(dbconn,ctagdata)

From the code of the two, the torndb module and the native module can be omitted as follows:

The torndb module does not need db. cursor for processing, but does not need db. comment for submission. torndb is automatically submitted;

Torndb does not need to perform db. ping () to determine whether the database socket connection is disconnected during each call, because torndb adds the reconnect method and supports automatic reconnection.

2. torndb method

Torndb provides the following parameters and methods:

Execute does not need to return values to execute statements.
Execute_lastrowid: obtains the table id after execution. it is generally used to obtain the returned value after insertion.
Executeplugin can be used to perform batch inserts. The returned value is the table id of the first request.
Executemany_rowcount is executed in batches. The returned value is the table id of the first request.
After the get command is executed, a row of data is obtained and dict is returned.
Iter returns the fields and data of the iteration after the query is executed.
After the query is executed, multiple rows of data are obtained and a List is returned.
Close
Max_idle_time maximum connection time
Reconnect is closed before connection
Example:

mysql> CREATE TABLE `ceshi` (`id` int(1) NULL AUTO_INCREMENT ,`num` int(1) NULL ,PRIMARY KEY (`id`));

>>> Import torndb >>> db = torndb. connection ("127.0.0.1", "database name", "username", "password", 24*3600) #24*3600 is the time-out period >>> get_id1 = db.exe cute_lastrowid ("insert ceshi (num) values ('1')") >>> print get_id11 >>> args1 = [('2'), ('3'), ('4')] >>> get1 = db.exe cute.pdf ("insert ceshi (num) values (% s) ", args1) >>> print get12 >>> rows = db. iter ("select * from ceshi") >>> for I in rows :... Print I

3. Error

Possible errors during use:

 File "/home/361way/database.py", line 145, in execute_lastrowid  self._execute(cursor, query, parameters) File "/home/361way/database.py", line 207, in _execute  return cursor.execute(query, parameters) File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 159, in execute  query = query % db.literal(args)TypeError: not enough arguments for format string

When I wrote the code above, I tried to reference data using the MySQLdb module at the beginning. The result showed a Parameter error. after checking the code, I found that, torndb is much simpler than MySQLdb when using several SQL methods. The parameter passing method for each method is as follows (note the number of parameters ):

close()reconnect()iter(query, *parameters, **kwparameters)query(query, *parameters, **kwparameters)get(query, *parameters, **kwparameters)execute(query, *parameters, **kwparameters)execute_lastrowid(query, *parameters, **kwparameters)execute_rowcount(query, *parameters, **kwparameters)executemany(query, parameters)executemany_lastrowid(query, parameters)executemany_rowcount(query, parameters)update(query, *parameters, **kwparameters)updatemany(query, parameters)insert(query, *parameters, **kwparameters)insertmany(query, parameters)