MySQL代码本地正常但Heroku报错(_mysql_connector.MySQLInterfaceError)排查

将代码从GitHub直接部署至Heroku，本地测试网站运行无异常，且本地与Heroku的MySQL版本完全一致，但在Heroku上运行时出现_mysql_connector.MySQLInterfaceError错误。

相关代码 #

Jinja模板代码 #

get_user_notis(session['username'])

SQL执行代码 #

mycursor.execute("""
                SELECT * FROM notif WHERE username = %s ORDER BY date DESC
                """, (username, ))

报错堆栈信息 #

2024-01-16T04:35:47.769366+00:00 app[web.1]: File "/app/flask_app.py", line 478, in index
2024-01-16T04:35:47.769366+00:00 app[web.1]:     return render_template("index.html")
2024-01-16T04:35:47.769367+00:00 app[web.1]:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769367+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/flask/templating.py", line 151, in render_template
2024-01-16T04:35:47.769367+00:00 app[web.1]:     return _render(app, template, context)
2024-01-16T04:35:47.769367+00:00 app[web.1]:            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769367+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/flask/templating.py", line 132, in _render
2024-01-16T04:35:47.769367+00:00 app[web.1]:     rv = template.render(context)
2024-01-16T04:35:47.769367+00:00 app[web.1]:          ^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769367+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/jinja2/environment.py", line 1301, in render
2024-01-16T04:35:47.769367+00:00 app[web.1]:     self.environment.handle_exception()
2024-01-16T04:35:47.769368+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/jinja2/environment.py", line 936, in handle_exception
2024-01-16T04:35:47.769369+00:00 app[web.1]:     raise rewrite_traceback_stack(source=source)
2024-01-16T04:35:47.769369+00:00 app[web.1]:   File "/app/templates/index.html", line 31, in top-level template code
2024-01-16T04:35:47.769370+00:00 app[web.1]:     {{ navbar_block() }}
2024-01-16T04:35:47.769370+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/jinja2/runtime.py", line 777, in _invoke
2024-01-16T04:35:47.769370+00:00 app[web.1]:     rv = self._func(*arguments)
2024-01-16T04:35:47.769370+00:00 app[web.1]:          ^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769370+00:00 app[web.1]:   File "/app/templates/macros.html", line 583, in template
2024-01-16T04:35:47.769370+00:00 app[web.1]:     {% for item in get_user_notis(session['username']) %}
2024-01-16T04:35:47.769371+00:00 app[web.1]:     ^^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769371+00:00 app[web.1]:   File "/app/helpers/notif.py", line 13, in get_user_notis
2024-01-16T04:35:47.769371+00:00 app[web.1]:     mycursor.execute("""
2024-01-16T04:35:47.769371+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/mysql/connector/cursor_cext.py", line 317, in execute
2024-01-16T04:35:47.769371+00:00 app[web.1]:     prepared = self._cnx.prepare_for_mysql(params)
2024-01-16T04:35:47.769371+00:00 app[web.1]:                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769373+00:00 app[web.1]:   File "/app/.heroku/python/lib/python3.11/site-packages/mysql/connector/connection_cext.py", line 780, in prepare_for_mysql
2024-01-16T04:35:47.769373+00:00 app[web.1]:     result = self._cmysql.convert_to_mysql(*params)
2024-01-16T04:35:47.769373+00:00 app[web.1]:              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2024-01-16T04:35:47.769373+00:00 app[web.1]: _mysql_connector.MySQLInterfaceError: Python type Undefined cannot be converted

排查与解决方法 #

1. 核心问题定位：报错Python type Undefined cannot be converted说明传入SQL的参数username是Jinja的Undefined类型，即session['username']不存在或未被正确赋值。
2. 本地与Heroku差异原因：本地测试时用户已登录，session['username']有值，但Heroku上可能存在未登录用户访问页面的情况，或者session配置有差异（比如Heroku默认用内存存储session，重启后session丢失，导致未登录状态触发错误）。
3. 修复步骤：
   • 在Jinja模板调用get_user_notis前，先判断session['username']是否存在：

     {% if session.get('username') %}
         {% for item in get_user_notis(session['username']) %}
             {# 通知渲染逻辑 #}
         {% endfor %}
     {% endif %}
     

   • 或者在get_user_notis函数内部添加参数校验，避免传入无效值：

     def get_user_notis(username):
         if not username:
             return []
         # 原SQL执行逻辑
         mycursor.execute("""
                 SELECT * FROM notif WHERE username = %s ORDER BY date DESC
                 """, (username, ))
         # 后续处理逻辑
     

   • 检查Heroku的session配置，建议使用Redis等持久化存储方案，避免重启后session丢失。

内容的提问来源于stack exchange，提问作者corieleclair