[SQL-CVS] [ sqlobject-Bugs-1592786 ] so-sqlite data corruption during updates

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Bugs item #1592786, was opened at 2006-11-08 20:20
Message generated for change (Comment added) made by phd
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1592786&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQLite
Group: None
Status: Open
Resolution: Invalid
Priority: 5
Private: No
Submitted By: Jayhem (jayhem)
Assigned to: Nobody/Anonymous (nobody)
Summary: so-sqlite data corruption during updates

Initial Comment:
I'm hitting a bug when using SO with turbogears 1.1b
with an sqlite database. When I do several successive
updates to a table (which are very slow btw), data gets
corrupted in the interface, select() returns invalid
objects, as if objects were pulled from a cache that
was out of synch with the db.
>From this point, successive select() without any
updates in-between can return different values,
randomly selecting one of the previsouly set values.

I have created a simple test-case (on turbogears)

Just launch it and change some values. Usually after
10-20 changes, the corruption appears.

I have tried this same testcase with MySQL and do not
hit this problem.

Note that dev.cfg declares
sqlobject.dburi="notrans_sqlite:///..."

file: model.py
======================================================
from sqlobject import *
from turbogears.database import PackageHub

hub = PackageHub("bug")
__connection__ = hub

class Item(SQLObject):
    data = UnicodeCol(length=40)

def loaddb():
    hub.begin()
    Item.dropTable(ifExists=True, cascade=True)
    hub.commit()
    Item.createTable(ifNotExists=True)
    for i in range(20):
        Item(data = str(i))
    hub.commit()
loaddb()

file: controllers.py
======================================================
import turbogears
from turbogears.controllers import RootController, expose
from model import Item

class Root(RootController):
    @expose(template="bug.templates.index")
    def index(self):
        items = Item.select()
        return dict(items = items)
    @expose(template="bug.templates.index")
    def change(self, id):
        item = Item.get(id)
        item.data += str(id)
        items = Item.select()
        return dict(items = items)

file: index.kid
======================================================
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0
Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:py="http://purl.org/kid/ns#"
    py:extends="'master.kid'">
<head>
    <meta content="text/html; charset=UTF-8"
http-equiv="content-type"
py:replace="''"/>
    <title>Items</title>
</head>
<body>
    <ul py:for="i in items">
        <li>${i.data} <a
href="/change/${i.id}">[edit]</a></li>
    </ul>
</body>
</html> 

----------------------------------------------------------------------

>Comment By: Oleg Broytmann (phd)
Date: 2007-04-25 17:50

Message:
Logged In: YES 
user_id=4799
Originator: NO

> >> TypeError: _init() takes exactly 2 arguments (4 given)

   Hector, this is a bug in your program, not in SQLObject. You've
declared ventas._init() with the following signature:

    def _init(self,id):

But in SQLObject it is

    def _init(self, id, connection=None, selectResults=None):

You must declare you ._init() the same way, and you must pass all
parameters to SQLObject:

    def _init(self, id, connection=None, selectResults=None):
        SQLObject._init(self, id, connection=connection,
selectResults=selectResults) ###Subrayala por favor

Now your program works.

----------------------------------------------------------------------

Comment By: Nobody/Anonymous (nobody)
Date: 2007-04-21 00:56

Message:
Logged In: NO 

from: he...@en...
Bug: so-sqlite data corruption during updates
In relation to this, I am running an aplication using only sqlobject,
sqlite and python, currently I am not using TG.
I have a similar error.  I had created quite a few tables using three
versions of sqlobject (0.8.2  0.8.1 and the development version 0.10 fron
svn)  in the three versions it had come with the following error:

Traceback (most recent call last):
  File "<pyshell#16>", line 1, in -toplevel-
    ventas.get(3)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.10dev_r2553-py2.4.egg/sqlobject/main.py",
line 919, in get
    val._init(id, connection, selectResults)
TypeError: _init() takes exactly 2 arguments (4 given)

Currently I am using Debian testing version and sqlite-3-3-14, 
I am able to isolate the error to the following

I create a small table called "ventas" with 24 records in it
then  I do in python:

for x in range(1,25):
	print x, ventas.get(x)

this print:
1 <ventas 1 producto_idID=1 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
2 <ventas 2 producto_idID=1 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
3 <ventas 3 producto_idID=1 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
4 <ventas 4 producto_idID=1 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
5 <ventas 5 producto_idID=2 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
6 <ventas 6 producto_idID=2 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
7 <ventas 7 producto_idID=2 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
8 <ventas 8 producto_idID=2 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
9 <ventas 9 producto_idID=3 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=6.0>
10 <ventas 10 producto_idID=3 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=6.0>
11 <ventas 11 producto_idID=3 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=6.0>
12 <ventas 12 producto_idID=3 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=6.0>
13 <ventas 13 producto_idID=4 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=7.0>
14 <ventas 14 producto_idID=4 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=7.0>
15 <ventas 15 producto_idID=4 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=7.0>
16 <ventas 16 producto_idID=4 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=7.0>
17 <ventas 17 producto_idID=5 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
18 <ventas 18 producto_idID=5 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
19 <ventas 19 producto_idID=5 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
20 <ventas 20 producto_idID=5 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
21 <ventas 21 producto_idID=6 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.5>
22 <ventas 22 producto_idID=6 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.5>
23 <ventas 23 producto_idID=6 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.5>
24 <ventas 24 producto_idID=6 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.5>

Then I do:

for x in ventas.select():
	print x

<ventas 1 producto_idID=1 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
<ventas 2 producto_idID=1 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
<ventas 3 producto_idID=1 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
<ventas 4 producto_idID=1 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
<ventas 5 producto_idID=2 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
<ventas 6 producto_idID=2 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
<ventas 7 producto_idID=2 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
<ventas 8 producto_idID=2 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
<ventas 9 producto_idID=3 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=6.0>
<ventas 10 producto_idID=3 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=6.0>
<ventas 11 producto_idID=3 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=6.0>
<ventas 12 producto_idID=3 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=6.0>
<ventas 13 producto_idID=4 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=7.0>
<ventas 14 producto_idID=4 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=7.0>
<ventas 15 producto_idID=4 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=7.0>
<ventas 16 producto_idID=4 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=7.0>
<ventas 17 producto_idID=5 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
<ventas 18 producto_idID=5 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
<ventas 19 producto_idID=5 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
<ventas 20 producto_idID=5 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
<ventas 21 producto_idID=6 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.5>
<ventas 22 producto_idID=6 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.5>
<ventas 23 producto_idID=6 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.5>
<ventas 24 producto_idID=6 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.5>

Then I do:

for x in range(1,25):
	print x, ventas.get(x)

1 <ventas 1 producto_idID=1 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
2 <ventas 2 producto_idID=1 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
3 <ventas 3 producto_idID=1 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
4 <ventas 4 producto_idID=1 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
5 <ventas 5 producto_idID=2 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
6 <ventas 6 producto_idID=2 fecha_idID=2 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
7 <ventas 7 producto_idID=2 fecha_idID=3 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
8 <ventas 8 producto_idID=2 fecha_idID=4 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
9 <ventas 9 producto_idID=3 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=6.0>
10 <ventas 10 producto_idID=3 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=6.0>
11 <ventas 11 producto_idID=3 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=6.0>
12 <ventas 12 producto_idID=3 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=6.0>
13 <ventas 13 producto_idID=4 fecha_idID=1 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=7.0>
14 <ventas 14 producto_idID=4 fecha_idID=2 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=7.0>
15 <ventas 15 producto_idID=4 fecha_idID=3 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=7.0>
16 <ventas 16 producto_idID=4 fecha_idID=4 status_idID=2 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=7.0>
17 <ventas 17 producto_idID=5 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>
18 <ventas 18 producto_idID=5 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.0>
19 <ventas 19 producto_idID=5 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.0>
20 <ventas 20 producto_idID=5 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.0>
21 <ventas 21 producto_idID=6 fecha_idID=1 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.5>
22 <ventas 22 producto_idID=6 fecha_idID=2 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=5000 precio=5.5>
23 <ventas 23 producto_idID=6 fecha_idID=3 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=6000 precio=5.5>
24 <ventas 24 producto_idID=6 fecha_idID=4 status_idID=3 region_idID=1
pais_idID=1 moneda_idID=2 volumen=7000 precio=5.5>

The I do:

ventas.get(1)
<ventas 1 producto_idID=1 fecha_idID=1 status_idID=1 region_idID=1
pais_idID=1 moneda_idID=2 volumen=4000 precio=5.0>

Then I do this one:

for x in ventas.select():
	print x

And I obtain this error:	

Traceback (most recent call last):
  File "<pyshell#39>", line 1, in -toplevel-
    for x in ventas.select():
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.10dev_r2553-py2.4.egg/sqlobject/sresults.py",
line 165, in __iter__
    return iter(list(self.lazyIter()))
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.10dev_r2553-py2.4.egg/sqlobject/dbconnection.py",
line 814, in next
    obj = self.select.sourceClass.get(result[0], selectResults=result[1:],
connection=self.dbconn)
  File
"/usr/lib/python2.4/site-packages/SQLObject-0.10dev_r2553-py2.4.egg/sqlobject/main.py",
line 919, in get
    val._init(id, connection, selectResults)
TypeError: _init() takes exactly 2 arguments (4 given)

Is important to note that to get the error I had to run the commands in
this sequence. Also the database is only corrupted in memory, the file in
the hard disk is ok. Also, it is only corrupted in odd registry id record
numbers, even records are ok.

I am using python2.4.4

I am sorry for the great amount of data posted.

----------------------------------------------------------------------

Comment By: Jayhem (jayhem)
Date: 2006-11-14 15:04

Message:
Logged In: YES 
user_id=1640652

You are right. the bug does not seem to cause data
corruption in the database (i.e it's not a db related
issue), it just causes random results to be returned from
queries.

Simply switching the database to MySQL solves the problem,
which confirms the bug is not in the db.

An even more annoying issue, which I think is related, is
the extremely slow inserts/updates into sqlite, and frequent
error messages telling me the db is locked.

I am new to turbogears, sqlobject, sqlite and python, so I'm
sorry I won't have time to work on a SqlObject-only
testcase. Let me know if I can do anything to help fix this.

----------------------------------------------------------------------

Comment By: Oleg Broytmann (phd)
Date: 2006-11-13 19:59

Message:
Logged In: YES 
user_id=4799

Our company has been using SQLObject with SQLite for three
rather large commercial programs. Neither of our developers,
testers or users ever experienced data loss. Are you sure
the problem is not TG-related? Please write an
SQLObject-only test. In any case I cannot run your program -
I don't have TG and not going to install it.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1592786&group_id=74338

[SQL-CVS] [ sqlobject-Bugs-1592786 ] so-sqlite data corruption during updates

SQLObject is a Python ORM.

[SQL-CVS] [ sqlobject-Bugs-1592786 ] so-sqlite data corruption during updates