Ytukyg

I am running a webapp on Ubuntu 16.04.4.
The stack is as follows

Python 3.5.2

MySQL 5.7.22

Flask

Flask-SQLAlchemy

The webapp has a feature for admins to upload some text using a xlsx. file which is read with openpyxl inside the webapp. However while saving I am getting errors like:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1366, "Incorrect string value: '\xC4\x9B nep...' 

In the beginning I was able to delete the characters which were making troubles (e.g. zero width whitespace). But now I am not able to do it anymore like this.

Reading a bit on the internet I think it could be that my db is not using utf8mb4. Could someone lead me to update my db and all its tables? Because I do not know anything about SQL and stuff.
As the webapp is used in production I do not like to try tutorials which are outdated.

Seems to work now. I did following steps:

1. Started the mysql cli with:
   mysql -u root -p
   


2. Logged in using the root pw.


3. 
   

   Checked the default parameters using
   show variables like "%character%";
   which gave me:

   
   
   

   +--------------------------+----------------------------+
   
   | Variable_name            | Value                      |
   
   +--------------------------+----------------------------+
   
   | character_set_client     | utf8                       |
   
   | character_set_connection | utf8                       |
   
   | character_set_database   | latin1                     |
   
   | character_set_filesystem | binary                     |
   
   | character_set_results    | utf8                       |
   
   | character_set_server     | latin1                     |
   
   | character_set_system     | utf8                       |
   
   | character_sets_dir       | /usr/share/mysql/charsets/ |
   
   +--------------------------+----------------------------+
   
   

   
   
   

   and
   show variables like "%collation%";
   which gave me

   
   
   

   +----------------------+-------------------+
   
   | Variable_name        | Value             |
   
   +----------------------+-------------------+
   
   | collation_connection | utf8_general_ci   |
   
   | collation_database   | latin1_swedish_ci |
   
   | collation_server     | latin1_swedish_ci |
   
   +----------------------+-------------------+
   
   

   
   


4. 
   

   So I edited /etc/mysql/my.cnf
   I added:

   
   
   

   [client]
   
   default-character-set = utf8mb4
   
   
   
   [mysql]
   
   default-character-set = utf8mb4
   
   
   
   [mysqld]
   
   character-set-server = utf8mb4
   
   collation-server = utf8mb4_unicode_ci
   
   

   
   


5. 
   

   Restarting mysql (sudo service mysql restart) and running the same commands as above now gave me

   
   
   

   +--------------------------+----------------------------+
   
   | Variable_name            | Value                      |
   
   +--------------------------+----------------------------+
   
   | character_set_client     | utf8mb4                    |
   
   | character_set_connection | utf8mb4                    |
   
   | character_set_database   | utf8mb4                    |
   
   | character_set_filesystem | binary                     |
   
   | character_set_results    | utf8mb4                    |
   
   | character_set_server     | utf8mb4                    |
   
   | character_set_system     | utf8                       |
   
   | character_sets_dir       | /usr/share/mysql/charsets/ |
   
   +--------------------------+----------------------------+
   
   

   
   
   

   and

   
   
   

   +----------------------+--------------------+
   
   | Variable_name        | Value              |
   
   +----------------------+--------------------+
   
   | collation_connection | utf8mb4_general_ci |
   
   | collation_database   | utf8mb4_unicode_ci |
   
   | collation_server     | utf8mb4_unicode_ci |
   
   +----------------------+--------------------+
   
   

   
   



6. So I looked up the table settings using
   SHOW TABLE STATUS FROM databasename;
   They still used stuff like latin1_swedish_ci



7. 
   

   I used following to change the database setting:
   ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   and following for each table:

   
   
   

   use databasename;
   
   ALTER TABLE assessments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   
   

   
   
   
   
   
   8. Looking up again the table settings showed that latin1_swedish_ci was now changed to utf8mb4_unicode_ci
      
   
   
   9. Then I changed the sqlalchemy connection url to use ?encoding=utf8mb4 at the end.
   
   
   10. Restarted mysql again and the webapp. Since then it's working properly.