why torndb will close connection when meeting OperationalError

[AntmanTmp]

from the docstring, we know torndb is a wrapper of mysqldb, and support some easy used DML sql method. the most important method is _execute and _cursor,you know

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()

    def _execute(self, cursor, query, parameters, kwparameters):
        try:
            return cursor.execute(query, kwparameters or parameters)
        except OperationalError:
            logging.error("Error connecting to MySQL on %s", self.host)
            self.close()
            raise

but why when catch OperationalError, will close the connection?

from the mysql doc we know OperationalError just some exception not related to sql execute, but from pep249 , this error is related to the database's operation, so maybe some sql execute can cause the error

mysql doc: https://dev.mysql.com/doc/connector-python/en/connector-python-api-errors-operationalerror.html

This exception is raised for errors which are related to MySQL's operations. For example: too many connections; a host name could not be resolved; bad handshake; server is shutting down, communication errors.

mysql doc: https://dev.mysql.com/doc/connector-python/en/connector-python-api-errors.html

he exception classes defined in this module mostly follow the Python Database API Specification v2.0 (PEP 249). For some MySQL client or server errors it is not always clear which exception to raise. It is good to discuss whether an error should be reclassified by opening a bug report.

pep249: https://www.python.org/dev/peps/pep-0249/

OperationalError Exception raised for errors that are related to the database's operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.

for example: when I execute a sql ,which a field type is not match table field schema, it also raise OperationalError

OperationalError(1366, "Incorrect integer value: 'aaaaa' for column 'rssi' at row 1")

in my example, this field schema of table is integer,but I put a string in sql

so if I put some sql in a transaction, when catch OperationalError, the connection is closed, mysql auto rollback, the rollback by myself has no effect.

Fortunately, I have extended tornndb's support for transactions in my code, but I also don`t know the reason of close connection when catch OperationalError

Even more confusing is according to mysql doc, 1366 will be DatabaseError, but in fact it raise OperationalError

server-error-reference

Error number: MY-011366; Symbol: ER_KEYRING_FAILED_TO_REMOVE_FILE; SQLSTATE: HY000 Message: Could not remove file %s OS retuned this error: %s

you see HY000

it will be DatabaseError when you check the table here connector-python-api-errors

[bdarnell]

It's been a long time and I'm not sure there was ever a lot of thought put into the error handling here, but based on the snippets you've quoted, I think it's a bug in the mysql connector that an incorrect type raises OperationalError instead of ProgrammingError.

so if I put some sql in a transaction, when catch OperationalError, the connection is closed, mysql auto rollback, the rollback by myself has no effect.

Why does this matter? It shouldn't be a problem to roll back twice.

Fortunately, I have extended tornndb's support for transactions in my code, but I also don`t know the reason of close connection when catch OperationalError

My understanding (which could be wrong) is that in general, after you get an OperationalError, the connection is in a broken state and you can't do anything with it but to close it and open another.

[AntmanTmp]

My understanding (which could be wrong) is that in general, after you get an OperationalError, the connection is in a broken state and you can't do anything with it but to close it and open another.

Yes, if the conn is closed by OperationalError, which raised by some sql execute, mysql server will auto rollback, so I can do nothing.

    def __enter__(self):
        if not self.conn.has_transaction():
            self._begin()
        self.conn.assign_transaction(self)
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            if exc_type:
                self.rollback()
            elif self.conn.has_transaction():
                try:
                    self.commit()
                except:
                    self.rollback()
                    raise
        finally:
            self.conn.del_transaction()

the rollback effect mysql will automatic completion, not self.rollback() manual completion