[Kochiro]

Actually, please disregard this. I finally found the issue after searching all day and compiling a few different resources together from more google page results than I care to admit, as well as some posts from this site.

What I found was that there was a slightly different order to the charsets in the apache configurations which was causing the differences. Also, when doing manual mysqldump exports I had to specify the default charset as latin1, even though that's not what I get when doing the tests on the database to see what the charsets are. I'm not sure of the reason for that, but as long as I can work around it, I really don't care at the moment.

Thank you for reading and helping.

Jay

[Kochiro]

Well…it's been quite a while since I have had time to visit this issue. I'm still trying to understand some aspects of what is setup where and why certain things happen. I apologize if this isn't the best place to ask, but the people here seem to be the most helpful. If there is a more appropriate mysql/php forum where I could post this, please feel free to direct me there.

Here is an example similar to what Peter was requesting in the last post.

This is what is output on the frontend of the source database and what was entered into the database by a user:

Marchés

This is what I see in SQLYog when viewing the same record:

Marchés

This is the hex output of the same record given by the query “select hex(field) from table where condition”

4D61726368C3A973

What I am trying to understand is this:

In the source database the character “é” is stored as “é” but yet still output as “é”. When that data is pushed to the target server (again nearly identical setups) the character “é” is actually displayed as “é” and not “é”.

I don't understand what is changing during the process of moving the data from one server to another.

Thank you,

Jay

[Kochiro]

Peter,

The “show variables” output was the same on both hosts so the one I showed you does represent both.

I think that you are right about this but it is going to take me some time to test all the options you have given me. I also just had something else dumped in my lap that has a higher priority.

I will go through all of your suggestions and reply back with what I find.

Thanks again,

Jay

[Kochiro]

peterlaursen wrote on Sep 19 2006, 03:55 PM:

First:

=====

are you

1) dumping/restoring

2) COPYing

3) or SYNCing?

With DATA SYNC there should be no issue/difference if the same charset is used on source and target!

Second:

======

Is the source and target database on the same server? if not are you then sure that webserver/PHP configuration is identical?

Peter,

Thanks again for the quick response. To answer your questions, I am running a scheduled synch. The source and target databases are on different servers. I believe that the general server and php configurations are the same. As I said originally the source database is 0.0.1 version higher than the target. I ran the show variables on the target and source again and they are identical.

I will check into all the things you mentioned and reply back with anything that I find.

Thanks,

Jay

[Kochiro]

Thanks Peter, I think I understand a bit more now.

When I ran the show variables command here is what was returned:

Code:

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

Here's a specific example, if you still need an example table let me know.

This is what is displayed in the web browser when running against the source database:

Code:

How’s it going?

Note that the apostrophe is the special character, instead of a standard one it is one of those special MS Word ones that have the thicker “bulb” on the top portion of the symbol.

In the database that is getting stored as (copied directly out of SQLyog):

Code:

How’s it going?

I don't fully understand where or how this is happening. My thought was that it was due to the fact that the default encoding of php (utf-8 I believe) understands the MS Word character and interprets it properly. But when it is saved to the database, mysql doesn't understand it and stores it as the strange characters. I can live with that as I am the only one who will be viewing the data in the database directly.

The issue that I run into is that when I export that data it exports just as it is stored. But when the browser looks at the same record on the target database it just shows the garbage instead of converting it into an apostrophe like it does on the source database.

I hope that makes some sense and helps. It might not really be an export issue but I don't understand why the same data in both databases doesn't display the same way when viewed with a web browser.

When this is done manually with a mysqldump is there a change that I can make to the mysql import statement that will make the data display the same way?

When this is done with a scheduled synch in SQLyog is there a change that I can make to make the data display the same way?

Thanks,

Jay

[Author]

Posts