Issues with special characters and umlauts after upgrading from PHP4 to PHP5

[olli80]

[Splitted from http://forum.coppermine-gallery.net/index.php/topic,77392.0.html]

Ok my server is now on php5.x

I was able to run update.php successfully.

But it was killing all my special charaters and my umlauts. How can i bring them back?

[phill104]

That is an area where I struggle. Usually it is the database collation that needs adjusting. There are plenty of threads dealing with that so a search might bring you the quickest results, or maybe one of the other team members can assist.

[olli80]

Yes it seems to be a problem with the collation. Dunno how i can change this.

Was reading some others thread but didnt find a solution for me!

[gmc]

A couple of questions:
What is the collation specified in the CPG tables?  (the real question is the charset, but the collation tells us that too...)
My guess is it will be either latin1 or utf8...

What is the character encoding specified in CPG config? (under language & charset settings)

Do you specify the $dbcharset variable (and with what value) in /include/config.inc.php ?

Does the data 'look' correct (special characters and umlauts) when viewing directly via a tool like phpMyAdmin?

Based on answers, we'll figure out the right specification or conversion needed...
Greg

[olli80]

A couple of questions:
What is the collation specified in the CPG tables?  (the real question is the charset, but the collation tells us that too...)
My guess is it will be either latin1 or utf8...

latin1_swedish_ci

What is the character encoding specified in CPG config? (under language & charset settings)

unicode utf-8

Do you specify the $dbcharset variable (and with what value) in /include/config.inc.php ?

No i did not change anything. There is no $dbcharset variable.

Does the data 'look' correct (special characters and umlauts) when viewing directly via a tool like phpMyAdmin?

The data is correct stored inside the mysql db.

[gmc]

OK... Try adding:

Code Select Expand

$dbcharset = 'utf8';
to config.inc.php (anywhere)

This will add a database call to force the charset to utf8 - and hopefully display the data correctly.
Just remove the line if it doesn't work. It won't change any existing data in your database.

At some point you will want to actually convert your MySQL tables to UTF8.

(I will be away from my PC for much of the day - I will respond later today/tonight... )
Greg

[olli80]

I added this line but it does not change anything.

The characters are still wrong............. 

[olli80]

At some point you will want to actually convert your MySQL tables to UTF8.

How can i do this?

[phill104]

This link should help - http://bit.ly/1gZciOh

Take a copy of your db first and work on that. Hopefully it should go smoothly though.

[olli80]

Ähm yes, entering this into google search is not the problem.

The problem is my missing knowledge about mysql databases.

I read a lot of articles about "Converting your MySQL database to UTF8" but i dont understand whats going on there.
It seems that there are a lot of ways to do this and a lot of way to fail.

Dont get me wrong now but is there no "nooby" way to do this?
I´m able to open phpmyadmin and look into my database. I can change some options and do some adjustments but i can NOT write my owns scripts to do a convert.

Mhhh maybe going to PHP5 was not that clever as i thought............

[olli80]

Ok i changed the notation by hand to looks like in the picture. But it seems to need more because it is still not working..............

[gmc]

Ok i changed the notation by hand to looks like in the picture. But it seems to need more because it is still not working..............

Appears you did the ALTER statements to change the charset/collation to UTF-8... but this doesn't convert existing data...

The data is correct stored inside the mysql db.

(from earlier post) This tells me (at least from other cases I've seen so far) that the data doesn't need to be converted...
If the data didn't appear correct in phpMyAdmin - I would suggest the SQL used in this post:
http://forum.coppermine-gallery.net/index.php/topic,77017.msg373366.html#msg373366
An SQL statement for each field to be converted:

Code Select Expand

UPDATE cpg_categories SET name= CONVERT(CAST(CONVERT(description USING latin1) AS binary) USING utf8);
but I expect that will have no effect with the data appearing correctly already.
Can you attach a screen print showing the data in phpMyAdmin?

It is a challenging area - be sure to back up your data first before making changes - and keep track of any changes made to code (like the $dbcharset variable) to be sure you are testing valid combinations...

PHP5 is the right choice for many reasons... I haven't seen a PHP change cause issues in this area - but won't rule it out... I have seen moving between hosts cause this (likely different mySQL config settings)... or a MySQL upgrade. Or even a user entering data for the first time with 'international' characters.

[olli80]

Ok so i will try to give you an overview:

Picture1:
You see the overview of the database. The collation is set to UTF8

Picture2:
You see the cpg_picture table. Inside the picture table the collation is still latin. I dont know how this is affecting the problem.

Picture3:
You see the data stored in the picture table. Here you see a lot of umlauts like ä, ü, ö displayed correct in the caption row.

Picture4:
Here you see the website. In the description of the pictures you see the wrong umlauts (ö,ä,ü). At the top red box you see a correct umlaut "super süß". This one i changed manually with the website admin tools. Doing so display it correct.

Picture5:
Trying to update the cpg_picture table with your sql gives me an syntax error.

Picture6:
Another try with modified syntax ends up with error also.

Picutre7:
Changed collation of caption row manually does not change anything.

So i tried a lot but nothing fixed the problem.

Do you have any ideas?

[olli80]

Picutres

[olli80]

Update:

Using this command:
UPDATE cpg15x_pictures SET caption= CONVERT(CAST(CONVERT(caption USING latin1) AS binary) USING utf8);

Command runs sucessfully but delete all umlauts............

[gmc]

Sorry... seemed I missed your last post... just saw it checking the thread.

Looking at your displays - it seems you changed the table definition to utf8 - but the individual fields are still latin1...
That may be causing the loss of the umlauts as it is trying to write utf8 data into a latin1 field.

Changing all the fields to utf8 prior to the convert SQL may resolve that...
Be sure they appear correctly in phpMyAdmin after the convert - you may still need to add the $dbcharset specification to your CPG confg.inc.php file.