Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Issues with special characters and umlauts after upgrading from PHP4 to PHP5  (Read 6820 times)

0 Members and 1 Guest are viewing this topic.

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

[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?
« Last Edit: April 22, 2014, 01:02:13 pm by Αndr »
Logged

Phill Luckhurst

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4723
    • Windsurf.me

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.
Logged
It is a mistake to think you can solve any major problems just with potatoes.

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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!
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery

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
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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

Quote
What is the character encoding specified in CPG config? (under language & charset settings)
unicode utf-8

Quote
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.

Quote
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.

Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery

OK... Try adding:
Code: [Select]
$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
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

I added this line but it does not change anything.

The characters are still wrong.............  :(
Logged

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

At some point you will want to actually convert your MySQL tables to UTF8.
How can i do this?
Logged

Phill Luckhurst

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4723
    • Windsurf.me

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.
Logged
It is a mistake to think you can solve any major problems just with potatoes.

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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?
Im 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............

Logged

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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..............

Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery

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...

Quote
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]
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.
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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?
Logged

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

Picutres
Logged

olli80

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 24

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............ :(
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery

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.
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money
Pages: [1]   Go Up
 

Page created in 0.021 seconds with 19 queries.