Issue: sql_error_handler not called
Author: tfeldmann
State: closed
Created: 2014-08-01 11:23:57

Hi! After connecting to a mysql database and waiting for a long time (>8h) subsequent database calls fail with a "2006 MySQL has gone away". To work around this problem I want to implement a "ReconnectingMySQLDatabase" that reconnects if this exception is detected. 
For testing purposes I created the BaseModel tables and called `BaseModel.create()` several times (works), then shut down the database and called create() again. I expected the sql_error_handler to be called but it wasn't. [See the logfile](http://pastebin.com/x4zBuRgh)

``` python
import datetime
from peewee import *


class ReconnectingMySQLDatabase(MySQLDatabase):

    def sql_error_handler(self, exception, sql, params, require_commit):
        # I just want to print the arguments...
        print(exception, sql, params, require_commit)


database = ReconnectingMySQLDatabase(
    host='host',
    user='user',
    passwd='pass',
    database='name',
    threadlocals=True,
)


class BaseModel(Model):
    created = DateTimeField(index=True, default=datetime.datetime.now)
    modified = DateTimeField(index=True)

    class Meta:
        database = database

    def save(self, *args, **kwargs):
        self.modified = datetime.datetime.now()
        return super().save(*args, **kwargs)
```

Is there a better way to do this? 


Yeah, I think you're right... Perhaps the `ExceptionWrapper` code is getting in the way. Let me look into it.

PS, you can use the `playhouse.pool.PooledMySQLDatabase` with a stale timeout and it will automatically recycle connections for you. Docs: http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pool


How experimental is this module really? Anything I have to consider?


> How experimental is this module really?

I myself have not used it in deployment (I use SQLite for most of my projects), but it's been unit tested and I believe it should be safe to use.

As an aside, I made a script that does what you described -- connect to a mysql db, create a row, kill the database server, try to create another row.

When I try to create another row, the `sql_error_handler()` is being called.


Strange. I tested this in an iPython Notebook and stopped the db manually. I'm using pymysql instead of MySQLdb -- maybe that's the cause?
If you send me the script I can test it on my machine.


``` python
#!/usr/bin/env python

import logging

from peewee import *
from peewee import create_model_tables

class FakeMySQLDatabase(MySQLDatabase):
    def sql_error_handler(self, exception, sql, params, require_commit):
        import ipdb; ipdb.set_trace()

db = FakeMySQLDatabase('peewee_test')

class BaseModel(Model):
    class Meta:
        database = db

class Testmodel(BaseModel):
    data = CharField()

def main():
    create_model_tables([Testmodel], fail_silently=True)
    Testmodel.create(data='1')
    raw_input('stop server and press enter')
    Testmodel.create(data='2')

if __name__ == '__main__':
    main()
```


Alright, well I think that `sql_error_handler` is behaving correctly so I'm going to close. Please comment if you feel there is still an issue.


Thanks for the script. I found it works successfully in Python2 with MySQLdb driver but fails with PyMySQL (0.6.2) in both Python2 and Python3. 


I got the error handler with pymysql using my script. The exception was:

``` python
OperationalError(2013, 'Lost connection to MySQL server during query')
```


The error is handled, but an ArgumentError follows:
[peewee_issue410.py](http://pastebin.com/E2ZSWey3)

MySqlDb

```
pip install -U virtualenv
virtualenv test1
source test1/bin/activate
pip install mysql-python peewee
python peewee_issue410.py

# Output:
Starting MySQL
. SUCCESS! 
Shutting down MySQL
.... SUCCESS! 
Error handled: (OperationalError(2006, 'MySQL server has gone away'), 'INSERT INTO `testmodel` (`data`) VALUES (%s)', [u'2'], True) {}
```

PyMySql:

```
pip install -U virtualenv
virtualenv test2
source test2/bin/activate
pip install pymysql peewee
python peewee_issue410.py

# Output:
Starting MySQL
. SUCCESS! 
Shutting down MySQL
.... SUCCESS! 
Error handled: (OperationalError(2013, "Lost connection to MySQL server during query (error(54, 'Connection reset by peer'))"), 'INSERT INTO `testmodel` (`data`) VALUES (%s)', [u'2'], True) {}
Traceback (most recent call last):
  File "peewee_issue410.py", line 33, in <module>
    main()
  File "peewee_issue410.py", line 30, in main
    Testmodel.create(data='2')
  File "/Users/thomasfeldmann/Desktop/test2/lib/python2.7/site-packages/peewee.py", line 3038, in create
    inst.save(force_insert=True)
  File "/Users/thomasfeldmann/Desktop/test2/lib/python2.7/site-packages/peewee.py", line 3163, in save
    pk_from_cursor = self.insert(**field_dict).execute()
  File "/Users/thomasfeldmann/Desktop/test2/lib/python2.7/site-packages/peewee.py", line 2247, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "/Users/thomasfeldmann/Desktop/test2/lib/python2.7/site-packages/peewee.py", line 2388, in last_insert_id
    return cursor.lastrowid
AttributeError: 'Cursor' object has no attribute 'lastrowid'
```


Right -- you need to `return True` from the `sql_error_handler()` in order to re-raise the original exception.


Okay, didn't know that, I didn't find that in the docs. But how would you stop the exception being thrown? `return False` doesn't work.


Yeah, I guess the issue is with `pymysql` not assigning the `lastrowid` if the insert doesn't succeed. I think this behavior is acceptable, since you can still reconnect and re-execute the query from within the error handler.


Upgrade your openssl. Also that is not even remotely related to peewee or
this ticket. Go away.
On Aug 22, 2015 6:35 AM, "Sateesh H" notifications@github.com wrote:

> I have the below connection and using the Pooled database as shown below.
> DATABASE = {
> 'name': 'testdb',
> 'engine': 'playhouse.pool.PooledPostgresqlExtDatabase',
> 'register_hstore': False,
> 'user': 'test',
> 'host':'localhost',
> 'password':'test',
> 'port':5432,
> 'max_connections': 100,
> 'stale_timeout': 6000,
> }
> Still i m facing the same the error
> "OpenSSL error of the type “decryption failed or bad record mac”?
> 
> Is there other way to solve this issue?
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/410#issuecomment-133678364.