Import from MySQL 4.0 to MySQL 4.1

jstubbs2 · March 29th, 2006, 04:28 AM

Am having a lot of issues exporting a PhotoPost database stored in MySQL 4.0 to a new web host which runs MySQL 4.1.

Basically, its to do with Norwegian characters which do not display properly in the browser. On the current site (we are moving web host), which runs 4.0, everything is ok.

The default character set is Latin1, and I have tried exporting, then importing into phpMyAdmin with the import character set to Latin1. This did not work. I tried UTF too, no cigar.

I have also tried exporting from the command line, then importing the same way. This did not work either.

Perhaps someone can help. Thanks a lot.

Chuck S · March 29th, 2006, 08:19 AM

Well what issue are you having. An error might help here

jstubbs2 · March 29th, 2006, 08:37 AM

Sorry, thought I mentioned that in my first post.

Basically the current PhotoPost installation is working fine on the current web host, which runs MySQL 4.0 and PHP 4.3.

We are changing the web host. The new host runs MySQL 4.1 and PHP5.

The problem is with the characters displayed in the browser - Norwegian characters. I have 3 applications on this server - Textpattern, PunBB and PhotoPost.

MySQL 4.0 uses Latin1 by default. When I exported the 3 apps via phpMyAdmin from the old server, then uploaded to the new server, again with phpMyAdmin, I selected "Latin1" as the character set before importing.

The only app of the 3 that worked was Textpattern. PhotoPost and PunBB display Norwegian characters as question marks in Safari.

I have tried exporting and importing the databases from the command line, but still get the errors in the browser. I notice that in the PhotoPost database the characters are stored as one would read them, but in the Textpattern database, the characters are mixed up, but readable in the browser.

Hope that was clearer ;-)

Chuck S · March 29th, 2006, 08:46 AM

Wait lets back up here

So you have no problem importing the database itself.

So your issue is browser display of language characters.

Well we don't support the Norwegian language in Photopost but my assumption here not really about Safari but about any browser is that you might want to be able to load the proper charset to display the language. I have seen conflicting reports on the net about displaying Norwegian characters so just a hint on that.

jstubbs2 · March 29th, 2006, 09:21 AM

Not sure about that. Latin1 or iso-8859-1 is the default character set for MySQL. PhotoPost headers are charset=iso-8859-1 by default, which should not create any issues at all.

I am wondering whether this is an issue from the storage of the data in MySQL, or something closer to what you are saying.

One thing, its very frustrating :-(

Chuck S · March 29th, 2006, 09:36 AM

I would say if all your dealing with is a specific browser that it is a browser issue

By the way your site seems to view fine to me

jstubbs2 · March 29th, 2006, 09:38 AM

Maybe you are right, but its pretty odd that exactly the same files are used, with the same charset headers. No difference at all - the files were just moved over.

The only thing that was done was export from one MySQL database and import to another. That did not change the default charset.

Chuck S · March 29th, 2006, 09:40 AM

Your site views fine in Mozilla Firefox and IE for me

jstubbs2 · March 29th, 2006, 09:55 AM

Chuck, I see you are right, but its difficult to know why and how to change this. The pages are exactly the same, with the charset set to Latin1:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

Textpattern is set to UTF.

Err, any idea why with exactly the same files and data, the browser picks up the charset correctly in the old installation, but not the new, except for UTF textpattern?

I realise this now has nothing to do with PhotoPost, but if you or someone else can shed some light I would appreciate it :-)

jstubbs2 · March 29th, 2006, 09:56 AM

Chuck, you would be viewing the old site. I see it on the new server by switching hosts, by editing the /etc/hosts file on my OSX machine.

Edit: That's what I meant above - its great now, just screwed up in the new site, even thought the headers etc are the same

Chuck S · March 29th, 2006, 10:11 AM

Right is this only the Safari browser though.

If I can not see it I can not comment on it.

jstubbs2 · March 29th, 2006, 10:14 AM

Safari and Firefox. I will investigate further.

jstubbs2 · April 16th, 2006, 12:02 AM

OK, I figured out this problem. The new web host we are using have UTF-8 setup as the default. For this reason, Textpattern displays foreign characters fine because it runs UTF.

punBB and PhotoPost are iso-8859-1. Thus, characters display badly unless one changes the encoding in the browser.

Do you know of a way to solve this problem? I need these 3 apps to live together.

Chuck S · April 16th, 2006, 05:35 AM

Never heard of punbb

Anyway Photopost changes the language encoding on browsing by the language your loading. English in our language file of course loads iso-8859-1 since it is the default for english. You can $content meta line in the english.php language file as well as every other language file. The admin section as well encoding is loaded in the adm-inc.php file $contentmeta string.

jstubbs2 · April 16th, 2006, 06:03 AM

Thanks for the reply. punBB is a very nice, lightweight forum.

OK, so you are saying if I go to line 4 in english.php I can just change this line to UTF-8?

Code:

Content visible to verified customers only.

And the same for line 279 in adm-inc.php:

Code:

Content visible to verified customers only.

That would fix it you think?

Chuck S · April 16th, 2006, 06:22 AM

I can not say as if you remember I never saw any issues.

jstubbs2 · April 16th, 2006, 06:31 AM

I changed the lines in the 2 scripts above to utf-8, and the characters are still messed up. I would think that means I have to convert the content in the database to UTF-8.

Have tried this before by dumping the database in Text Wrangler, converting to UTF-8, then importing. Causes SQL errors.

If the apps are not in UTF-8 then it will cause character encoding problems.

jstubbs2 · April 16th, 2006, 06:32 AM

Chuck, you are right! You can't see anything! Would you like me to PM you with the details for adjusting the /ect/hosts file so you can see it?

Chuck S · April 16th, 2006, 07:19 AM

You issue seems to be one of trying to convert previous mysql data stored in one format to another and not specifically something with the program if I follow this thread right.

If you post a new photo etc that displays correctly right?

Just set your site so that I can see it and post a link so I can have more of an idea what your talking about.

jstubbs2 · April 16th, 2006, 07:33 AM

Quote:

Originally Posted by Chuck S

You issue seems to be one of trying to convert previous mysql data stored in one format to another and not specifically something with the program if I follow this thread right.

If you post a new photo etc that displays correctly right?

Correct. Its not an issue with PhotoPost functioning. Its an issue that we are moving the solborg.fhs.no domain to a new web host that has UTF-8 as the default character encoding.

In the solborg.fhs.no domain, we have PhotoPost, punBB forum and Textpattern. Of these, only Textpattern works in UTF-8 (as best I know).

I have setup the new hosting of this site for testing, so when I refer to errors, I am referring to the new host on which UTF-8 is the default.

To see the new site, I edit my /etc/hosts file and add this line:
207.7.108.212 solborg.fhs.no www.solborg.fhs.no

The flush the cache:
lookupd -flushcache

The solborg.fhs.no will resolve to the new site, so you can see what is happening.

Instructions for editing the hosts file is here. The new web host is TextDrive.