Re: [sqlalchemy] StaleDataError on UPDATE for a DATETIME field on multiple consecutive requests
unless the row is being deleted, this would possibly be a driver bug. the
pymysql driver should work pretty well whereas mysql-connector I would stay
away from.I would need more detail to reproduce beyond that, the SQL
statement has nohting wrong with it.
On Fri, Sep 1, 2023, at 12:09 PM, Dumitru Gîra wrote:
> Hi Mike,
> thanks for the quick response.
>
> These would be the debug logs for
>
> import requests
> for i in range(2):
> r = requests.put('http://0.0.0.0:8080/update-datetime')
> print(r.text, r.code)
>
> 2023-09-01 16:06:20,711 INFO sqlalchemy.engine.Engine SELECT DATABASE()
> 2023-09-01 16:06:20,711 INFO sqlalchemy.engine.Engine [raw sql] ()
> 2023-09-01 16:06:20,713 DEBUG sqlalchemy.engine.Engine Col ('DATABASE()',)
> 2023-09-01 16:06:20,713 DEBUG sqlalchemy.engine.Engine Row ('dbname',)
> 2023-09-01 16:06:20,714 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
> 2023-09-01 16:06:20,714 INFO sqlalchemy.engine.Engine [raw sql] ()
> 2023-09-01 16:06:20,715 DEBUG sqlalchemy.engine.Engine Col ('@@sql_mode',)
> 2023-09-01 16:06:20,715 DEBUG sqlalchemy.engine.Engine Row
> ('ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',)
> 2023-09-01 16:06:20,716 INFO sqlalchemy.engine.Engine SELECT
> @@lower_case_table_names
> 2023-09-01 16:06:20,716 INFO sqlalchemy.engine.Engine [raw sql] ()
> 2023-09-01 16:06:20,717 DEBUG sqlalchemy.engine.Engine Col
> ('@@lower_case_table_names',)
> 2023-09-01 16:06:20,717 DEBUG sqlalchemy.engine.Engine Row (0,)
> 2023-09-01 16:06:20,717 INFO sqlalchemy.engine.Engine BEGIN (implicit)
> 2023-09-01 16:06:20,719 INFO sqlalchemy.engine.Engine SELECT foo.id AS
> foo_id, foo.last_login AS foo_last_login, foo.name AS foo_name
> FROM foo
> WHERE foo.id = %s
> LIMIT %s
> 2023-09-01 16:06:20,719 INFO sqlalchemy.engine.Engine [generated in 0.00016s]
> (1, 1)
> 2023-09-01 16:06:20,720 DEBUG sqlalchemy.engine.Engine Col ('foo_id',
> 'foo_last_login', 'foo_name')
> 2023-09-01 16:06:20,720 DEBUG sqlalchemy.engine.Engine Row (1,
> datetime.datetime(2023, 9, 1, 16, 6, 12), '2023-09-01 15:50:46.029445')
> 2023-09-01 16:06:20,722 INFO sqlalchemy.engine.Engine UPDATE foo SET
> last_login=%s WHERE foo.id = %s
> 2023-09-01 16:06:20,722 INFO sqlalchemy.engine.Engine [generated in 0.00014s]
> (datetime.datetime(2023, 9, 1, 16, 6, 20, 721146), 1)
> 2023-09-01 16:06:20,723 INFO sqlalchemy.engine.Engine COMMIT
> 192.168.0.1 - - [01/Sep/2023 16:06:20] "PUT /update-datetime HTTP/1.1" 200 -
> 2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine BEGIN (implicit)
> 2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine SELECT foo.id AS
> foo_id, foo.last_login AS foo_last_login, foo.name AS foo_name
> FROM foo
> WHERE foo.id = %s
> LIMIT %s
> 2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine [cached since 0.01402s
> ago] (1, 1)
> 2023-09-01 16:06:20,734 DEBUG sqlalchemy.engine.Engine Col ('foo_id',
> 'foo_last_login', 'foo_name')
> 2023-09-01 16:06:20,735 DEBUG sqlalchemy.engine.Engine Row (1,
> datetime.datetime(2023, 9, 1, 16, 6, 21), '2023-09-01 15:50:46.029445')
> 2023-09-01 16:06:20,736 INFO sqlalchemy.engine.Engine UPDATE foo SET
> last_login=%s WHERE foo.id = %s
> 2023-09-01 16:06:20,736 INFO sqlalchemy.engine.Engine [cached since 0.01399s
> ago] (datetime.datetime(2023, 9, 1, 16, 6, 20, 735704), 1)
> 2023-09-01 16:06:20,737 INFO sqlalchemy.engine.Engine ROLLBACK
> 192.168.0.1 - - [01/Sep/2023 16:06:20] "PUT /update-datetime HTTP/1.1" 500 -
> Traceback (most recent call last):
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2548, in
> __call__
> return self.wsgi_app(environ, start_response)
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2528, in
> wsgi_app
> response = self.handle_exception(e)
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2525, in
> wsgi_app
> response = self.full_dispatch_request()
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1822, in
> full_dispatch_request
> rv = self.handle_user_exception(e)
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1820, in
> full_dispatch_request
> rv = self.dispatch_request()
> File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1796, in
> dispatch_request
> return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
> File "/usr/src/app/sqa.py", line 69, in update_datetime
> db_session.commit()
> File "", line 2, in commit
> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 1454, in commit
> self._transaction.commit(_to_root=self.future)
> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 832, in commit
> self._prepare_impl()
> File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 811, in _prepare_impl
> self.session.flush()
> File
Re: [sqlalchemy] StaleDataError on UPDATE for a DATETIME field on multiple consecutive requests
Hi Mike,
thanks for the quick response.
These would be the debug logs for
import requests
for i in range(2):
r = requests.put('http://0.0.0.0:8080/update-datetime')
print(r.text, r.code)
2023-09-01 16:06:20,711 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-09-01 16:06:20,711 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-09-01 16:06:20,713 DEBUG sqlalchemy.engine.Engine Col ('DATABASE()',)
2023-09-01 16:06:20,713 DEBUG sqlalchemy.engine.Engine Row ('dbname',)
2023-09-01 16:06:20,714 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-09-01 16:06:20,714 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-09-01 16:06:20,715 DEBUG sqlalchemy.engine.Engine Col ('@@sql_mode',)
2023-09-01 16:06:20,715 DEBUG sqlalchemy.engine.Engine Row
('ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',)
2023-09-01 16:06:20,716 INFO sqlalchemy.engine.Engine SELECT
@@lower_case_table_names
2023-09-01 16:06:20,716 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-09-01 16:06:20,717 DEBUG sqlalchemy.engine.Engine Col
('@@lower_case_table_names',)
2023-09-01 16:06:20,717 DEBUG sqlalchemy.engine.Engine Row (0,)
2023-09-01 16:06:20,717 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-01 16:06:20,719 INFO sqlalchemy.engine.Engine SELECT foo.id AS
foo_id, foo.last_login AS foo_last_login, foo.name AS foo_name
FROM foo
WHERE foo.id = %s
LIMIT %s
2023-09-01 16:06:20,719 INFO sqlalchemy.engine.Engine [generated in
0.00016s] (1, 1)
2023-09-01 16:06:20,720 DEBUG sqlalchemy.engine.Engine Col ('foo_id',
'foo_last_login', 'foo_name')
2023-09-01 16:06:20,720 DEBUG sqlalchemy.engine.Engine Row (1,
datetime.datetime(2023, 9, 1, 16, 6, 12), '2023-09-01 15:50:46.029445')
2023-09-01 16:06:20,722 INFO sqlalchemy.engine.Engine UPDATE foo SET
last_login=%s WHERE foo.id = %s
2023-09-01 16:06:20,722 INFO sqlalchemy.engine.Engine [generated in
0.00014s] (datetime.datetime(2023, 9, 1, 16, 6, 20, 721146), 1)
2023-09-01 16:06:20,723 INFO sqlalchemy.engine.Engine COMMIT
192.168.0.1 - - [01/Sep/2023 16:06:20] "PUT /update-datetime HTTP/1.1" 200 -
2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine SELECT foo.id AS
foo_id, foo.last_login AS foo_last_login, foo.name AS foo_name
FROM foo
WHERE foo.id = %s
LIMIT %s
2023-09-01 16:06:20,733 INFO sqlalchemy.engine.Engine [cached since
0.01402s ago] (1, 1)
2023-09-01 16:06:20,734 DEBUG sqlalchemy.engine.Engine Col ('foo_id',
'foo_last_login', 'foo_name')
2023-09-01 16:06:20,735 DEBUG sqlalchemy.engine.Engine Row (1,
datetime.datetime(2023, 9, 1, 16, 6, 21), '2023-09-01 15:50:46.029445')
2023-09-01 16:06:20,736 INFO sqlalchemy.engine.Engine UPDATE foo SET
last_login=%s WHERE foo.id = %s
2023-09-01 16:06:20,736 INFO sqlalchemy.engine.Engine [cached since
0.01399s ago] (datetime.datetime(2023, 9, 1, 16, 6, 20, 735704), 1)
2023-09-01 16:06:20,737 INFO sqlalchemy.engine.Engine ROLLBACK
192.168.0.1 - - [01/Sep/2023 16:06:20] "PUT /update-datetime HTTP/1.1" 500 -
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2548, in
__call__
return self.wsgi_app(environ, start_response)
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2528, in
wsgi_app
response = self.handle_exception(e)
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2525, in
wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1822, in
full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1820, in
full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1796, in
dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/usr/src/app/sqa.py", line 69, in update_datetime
db_session.commit()
File "", line 2, in commit
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 1454, in commit
self._transaction.commit(_to_root=self.future)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 832, in commit
self._prepare_impl()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 811, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 3449, in flush
self._flush(objects)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
line 3589, in _flush
transaction.rollback(_capture_exception=True)
File
"/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
line 211, in raise_
raise exception
File
Re: [sqlalchemy] StaleDataError on UPDATE for a DATETIME field on multiple consecutive requests
a stale data error with date/time fields is usually a mismatch between the
date/time value you have locally and the one that's in the database,. assuming
you are using the datetime column as a "version_id" in your mapping. Issues
like strings vs. datetimes and/or microseconds portions being present or
non-present are the most common.
would need to see SQL debug logging to have more insight / confirm that's the
problem.
On Fri, Sep 1, 2023, at 11:59 AM, Dumitru Gîra wrote:
> I have encountered a very strange behaviour and I don't know where to look
> further.
> I have a small API https://pastebin.com/qW01jNz8 with 2 endpoints: one to
> update the name (VARCHAR) and one to update the last_login (DATETIME).
>
> If I make 10 consecutive requests to /update-string - everything works as
> expected.
> If I make 10 consecutive requests to /update-datetime - the first request
> works and other 9 fail with sqlalchemy.orm.exc.StaleDataError.
> NOTE: putting a time.sleep(1) after every request does not raise the error
>
> Consecutive requests are being made using the simple snippet
> import requests
> for i in range(10):
> r = requests.put('http://0.0.0.0:8080/update-datetime')
> print(r.text, r.code)
>
> The source code and the logs are in above link and for the reference I am
> using:
> - MySQL 8.0, but the same happens with 5.7
> - mysqldb connector, but the same happens with pymysql
> - SQLAlchemy==1.4.49
>
> Is this related to MySQL and specifically to date/time related fields (as the
> same happens with TIMESTAMP) or can it be something from SQLAlchemy side?
> Any directions would be highly appreciated.
>
> Kind regards,
> Dumitru
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e53169bd-617f-41f3-b9d3-8002ea75496an%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8ddfacc0-e23b-4672-b05f-92b2517d830a%40app.fastmail.com.
[sqlalchemy] StaleDataError on UPDATE for a DATETIME field on multiple consecutive requests
I have encountered a very strange behaviour and I don't know where to look
further.
I have a small API https://pastebin.com/qW01jNz8 with 2 endpoints: one to
update the name (VARCHAR) and one to update the last_login (DATETIME).
If I make 10 consecutive requests to /update-string - everything works as
expected.
If I make 10 consecutive requests to /update-datetime - the first request
works and other 9 fail with sqlalchemy.orm.exc.StaleDataError.
NOTE: putting a time.sleep(1) after every request does not raise the error
Consecutive requests are being made using the simple snippet
import requests
for i in range(10):
r = requests.put('http://0.0.0.0:8080/update-datetime')
print(r.text, r.code)
The source code and the logs are in above link and for the reference I am
using:
- MySQL 8.0, but the same happens with 5.7
- mysqldb connector, but the same happens with pymysql
- SQLAlchemy==1.4.49
Is this related to MySQL and specifically to date/time related fields (as
the same happens with TIMESTAMP) or can it be something from SQLAlchemy
side?
Any directions would be highly appreciated.
Kind regards,
Dumitru
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e53169bd-617f-41f3-b9d3-8002ea75496an%40googlegroups.com.
