美文网首页
MySQL：处理 alchemy 连接 mysql 报 Auth

MySQL：处理 alchemy 连接 mysql 报 Auth

作者: dex0423 | 来源:发表于2020-07-25 18:04 被阅读0次

    1. 环境

    • windows 10；
    • MySQL 8.0.20；
    • Alchemy 20.5；
    • Python 3.7.5；

    2. 问题

    • alchemy 连接 mysql 数据库时，报 Authentication plugin 'caching_sha2_password' is not supported 错误；
    • 报错内容：

    Traceback (most recent call last):
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2339, in _wrap_pool_connect
    return fn()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self.dec_overflow()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
    exc_value, with_traceback=exc_tb,
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
    raise exception
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in init
    self.__connect(first_connect_check=True)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in _connect
    pool.logger.debug("Error on connect(): %s", e)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
    exc_value, with_traceback=exc_tb,
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
    raise exception
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool.invoke_creator(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, *cparams)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
    return self.dbapi.connect(    cargs, **cparams)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector_init    .py", line 179, in connect
    return MySQLConnection(*args, kwargs)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 95, in init
    self.connect(    kwargs)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 210, in _open_connection
    self._ssl)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 142, in _do_auth
    auth_plugin=self._auth_plugin)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 102, in make_auth
    auth_data, ssl_enabled)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\authentication.py", line 191, in get_auth_plugin
    "Authentication plugin '{0}' is not supported".format(plugin_name))
    mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

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

    Traceback (most recent call last):
    File "C:\Users\Administrator\Desktop-\magic_method.py", line 748, in <module>
    Base.metadata.create_all(engine)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\sql\schema.py", line 4547, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2097, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\contextlib.py", line 112, in enter
    return next(self.gen)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2089, in _optional_conn_ctx_manager
    with self._contextual_connect() as conn:
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2305, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2343, in wrap_pool_connect
    e, dialect, self
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 1585, in handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from    =e
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
    raise exception
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2339, in _wrap_pool_connect
    return fn()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self.dec_overflow()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
    exc_value, with_traceback=exc_tb,
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
    raise exception
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in init
    self.__connect(first_connect_check=True)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in _connect
    pool.logger.debug("Error on connect(): %s", e)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 69, in exit
    exc_value, with_traceback=exc_tb,
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise
    raise exception
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool.invoke_creator(self)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, *cparams)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
    return self.dbapi.connect(    cargs, **cparams)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector_init    .py", line 179, in connect
    return MySQLConnection(*args, kwargs)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 95, in init
    self.connect(    kwargs)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 210, in _open_connection
    self._ssl)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 142, in _do_auth
    auth_plugin=self._auth_plugin)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 102, in make_auth
    auth_data, ssl_enabled)
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
    File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\authentication.py", line 191, in get_auth_plugin
    "Authentication plugin '{0}' is not supported".format(plugin_name))
    sqlalchemy.exc.NotSupportedError: (mysql.connector.errors.NotSupportedError) Authentication plugin 'caching_sha2_password' is not supported

    3. 原因：

    • 在MySQL 8.0以后，默认的身份认证是caching_sha2_password而不是mysql_native_password。

    4. 解决

    • 打开 mysql cli 终端，输入如下代码：
    alter user root@localhost identified with mysql_native_password by 'passwd';
    • 查看修改结果：
    use mysql;
select * from user\G;
    • 修改连接方式：
    engine = create_engine(
"mysql+mysqlconnector://username:password@host:port/database?auth_plugin=mysql_native_password"
)
    • 连接成功，问题解决；

    相关文章

      网友评论

          本文标题：MySQL：处理 alchemy 连接 mysql 报 Auth

          本文链接：https://www.haomeiwen.com/subject/txjulktx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL：处理 alchemy 连接 mysql 报 Auth|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！