CODEWITHSUNDEEP
pythonmysqltornado
AivanF.
AivanF.

Reputation: 1226

Python, Tornado, TorMySQL

I have an old, large project based in Python 2.7 with Tornado framework. To work with MySQL, it initially used Tornado-MySQL with raw SQL queries, and it worked well, but now it must use MySQL 8, and that library is obsolete, unmaintained.

So, now I set TorMySQL library – it connects well to MySQL Server 8, but I don't fully understand how to use it, and this leads so bugs.

In one project's file I wrote this code to access databases:

from tornado import gen
from tornado.gen import Return
from tornado.ioloop import IOLoop
import tormysql
import settings

POOL = tormysql.ConnectionPool(
    max_connections = 20,
    idle_seconds = 7200, #timeout time, 0 is not timeout
    wait_connection_timeout = 3,
    host='127.0.0.1',
    port=3306,
    user=settings.MYSQL_USER,
    passwd=settings.MYSQL_PASSWORD,
    db='aivanf',
    use_unicode=True,
    charset='utf8mb4')

@gen.coroutine
def executePool(query, params):
    with (yield POOL.Connection()) as conn:
        with conn.cursor() as cursor:
            try:
                yield cursor.execute(query, params)
            except Exception, ex:
                print('Exception!\n{}'.format(ex))
                yield conn.rollback()
                raise Return(None)
            else:
                first = query[:10].lower()
                if 'update' in first or 'insert' in first:
                    yield conn.commit()
                if 'select' in first:
                    raise Return(cursor.fetchall())
                else:
                    raise Return(None)

I use if's because this single function is called with different types of queries. I know, it's ugly, but works fine. Similar, but even simpler code for Tornado-MySQL worked completely perfect, but with MySQL 5.7 only.

However, some UPDATE / INSERT queries seem to be skipped, and I get these messages:

(1213, u'Deadlock found when trying to get lock; try restarting transaction')

WARNING:root:Connection maybe not release, used time 25.32s {'port': 3306, 'host': '127.0.0.1', 'user': '...', 'database': '...'} <3,2>.

Also, sometimes different clients of the server see different versions of data – like if they had different connections with own uncommitted data.

How to solve the problem?

I suppose that the problem about the pool – maybe I have to close / recreate it? The TorMySQL page has also this line: yield pool.close()

Upvotes: 1

Views: 759

Answers (1)

Pierce
Pierce

Reputation: 526

You probably have to conn.commit() even after a SELECT query - otherwise a run of SELECT queries are done within the same transaction as the first.

I think most users are accustomed to "autocommit" by default, but that does not seem to be the default mode for TorMySQL

(I was confused the same as you were, for the first couple days of using TorMySQL :)

Upvotes: 2

Related Questions