竞技场查询功能查询失败

Rinco304 commented 1 year ago

看起来像是创建的sql语句有一些问题除此功能外的其他功能正常使用系统：WinServer2012R2 Python：3.8.2 aiosqlite：0.19.0 sqlmodel：0.0.8

[2023-07-25 11:25:06,301] INFO in msghandler: Message 917296609 triggered on_query_arena.
[2023-07-25 11:25:06,301 nonebot] INFO: Message 917296609 is ignored: Handled by Hoshino
[2023-07-25 11:25:06,301] INFO in message: Message 917296609 is ignored: Handled by Hoshino
Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 1900, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", l
ine 736, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 100, in execute
    self._adapt_connection._handle_exception(error)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 228, in _handle_exception
    raise error
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 82, in execute
    self.await_(_cursor.execute(operation, parameters))
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\_concurrency_py3
k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\_concurrency_py3
k.py", line 121, in greenlet_spawn
    value = await result
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\cursor.py", line 48, i
n execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\cursor.py", line 40, i
n _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 133, in
 _execute
    return await future
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 106, in
 run
    result = function()
sqlite3.OperationalError: near "AS": syntax error

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

Traceback (most recent call last):
  File "C:\QQbot\bot\HoshinoBot\hoshino\modules\pcrjjc_huannai2\utils.py", line 110, in user_query
    jjc_up, grand_jjc_up = await pcr_sqla.get_up_num(platfrom, pcrid, int(datetime.now().timestamp()))
  File "C:\QQbot\bot\HoshinoBot\hoshino\modules\pcrjjc_huannai2\database\dal.py", line 135, in get_up_num
    result = await session.execute(sql)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\ext\asyncio\session.p
y", line 215, in execute
    result = await greenlet_spawn(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\_concurrency_py3
k.py", line 126, in greenlet_spawn
    result = context.throw(*sys.exc_info())
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\orm\session.py", line
 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\sql\elements.py", lin
e 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 1943, in _execute_context
    self._handle_dbapi_exception(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 2124, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\compat.py", line
 208, in raise_
    raise exception
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\base.py", line
 1900, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\engine\default.py", l
ine 736, in do_execute
    cursor.execute(statement, parameters)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 100, in execute
    self._adapt_connection._handle_exception(error)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 228, in _handle_exception
    raise error
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\dialects\sqlite\aiosq
lite.py", line 82, in execute
    self.await_(_cursor.execute(operation, parameters))
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\_concurrency_py3
k.py", line 68, in await_only
    return current.driver.switch(awaitable)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\sqlalchemy\util\_concurrency_py3
k.py", line 121, in greenlet_spawn
    value = await result
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\cursor.py", line 48, i
n execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\cursor.py", line 40, i
n _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 133, in
 _execute
    return await future
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\aiosqlite\core.py", line 106, in
 run
    result = function()
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "AS": syntax error
[SQL: SELECT count(*) FILTER (WHERE jjchistory.item = ?) AS anon_1, count(*) FILTER (WHERE jjchistory.item = ?) AS anon_
2
FROM jjchistory
WHERE jjchistory.pcrid = ? AND jjchistory.date < ? AND jjchistory.date > ? AND jjchistory.platform = ? AND jjchistory."b
efore" > jjchistory."after"]
[parameters: (0, 1, 1036165931572, 1690318800.0, 1690232400.0, 0)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
[2023-07-25 11:25:06,833 nonebot] ERROR: None
[2023-07-25 11:25:06,833] ERROR in utils: None

SonderXiaoming commented 1 year ago

orm理论上应该不会出现这个问题，是否有其他插件冲突，或者是版本问题

SonderXiaoming commented 1 year ago

aiosqlite 0.17.0，SQLAlchemy 1.4.41降级试试

Rinco304 commented 1 year ago

aiosqlite 0.17.0，SQLAlchemy 1.4.41降级试试

经降级尝试和清空绑定后重新绑定测试了还是报同样的错，其他插件冲突暂时没找出来，总感觉不应该会产生冲突（这是我使用到的有关此项目的包版本

msgpack               1.0.4
nonebot               1.8.4
Pillow                9.1.0
pycryptodome          3.14.1
python-dateutil       2.8.2
requests              2.27.1
zhconv                1.4.3
Jinja2                3.0.3
quart                 0.18.3
SQLAlchemy            1.4.41

httpcore              0.16.3
httpx                 0.23.3
新安装
sqlmodel              0.0.8
aiosqlite             0.17.0

除这个功能无法使用外发现了一个新问题：插件经常连接超时导致整个bot卡死一分钟无响应(几分钟复现一次)，在此期间无法接收任何消息，然后抛出了下面的错误，更新httpcore和httpx仍然无效，在安装此插件前从未出现过此情况总不能是服务器被玩坏了吧，腾讯云的话应该不至于（

[2023-08-01 11:29:13,619 nonebot] INFO: 竞技场推送结束，用时0
[2023-08-01 11:29:13,619 nonebot] INFO: 竞技场推送开始
[2023-08-01 11:30:17,173] INFO in utils: 竞技场推送结束，用时62
[2023-08-01 11:30:17,173] INFO in utils: 竞技场推送开始
Traceback (most recent call last):
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_transports\default.py", l
ine 60, in map_httpcore_exceptions
    yield
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_transports\default.py", l
ine 353, in handle_async_request
    resp = await self._pool.handle_async_request(req)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\connection_pool.
py", line 253, in handle_async_request
    raise exc
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\connection_pool.
py", line 237, in handle_async_request
    response = await connection.handle_async_request(request)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\connection.py",
line 90, in handle_async_request
    return await self._connection.handle_async_request(request)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\http11.py", line
 112, in handle_async_request
    raise exc
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\http11.py", line
 91, in handle_async_request
    ) = await self._receive_response_headers(**kwargs)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\http11.py", line
 155, in _receive_response_headers
    event = await self._receive_event(timeout=timeout)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_async\http11.py", line
 191, in _receive_event
    data = await self._network_stream.read(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\backends\asyncio.py", l
ine 36, in read
    return b""
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpcore\_exceptions.py", line 1
4, in map_exceptions
    raise to_exc(exc)
httpcore.ReadTimeout

[2023-08-01 11:30:18,517] INFO in query: zvxxx1bV查询1177xxx03594失败

[2023-08-01 11:30:18,517] INFO in query: Traceback (most recent call last):
  File "C:\QQbot\bot\HoshinoBot\hoshino\modules\pcrjjc_huannai2\client\pcrclient.py", line 120, in callapi
    response = (await self.client.post(get_api_root(self.bsdk.qudao) + apiurl, data=pcrclient.pack(request, key) if cryp
ted else str(request).encode('utf8'), headers=self.headers, timeout=20)).content
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1848, in
 post
    return await self.request(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1533, in
 request
    return await self.send(request, auth=auth, follow_redirects=follow_redirects)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1620, in
 send
    response = await self._send_handling_auth(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1648, in
 _send_handling_auth
    response = await self._send_handling_redirects(
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1685, in
 _send_handling_redirects
    response = await self._send_single_request(request)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_client.py", line 1722, in
 _send_single_request
    response = await transport.handle_async_request(request)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_transports\default.py", l
ine 353, in handle_async_request
    resp = await self._pool.handle_async_request(req)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python38\lib\site-packages\httpx\_transports\default.py", l
ine 77, in map_httpcore_exceptions
    raise mapped_exc(message) from exc
httpx.ReadTimeout

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\QQbot\bot\HoshinoBot\hoshino\modules\pcrjjc_huannai2\query.py", line 29, in _query
    res = await client.callapi('/profile/get_profile', {'target_viewer_id': uid})
  File "C:\QQbot\bot\HoshinoBot\hoshino\modules\pcrjjc_huannai2\client\pcrclient.py", line 144, in callapi
    raise ApiException("未知错误" + str(e), 501)
hoshino.modules.pcrjjc_huannai2.client.pcrclient.ApiException: 未知错误

[2023-08-01 11:42:51,338] INFO in query: 重试：zvxxx1bV 对于 116785xxx9192 的查询请求超时或不匹配。

SonderXiaoming commented 1 year ago

Timeout可还行，你自己电脑跑怎么样，还有就是你查询账号几个，又有多少人绑你这个竞技场

Rinco304 commented 1 year ago

Timeout可还行，你自己电脑跑怎么样，还有就是你查询账号几个，又有多少人绑你这个竞技场

早上自己电脑上跑了半天啥毛病没有，放服务器上就出事，关了代理都一样，感觉像服务器被玩坏了（悲），现在回去用huannai1将就一下（好像还是有卡死超时问题，但不报错也没有2这么严重）等国庆换个服务器全部重新配置罢

SonderXiaoming / pcrjjc_huannai2

竞技场查询功能查询失败 #5