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: utf8 weird character encoding in "keyword" field only  (Read 2917 times)

0 Members and 1 Guest are viewing this topic.

flapane

  • Contributor
  • Coppermine frequent poster
  • ***
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 251
    • Flavio's page
utf8 weird character encoding in "keyword" field only
« on: June 28, 2014, 12:07:51 pm »

I've just noticed that some special characters are displayed well in the title but not in the keyword field. Please see http://www.flapane.com/gallery/displayimage.php?album=43&pid=2152#top_display_media where the letter is displayed as capital A with a tilde on it followed by a symbol in the keywords.
The page is in utf-8, however I see that the sql dbase is in latin1_swedish_ci. The dbase has been created under cpg1.3 or cpg1.4 years ago, but frankly I don't know why such encoding has been selected while creating the dbase.

Any hints?
Thanks in advance

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: utf8 weird character encoding in "keyword" field only
« Reply #1 on: June 28, 2014, 01:39:03 pm »

You appear to be running 1.5.26. Please upgrade to 1.5.28 and see if it addresses it.
If not, does the data appear correct when viewed with a tool like phpMyAdmin? (direct in table)

See thread http://forum.coppermine-gallery.net/index.php/topic,77355.msg373996.html#msg373996 for converting and 'fixing' data not stored correctly (also references the fix in 1.5.28).
Even if 1.5.28 fixes your problem, converting the database/tables to utf8 is a good idea - especially when 'non-Latin' characters are in use.
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

flapane

  • Contributor
  • Coppermine frequent poster
  • ***
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 251
    • Flavio's page
Re: utf8 weird character encoding in "keyword" field only
« Reply #2 on: June 28, 2014, 10:38:35 pm »

Woops, I forgot to upgrade to 1.5.28.
Thanks, it did the trick.
Also, it seems that special characters are not shown correctly in PhpMyAdmin. Attached are screenshoots from _dict and _languages tables.

So I followed your instructions for, let's say, _pictures table (where letter in captions and titles are shown as ì in PhpMyAdmin, but are shown correctly in an utf-8 web page)
Code: [Select]
ALTER TABLE cpg132_pictures CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

UPDATE cpg132_pictures SET title= CONVERT(CAST(CONVERT(title USING latin1) AS binary) USING utf8);

UPDATE cpg132_pictures SET caption= CONVERT(CAST(CONVERT(caption USING latin1) AS binary) USING utf8);

UPDATE cpg132_pictures SET keywords= CONVERT(CAST(CONVERT(keywords USING latin1) AS binary) USING utf8);

Now is shown as in PhpMyAdmin, but it's become an ugly � in an utf-8 web page (Firefox).
Character encoding is set to utf-8 in cpg config and at web server level (AddDefaultCharset UTF-8).

In theory v1.5.28 fixed the issue, but it wouldn't hurt if tables could be converted to utf-8.

Thanks
« Last Edit: June 28, 2014, 11:21:13 pm by flapane »
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: utf8 weird character encoding in "keyword" field only
« Reply #3 on: June 29, 2014, 01:59:23 am »

OK... one more place to specify utf8...
In /include/config_inc.php add line:
Code: [Select]
$CONFIG['dbcharset'] ='utf8';
This will force CPG to explicitly tell MySQL to use UTF8 encoding... I expect that will fix the display.
Logged
Thanks!
Greg
My Coppermine Gallery
Need a web hosting account? See my gallery for an offer for CPG Forum users.
Send me money

flapane

  • Contributor
  • Coppermine frequent poster
  • ***
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 251
    • Flavio's page
Re: utf8 weird character encoding in "keyword" field only
« Reply #4 on: June 29, 2014, 11:03:25 am »

Thanks, it worked great! Now I converted all the tables, taking care of the columns where "strange" data was present.
I wonder why it needs to be forced and it doesn't automatically recognize the encoding of the sql tables, and if it due to my web server (shared hosting) configuration.
« Last Edit: June 29, 2014, 11:29:38 am by flapane »
Logged

gmc

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 785
    • GMC Design Photo Gallery
Re: utf8 weird character encoding in "keyword" field only
« Reply #5 on: June 29, 2014, 01:13:05 pm »

Support for $CONFIG['dbcharset'] ='utf8'; was added specifically for cases where things don't synch properly.. In theory it shouldn't be needed - as the table setting should override.
MySQL tries to 'fix' mismatches - and doesn't always choose right. I haven't been able to figure out what settings (MySQL installation) drive this. We have had people need to add that statement with a change of hosts.. Or have characters start displaying incorrectly needing the conversion steps to resolve.

Please mark your topic as solved (bottom left of page).
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 21 queries.