search

dongweiming / lyanna

My Blog Using Sanic
http://www.dongwm.com
GNU General Public License v3.0
637 stars 173 forks source link

database collation error #7

Closed fanjindong closed 5 years ago

fanjindong commented 5 years ago

错误如下，难道mysql不能用 utf8mb4_general_ci ?

[2019-01-21 11:03:57 +0800] [29238] [ERROR] Exception occurred while handling uri: 'http://blog.fanjindong.com/admin/posts/new'
Traceback (most recent call last):
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/tortoise/backends/mysql/client.py", line 22, in wrapped
    return await func(self, query, *args)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/tortoise/backends/mysql/client.py", line 121, in execute_query
    await cursor.execute(query)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/cursors.py", line 239, in execute
    await self._query(query)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/cursors.py", line 457, in _query
    await conn.query(q)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/connection.py", line 428, in query
    await self._read_query_result(unbuffered=unbuffered)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/connection.py", line 622, in _read_query_result
    await result.read()
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/connection.py", line 1105, in read
    first_packet = await self.connection._read_packet()
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/aiomysql/connection.py", line 593, in _read_packet
    packet.check_error()
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/sanic/app.py", line 750, in handle_request
    response = await response
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/sanic_auth/__init__.py", line 111, in privileged
    resp = await resp
  File "/root/repository/lyanna/views/admin.py", line 45, in new_post
    return await _post(request)
  File "/root/repository/lyanna/views/admin.py", line 58, in _post
    post = await Post.filter(title=title).first()
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/tortoise/queryset.py", line 421, in _execute
    self.query, custom_fields=list(self._annotations.keys())
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/tortoise/backends/base/executor.py", line 23, in execute_select
    raw_results = await self.db.execute_query(str(query))
  File "/root/.local/share/virtualenvs/lyanna-a-akDfJl/lib/python3.6/site-packages/tortoise/backends/mysql/client.py", line 26, in wrapped
    raise OperationalError(exc)
tortoise.exceptions.OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='")
[2019-01-21 11:03:57 +0800] - (sanic.access)[INFO][47.98.223.16:48804]: POST http://blog.fanjindong.com/admin/posts/new  500 144
dongweiming commented 5 years ago

Default use utf-8, you can custom DB_URL in local_settings.py, like this:

DB_URL = 'mysql+pymysql://user:pass@1.2.3.4/database?charset=utf8mb4'
fanjindong commented 5 years ago

I have modified, this DB_URL = 'mysql://root:fanjindong@127.0.0.1:3306/blog?charset=utf8mb4' But still error tortoise.exceptions.OperationalError: (1267, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='")

and show variables like "%char%"; result is :

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
dongweiming commented 5 years ago

Can https://stackoverflow.com/a/1008336 help you?