Re: [sqlalchemy] Deadlock in iterating over a session

[Lenza McElrath]

Ok cool.  I actually could write my code to produce exactly that effect
fairly quickly since I was already accessing the mutable object through a
comparable_property on the model.  It was surprising how easy it was
actually... SQLAlchemy really makes things awesome.

We will start looking at moving to SQLAlchemy 0.7.  It will probably be hard
to convince people to move to a beta version on production systems
though.  When do you expect 0.7 to leave beta?

I wonder if you could help with one other issue I have occationally observed
that might be related to this.  Sometimes of these mutable objects are None
(myobject.value == None).  These values are non-nullable columns in the
database, and should always be represented as an actual class in logic.  I
have suspected this issue is related to objects being expired (by explicitly
called session.expire()) and then detached from the session.  But it seemed
like SQLAlchemy should always either return a correct value or raise an
exception.  Returning None could result in incorrect behavor for code that
does: if myobject.value: save_the_world().  Does it make sense that this
would be happening?  I think I saw documentation explaining that attribute
values are loaded individually once they are expired?  Could the
resurrecting of state not be happening properly when only loading a single
attribute?

On Fri, Mar 4, 2011 at 2:16 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Mar 4, 2011, at 5:09 PM, Lenza McElrath wrote:

 
  So there is no way to accomplish this in 0.6?  I was looking at doing it
 the way I describe above, but it is not trivial to figure out which
 model/session a value is attached to.  And I guess it is theoretically
 possible that a value could be connected to two models/sessions.  Definitely
 scared of moving to an untested code... but looks like there are lots of
 improvements in 0.7 that might make it worth it...

 well, the on-change event required some less than trivial features so its
 an 0.7 thing.  The best solution of all is to not use mutable types in the
 first place.   I.e. if you need to change a scalar value in place, do
 something like myobject.value = value.mutate(xyz).



 
 
 
  --
  You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
  To post to this group, send email to sqlalchemy@googlegroups.com.
  To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
  For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Deadlock in iterating over a session

[Lenza McElrath]

On Sun, Mar 6, 2011 at 5:21 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Mar 6, 2011, at 8:16 PM, Lenza McElrath wrote:

 Ok cool.  I actually could write my code to produce exactly that effect
 fairly quickly since I was already accessing the mutable object through a
 comparable_property on the model.  It was surprising how easy it was
 actually... SQLAlchemy really makes things awesome.

 We will start looking at moving to SQLAlchemy 0.7.  It will probably be
 hard to convince people to move to a beta version on production systems
 though.  When do you expect 0.7 to leave beta?

 I wonder if you could help with one other issue I have occationally
 observed that might be related to this.  Sometimes of these mutable objects
 are None (myobject.value == None).  These values are non-nullable columns
 in the database, and should always be represented as an actual class in
 logic.  I have suspected this issue is related to objects being expired (by
 explicitly called session.expire()) and then detached from the session.  But
 it seemed like SQLAlchemy should always either return a correct value or
 raise an exception.  Returning None could result in incorrect behavor for
 code that does: if myobject.value: save_the_world().  Does it make sense
 that this would be happening?  I think I saw documentation explaining that
 attribute values are loaded individually once they are expired?  Could the
 resurrecting of state not be happening properly when only loading a single
 attribute?


 expired attributes don't return None - they invoke a SELECT from the
 database when they are accessed, or raise an error (to the chagrin of many
 users) if the object is not associated with a session.

 I don't know offhand what would make your values come back as None,
 wondering what you mean by resurrecting, are you pickling the parent
 objects as well ?   Without looking at the source I suppose that would be
 somewhat suspect though pickling/unpickling of mapped objects is well tested
 and supported.


No pickling is happening.  By resurrect I mean whatever is happening
in MutableAttrInstanceState.__resurrect.
I am definitely getting a None and not an error -- I happen to have an assert
myobject.value is not None.  I will try to make a test case.

It is hard because I don't know exactly how to trigger this code path.  I
ran into an issues here before with mutable columns not being updated after
resurrecting (fixed in r26f423a667ca).  If I recall the test case I made for
that it involved modifying the attribute in one function and then flushing
the session in another.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Deadlock in iterating over a session

[Lenza McElrath]

Thanks for the quick response to this as usual.  See me responses below.

On Fri, Mar 4, 2011 at 7:32 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Mar 4, 2011, at 2:58 AM, Lenza McElrath wrote:

 Hello!  I'm iterating over a session to look at all the objects:

 for obj in session:
   do_something_cool(obj)

 Yesterday this caused what looks like a deadlock in SQLAlchemy code.  Here
 is the stack I grabbed using gdb:

1. /python2.5/sqlalchemy/orm/session.py:1353 (Session.__iter__)
2. /python2.5/sqlalchemy/orm/identity.py:184 (WeakInstanceDict.values)
3. /python2.5/sqlalchemy/orm/identity.py:188 (
WeakInstanceDict.itervalues) ** self._remove_mutex.acquire()
4. /python2.5/sqlalchemy/orm/state.py:501 (
MutableAttrInstanceState.__resurrect)
5. /python2.5/sqlalchemy/orm/attributes.py:925 (Events.run)
6. /python2.5/sqlalchemy/orm/mapper.py:2424 (_event_on_resurrect)
7. /python2.5/sqlalchemy/util.py:953 (OrderedSet.__iter__)
8. /python2.5/sqlalchemy/orm/state.py:477 (
MutableAttrInstanceState._cleanup)
9. /python2.5/sqlalchemy/orm/identity.py:139A (WeakInstanceDict.remove)** 
 self._remove_mutex.acquire
()

 I'm running SQLAlchemy 0.6.5.  Is this a known issue or am I doing
 something wrong?


 I've never seen that before.A reentrant mutex would fix this but I
 really hate to use those as they have a big performance hit and thats a very
 critical section.   Its true this is also related to mutable attributes
 which is something I'd eventually like to remove entirely - they are pending
 deprecation in 0.7.   Is this issue consistently reproducible and can you
 send me a test case ?   This would be very high priority.

 Not sure if this helps but one of the triggers there is you have an object
 that has mutable attributes on it, which is dirty, and has been garbage
 collected.   The state hangs around in the Session and when accessed
 resurrects itself.  At that point, it seems like some other object that was
 also garbage collected starts doing the same thing before the process for
 object #1 can complete, its not clear why that would happen here, thats the
 point at which I'd want to pdb around to see what that's about.


This issue was in-consistently reproducible, but it is in a production
system where a workaround has been applied, so now it is not-so-reproducible
(hopefully).

Every object has mutable attributes on it, so it is not surprising that
there would be a dirty one in the session.  Here is the do_something_cool
function in case it provides some insight:

def do_something_cool(obj, do_copy=False):
  for obj in session:
if do_copy:
  ret_obj = copy.deepcopy(obj)
else:
  ret_obj = obj

ret_obj._has_been_flagged = True
return ret_obj

I believe do_copy should be False in this situation, but given the weirdness
I would exclude the possibility that it is True.




 On a somewhat related note, am experiencing the issue where lots of objects
 in a session significantly reduces performance.  It appears I am
 experiencing the penalty for using MutableTypes on objects described here:
 http://readthedocs.org/docs/sqlalchemy/en/latest/core/types.html#base-type-api.
 The doc states:

 In order to detect changes, the ORM must create a copy of the value when it
 is first accessed, so that changes to the current value can be compared
 against the “clean” database-loaded value. Additionally, when the ORM checks
 to see if any data requires flushing, it must scan through all instances in
 the session which are known to have “mutable” attributes and compare the
 current value of each one to its “clean” value.

 It doesn't seem like I should have to pay this penalty because with my type
 I actually know when any updates occur.  The type just supports
 models.mutable_value.update(data).  Is there a way to notify the
 model/session that the value has been updated when someone calls update, so
 the full scan of all instances is not needed?  Do I need to rewrite the
 update function to translate models.mutable_value.update(data) to actually
 generate models.mutable_value = new_mutable_value or is there a better way
 to do this?


 very easy.  Upgrade to 0.7.   All has been resolved there, to support
 in-place mutation detection you use the techniques described at
 http://www.sqlalchemy.org/docs/07/orm/extensions/mutable.html .

 Of course you'd be beta testing something I'm not sure anyone is using yet.
   Hopefully no deadlocks though !   :)   (but again, I'd love to fix that
 deadlock, I'll be looking at that today and a test case would be v. helpful)


So there is no way to accomplish this in 0.6?  I was looking at doing it the
way I describe above, but it is not trivial to figure out which
model/session a value is attached to.  And I guess it is theoretically
possible that a value could be connected to two models/sessions.  Definitely
scared of moving to an untested code... but looks like there are lots of
improvements in 0.7

[sqlalchemy] Deadlock in iterating over a session

[Lenza McElrath]

Hello!  I'm iterating over a session to look at all the objects:

for obj in session:
  do_something_cool(obj)

Yesterday this caused what looks like a deadlock in SQLAlchemy code.  Here
is the stack I grabbed using gdb:

   1. /python2.5/sqlalchemy/orm/session.py:1353 (Session.__iter__)
   2. /python2.5/sqlalchemy/orm/identity.py:184 (WeakInstanceDict.values)
   3. /python2.5/sqlalchemy/orm/identity.py:188 (WeakInstanceDict.itervalues
   ) ** self._remove_mutex.acquire()
   4. /python2.5/sqlalchemy/orm/state.py:501 (
   MutableAttrInstanceState.__resurrect)
   5. /python2.5/sqlalchemy/orm/attributes.py:925 (Events.run)
   6. /python2.5/sqlalchemy/orm/mapper.py:2424 (_event_on_resurrect)
   7. /python2.5/sqlalchemy/util.py:953 (OrderedSet.__iter__)
   8. /python2.5/sqlalchemy/orm/state.py:477 (
   MutableAttrInstanceState._cleanup)
   9. /python2.5/sqlalchemy/orm/identity.py:139A
(WeakInstanceDict.remove)** self._remove_mutex.acquire
   ()

I'm running SQLAlchemy 0.6.5.  Is this a known issue or am I doing something
wrong?

On a somewhat related note, am experiencing the issue where lots of objects
in a session significantly reduces performance.  It appears I am
experiencing the penalty for using MutableTypes on objects described here:
http://readthedocs.org/docs/sqlalchemy/en/latest/core/types.html#base-type-api.
The doc states:

In order to detect changes, the ORM must create a copy of the value when it
is first accessed, so that changes to the current value can be compared
against the “clean” database-loaded value. Additionally, when the ORM checks
to see if any data requires flushing, it must scan through all instances in
the session which are known to have “mutable” attributes and compare the
current value of each one to its “clean” value.

It doesn't seem like I should have to pay this penalty because with my type
I actually know when any updates occur.  The type just supports
models.mutable_value.update(data).  Is there a way to notify the
model/session that the value has been updated when someone calls update, so
the full scan of all instances is not needed?  Do I need to rewrite the
update function to translate models.mutable_value.update(data) to actually
generate models.mutable_value = new_mutable_value or is there a better way
to do this?

Thanks for any help!

  -Lenza

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Can you declaratively concatenate two columns

[Lenza McElrath]

Hey Royce,

This sounds like a job for composite columns:
http://www.sqlalchemy.org/docs/orm/mapper_config.html#composite-column-types

One gotcha that I ran into here is that you cannot have both the component
columns and the composite column mapped at the same time, like you do in
your example.  So depending on what you are trying to do, you might need to
make a comparable property instead:
http://www.sqlalchemy.org/docs/orm/mapper_config.html#custom-comparators

Let me know if you need any more help.

  -Lenza

On Mon, Jan 24, 2011 at 7:35 PM, Royce roycek...@gmail.com wrote:

 Hi does anyone know if is possible  to  declaratively concatenate two
 columns together which you can later do query's on.

 E.g. if I wanted to compute a new column course_name made up of
 CONCAT(course_code,course_name)

 Base = declarative_base()
 class Course(Base):
__tablename__ = 'Course'

course_code  = Column(VARCHAR(length=4), nullable=False)
course_num   = Column(INTEGER(), nullable=False)

course_name = func.CONCAT(course_code,course_num)  # only an
 example, this doesn't actually work


 So later you could do queries on the Course table like

 course_data =
 session.query(Course).filter( Course.course_name.op('regexp')
 ('^A.*4')  )).first()
 print course_data.course_name


 It is possible to do a query to generate the data outside the Course
 class as below, but how can you
 make it as a normal mapped column in the Course class ?

 query = session.query( func.CONCAT(Course.course_code,
 Course.course_num) )
 query = query.filter( func.CONCAT(Course.course_code,
 Course.course_num).op('regexp')('^A.*4') )

 Cheers
 Royce

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Row insert does not update relations in identity map

[Lenza McElrath]

I ran into an issue where a query was returning a object where the relations
were stale.  It turned out that this is because inserting a row does not
update relations in identity map.  The basic issue looks like this:

my_model = MyModel()
session.add(my_model)
session.flush()
assert len(my_model.related_models) == 0

# Create a RelatedModel that is related to my_model
related = self.RelatedModel(my_model_id=my_model.id)
session.add(related)
session.flush()

# This works
related_models = session.query(RelatedModel).filter_by(my_model_id=
my_model.id).all()
assert len(related_models) == 1

# This will issue a SQL query, but the related model that is
returned is ignored
# NOTE: Test will pass if we remove my_model from the session
#session.expunge(my_model)
my_model =
session.query(MyModel).options(sqlalchemy.orm.eagerload('related_models')).one()
assert len(my_model.related_models) == 1,
len(my_model.related_models)

Note that this happens even if you are selected MyModel (instead of creating
it).  A full version of this test can be found here:
http://pastebin.com/5HFiGrc1

What is the proper way to deal with this issue?  It's not as simple as
simply adding the expunge because the operations are in independent parts of
my code (that part of the code that creates the related model does not know
about MyModel).  Thanks for any help!

  -Lenza

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Avoiding StaleDataError when updating Float values

[Lenza McElrath]

Well, turns out that it doesn't take much of a pool at all to get the test
to fail: http://pastebin.com/trHhiG47

On Thu, Nov 4, 2010 at 10:33 PM, Lenza McElrath le...@lenza.org wrote:

 Interesting.  When I use your simple engine I do not get the error either,
 but I definitely get it when using my real configuration.  We are using a
 connection pool.  So the construction of the session maker for the test
 looks like this:

 e = sqlalchemy.create_engine('mysql://', pool=connections.get_pool())
 sessionmaker = sqlalchemy.orm.sessionmaker(e)

 The connection pool is somewhat complex, so I will try to find the simplest
 possible pool that reproduces the problem.  But, do you have ideas based on
 this?  The script only fails when I use a Float type with a test_value that
 is higher percision than the DB can represent.  It works when I use an
 Integer type, or when test_value is 44 or 0.44.

 Thanks for your help!


 On Thu, Nov 4, 2010 at 6:18 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Nov 4, 2010, at 8:17 PM, Lenza McElrath wrote:

 I am getting a StaleDataError when updating a Float column to the same
 value twice in a row.  This happens because SQLAlchemy thinks that I am
 changing the value, but then the DB reports that no value was changed.

 Test case can be seen here: http://pastebin.com/vxFBAMxm


 the test fails to note what sessionmaker is, if I run it like this:

 e = create_engine('mysql://scott:ti...@localhost/test')
 test_class = UpdateFunctionTest(sessionmaker(e))

 the script runs as expected with no errors.   StaleDataError only occurs
 if you execute() an update() or delete() construct that affects the primary
 key or version number of a row in the same transaction as one that also
 calls flush() and also targets that row.  This test doesn't emit any raw SQL
 constructs nor does it modify any primary key attributes or use version ids
 so no error occurs.   Float columns that aren't primary keys have nothing to
 do with StaleDataError.


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Avoiding StaleDataError when updating Float values

[Lenza McElrath]

Here is my output.  Note that my updates are for 0.2 not
0..  Both Python 2.5.2 and 2.6.5 interpret the test_value
this way.  I wonder if you are not seeing the error due to the float changes
in Python 2.7?

$ uname -a
Linux dev05 2.6.24-24-server #1 SMP Wed Apr 15 15:41:09 UTC 2009 x86_64
GNU/Linux
$ python
Python 2.5.2 (r252:60911, Jan 20 2010, 23:14:04)
[GCC 4.2.4 (Ubuntu 4.2.4-1ubuntu3)] on linux2
Type help, copyright, credits or license for more information.
 import sqlalchemy
 sqlalchemy.__version__
'0.6.5'
 0.444
0.2

$ python ~/float_update_test.py
2010-11-05 06:12:13,122 INFO sqlalchemy.engine.base.Engine.0x...b750 SELECT
DATABASE()
2010-11-05 06:12:13,122 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,124 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
VARIABLES LIKE 'character_set%%'
2010-11-05 06:12:13,124 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,125 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
VARIABLES LIKE 'lower_case_table_names'
2010-11-05 06:12:13,125 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,126 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
COLLATION
2010-11-05 06:12:13,126 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,130 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
VARIABLES LIKE 'sql_mode'
2010-11-05 06:12:13,130 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,131 INFO sqlalchemy.engine.base.Engine.0x...b750
CREATE TABLE sqlalchemy_test_my_model (
id INTEGER NOT NULL AUTO_INCREMENT,
float_value FLOAT,
PRIMARY KEY (id)
)ENGINE=InnoDB


2010-11-05 06:12:13,132 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,135 INFO sqlalchemy.engine.base.Engine.0x...b750 COMMIT
2010-11-05 06:12:13,138 INFO sqlalchemy.engine.base.Engine.0x...b750 BEGIN
(implicit)
2010-11-05 06:12:13,139 INFO sqlalchemy.engine.base.Engine.0x...b750 INSERT
INTO sqlalchemy_test_my_model (float_value) VALUES (%s)
2010-11-05 06:12:13,139 INFO sqlalchemy.engine.base.Engine.0x...b750
(0.2,)
2010-11-05 06:12:13,140 INFO sqlalchemy.engine.base.Engine.0x...b750 COMMIT
2010-11-05 06:12:13,143 INFO sqlalchemy.engine.base.Engine.0x...b750 BEGIN
(implicit)
2010-11-05 06:12:13,144 INFO sqlalchemy.engine.base.Engine.0x...b750 SELECT
sqlalchemy_test_my_model.id AS sqlalchemy_test_my_model_id,
sqlalchemy_test_my_model.float_value AS sqlalchemy_test_my_model_float_value
FROM sqlalchemy_test_my_model
WHERE sqlalchemy_test_my_model.id = %s
2010-11-05 06:12:13,144 INFO sqlalchemy.engine.base.Engine.0x...b750 (1L,)
2010-11-05 06:12:13,146 INFO sqlalchemy.engine.base.Engine.0x...b750 UPDATE
sqlalchemy_test_my_model SET float_value=%s WHERE
sqlalchemy_test_my_model.id = %s
2010-11-05 06:12:13,147 INFO sqlalchemy.engine.base.Engine.0x...b750
(0.2, 1L)
2010-11-05 06:12:13,147 INFO sqlalchemy.engine.base.Engine.0x...b750
ROLLBACK
2010-11-05 06:12:13,149 INFO sqlalchemy.engine.base.Engine.0x...b750
DROP TABLE sqlalchemy_test_my_model
2010-11-05 06:12:13,149 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
2010-11-05 06:12:13,425 INFO sqlalchemy.engine.base.Engine.0x...b750 COMMIT
Traceback (most recent call last):
  File /nail/home/lenza/float_update_test.py, line 70, in module
test_class.test_update_data()
  File /nail/home/lenza/float_update_test.py, line 51, in test_update_data
session.commit()
  File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line
623, in commit
self.transaction.commit()
  File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line
385, in commit
self._prepare_impl()
  File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line
369, in _prepare_impl
self.session.flush()
  File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line
1397, in flush
self._flush(objects)
  File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line
1478, in _flush
flush_context.execute()
  File /var/lib/python-support/python2.5/sqlalchemy/orm/unitofwork.py,
line 304, in execute
rec.execute(self)
  File /var/lib/python-support/python2.5/sqlalchemy/orm/unitofwork.py,
line 448, in execute
uow
  File /var/lib/python-support/python2.5/sqlalchemy/orm/mapper.py, line
1852, in _save_obj
(table.description, len(update), rows))
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table
'sqlalchemy_test_my_model' expected to update 1 row(s); 0 were matched.

On Fri, Nov 5, 2010 at 12:27 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Nov 5, 2010, at 2:51 AM, Lenza McElrath wrote:

 Well, turns out that it doesn't take much of a pool at all to get the test
 to fail: http://pastebin.com/trHhiG47


 sorry, again this works fine, and theres still nothing in the test as given
 that can cause StaleDataError.

 output when I add echo=True:

 010-11-05 03:24:49,463 INFO

Re: [sqlalchemy] Avoiding StaleDataError when updating Float values

[Lenza McElrath]

This is happening using MySQLdb 1.2.2 and MySQL server 5.0.87.  Upgrading is
not really a viable option.

I guess  you expect cursor.rowcount to be the number of matched rows, not
the number of changed rows?  Where is the documentation that says this is
what it should be?  Obviously no rows are being updated since you are
setting float_value to the exact same value:

mysql CREATE TABLE sqlalchemy_test_my_model (
- id INTEGER NOT NULL AUTO_INCREMENT,
- float_value FLOAT,
- PRIMARY KEY (id)
- )ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql INSERT INTO sqlalchemy_test_my_model (float_value) VALUES
(0.);
Query OK, 1 row affected (0.00 sec)

mysql UPDATE sqlalchemy_test_my_model SET float_value=0.
WHERE id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

When not using the pool the rowcount is 1 as you expect.  If this is the
issue, how can I tell SQLAlchemy that my connection does not support sane
rowcount?

I have updated the test to use an integer test value and to show what
cursor.rowcount is for the query: http://pastebin.com/SR6N4UhL

The works because SQLAlchemy realizes that the value has not changed so does
not even attempt an update.  Here is the relevant output:

2010-11-05 09:30:19,769 INFO sqlalchemy.engine.base.Engine.0x...f690 BEGIN
(implicit)
2010-11-05 09:30:19,770 INFO sqlalchemy.engine.base.Engine.0x...f690 INSERT
INTO sqlalchemy_test_my_model (float_value) VALUES (%s)
2010-11-05 09:30:19,770 INFO sqlalchemy.engine.base.Engine.0x...f690 (44,)
2010-11-05 09:30:19,771 INFO sqlalchemy.engine.base.Engine.0x...f690 COMMIT
# Update statement generates a rowcount of: 0
2010-11-05 09:30:19,775 INFO sqlalchemy.engine.base.Engine.0x...f690 BEGIN
(implicit)
2010-11-05 09:30:19,776 INFO sqlalchemy.engine.base.Engine.0x...f690 SELECT
sqlalchemy_test_my_model.id AS sqlalchemy_test_my_model_id,
sqlalchemy_test_my_model.float_value AS sqlalchemy_test_my_model_float_value
FROM sqlalchemy_test_my_model
WHERE sqlalchemy_test_my_model.id = %s
2010-11-05 09:30:19,776 INFO sqlalchemy.engine.base.Engine.0x...f690 (1L,)
2010-11-05 09:30:19,778 INFO sqlalchemy.engine.base.Engine.0x...f690 COMMIT



On Fri, Nov 5, 2010 at 7:59 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 so, what has to be happening is cursor.rowcount is returning zero, when it
 should be 1.   The version of MySQL as well as MySQLdb here could play a
 role.   I would try ensuring you're on the latest MySQLdb, trying the same
 script against other MySQL installations.   Try converting the script to use
 MySQLdb directly, using cursor.execute() and such - see if cursor.rowcount
 is zero when it should be one.   If I think of something else I'll let you
 know.

 On Nov 5, 2010, at 9:41 AM, Lenza McElrath wrote:

 Here is my output.  Note that my updates are for 0.2 not
 0..  Both Python 2.5.2 and 2.6.5 interpret the test_value
 this way.  I wonder if you are not seeing the error due to the float changes
 in Python 2.7?

 $ uname -a
 Linux dev05 2.6.24-24-server #1 SMP Wed Apr 15 15:41:09 UTC 2009 x86_64
 GNU/Linux
 $ python
 Python 2.5.2 (r252:60911, Jan 20 2010, 23:14:04)
 [GCC 4.2.4 (Ubuntu 4.2.4-1ubuntu3)] on linux2
 Type help, copyright, credits or license for more information.
  import sqlalchemy
  sqlalchemy.__version__
 '0.6.5'
  0.444
 0.2
 
 $ python ~/float_update_test.py
 2010-11-05 06:12:13,122 INFO sqlalchemy.engine.base.Engine.0x...b750 SELECT
 DATABASE()
 2010-11-05 06:12:13,122 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,124 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
 VARIABLES LIKE 'character_set%%'
 2010-11-05 06:12:13,124 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,125 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
 VARIABLES LIKE 'lower_case_table_names'
 2010-11-05 06:12:13,125 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,126 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
 COLLATION
 2010-11-05 06:12:13,126 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,130 INFO sqlalchemy.engine.base.Engine.0x...b750 SHOW
 VARIABLES LIKE 'sql_mode'
 2010-11-05 06:12:13,130 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,131 INFO sqlalchemy.engine.base.Engine.0x...b750
 CREATE TABLE sqlalchemy_test_my_model (
 id INTEGER NOT NULL AUTO_INCREMENT,
 float_value FLOAT,
 PRIMARY KEY (id)
 )ENGINE=InnoDB


 2010-11-05 06:12:13,132 INFO sqlalchemy.engine.base.Engine.0x...b750 ()
 2010-11-05 06:12:13,135 INFO sqlalchemy.engine.base.Engine.0x...b750 COMMIT
 2010-11-05 06:12:13,138 INFO sqlalchemy.engine.base.Engine.0x...b750 BEGIN
 (implicit)
 2010-11-05 06:12:13,139 INFO sqlalchemy.engine.base.Engine.0x...b750 INSERT
 INTO sqlalchemy_test_my_model (float_value) VALUES (%s)
 2010-11-05 06:12:13,139 INFO sqlalchemy.engine.base.Engine.0x...b750

[sqlalchemy] Avoiding StaleDataError when updating Float values

[Lenza McElrath]

I am getting a StaleDataError when updating a Float column to the same value
twice in a row.  This happens because SQLAlchemy thinks that I am changing
the value, but then the DB reports that no value was changed.

Test case can be seen here: http://pastebin.com/vxFBAMxm

Is there an easy way around this issue?  I could make my own type that
cancels the update if the value hasn't changed more than a specified
precision, but this seems like an issue that should be handled out of the
box.  The precision argument to Float didn't seem to help.  Thanks for any
assistance!

  -Lenza

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Avoiding StaleDataError when updating Float values

[Lenza McElrath]

Interesting.  When I use your simple engine I do not get the error either,
but I definitely get it when using my real configuration.  We are using a
connection pool.  So the construction of the session maker for the test
looks like this:

e = sqlalchemy.create_engine('mysql://', pool=connections.get_pool())
sessionmaker = sqlalchemy.orm.sessionmaker(e)

The connection pool is somewhat complex, so I will try to find the simplest
possible pool that reproduces the problem.  But, do you have ideas based on
this?  The script only fails when I use a Float type with a test_value that
is higher percision than the DB can represent.  It works when I use an
Integer type, or when test_value is 44 or 0.44.

Thanks for your help!

On Thu, Nov 4, 2010 at 6:18 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Nov 4, 2010, at 8:17 PM, Lenza McElrath wrote:

 I am getting a StaleDataError when updating a Float column to the same
 value twice in a row.  This happens because SQLAlchemy thinks that I am
 changing the value, but then the DB reports that no value was changed.

 Test case can be seen here: http://pastebin.com/vxFBAMxm


 the test fails to note what sessionmaker is, if I run it like this:

 e = create_engine('mysql://scott:ti...@localhost/test')
 test_class = UpdateFunctionTest(sessionmaker(e))

 the script runs as expected with no errors.   StaleDataError only occurs if
 you execute() an update() or delete() construct that affects the primary key
 or version number of a row in the same transaction as one that also calls
 flush() and also targets that row.  This test doesn't emit any raw SQL
 constructs nor does it modify any primary key attributes or use version ids
 so no error occurs.   Float columns that aren't primary keys have nothing to
 do with StaleDataError.


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: SQLAlchemy not updating all expected columns after mutable attribute modification

[Lenza McElrath]

Thanks for the quick response Michael.

I do not know why all references to the object are lost.  Adding the asserts
you suggested shows that the object is in the session and in the dirty list
as expected.

I have been able to reproduce the issue with a simple test case.  I have
untangled it from my infrastructure as much as possible, but you will have
to provide your own sessionmaker and BaseModel.  Please let me know if you
are able to reproduce with this: http://pastebin.com/pC45S59E

Interestingly, if I run the test on an existing row (as opposed to creating
the row in the test) the test passes.

  -Lenza

On Mon, Oct 25, 2010 at 10:33 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Oct 25, 2010, at 1:07 PM, Lenza McElrath wrote:

 I am running into a issue where it looks like SQLAlchemy is not performing
 the proper DB update when committing a session after modifying an object.
 This happens only when all references to the updated object are lost, and I
 update a mutable attribute BEFORE updating another attribute.  It seems as
 if MutableAttrInstanceState.__resurrect is not properly resurrecting the
 object state.

 I have code that basically looks like this:

 def update_my_model(session, my_model_id):
 my_model = session.query(MyModel, id=my_model_id).one()
 my_model.mutable_attribute.mutate()
 my_model.normal_attribute = 42
 return my_model

 session = self.logic.session_maker()
 update_my_model(session, my_model_id)
 session.commit()

 The above code does issues the SQL to update mutable_attribute, but not
 normal_attribute.  Everything works if I move the mutable_attribute change
 to after the normal_attribute change, remove it completely, or assign the
 return value of the update_my_model call to a variable.

 I have been able to determine that in that case that fails (and only in
 that case) MutableAttrInstanceState._cleanup is being called as
 update_my_model returns.  However, during the session.commit(), the call
 to self.manager.new_instance(state=self) in
 MutableAttrInstanceState.__resurrect is not returning an object that has
 normal_attribute set.  From what I can tell the MutableAttrInstanceState
 instance should know about the update to normal_attribute (
 InstanceState.modified_event is being called when it is set).  But I'm not
 sure of the inner-workings of MutableAttrInstanceState,so I haven't been
 able to confirm this.


 What is not explained here is how the reference would be lost in the first
 place.   The change to my_model.normal_attribute would place the object in
 the session's dirty list which results in a strong reference being created
 from the state to the object, which in turn is strongly referenced by the
 Session's identity map.



 My mutable_attribute is rather complex, so it is entirely possible that it
 is behaving badly in some why.  However, this seems like a strange failure
 mode for an issue with that attribute?  Anyone have ideas on what is going
 on here, or what my next steps should be to track it down?  If any
 information I have not provided would be helpful, please let me know.


 oh well definitely, try to create a simple test case.   For example, if I
 were to just take code like the above with a generic model, does that
 reproduce the issue ?If you think something about your mutable attribute
 is at fault, try replacing it with a plain dictionary and change a value
 within.   I can't really imagine how anything regarding your mutable type
 could be involved unless it reaches out and modifies the environment
 containing my_model somehow.

 If just the code above, I'd do things like:

 def update_my_model(session, my_model_id):
 my_model = session.query(MyModel, id=my_model_id).one()
 my_model.mutable_attribute.mutate()
 assert my_model in session
 my_model.normal_attribute = 42
 assert my_model in session.dirty
 return my_model





  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: SQLAlchemy not updating all expected columns after mutable attribute modification

[Lenza McElrath]

I am running into a issue where it looks like SQLAlchemy is not performing
the proper DB update when committing a session after modifying an object.
This happens only when all references to the updated object are lost, and I
update a mutable attribute BEFORE updating another attribute.  It seems as
if MutableAttrInstanceState.__resurrect is not properly resurrecting the
object state.

I have code that basically looks like this:

def update_my_model(session, my_model_id):
my_model = session.query(MyModel, id=my_model_id).one()
my_model.mutable_attribute.mutate()
my_model.normal_attribute = 42
return my_model

session = self.logic.session_maker()
update_my_model(session, my_model_id)
session.commit()

The above code does issues the SQL to update mutable_attribute, but not
normal_attribute.  Everything works if I move the mutable_attribute change
to after the normal_attribute change, remove it completely, or assign the
return value of the update_my_model call to a variable.

I have been able to determine that in that case that fails (and only in that
case) MutableAttrInstanceState._cleanup is being called as
update_my_modelreturns.  However, during the
session.commit(), the call to self.manager.new_instance(state=self) in
MutableAttrInstanceState.__resurrect is not returning an object that has
normal_attribute set.  From what I can tell the MutableAttrInstanceState
instance should know about the update to normal_attribute (
InstanceState.modified_event is being called when it is set).  But I'm not
sure of the inner-workings of MutableAttrInstanceState,so I haven't been
able to confirm this.

My mutable_attribute is rather complex, so it is entirely possible that it
is behaving badly in some why.  However, this seems like a strange failure
mode for an issue with that attribute?  Anyone have ideas on what is going
on here, or what my next steps should be to track it down?  If any
information I have not provided would be helpful, please let me know.

Thanks for any help!

  -Lenza

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.