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.4444444444444444);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE sqlalchemy_test_my_model SET float_value=0.4444444444444444
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.com>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.44444444444444442" not
> "0.4444444444444444". 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.4444444444444444444
> 0.44444444444444442
> >>>
> $ 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.44444444444444442,)
> 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.44444444444444442, 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.com>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 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.4444444444444444,)
>> 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.4444444444444444, 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 <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.com>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<sqlalchemy%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.
>>
>>
>>
>> --
>> 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<sqlalchemy%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.
>
>
> --
> 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<sqlalchemy%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.
