search

bot-ssttkkl / nonebot-plugin-pixivbot

NoneBot插件，发送随机Pixiv插画、画师更新推送、定时订阅推送……
MIT License
111 stars 2 forks source link

sqlite下来张图功能出现内部错误：<class 'sqlalchemy.exc.OperationalError'> #141

Closed JvJvless closed 1 year ago

JvJvless commented 1 year ago

插件使用时出现异常（首次运行），请问可能是哪方面的问题呢？

nonebot2运行于阿里云2核2G上，系统镜像为ubuntu_18_04_x64_20G_alibase_20210420 python环境为 3.9.9 (main, Jan 11 2023, 13:36:57) [GCC 7.5.0] on linux 代理使用clash，插件配置中使用socket5连接 http代理也试过，同样报错。测试代理下可以curl通pixiv 插件通过nbcli安装，plugins = ["nonebot_plugin_access_control", "nonebot_plugin_pixivbot"]

测试来张图功能的时候出现了内部错误

内部错误：<class 'sqlalchemy.exc.OperationalError'>(sqlite3.OperationalError) near "ON": syntax error
[SQL: INSERT INTO illust_set_cache_illust (cache_id, illust_id, rank) VALUES (?, ?, ?) ON CONFLICT (cache_id, illust_id) DO NOTHING]
[parameters: (1, 85338970, 0)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

后台日志如下（pm2）

3|bot_test  | 02-24 16:39:12 [SUCCESS] nonebot_plugin_pixivbot | refresh access token successfully. new token expires in 3600 seconds.
3|bot_test  | 02-24 16:39:25 [SUCCESS] nonebot | OneBot V11 ********** | [message.private.friend]: Message 113049793 from ********* "来张图"
3|bot_test  | 02-24 16:39:25 [INFO] nonebot | Event will be handled by Matcher(type='message', module=nonebot_plugin_pixivbot.handler.common.random_recommended_illust)
3|bot_test  | 02-24 16:39:25 [INFO] nonebot_plugin_pixivbot | [mediator] recommended_illusts cache_strategy=NORMAL
3|bot_test  | 02-24 16:39:25 [INFO] nonebot_plugin_pixivbot | [local] recommended_illusts
3|bot_test  | 02-24 16:39:25 [INFO] nonebot_plugin_pixivbot | [mediator] no cache
3|bot_test  | 02-24 16:39:25 [INFO] nonebot_plugin_pixivbot | [remote] recommended_illusts
3|bot_test  | 02-24 16:39:29 [INFO] nonebot_plugin_pixivbot | [local] append recommended_illusts (89 items) (update_time=2023-02-24 08:39:29.467578+00:00, pages=1, next_qs={'content_type': 'illust', 'filter': 'for_ios', 'include_ranking_label': 'true', 'min_bookmark_id_for_recent_illust': '19409055318', 'max_bookmark_id_for_recommend': '19292895144', 'offset': '0', 'include_ranking_illusts': 'false', 'include_privacy_policy': 'false', 'viewed': ['74902982', '103070297', '105565310', '77202252', '93287605', '90724362', '95927502', '93508362', '75691856', '98214912', '75426467', '78253736', '93355299', '98366214', '97972911', '74641348', '104564463', '105353565', '79895186', '99827857', '87740032', '25950227', '100214821', '87543808', '74670640', '95725261', '54330336', '95210012', '105538757', '96352877', '58099279', '75148244', '97736256', '65456653', '73424709', '87135864', '85742450', '96777964', '74938719', '105503743', '95913659', '77042494', '104058515', '105469182', '73372039', '76101179', '90896936', '43796605', '105555300', '86849255', '95370037', '95625131', '97086274', '74072598', '81300641', '85852984', '105151086', '86920942', '72939532', '86930658', '87135347', '89416175', '77629434', '100441318', '87011701', '94795485', '79919240', '77888645', '74636713', '87040731', '102677874', '79917459', '69630731', '86113095', '87200529', '74603343', '88405884', '90496169', '76591462', '94432761', '96433029', '82542737', '98508889', '77980028', '71249092', '76200564', '77048512', '86529151', '90392959']})
3|bot_test  | 02-24 16:39:29 [ERROR] nonebot_plugin_pixivbot | error <class 'sqlalchemy.exc.OperationalError'> raised by agen (RECOMMENDED_ILLUSTS )
3|bot_test  | 02-24 16:39:29 [INFO] nonebot_plugin_pixivbot | [remote] got 89 illusts, illust_detail of 0 are missed
3|bot_test  | 02-24 16:39:29 [INFO] nonebot | Matcher(type='message', module=nonebot_plugin_pixivbot.handler.common.random_recommended_illust) running complete
3|bot_test  | 02-24 16:39:29 [ERROR] nonebot | Running Matcher(type='message', module=nonebot_plugin_pixivbot.handler.common.random_recommended_illust) failed.
3|bot_test  | Traceback (most recent call last):
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
3|bot_test  |     self.dialect.do_execute(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
3|bot_test  |     cursor.execute(statement, parameters)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 100, in execute
3|bot_test  |     self._adapt_connection._handle_exception(error)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 228, in _handle_exception
3|bot_test  |     raise error
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 82, in execute
3|bot_test  |     self.await_(_cursor.execute(operation, parameters))
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
3|bot_test  |     return current.driver.switch(awaitable)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
3|bot_test  |     value = await result
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/cursor.py", line 37, in execute
3|bot_test  |     await self._execute(self._cursor.execute, sql, parameters)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/cursor.py", line 31, in _execute
3|bot_test  |     return await self._conn._execute(fn, *args, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/core.py", line 129, in _execute
3|bot_test  |     return await future
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/core.py", line 102, in run
3|bot_test  |     result = function()
3|bot_test  | sqlite3.OperationalError: near "ON": syntax error
3|bot_test  | The above exception was the direct cause of the following exception:
3|bot_test  | Traceback (most recent call last):
3|bot_test  |   File "/usr/local/python3/bin/nb", line 8, in <module>
3|bot_test  |     sys.exit(main())
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
3|bot_test  |     return self.main(*args, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1055, in main
3|bot_test  |     rv = self.invoke(ctx)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
3|bot_test  |     return _process_result(sub_ctx.command.invoke(sub_ctx))
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
3|bot_test  |     return ctx.invoke(self.callback, **ctx.params)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/click/core.py", line 760, in invoke
3|bot_test  |     return __callback(*args, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nb_cli/commands/main.py", line 30, in run
3|bot_test  |     run_bot(file, app)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nb_cli/handlers/deploy.py", line 25, in run_bot
3|bot_test  |     nonebot.run(app=f"{module_name}:{app}")
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/__init__.py", line 273, in run
3|bot_test  |     get_driver().run(*args, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/drivers/fastapi.py", line 187, in run
3|bot_test  |     uvicorn.run(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/uvicorn/main.py", line 569, in run
3|bot_test  |     server.run()
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/uvicorn/server.py", line 60, in run
3|bot_test  |     return asyncio.run(self.serve(sockets=sockets))
3|bot_test  |   File "/usr/local/python3/lib/python3.9/asyncio/runners.py", line 44, in run
3|bot_test  |     return loop.run_until_complete(main)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/message.py", line 141, in _check_matcher
3|bot_test  |     await _run_matcher(Matcher, bot, event, state, stack, dependency_cache)
3|bot_test  | > File "/usr/local/python3/lib/python3.9/site-packages/nonebot/message.py", line 187, in _run_matcher
3|bot_test  |     await matcher.run(bot, event, state, stack, dependency_cache)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/internal/matcher/matcher.py", line 732, in run
3|bot_test  |     await self.simple_run(bot, event, state, stack, dependency_cache)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/internal/matcher/matcher.py", line 707, in simple_run
3|bot_test  |     await handler(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot/dependencies/__init__.py", line 108, in __call__
3|bot_test  |     return await cast(Callable[..., Awaitable[R]], self.call)(**values)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/common/random_recommended_illust.py", line 38, in on_match
3|bot_test  |     await self.handle(count=get_count(state), post_dest=post_dest)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/base.py", line 96, in handle
3|bot_test  |     await self.interceptor.intercept(self._parse_args_and_actual_handle, *args,
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/combined_interceptor.py", line 56, in intercept
3|bot_test  |     await self.x.intercept(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/combined_interceptor.py", line 56, in intercept
3|bot_test  |     await self.x.intercept(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/combined_interceptor.py", line 56, in intercept
3|bot_test  |     await self.x.intercept(
3|bot_test  |   [Previous line repeated 4 more times]
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/default_error_interceptor.py", line 36, in intercept
3|bot_test  |     raise e  # 重新抛出，让上层可以处理（如scheduler中需要处理Handler的异常）
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/default_error_interceptor.py", line 22, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/permission_interceptor.py", line 34, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/permission_interceptor.py", line 34, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/timeout_interceptor.py", line 21, in intercept
3|bot_test  |     await wait_for(wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs),
3|bot_test  |   File "/usr/local/python3/lib/python3.9/asyncio/tasks.py", line 481, in wait_for
3|bot_test  |     return fut.result()
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/retry_interceptor.py", line 32, in intercept
3|bot_test  |     raise e
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/retry_interceptor.py", line 26, in intercept
3|bot_test  |     return await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/loading_prompt_interceptor.py", line 33, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/service_interceptor.py", line 35, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/interceptor/record_req_interceptor.py", line 20, in intercept
3|bot_test  |     await wrapped_func(*args, post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/base.py", line 127, in _parse_args_and_actual_handle
3|bot_test  |     await self.actual_handle(post_dest=post_dest, silently=silently, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/handler/common/random_recommended_illust.py", line 48, in actual_handle
3|bot_test  |     illusts = await self.service.random_recommended_illust(count=count,
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/service/pixiv_service.py", line 112, in random_recommended_illust
3|bot_test  |     illusts = [x async for x in self.repo.recommended_illusts()]
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/service/pixiv_service.py", line 112, in <listcomp>
3|bot_test  |     illusts = [x async for x in self.repo.recommended_illusts()]
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/data/pixiv_repo/mediator_repo.py", line 348, in recommended_illusts
3|bot_test  |     async for x in gen:
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/utils/shared_agen.py", line 63, in _generator
3|bot_test  |     raise e
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/utils/shared_agen.py", line 55, in _generator
3|bot_test  |     new_data = await self._origin.__anext__()
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/data/pixiv_repo/mediator.py", line 131, in mediate_many
3|bot_test  |     async for x in _load_many_from_remote_and_append(remote_factory, query_kwargs,
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/data/pixiv_repo/mediator.py", line 97, in _load_many_from_remote_and_append
3|bot_test  |     await cache_appender(buffer, item)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/data/pixiv_repo/local_repo/sql.py", line 512, in append_recommended_illusts
3|bot_test  |     return await self._append_and_check_illusts(session, "other", {"type": "recommended_illusts"},
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/nonebot_plugin_pixivbot/data/pixiv_repo/local_repo/sql.py", line 153, in _append_and_check_illusts
3|bot_test  |     row_count += (await session.execute(stmt)).rowcount
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/ext/asyncio/session.py", line 215, in execute
3|bot_test  |     result = await greenlet_spawn(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 126, in greenlet_spawn
3|bot_test  |     result = context.throw(*sys.exc_info())
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
3|bot_test  |     result = conn._execute_20(statement, params or {}, execution_options)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
3|bot_test  |     return meth(self, args_10style, kwargs_10style, execution_options)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
3|bot_test  |     return connection._execute_clauseelement(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
3|bot_test  |     ret = self._execute_context(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
3|bot_test  |     self._handle_dbapi_exception(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
3|bot_test  |     util.raise_(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
3|bot_test  |     raise exception
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
3|bot_test  |     self.dialect.do_execute(
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
3|bot_test  |     cursor.execute(statement, parameters)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 100, in execute
3|bot_test  |     self._adapt_connection._handle_exception(error)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 228, in _handle_exception
3|bot_test  |     raise error
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 82, in execute
3|bot_test  |     self.await_(_cursor.execute(operation, parameters))
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
3|bot_test  |     return current.driver.switch(awaitable)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
3|bot_test  |     value = await result
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/cursor.py", line 37, in execute
3|bot_test  |     await self._execute(self._cursor.execute, sql, parameters)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/cursor.py", line 31, in _execute
3|bot_test  |     return await self._conn._execute(fn, *args, **kwargs)
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/core.py", line 129, in _execute
3|bot_test  |     return await future
3|bot_test  |   File "/usr/local/python3/lib/python3.9/site-packages/aiosqlite/core.py", line 102, in run
3|bot_test  |     result = function()
3|bot_test  | sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ON": syntax error
3|bot_test  | [SQL: INSERT INTO illust_set_cache_illust (cache_id, illust_id, rank) VALUES (?, ?, ?) ON CONFLICT (cache_id, illust_id) DO NOTHING]
3|bot_test  | [parameters: (1, 74902982, 0)]
3|bot_test  | (Background on this error at: https://sqlalche.me/e/14/e3q8)
ssttkkl commented 1 year ago

看起来像是sqlite版本太旧了不支持ON CONFLICT子句，执行pip install aiosqlite -U试试

JvJvless commented 1 year ago

看起来像是sqlite版本太旧了不支持ON CONFLICT子句，执行pip install aiosqlite -U试试

感谢帮助，不过尝试后还是存在同样的问题，报错相同。 同时pip install时有提示版本问题 另外尝试换用了官方源的0.17，0.18版本，报错同原问题。

Looking in indexes: http://mirrors.cloud.aliyuncs.com/pypi/simple/
Requirement already satisfied: aiosqlite in /usr/local/python3/lib/python3.9/site-packages (0.17.0)
Collecting aiosqlite
  Downloading http://mirrors.cloud.aliyuncs.com/pypi/packages/28/b0/2c2766e12cc7ec623294d12f1e544ea50f17403d723a03c671f6c5eadac9/aiosqlite-0.18.0-py3-none-any.whl (15 kB)
Installing collected packages: aiosqlite
  Attempting uninstall: aiosqlite
    Found existing installation: aiosqlite 0.17.0
    Uninstalling aiosqlite-0.17.0:
      Successfully uninstalled aiosqlite-0.17.0
ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
nonebot-plugin-pixivbot 1.7.4 requires aiosqlite<0.18.0,>=0.17.0, but you have aiosqlite 0.18.0 which is incompatible.
Successfully installed aiosqlite-0.18.0
JvJvless commented 1 year ago

更新系统至Ubuntu20.04，Python环境为3.10后，重新部署nonebot2后可正常使用 后台有较多报错，但已不影响使用，后续有机会再研究