1

For a very long time i was suffering form the Latin-1 encoding in My Django web application DB causing this error when trying to select a matching string using LIKE :

 -- UnicodeEncodeError:'latin-1' codec can't encode character--

i've tried every solution from setting (charset='utf8') in the connection to applying cursor.execute("set names 'utf8'") before my actual query but nothing seams to work.

Until i came across this blog post: https://www.whitesmith.co/blog/latin1-to-utf8/ about the encoding problem and it is the same problem that i have because when i looked in phpMyAdmin a saw that by default my DB i Latin-1 encoding:

chatbot_brain=>utf8_general_ci
information_schema=>utf8_general_ci
Total: 2=>    latin1_swedish_ci

So, the solution is to dump the DB and change the description in the schema file:

 # Login into your future database host to create a new database with an UTF-8 charset 
 $ mysql -h FUTURE_HOST -u FUTURE_USER -p
 mysql> CREATE DATABASE `FUTURE_DB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
 # Flush the current database schema on the future host, replacing all CHARSET=latin1 occurrences along the way 
 mysqldump -h CURRENT_HOST -u CURRENT_USER -p CURRENT_DB --no-data --skip-set-charset --default-character-set=latin1 \  
 | sed 's/CHARSET=latin1/CHARSET=utf8/g' \ 
 | mysql -h FUTURE_HOST -u FUTURE_USER -p FUTURE_DB --default-character-set=utf8
 # Flush the current database data on the future host 
 mysqldump -h CURRENT_HOST -u CURRENT_USER -p --no-create-db --no-create-info --skip-set-charset --default-character-set=latin1 CURRENT_DB \  
 | mysql -h FUTURE_HOST -u FUTURE_USER -p FUTURE_DB --default-character-set=utf8

Now i know what is the problem and the solution, But my question is how i can applied to my Django project-- Do i have to use my computer terminal and SSH session or is there any application of that?

this is a sceen shot of my DB in phpmyAdmin:

https://ibb.co/dDu7D5

Thank you

PS(I am using Django10 , Python3.5,Mysql,Webfaction sherd host)

Eman
  • 127
  • 2
  • 9
  • If you have phpMyAdmin on your host, you could use that to make the changes. Also, you don't need to export/import the db to change the collation. Google "mysql change collation" and you'll see lots of examples on how to write SQL statements that will change the collation on all of the tables, and all of the columns within the tables. – Sloan Thrasher Apr 30 '17 at 18:10
  • Don't need to use a terminal. You might want to check GUIs like [MySQL Workbench](https://en.wikipedia.org/wiki/MySQL_Workbench), the GPL version. – code_dredd May 01 '17 at 04:07
  • @SloanThrasher thank you , i have checked every thing and its all sets to utf8_general_ci, and tried to use phpmyAdmin(Opration) and change the encoding and it didn't solve the problem, my problem is that the tables are utf8_general_ci but the collection is latin1_swedish_ci, and like i asked before where do i write those queries like (SELECT HEX(col)...) – Eman May 01 '17 at 13:26
  • Did you mean _collation_ or _collection_? Are you saying the tables have one collation, but the columns have a different collation? If that's the case, re-read the last sentence in my previous comment. The columns within each table have to be changed as well as the tables. – Sloan Thrasher May 01 '17 at 14:10
  • The _table_ charset&collation are only _defaults_ for any newly created _columns_. – Rick James May 01 '17 at 15:27
  • sorry mistyping i meant collation, i've add a screen shot of my DB – Eman May 01 '17 at 17:00

1 Answers1

0

Start with the "best practice" in Trouble with utf8 characters; what I see is not what I stored

Check for improper setup in Python: http://mysql.rjweb.org/doc.php/charcoll#python

Hopefully that 2-step mysqldump will work.

After loading, check some of the data by using "Test the data" in my first link. (SELECT HEX(col)...)

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • thank you , i have checked every thing and its all sets to utf8_general_ci, and tried to use phpmyAdmin(Opration) and change the encoding and it didn't solve the problem, my problem is that the tables are utf8_general_ci but the collection is latin1_swedish_ci, and like i asked before where do i write those queries like `(SELECT HEX(col)...)` – Eman May 01 '17 at 13:25
  • phpmyadmin has a window where you can enter arbitrary SQL. As you get more info, edit your question with the additional info. The HEX is important because it usually says whether the data was written wrong or read wrong. – Rick James May 01 '17 at 15:25
  • i have add a screen shot of my DB – Eman May 01 '17 at 16:59