Re: [sqlalchemy] Avoiding StaleDataError when updating Float values
On Nov 5, 2010, at 1:04 PM, Lenza McElrath wrote: > This is happening using MySQLdb 1.2.2 and MySQL server 5.0.87. Upgrading is > not really a viable option. Wasn't asking you to upgrade. Only to try a different version of the software in an attempt to isolate the issue. > > 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: That's not how "updated rowcount" usually works. The standard meaning of "rowcount" is how many rows *matched* the update statement, not how many were actually modified. MySQL in fact has two different modes for "rowcount", and it usually defaults to the latter, however its very possible that different default behaviors across MySQL/MySQLdb versions is why your test works for me and not for you. SQLAlchemy sets this on the MySQLdb DBAPI using the CLIENT_FLAGS.FOUND_ROWS bitmask so that it uses the former. For some reason, whenI use your test verbatim, the FOUND ROWS behavior works consistently with the behavior that SQLA usually sets up, even though your test isn't actually configuring it correctly. This is why some differing of default behavior is suggested. so do this: from MySQLdb.constants import CLIENT as CLIENT_FLAGS client_flag = CLIENT_FLAGS.FOUND_ROWS conn_getter = lambda: MySQLdb.connect(db='test', user='scott', host='localhost', passwd='tiger', client_flag=client_flag) > > 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: but that's not a "fix".There are other scenarios where an UPDATE statement may be emitted redundantly, such as if you set the value of an attribute that was expired. SQLA in the simple case chooses not to expensively load the "old" value of an attribute when a change event is received, if the previous value wasn't present. You'd get the same issue in that case. -- 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
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 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) >
Re: [sqlalchemy] Avoiding StaleDataError when updating Float values
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 > (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/
Re: [sqlalchemy] Avoiding StaleDataError when updating Float values
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'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 I
Re: [sqlalchemy] Avoiding StaleDataError when updating Float values
On Nov 5, 2010, at 1:33 AM, 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.
The only cause for StaleDataError is if a flush() attempts to UPDATE the row
indicated by "self.row_id", and that row is no longer present such that it
returns an unexpected rowcount for the statement. This can only occur if you
have some other thread, process, or non-ORM instruction which deletes or
changes the primary key of the target row in between when you loaded the object
and when you flushed it. If changing the Float precision is the trigger, then
that change is causing something else to occur in your real application which
causes the primary key to not be present.MySQL is an extremely buggy
database so its well within the realm of possibility that using the too-large
float value is causing some incongruous condition to occur as well.If this
were the case, the key to isolating such issues with MySQL is to create a SQL
script that reproduces the issue, i.e. that the ultimate UPDATE statement
doesn't update the row as expected. The echo output of your application and
test script is a good place to start.
--
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
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 sqlalchemy.engine.base.Engine.0x...e610 SELECT
DATABASE()
2010-11-05 03:24:49,463 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,465 INFO sqlalchemy.engine.base.Engine.0x...e610 SHOW
VARIABLES LIKE 'character_set%%'
2010-11-05 03:24:49,465 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,465 INFO sqlalchemy.engine.base.Engine.0x...e610 SHOW
VARIABLES LIKE 'lower_case_table_names'
2010-11-05 03:24:49,466 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,466 INFO sqlalchemy.engine.base.Engine.0x...e610 SHOW
COLLATION
2010-11-05 03:24:49,466 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,469 INFO sqlalchemy.engine.base.Engine.0x...e610 SHOW
VARIABLES LIKE 'sql_mode'
2010-11-05 03:24:49,469 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,470 INFO sqlalchemy.engine.base.Engine.0x...e610
CREATE TABLE sqlalchemy_test_my_model (
id INTEGER NOT NULL AUTO_INCREMENT,
float_value FLOAT,
PRIMARY KEY (id)
)ENGINE=InnoDB
2010-11-05 03:24:49,470 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,473 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
2010-11-05 03:24:49,474 INFO sqlalchemy.engine.base.Engine.0x...e610 BEGIN
(implicit)
2010-11-05 03:24:49,475 INFO sqlalchemy.engine.base.Engine.0x...e610 INSERT
INTO sqlalchemy_test_my_model (float_value) VALUES (%s)
2010-11-05 03:24:49,475 INFO sqlalchemy.engine.base.Engine.0x...e610
(0.,)
2010-11-05 03:24:49,476 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
2010-11-05 03:24:49,477 INFO sqlalchemy.engine.base.Engine.0x...e610 BEGIN
(implicit)
2010-11-05 03:24:49,477 INFO sqlalchemy.engine.base.Engine.0x...e610 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 03:24:49,477 INFO sqlalchemy.engine.base.Engine.0x...e610 (1L,)
2010-11-05 03:24:49,479 INFO sqlalchemy.engine.base.Engine.0x...e610 UPDATE
sqlalchemy_test_my_model SET float_value=%s WHERE sqlalchemy_test_my_model.id =
%s
2010-11-05 03:24:49,479 INFO sqlalchemy.engine.base.Engine.0x...e610
(0., 1L)
2010-11-05 03:24:49,479 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
2010-11-05 03:24:49,480 INFO sqlalchemy.engine.base.Engine.0x...e610
DROP TABLE sqlalchemy_test_my_model
2010-11-05 03:24:49,480 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
2010-11-05 03:24:49,482 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
>
> 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
> "sqlalchem
Re: [sqlalchemy] Avoiding StaleDataError when updating Float values
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
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
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.
[sqlalchemy] Avoiding StaleDataError when updating Float values
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.
