Issue: query always return same data even the data is updated in mysql db
Author: dista
State: closed
Created: 2016-01-05 11:36:32

I have encountered such problem:

There is an simple web application that using peewee to retrieve data from db. something like this:

```
@route('/log_cluster/test')
def test():
    processes_q = ProcessState.select().where(ProcessState.hostname == "dista81")

    for item in processes_q:
        print item.start_time
```

This app does `NOT` have any modification action to db, only 'select' calls.

I update data in mysql using mysql cli,   but after I changed the data in mysql, 
the data returned in my simple web application is `NOT` updated.


I turn on mysql log, it seems the query really go to mysql

```
mysql> select * from mysql.general_log;
+---------------------+------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time          | user_host                          | thread_id | server_id | command_type | argument                                                                                                                                               |
+---------------------+------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-01-05 19:58:04 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log                                                                                                                        |
| 2016-01-05 19:58:15 | tvie[tvie] @ localhost [127.0.0.1] |        83 |         0 | Query        | SELECT `t1`.`id`, `t1`.`hostname`, `t1`.`processname`, `t1`.`version`, `t1`.`start_time` FROM `processstate` AS t1 WHERE (`t1`.`hostname` = 'dista81') |
| 2016-01-05 19:58:21 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log                                                                                                                        |
| 2016-01-05 19:58:32 | tvie[tvie] @ localhost [127.0.0.1] |        83 |         0 | Query        | SELECT `t1`.`id`, `t1`.`hostname`, `t1`.`processname`, `t1`.`version`, `t1`.`start_time` FROM `processstate` AS t1 WHERE (`t1`.`hostname` = 'dista81') |
| 2016-01-05 19:58:34 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log                                                                                                                        |
| 2016-01-05 19:58:50 | tvie[tvie] @ localhost [127.0.0.1] |        83 |         0 | Query        | SELECT `t1`.`id`, `t1`.`hostname`, `t1`.`processname`, `t1`.`version`, `t1`.`start_time` FROM `processstate` AS t1 WHERE (`t1`.`hostname` = 'dista81') |
| 2016-01-05 19:58:51 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log                                                                                                                        |
| 2016-01-05 19:59:43 | tvie[tvie] @ localhost [127.0.0.1] |        83 |         0 | Query        | SELECT `t1`.`id`, `t1`.`hostname`, `t1`.`processname`, `t1`.`version`, `t1`.`start_time` FROM `processstate` AS t1 WHERE (`t1`.`hostname` = 'dista81') |
| 2016-01-05 19:59:48 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log                                                                                                                        |
+---------------------+------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

```


After change code to the following, it works

```
    processes_q = ProcessState.select().where(ProcessState.hostname == "dista81")

    for item in processes_q:
        print item.start_time

    db.commit()
```

But I do not want to write that because it is ugly, Is there any other way to make sure it will read the latest result?

my connection statement

```
connect('mysql://xx:xxx@127.0.0.1:3306/lava_log_cluster')
```


I check mysql log again, I found this:

```
+---------------------+------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-01-05 20:56:28 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log order by event_time desc limit 10                                                                                                     |
| 2016-01-05 20:56:12 | tvie[tvie] @ localhost []          |        31 |         0 | Query        | select * from mysql.general_log order by event_time desc limit 10                                                                                                     |
| 2016-01-05 20:56:08 | [tvie] @ localhost [127.0.0.1]     |        94 |         0 | Connect      | tvie@localhost on lava_log_cluster                                                                                                                                    |
| 2016-01-05 20:56:08 | tvie[tvie] @ localhost [127.0.0.1] |        94 |         0 | Query        | SET NAMES utf8                                                                                                                                                        |
| 2016-01-05 20:56:08 | tvie[tvie] @ localhost [127.0.0.1] |        94 |         0 | Query        | set autocommit=0                                                                        
```

the autocommit mode in the log is 0.

But I do set autocommit=True in code:

```
realdb = MySQLDatabase("lava_log_cluster", host="127.0.0.1",port=3306,user="xx",passwd="xxx", autocommit=True)
#realdb = connect('mysql://xx:xxx@127.0.0.1:3306/lava_log_cluster')
db.initialize(realdb)
print db.get_autocommit()
db.set_autocommit(True)

```

Why is that?


We've just noticed the same issue. We've reverted to 2.6.4 which works fine, the bug was introduced in version 2.7.0.


issue #727 introduced this bug, commit is required after select for mysql.

Well, at least for long living connections. IF you disconnect after each request there should be no problem. Or commit at the end of each request.


What about wrapping your code in a transaction?

``` python
with db.atomic():
    processes_q = ProcessState.select().where(ProcessState.hostname == "dista81")

    for item in processes_q:
        print item.start_time
```

Furthermore, for a web app you really should not be using a single static connection. Did you read the docs here:

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-request-hooks

If you read that and still are sure that a long-lived conn is what you want, I'd suggest wrapping each view in a transaction using the `atomic()` decorator or context manager.


I use bottle and I read the document the one you specified, but I am a little confused,

```
# app.py
from bottle import hook  #, route, etc, etc.
from peewee import *

db = SqliteDatabase('my-bottle-app.db')

@hook('before_request')
def _connect_db():
    db.connect()

@hook('after_request')
def _close_db():
    if not db.is_closed():
        db.close()

# Rest of your bottle app goes here.
```

If I run bottle in multi thread server like `cherrypy   cherrypy    Multi-threaded and very stable`, can it work?

for example:
thread 1 call the db.connect hook, then execute the request, before thread 1 call db.close hook,
thread 2 call db.connect hook. 


@goir , thanks, I manually set commit_select = True to db, and the problem is gone


Database connections are thread local and are safe in multithreaded environments.


thanks


Wanted to get some clarification on this, since while debugging performance of my web app, I noticed that a lot of time is being spend doing commits after every SELECT (notably on requests that involve a lot of SELECT queries), which led me to finding the `commit_select = True` attribute in `MySQLDatabase`.

Is my understanding correct, that if I properly wrap my request start/end with db connect/close (in my case I am just using `playhouse.flask_utils.FlaskDB`), then I can safely set `commit_select = False`?

> Is my understanding correct, that if I properly wrap my request start/end with db connect/close (in my case I am just using playhouse.flask_utils.FlaskDB), then I can safely set commit_select = False?

Ahh, perhaps. Better would be to wrap your database code with the `db.atomic()` decorator, as this will disable the autocommit-type behavior.

Hmm well my application is pretty large and there are a lot of queries all over the place so it's not really practical to wrap each one with an atomic() decorator. Unless you mean to wrap the whole request itself? But that wouldn't be great either because there are several endpoints/APIs where I would still want the write operations to commit, even if an exception happens mid-request.

Also I'm curious about this from a high-level/architecture perspective. At least in the case of web services, I would think that the expected/default behavior should be for all queries within a request to operate in the same "commit lifecycle" (not sure on the correct terminology here). E.g. if I am re-reading rows from the same table, I don't want the data to change mid-request if a write in a separate request changed the data.

Also, even with `commit_select = False`, I still see `set autocommit=0` at the beginning of each request in the mysql log. Is that expected?


I am not suggesting you wrap each query with `atomic()`. That's silly.

Wrapping the whole request in a transaction is a very good idea and very sensible. Wrapping even distinct subsets of operations in `atomic()` is fine.

> E.g. if I am re-reading rows from the same table, I don't want the data to change mid-request if a write in a separate request changed the data.

This is called isolation, and you need to wrap your blocks with `atomic()` to get this.

> I still see set autocommit=0 at the beginning of each request in the mysql log. Is that expected?

Yes. Peewee handles all this stuff.

-------------------

So to step back. Peewee operates in autocommit mode by default. No, not your database server's "autocommit", but it is effectively the same. This is to help beginners and not be confusing...e.g., when you save a model or call `create()`, then the operation happens and is committed immediately. But it is not ideal from a performance or even correctness point-of-view.

To achieve better performance, isolation, etc., you should make use of the `atomic()` helpers provided by Peewee (which can even be nested, committed/rolled-back mid-way through, and all sorts of stuff). For web apps, a transaction-per-request is usually a good way to go. If you need to commit midway through, you can do this:

```python

with db.atomic() as txn:
    # do some stuff.
    txn.commit()  # everything up til now is committed, new txn starts.

    # do some stuff

# txn is committed implicitly at end of "with" block, unless
# an exception occurred, in which case it is rolled-back.
```

I'd suggest you probably would want to read up on ACID and how COMMIT works in your db. 

> I am not suggesting you wrap each query with `atomic()`. That's silly.

Ok, that's what I thought :), but just checking.

Thanks for the details. I do have a basic understanding of ACID and I do currently use `db.atomic`/rollback/etc in some places with expected results, but I admit my knowledge on it could be improved, especially as to exactly what's going on at the lower levels. I'll do some more reading on that. And yeah, it hadn't occurred to me that it makes sense to have each request operate in its own transaction (and make use of nested transaction and commits). I think that's the missing piece that will solve a lot of my questions/issues.

Thanks again for the support/explanation.