0

We were using myOdbc connector to connect our asp pages to database, our database is quite old, we have big table with user records approx. 500k rows. Everything is utf8 and working good. In mysql / phpmyadmin records are shown as ansii chars. For example ελληνικά is the Word, but we see ελληνικά in database. But everything works fine at frontend.

Now we decided to create restful java webservices, and front end with angular/bootstrap. Everything seems fine all utf settings are done, but when we post/get we receive strings as they are in mysql table; we get ελληνικά for this sample, not ελληνικά.

On same server I checked another Project, it is utf, I added some words, but as I see phpymadmin shows them with normal letters. If I post/insert ελληνικά , it is ελληνικά in table and phpmyadmin.

  • What should I do, what is the correct way to handle this issue ?
  • If I insert ελληνικά by hand in phpmyadmin, the old website shows ?.
  • Which is the best and correct way?
  • I have to change all data to normal letters, how should I do that ?
  • Can mysql handle this ?

Thanks in advance

coder
  • 12,832
  • 5
  • 39
  • 53
mehmet
  • 1
  • 2
  • It looks like your frontend ASP app does not store them correctly. Are you sure they are stored as UTF-8? – JIV Oct 25 '16 at 12:32
  • When we first created it was mysql 4, and as I remember mysql was keeping utf-8 as in given example. If ASP didn't store them correctly, it succeded to Show back incorrect data as correct data because even now it Works very fine, but we have to move data. Thanks – mehmet Oct 25 '16 at 12:43
  • Mysql setting for UTF-8 doesn't say anything about what data can be pushed in. If you store gibberish, you will get gibberish back. Your luck so far was that reading and writing was withing the same app :) – JIV Oct 25 '16 at 13:00
  • Then I think one way is generating sql backup files with that application, what do you think ? Problem may be double encoding as I read but not sure , I believe some people encountered same issue. – mehmet Oct 25 '16 at 13:27
  • I am not sure what would be best approach. You actually may not even need to fix DB if you will find a way how to translate it to correct letters and use that converter for new restful project. – JIV Oct 25 '16 at 13:57
  • I had the same issue with a JavaScript application. The issue was at two locations: The POST request towards the SERVLET needed to include a header field stating that the encoding was UTF-8, and the SERVLET container (TOMCAT in my case) needed to be configured also to identify incoming requests as UTF-8 built. Hope this gives you some line start pulling from. – FDavidov Oct 25 '16 at 14:08
  • FDavidov thanks for response. What about values in tables ? Were they working properler and they were stored as I explained – mehmet Oct 25 '16 at 15:04
  • It seems like the fastest solution is update/replace, I could eliminate the most used letters but I don't know about russian, arabic. I have to write a script to handle it, output sql with correct letters. – mehmet Oct 25 '16 at 16:27
  • [_This_](http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored) discusses both "Mojibake" and "question marks". – Rick James Oct 25 '16 at 19:46

2 Answers2

1

Whole process can be divided into two parts

1) Need to update Historical Data

Get Count of affected rows

SELECT COUNT(*) FROM `users` t WHERE  t.`fullname`  <> CONVERT(t.`fullname` USING ASCII);

Need to update affected rows

UPDATE `users` t SET t.`fullname`= REPLACE(REPLACE(REPLACE(REPLACE(t.`fullname`,'Ã',''),'Â',''),'¢',''),'â','') WHERE  t.`fullname`  <> CONVERT(t.`fullname` USING ASCII);

2) now onward data can be fetched in readable format, for this you need to modify your code with below query

SELECT  CONVERT(BINARY CONVERT(t.`fullname` USING latin1) USING utf8) FROM `internal_trainingclassmaster` t  WHERE  t.`users`  <> CONVERT(t.`fullname` USING ASCII);
Keval Pithva
  • 600
  • 2
  • 5
  • 21
0

UPDATE users SET fullname= convert(cast(convert(fullname using latin1) as binary) using utf8) WHERE id>0;

This was the solution, script were converting utf8 to latin1 before storing data, then vice versa.

Command worked for all languages. Thanks for comments.

mehmet
  • 1
  • 2