0

My CherryPy app does some cleaning every hour with the following code:

def every_hour():
    two_hours_ago = time.time() - 2 * 60 * 60
    DbChoice.delete().where(DbChoice.time_stamp < two_hours_ago).execute()

monitor_every_hour = Monitor(cherrypy.engine, every_hour, frequency=60 * 60)
monitor_every_hour.start()

Sometimes it crashes with he following message:

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\peewee.py", line 2364, in execute_sql
    self.commit()
  File "C:\Python34\lib\site-packages\peewee.py", line 2371, in commit
    self.get_conn().commit()
sqlite3.OperationalError: cannot commit - no transaction is active

This thread and others talk about how to fix the problem when working directly with sqlite, but I am using Peewee and I don't know if I am doing something wrong with Peewee or it is a bug and I need to work around it.

I start the connection with:

db = peewee.SqliteDatabase(path_name + '/doc.db', check_same_thread=False)
Community
  • 1
  • 1
stenci
  • 8,290
  • 14
  • 64
  • 104
  • Here is the answer: http://stackoverflow.com/questions/25850681/cherrypy-sqlite3-peewee-crashes-when-two-processes-execute-the-same-code-at/25851123#25851123 – stenci Sep 15 '14 at 15:37

1 Answers1

1

Looks like the query is automatically committed by default. So Try setting autocommit to False.

db = peewee.SqliteDatabase(path_name + '/doc.db', check_same_thread=False)
db.set_autocommit(False)

http://peewee.readthedocs.org/en/2.0.2/peewee/cookbook.html#changing-autocommit-behavior

Hope this helps!

Andrew Kloos
  • 4,189
  • 4
  • 28
  • 36
  • How do I not commit a transaction with Peewee? If that is the case, then why does it work all the times and it fails once every 2-3 days? – stenci Aug 27 '14 at 17:59
  • Hmmm so most commits are accurately updating data in the db? Does your delete() use a shared db connection or are you creating a new connection with auto-commit set to true? Can you comment out the commit and see if your record gets inserted or updated? – Andrew Kloos Aug 27 '14 at 18:16
  • When the app starts it executes the `db = ...` line as shown in the post. I don't know if Peewee starts a connection at that time or if it creates a new connection whenever it needs one (that's why I use Peewee, because I don't want to deal with these little details). What do you men by "comment out the commit" ? – stenci Aug 27 '14 at 19:02
  • any chance updates, inserts and deletes are only happening every 2/3 days? I updated my answer with something you could try. – Andrew Kloos Aug 27 '14 at 19:22
  • A few records are deleted every hour. I looked back at the log, and I noticed that the function `every_hour` ran at the same time as a similar function `every_day` (executed by another monitor). The `every_day` function scanned a folder and added 7 records with `DbProject.create(number=number, name=name, time_stamp=0)`during a 45 second run. The 7 records could happen at any time during the slow execution (that's why they are not in one transaction). Is it possible that the two functions executed by `Monitor` shared the same transaction? How do I fix that? – stenci Aug 27 '14 at 19:53
  • Maybe your threads are sharing a connection. That could explain the mix up. And it should only cause an error when both monitors are running at the same time. try adding this to your connection... db = peewee.SqliteDatabase(path_name + '/doc.db', check_same_thread=False, threadlocals=True) – Andrew Kloos Aug 27 '14 at 20:00
  • I will try your suggestion, even if I don't have a way to know if it's the correct answer for sure, until I wait a few days... but you should edit your answer and add the connection details there. – stenci Aug 27 '14 at 20:10