Python MySql query silently failing
Question: I've got a MySql database (running using the stock Docker image) and it
contains a table defined like this:

    
    
    CREATE TABLE `Edits` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `context` varchar(255) NOT NULL,
      `field` varchar(255) NOT NULL,
      `value` text,
      `username` varchar(255) DEFAULT NULL,
      `requested` datetime DEFAULT NULL,
      PRIMARY KEY (`id`,`context`,`field`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
    

I'm connecting to it in a WSGI application, and my connection code looks like
this:

    
    
    import contextlib
    import MySQLdb
    
    mysql = MySQLdb.connect(host = 'host', db = 'db')
    
    def _cursor():
      mysql.ping(True)
      return contextlib.closing(mysql.cursor())
    
    def _exec(sql, params = None):
      with _cursor() as c:
        c.execute(sql, params)
    
    def save_edits(id, context, field, value, username):
      return _exec('REPLACE INTO Edits SET id = %(id)s, context = %(context)s, 
        field = %(field)s, value = %(value)s, username = %(username)s, 
        requested = UTC_TIMESTAMP()', {
          'id': id,
          'context': context,
          'field': field,
          'value': value,
          'username': username,
      })
    

When I call the `save_edits` function, it doesn't throw an exception, but it
fails to update the database. Furthermore, attempting to run the query
`REPLACE INTO Edits SET id = 1, context = 'x', field = 'y', value = 'z',
username = 'foo', requested = UTC_TIMESTAMP();` through a `mysql` shell
afterwards fails with an `ERROR 1205 (HY000): Lock wait timeout exceeded; try
restarting transaction` error. If I wait about 5 minutes, however, that error
goes away and I can run the `REPLACE INTO` query against the MySql database
again.

What's going on? How can I fix this error?


Answer: It turns out that `autocommit` is `false` by default, and closing the cursor
without closing the connection will keep the transaction open. I changed the
method to:

    
    
    def _exec(sql, params = None):
      with _cursor() as c:
        c.execute(sql, params)
        mysql.commit()
    

And that fixed the problem.

