php - MySQL error on special chars -


i have mysql database hosted in provider. running ok last 3 years somehow since yesterday special characters show invalid chars. instance :

'sugestão' shows 'sugestão'

i did not make changes in database , host company claims can't nothing help. , stranger yet, new inclusions not being affected problem, data included prior yesterday.

i know happens in host back-end, refuse admit because claim don't have logs prove did not change db charset or collation. of course, know did not this, app running 3 years , never had problem.

so i'm on own fix problem updating columns kind of problem manually, in each table. there way faster inside mysql workbench itself, instead of creating app ?

for instance, have table called 'crm'. here example result set :

select * crm; cr_date     cr_history 2016-07-11  quer sugestão  2016-07-11  país de destino : canadá

here how result set looked before problem , how need fix again :

select * crm; cr_date     cr_history 2016-07-11  quer sugestão  2016-07-11  país de destino : canadá

is there way update query change 'ã' strings inside column 'ã', keeping rest of column content intact ?

something : replace ocurencies o 'ã' in field, 'ã'. php's str_replace directly in mysql.

just add more information, db it's accessed php application, problem happens accessing db directly mysql workbench, it's not php related problem.

thanks in advance !

this typical (and seen) result when stored utf-8 data in ansi (or other non-utf) column. php seems prone type of error (i've seen several times before). so, when retrieving data interpreted in encoding of column instead of true encoding - hence wrong display.

you can see this:

mysql> select cast(_latin1'müller' char character set utf8); +---------------------------------------------------+ | cast(_latin1'müller' char character set utf8)  | +---------------------------------------------------+ | müller                                           | +---------------------------------------------------+ 1 row in set (0,00 sec)  mysql> select cast('müller' char character set utf8); +--------------------------------------------+ | cast('müller' char character set utf8)  | +--------------------------------------------+ | müller                                     | +--------------------------------------------+ 1 row in set (0,00 sec)

what can set right encoding (charset in (my)sql) column. use alter table command, mess existing values further (it try re-encode utf-8 encoded values again in utf-8). better approach this:

  • add new column utf-8 charset + collation.
  • update table , set values of new column values of old colum. important here to cast old column's charset true value (utf-8). not convert data store existing bytes in new column, time correct encoding.
  • once done remove old column , rename new 1 old name.

with additional column make sure don't lose data (a backup still recommendable) , can drop , start on if goes wrong.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more