Getting an asyncpg naive/aware datetime subtraction issue

[StephixOne]

Hi,

I decided to fork and run this for my company and everything seems in order until I try to connect my Slack, as a user. Basically, this gets thrown:

Traceback (most recent call last):
backend_1   |   File "/usr/local/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 385, in run_asgi
backend_1   |     result = await app(self.scope, self.receive, self.send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
backend_1   |     return await self.app(scope, receive, send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/fastapi/applications.py", line 140, in __call__
backend_1   |     await super().__call__(scope, receive, send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/applications.py", line 134, in __call__
backend_1   |     await self.error_middleware(scope, receive, send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 178, in __call__
backend_1   |     raise exc from None
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 156, in __call__
backend_1   |     await self.app(scope, receive, _send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/middleware/sessions.py", line 75, in __call__
backend_1   |     await self.app(scope, receive, send_wrapper)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 73, in __call__
backend_1   |     raise exc from None
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 62, in __call__
backend_1   |     await self.app(scope, receive, sender)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 590, in __call__
backend_1   |     await route(scope, receive, send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 208, in __call__
backend_1   |     await self.app(scope, receive, send)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 41, in app
backend_1   |     response = await func(request)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 133, in app
backend_1   |     raw_response = await dependant.call(**values)
backend_1   |   File "/spotify-my-slack/backend/routers/slack.py", line 62, in slack_grant_callback
backend_1   |     await sign_in(request)
backend_1   |   File "/spotify-my-slack/backend/utils/auth.py", line 18, in sign_in
backend_1   |     user, created = await get_or_create_from_session(session=request.session)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/databases/core.py", line 321, in wrapper
backend_1   |     return await func(*args, **kwargs)
backend_1   |   File "/spotify-my-slack/backend/database/users.py", line 76, in get_or_create_from_session
backend_1   |     user = await User.objects.create(
backend_1   |   File "/usr/local/lib/python3.8/site-packages/orm/models.py", line 237, in create
backend_1   |     instance.pk = await self.database.execute(expr)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/databases/core.py", line 152, in execute
backend_1   |     return await connection.execute(query, values)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/databases/core.py", line 240, in execute
backend_1   |     return await self._connection.execute(self._build_query(query, values))
backend_1   |   File "/usr/local/lib/python3.8/site-packages/databases/backends/postgres.py", line 158, in execute
backend_1   |     return await self._connection.fetchval(query, *args)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 438, in fetchval
backend_1   |     data = await self._execute(query, args, 1, timeout)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1402, in _execute
backend_1   |     result, _ = await self.__execute(
backend_1   |   File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1411, in __execute
backend_1   |     return await self._do_execute(query, executor, timeout)
backend_1   |   File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1433, in _do_execute
backend_1   |     result = await executor(stmt, None)
backend_1   |   File "asyncpg/protocol/protocol.pyx", line 178, in bind_execute
backend_1   |   File "asyncpg/protocol/prepared_stmt.pyx", line 160, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
backend_1   | asyncpg.exceptions.DataError: invalid input for query argument $1: datetime.datetime(2020, 2, 26, 15, 13, 6... (can't subtract offset-naive and offset-aware datetimes)

I've tried to make all the datetime objects in the code timezone-unaware, to see if anything odd was going on there, but that didn't help anything. I realize you're no longer maintaining this, but I thought I'd ask, just in case it's something you've already encountered and had a minute to answer. Thanks !

[micthiesen]

Hey, the app is built to expect timezone-aware datetimes everywhere. Maybe the datetimes you have in the database are not timezone-aware? I would go in the direction of trying to make everything timezone-aware rather than timezone-unaware.

Note that I was in the middle of refactoring the backend (from Node.js to Python) when I stopped working on the project. So there may be bugs, in the update loop for example. There are also no migrations for the database right now, so you'd have to either set up Alembic (which I want to do eventually) or create the initial tables manually.

One other approach, if you just want to get something working, would be to check out a version of the repository that was still using Node.js for the backend. That version has migrations and should be more robust (in certain ways). You can probably see when I started refactoring the backend fairly easily in the git history. Good luck!

[StephixOne]

Thanks for the reply! I got around the no migration issue by using scripts/shell_backend.sh, importing engine from sqlalchemy then doing a METADATA.create_all(engine) which seemed to give me the User table. However, I'm now thinking that maybe this method created improperly-timezone-aware fields, so I'll have a look into that. If not, will checkout a commit from before the move to Python and try and get that going.

[StephixOne]

Posting what the issue was in case other people will be looking to host their own and run into a similar thing or attempt the same things. As I was suspecting above, my method of using sqlalchemy to create the table did not make the timestamp columns tz-aware. Dropped the table created that way, used the db connection script in /scripts and created one with this shape:

CREATE TABLE "Users"(
id serial PRIMARY KEY,
"slackId" VARCHAR(255) NOT NULL,
"slackAccessToken" VARCHAR(255) NOT NULL,
"spotifyId" VARCHAR(255) NOT NULL,
"spotifyExpiresAt" TIMESTAMPTZ NOT NULL,
"spotifyAccessToken" VARCHAR(255) NOT NULL,
"spotifyRefreshToken" VARCHAR(255),
"createdAt" TIMESTAMPTZ NOT NULL,
"updatedAt" TIMESTAMPTZ NOT NULL,
"statusSetLastTime" BOOLEAN DEFAULT FALSE,
"useCustomEmojis" BOOLEAN DEFAULT TRUE);

Everything seems in order now ! 👍

[ksession]

@StephixOne Are you able to help me possibly set this up? If not, no worries! I'm trying to do it similarly for my company.

[StephixOne]

@ksession Could you tell me where you're getting stuck? I'm happy to help if I can, but this might be easier over some form of IM. The handiest one I have is Discord: Stephix#0001

[ksession]

Honestly from the beginning would be helpful - I was given this project by my team and we are using a script currently but would love to switch to the website form.

…

________________________________ From: Rares Stefan <[email protected]> Sent: Tuesday, April 7, 2020 11:28 AM To: micthiesen/spotify-my-slack <[email protected]> Cc: ksession <[email protected]>; Mention <[email protected]> Subject: Re: [micthiesen/spotify-my-slack] Getting an asyncpg naive/aware datetime subtraction issue (#20) @ksession<https://github.com/ksession> Could you tell me where you're getting stuck? I'm happy to help if I can, but this might be easier over some form of IM. The handiest one I have is Discord: Stephix#0001 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#20 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABAH4ESCECUU336CKLXDZALRLNPFTANCNFSM4K4IC6LQ>.