1

I'm using flask_sqlalchemy in my flask application with a local MySQL (8.0.19) database. I've never got this issue before (started to develop this app months ago). Not sure what've changed, what component of the app got updated but I'm getting this error out of nowhere at the moment. I've searched and found that it might be some character encoding issue, but following the instructions I still get the warning when I open my app:

C:\Users\MyUserName\AppData\Local\Programs\Python\Python37\lib\site packages\pymysql\cursors.py:170:Warning: 
(1366, "Incorrect string value: '\\xF6z\\xE9p-e...' for column 'VARIABLE_VALUE' at row 1")
result = self._query(query)

This is my url env variable:

MYSQL_URL = mysql+pymysql://user:passoword@localhost:3306/testdb?charset=utf8mb4

And this is how I create my db session:

db_url = os.getenv('MYSQL_URL')
engine = create_engine(db_url, echo=True)

Session = sessionmaker()
Session.configure(bind=engine)
session = Session()

This is the most simple usage of the session:

def row_count():
    return (
        session.query(Value.ValueID).count()
    )

When I inspect this local database with HeidiSQL it says its collation is utf8mb4_0900_ai_ci. I don't know what those suffix specifics mean and there's a ton of utf8mb4 variant available. This is the default value.

enter image description here

Anyone has any idea how to resolve this warning? What does it mean exactly? As I'm using an ORM I'm not creating any database or running any query by hand, so how should I handle this?

MattSom
  • 2,097
  • 5
  • 31
  • 53
  • Does this answer your question? [How to fix "Incorrect string value" errors?](https://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors) – snakecharmerb Jun 28 '20 at 17:40
  • Please display the hex of the string in the database. That might give us some more insight into where the problem lies. – Rick James Sep 04 '20 at 02:51
  • @RickJames How can I get the value of this string first? It seems like a generated error message. Should I catch it in some way? – MattSom Sep 04 '20 at 11:39
  • Do `SET NAMES utf8mb4` right after connecting. – Rick James Sep 05 '20 at 02:00

1 Answers1

0

ai : accent insensitive

ci : case insensitive

Did your try the following URL:

MYSQL_URL = mysql+pymysql://user:passoword@localhost:3306/testdb?charset=utf8mb4_ai_ci
Nomi Laura
  • 23
  • 1
  • 6
  • 1
    It throws this error: `File "C:\Users\Username\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\connections.py", line 283, in __init__ self.encoding = charset_by_name(self.charset).encoding AttributeError: 'NoneType' object has no attribute 'encoding'` – MattSom Jun 30 '20 at 09:38
  • which version of MySQL ? – Nomi Laura Jun 30 '20 at 17:14
  • 1
    The version is 8.0.19 – MattSom Jul 01 '20 at 11:51