Re: [sqlalchemy] Deadlock in iterating over a session

[Lenza McElrath]

On Sun, Mar 6, 2011 at 5:21 PM, Michael Bayer wrote:

>
> 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]

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 wrote:

>
> 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]

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

On Fri, Mar 4, 2011 at 7:32 AM, Michael Bayer wrote:

>
> 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 the

[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  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.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] Specifying a type for primary key results in strange behaviors

[Lenza McElrath]

I am writing a system where I want my DB primary keys to be represented in
the system by a type other than an integer.  I have run into several issues
with this:

1) I tried using a TypeDecorator, but SQLAlchemy does not like this.  Seems
like the main problem is the auto_increment is not set on the column when
creating the table, but I think I ran into other issues even after making
the table by hand.  This issue was discussed here:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/4b13261da8c4c932/d0b84b4c14a6645e.
However, specifying a Sequence does not solve the problem for me (I'm using
MySQL).

So, I resorted to using a straight Type, and have a couple of other
problems:

2) After session.flush() the ID is an integer, not the type returned from my
result processor.  After a query the types are correct.

3) When doing queries, filters on the ID are not passed through the bind
process, so they return no results. This broke after 0.6beta1.

I have created a test case the illustrates all of these issues here:
http://pastebin.com/CnAmptck

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.

[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]

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 wrote:

>
> 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 sqlalc

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 
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 wrote:

>
> On Nov 5, 2010, at 2:51 AM, Lenza McElrath wrote:
>
> Well, turns out that it doesn&#

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  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 wrote:
>
>>
>> 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.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]

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 wrote:

>
> 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.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] 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] 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 wrote:

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