Help! MySQL DB transfer is adding "�" to the end of some sentences

evossman

Aloah,

I'm transferring databases from one hosting account to another and my transfer seems to be adding little squares to some sentences but not all. the squares look like "� Blogtown Press " You can see an example at http://www.blogtownpress.com/2007/02/07/snap-crackle-blog-your-way-to-weird-news/

In firefox they are triangular question marks.

The old host is using MySQL version 4.0.27 and the new one is using 4.1.21, should transfer nicely. I've done version 4 to version 5 before just using the INSERT functions and this never was a problem.

Also, when I INSERT the old into the new database it inserts as latin1_swedish_ci when my MySQL CharSet is UTF-8 Unicode (utf8) and my connection collation is utf8_unicode_ci. I've altered the tables in the the database using the "ALTER TABLE" command to change to utf8 unicode, and the tables are all change, but the "SUM" at the bottom still shows latin1_swedish_ci.

I'm not sure what all this really means. Only what I've read.

Any help would be appreciated

leatherback

Once you start playing with page encodings, you have to make sure that all the tables have the same encoding as your PHP files and that the output HTML has an encoding tag added to it. It probably is an encoding issue, but as I am not sure which one was used to create the tables, data and pages it is a bit hard to do anything. I tried the page, and tried to view them with different encodings, but could not solve it. It looks to me like you submitted data in one encoding to a script processing in another encoding, and storing in a database yet in another encoding.

All I can say is: Be consistent, and tell the browser which encoding you are using..

Roger_Ramjet

The latin1_swedish_ci encoding is the server default and gets applied whenever no other encoding is specified, which is why the SUM comes out as that even though the base tables are utf8. Chances are the actual data in the tables is still latin1_swedish_ci as well despite the table def.

I ran into this encoding problem myself a couple of days ago. I was using mysql query browser to build a view, but wrote part of the sql in my editor and pasted it in. Mysql threw the error that the union query had conflicting encodings if the 2 selects, and it did. Go figure? Just pasting in the sql preserved the utf8 from my text editor. When I look into it, another view I had created had utf8 for all the aggregate fields even though the underlying data is all latin1_swedish_ci. So apparently, mysql will take the encoding of the actual sql string and apply that to the data for a SUM() field in the output.

Oh for the good old days when everything was ASCII.

leatherback

Roger Ramjet wrote:
Oh for the good old days when everything was ASCII.

Quick solution FOR THE FUTURE:
Set your coder default to UTF-8, set the database up in UTF-8, and set the website up in UTF-8...

evossman

OK, so i'm looking further into setting the server up so everything is the same. Here is what I get if I look into wha the localhost settings are:

character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

All different.
Then if I run:
show variables like "character_set_%";
on the database for the above mentioned blog I get
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_results utf8
character_set_server latin1
character_set_system utf8

as you can see I managed to change the database to utf8 but can't get the server to change.

then in my wordpress diagnosis plugin from the dashboard i get this as what it's reading:
Character set client (?) latin1 The character set in which statements are sent by the client
Character set connection (?) - latin1 - What character set the server translates a statement to after receiving it
Character set database (?) - utf8 -The character set used for databases
Character set results (?)- latin1- What character set the server translates to before shipping result sets or error messages back to the client
Character set server (?) -latin1 -The character set used by the MySQL server itself
Character set system (?)- utf8 - The character set used by the system

I'm guessing these values are taken by this plugin off the server since they are different than what the database is showing.

Eeek.

I am seeting this up on hostgator. Does anyone have a solution for how to change the localhost server settings?

Roger_Ramjet

You can't do that on shared hosting so you have to set them at runtime.

evossman

Thanks for the response Ramjet.

i have a semi dedicated account with hostgator.

I can get SSH access to the usr/share/mysql/charsets/ folder so i'm told, where it appears these reside.

If i can't set this through the charsets folder how would i do it during runtime?

Roger_Ramjet

Bset thing is for you to read it yourself http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html

evossman

does that mean that i have to add code within some wordpress php files to tell the server that i don't want it to use its goofy defaults and that I want it to use specifics?

Sorry if I sound a bit elementary, I'm not that good at mysql.

Thanks 🙂

Roger_Ramjet

Yes, but don't ask me which ones. You should go to the WP support and ask there.

evossman

OK, I found a workaround for the client connections etc using my php files in wordpress where it connects and that has change all my connections to utf8 except the server character set which is still set to latin1

Is there a way I can change this via and SQL query in phpmyadmin or maybe even SSH if I get my SSH account setup?

Roger_Ramjet

This is a cascade of charset from server to db to table to column. Now since it is not your server I doubt that you can change it cos that would change it for all users. Just specify the encoding at runtime with the charset directive.