old field value reappears

[JM]

Hello!

I'm hitting a weird problem. I have the following classes

class Container(SQLObject):
name = UnicodeCol(length=40, alternateID=True)
tests = MultipleJoin('Test')

class Test(SQLObject):
name = UnicodeCol(length=40, alternateID=True)
datafile = ForeignKey('DataFile')
container = ForeignKey('Container')
...

and a method:

@expose()
@validate(form=bm_form)
@error_handler(post)
@identity.require(identity.not_anonymous())
def save(self, id, name, datafile):
user = identity.current.user;

try:
test = Test.get(id)
except SQLObjectNotFound:
print "error"

...

if testdef.name != name:
testdef.name = name
print "New Name is %s" % testdef.name

After I call "save" and successfully change the an existing object's
name, I display a page listing all the tests in the container, and I
also double check with CatWalk and everything looks fine in the DB
(notrans_sqlite), the test name has changed to the new name.

However, if I keep reloading the page, once every few times the name
changes back to the old one! Trying to retrieve the object by its name
from another method fails, it does not exist in the DB. Even CatWalk
does not show the old name... is it some caching error in CherryPy?

How would you debug this if you were a beginner TG user like me?

Thanks in advance for your help.
JM

[JM]

A further test, if I shut down and restart the server, everything works
fine. I only get the problem with reload after changing a record after
retrieving it through its 'id' field. I will do further tests when I
have a minute...

[Krys]

I believe this is a known issue related to sqlite. You should check
TG's trac for a related ticket.

I could be wrong though.

Krys

[JM]

> I believe this is a known issue related to sqlite. You should check
> TG's trac for a related ticket.
>
> I could be wrong though.
>
> Krys

Thanks Krys for your reply. I am quite surprised that such a huge
problem would exist and not be fixed or affect more users... or is
everyone else using mysql? Sqlite would perfectly fit my needs here...
if it worked. I also hit some problems with sqlite and unicode strings,
I will investigate more...

I had a look on Trac and sqlite support but did not find the related
issue, any chance someone could point me to it?

Thanks,
JM

[JM]

I still have this problem, can anyone please help me?

[Jorge Godoy]

"JM" <jmku...@gmail.com> writes:

> I still have this problem, can anyone please help me?

Sorry but these messages have expired for me here. What problem?

--
Jorge Godoy <jgo...@gmail.com>

[JM]

Hi Jorge,

Thanks for the reply.
Basically, I'm trying to update data stored in Sqlite. I do it through
exposed methods that take an 'id' as parameter and update the object,
by getting a reference with o=Object.get(id) and the setting a new
value for one of the fields (o.field='new val'). Now, if I do it once,
ok everything works ok. But if I do it over and over again on a series
of records, suddenly all the old values reappear, randomly.
If I go to a page displaying all the records -- which I send from a
method returning a dict(data=list(Object.select())) -- and keep
hitting the refresh button in my browser, the values displayed for
these fields keep switching from the old to the new values, apparently
randomly, although there are no updates made. I suspect this is a
problem with the SQLObject cache, but I have no idea how to debug this,
I'm expert in C++ but new to python/TG/web development.

Thanks
JM

[Lee McFadden]

I realise that you're trying to get this to work with Sqlite, but does
the same behaviour occur if you use another backend, e.g. MySQL or
Postgresql?

I know that this doesn't actually help to fix the problem but it will
help diagnose where the problem lies, i.e. SQLObject itself or
SQLObject's use of the Sqlite dbapi module. It may also be worth
posting this with an example at the SO list.

Lee

--
Lee McFadden

blog: http://www.splee.co.uk
work: http://fireflisystems.com

[JM]

Thanks Lee, I will try this next.

In the meantime I have created a small test-case that reproduces the
problem for me, here it is. Just click around 10-20 times and you can
see the values changing, then try refreshing...

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()
import turbogears
from turbogears.controllers import RootController, expose
from model import Item

file: controllers.py
======================================================
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>

[JM]

Lee McFadden wrote:
> I realise that you're trying to get this to work with Sqlite, but does
> the same behaviour occur if you use another backend, e.g. MySQL or
> Postgresql?
>
> I know that this doesn't actually help to fix the problem but it will
> help diagnose where the problem lies, i.e. SQLObject itself or
> SQLObject's use of the Sqlite dbapi module. It may also be worth
> posting this with an example at the SO list.

I have been unable to reproduce this behaviour when using my simple
testcase with MySQL.
Looks like there is a serious bug in the SO-sqlite interface... strange
that nobody else is hitting it, as sqlite is the default db installed
with turbogears.

Thanks
JM

[Jorge Godoy]

"JM" <jmku...@gmail.com> writes:

> Thanks Lee, I will try this next.
>
> In the meantime I have created a small test-case that reproduces the
> problem for me, here it is. Just click around 10-20 times and you can
> see the values changing, then try refreshing...

Have you disabled your browser's cache? BTW, what browser are you using? IE
caches a lot and if you don't take care it skips some things and don't go to
your application to retrieve data...

--
Jorge Godoy <jgo...@gmail.com>

[JM]

> Have you disabled your browser's cache? BTW, what browser are you using? IE
> caches a lot and if you don't take care it skips some things and don't go to
> your application to retrieve data...

I use Mozilla 1.7.12 and Firefox 1.5.0.7 on Debian

It only happens with sqlite, not when I use MySQL, which seems to rule
out a problem with the browser's cache. I have now tried with disabled
cache and see the problem still happens indeed.

The problem might be the same than the one that causes the
inserts/updates into sqlite to be incredibly slow. To be honest, I do
not understand why TurboGears sets SO+Sqlite as defaults, as it clearly
doesn't work.

[Jorge Godoy]

"JM" <jmku...@gmail.com> writes:

> The problem might be the same than the one that causes the
> inserts/updates into sqlite to be incredibly slow. To be honest, I do
> not understand why TurboGears sets SO+Sqlite as defaults, as it clearly
> doesn't work.

It is easier to test. And to provide a quickstart. It is not recommended
anywhere for production code, even though it is enough for a lot of things.

I have a concept-project that I used to demo the final project to the client
with 720K rows, summing up more than 80MB of SQLite-database. It works.

I use PostgreSQL for production code, though, since it is much more robust and
resourceful than the alternatives.

--
Jorge Godoy <jgo...@gmail.com>

[JM]

http://trac.turbogears.org/turbogears/ticket/1154

[JM]

Jorge,

Thanks for your reply. I believe the problem has something to do with
the extremely slow inserts/updates from SO into sqlite. I am only
hitting the problem if I do several updates to existing records in a
quick succession, which might be faster than the cache can work to keep
in synch with the db.

However, I am reassured that a different usage pattern can lead to a
more successful use of this configuration:-)

JM

[JM]

Response by the SQLObject folks:

"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."

~JM

[JM]

I tried to reproduce this bug with an SO-only testcase but failed so
far. Could it be that it is just in the way TG integrates SO-sqlite,
using/configuring a cache (part of SO or not...) that gets out of synch
with the db file? In this case, the bug could appear again with other
databases and mappers... just a thought.

~JM

[José de Paula Eufrásio Júnior]

Hmm.

Just hit something like that myself. It wasn't a fast sucession of
updates, I edit user data on one method and redirect back to the
index, and the "welcome ${tg.identity.display_name}" on master.kid
still shows the old name. It shows the new one if I hit refresh a
couple of times.
Watching the inserts/updates on the console I see that those are done
right after I submit the page, but seems like the objects are kept in
some weird memory/cache, because it doesn't refresh it with a new
select or something. That's kinda weird. I added a .sync() to my
objects, tho, and now it seems less senile.

--
José de Paula Eufrásio Júnior
aka coredump
http://core.eti.br