Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

bug in version 1.3.13: sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type bigint: #316

Closed
xg1990 opened this issue Oct 31, 2022 · 4 comments
Closed

bug in version 1.3.13: sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type bigint: #316

xg1990 opened this issue Oct 31, 2022 · 4 comments

Comments

@xg1990
Copy link

xg1990 commented Oct 31, 2022

The problem described below only happens with SQLAlchemy-Continuum-1.3.13

Error Stack:

src-app-1       | Traceback (most recent call last):
src-app-1       |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
src-app-1       |     self.dialect.do_execute(
src-app-1       |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
src-app-1       |     cursor.execute(statement, parameters)
src-app-1       | psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type bigint: "gAAAAABjX7Cl5RBwK******"
src-app-1       | LINE 1: ...amp, nextval('transaction_id_seq'), '172.20.0.5', 'gAAAAABjX...
src-app-1       |                                                              ^
src-app-1       |

The above exception was the direct cause of the following exception:

File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.9/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.9/site-packages/flask_user/decorators.py", line 143, in decorator
    return view_function(*args, **kwargs)
  File "/app/app/views/*******", line 96, in ******
    db.session.commit()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2569, in _flush
    self.dispatch.before_flush(self, flush_context, objects)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 261, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy_continuum/manager.py", line 340, in before_flush
    uow.process_before_flush(session)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy_continuum/unit_of_work.py", line 71, in process_before_flush
    self.create_transaction(session)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy_continuum/unit_of_work.py", line 121, in create_transaction
    self.version_session.flush()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type bigint: "gAAAAABjX2*******"
@xg1990
Copy link
Author

xg1990 commented Oct 31, 2022

DEBUG log

src-db-1        | 2022-10-31 11:25:25.107 UTC [52] ERROR:  invalid input syntax for type bigint: "gAAAAABjX******" at character 157
src-db-1        | 2022-10-31 11:25:25.107 UTC [52] STATEMENT:  INSERT INTO transaction (issued_at, id, remote_addr, user_id) VALUES ('2022-10-31T11:25:25.106651'::timestamp, nextval('transaction_id_seq'), '172.20.0.5', 'gAAAAABjX7C******') RETURNING transaction.id

a string type value is set for user_id

@xg1990
Copy link
Author

xg1990 commented Oct 31, 2022

problem is caused by 7eda527 & #149

xg1990 added a commit to xg1990/sqlalchemy-continuum that referenced this issue Oct 31, 2022
@xg1990
Update CHANGES.rst to address problems in kvesteri#316
a5f4d39 
kvesteri#316
@xg1990 xg1990 mentioned this issue Oct 31, 2022
Closed
@anthraxx
Copy link

anthraxx commented Jan 3, 2023

This is exactly the issue I have summarized in #149 (comment)

@marksteward please revert this commit 🐈

@anthraxx anthraxx mentioned this issue Jan 3, 2023
Closed
anthraxx added a commit to anthraxx/sqlalchemy-continuum that referenced this issue Jan 3, 2023
@anthraxx
Revert "FlaskPlugin: use get_id() instead of id attr"
543b398 
This reverts commit 7eda527.

This was actually a misinterpretation of the API:

The API expects get_id() to be a unique string, that may or may not be
an integer. This feature is only used to uniquely identify the user, but
its not the same as the user's primary key.
https://flask-login.readthedocs.io/en/latest/#your-user-class

Please take a note on how get_id() is expected to behave on this feature
of flask-login:
https://flask-login.readthedocs.io/en/latest/#alternative-tokens

This means it can return any arbitrary string to identify the user which
can be swapped to any other value to invalidate all sessions. This
explicitly states that it is not the same as the user's primary id which
is also used as foreign key on the table.

sqlalchemy-continuum's transaction tables reference the user with a
foreign key of the actual id. If a downstream application uses the
alternative-tokens feature as described by the flask-login
documentation, this breaks horribly as sqlalchemy-continuum will then
try to insert an arbitrary unique string of the user as the foreign key
to reference the user in the user table.

Fixes kvesteri#316
Caused by kvesteri#149
marksteward added a commit that referenced this issue Jan 4, 2023
@marksteward
Revert get_id change from 8eda527 (#325)
6c4dfc2 
This was a misreading of the contract, and would result in errors
using alternative IDs as described in the Flask-Login docs (#316).
@marksteward
Copy link
Collaborator

Fixed in 1.3.14, sorry for not picking this up before!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Revert "FlaskPlugin: use get_id() instead of id attr" anthraxx/sqlalchemy-continuum
3 participants
@marksteward @anthraxx @xg1990