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: Upgrade from ancient 1.1: can't solve charset problem  (Read 8293 times)

0 Members and 1 Guest are viewing this topic.

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Upgrade from ancient 1.1: can't solve charset problem
« on: December 09, 2010, 11:57:30 pm »

Hello,

Recently I upgraded from 1.1 first to 1.4.x and then to 1.5.10.

Stangely some tables changed from latin1_swedish_ci to utf8_general_ci but some didn't:
 cpg11d_albums
 cpg11d_categories 
 cpg11d_comments
 cpg11d_config
 cpg11d_pictures
 cpg11d_usergroups
 cpg11d_users
 cpg11d_votes 

I changed them by hand and put them in the database. All looks well in the database, but still I see the wrong characters in filenames.

Also the configuration in coppermine is set to unicode (utf8)

I tried this solution by changing functions.inc:
Code: [Select]
        if (!mysql_select_db($CONFIG['dbname']))
                return false;
        mysql_query("SET NAMES 'utf8'", $result);
        return $result;

Then the characters in the gallery seem to be alright, BUT then the links to the pictures (with filenames with special characters) are not working anymore. So now I have the original functions.inc again.

Also adding a line in config.inc.php did not work:
Code: [Select]
$CONFIG['dbcharset'] = 'utf8';
Please can somebody help me? Thanks in advance!

This is the link to our gallery

The database table for _pictures looks like this:
Code: [Select]
-- phpMyAdmin SQL Dump
-- version 2.9.2-Debian-1.one.com1
-- http://www.phpmyadmin.net
--
-- Host: MySQL Server
-- Generatie Tijd: 09 Dec 2010 om 22:48
-- Server versie: 5.0.51
-- PHP Versie: 5.2.0-8+etch16
--
-- Database: `magyar_vizsla_n`
--

-- --------------------------------------------------------

--
-- Tabel structuur voor tabel `cpg11d_pictures`
--

CREATE TABLE `cpg11d_pictures` (
  `pid` int(11) NOT NULL auto_increment,
  `aid` int(11) NOT NULL default '0',
  `filepath` varchar(255) NOT NULL default '',
  `filename` varchar(255) NOT NULL default '',
  `filesize` int(11) NOT NULL default '0',
  `total_filesize` int(11) NOT NULL default '0',
  `pwidth` smallint(6) NOT NULL default '0',
  `pheight` smallint(6) NOT NULL default '0',
  `hits` int(10) NOT NULL default '0',
  `mtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ctime` int(11) NOT NULL default '0',
  `owner_id` int(11) NOT NULL default '0',
  `pic_rating` int(11) NOT NULL default '0',
  `votes` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `caption` text NOT NULL,
  `keywords` varchar(255) NOT NULL default '',
  `approved` enum('YES','NO') NOT NULL default 'NO',
  `galleryicon` int(10) unsigned NOT NULL default '0',
  `user1` varchar(255) NOT NULL default '',
  `user2` varchar(255) NOT NULL default '',
  `user3` varchar(255) NOT NULL default '',
  `user4` varchar(255) NOT NULL default '',
  `url_prefix` tinyint(4) NOT NULL default '0',
  `pic_raw_ip` tinytext,
  `pic_hdr_ip` tinytext,
  `lasthit_ip` tinytext,
  `position` int(11) NOT NULL default '0',
  `guest_token` varchar(32) default '',
  PRIMARY KEY  (`pid`),
  KEY `pic_hits` (`hits`),
  KEY `pic_rate` (`pic_rating`),
  KEY `aid_approved` (`aid`,`approved`),
  KEY `owner_id` (`owner_id`),
  KEY `pic_aid` (`aid`,`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10329 ;
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #1 on: December 10, 2010, 12:22:01 pm »

All looks well in the database, but still I see the wrong characters in filenames.
Can you please post some examples? I didn't found any issues with your gallery on the quick.
Logged

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #2 on: December 10, 2010, 07:12:29 pm »

Ofcourse, thanks for your reply.

If you put "Tess (de Teckel)" in the search field (it's ZOEK in Dutch) then you will see that the name Büszke looks like this B�szke.

In the database (for example) is still looks like this:
Code: [Select]
INSERT INTO `cpg11d_pictures` VALUES (8898, 186, 'userpics/10109/', 'Büszke (17).jpg', 88376, 91966, 800, 600, 450, '2010-11-04 18:44:57', 1255887855, 0, 8000, 1, 'Büszke en Tess (de Teckel)', '', '', 'YES', 0, '', '', '', '', 0, NULL, NULL, NULL, 0, '');


Or another http://www.magyar-vizsla.nl/coppermine/albums/userpics/10277/thumb_Szer%E9ny%20resized.JPG. You can find it by zoek: "schapenvacht".

Code: [Select]
INSERT INTO `cpg11d_pictures` VALUES (8670, 183, 'userpics/10028/', 'Szérény.JPG', 108133, 110940, 640, 427, 407, '2010-11-22 21:03:44', 1251881706, 28, 0, 0, 'Szérény d\\', '', '', 'YES', 0, '', '', '', '', 0, NULL, NULL, '62.194.161.219', 0, '');


Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #3 on: December 10, 2010, 09:36:26 pm »

Did you set 'Character encoding (Unicode recommended!)'  in Coppermine's config to utf-8, too?
Logged

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #4 on: December 10, 2010, 10:45:59 pm »

Yes, I did. You can see in the attachment.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #5 on: December 12, 2010, 11:13:15 am »

Does it work in a second test gallery with a new installation of cpg1.5.10 on the same server?
Logged

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #6 on: December 12, 2010, 04:11:28 pm »

A new installation works fine. I uploaded one picture with a 'strange' name and that all works well.

This is the test gallery

I uploaded all pictures into the right album. And also filled the database with all the 'old' content. The pictures show up, but not the (whole) title and names. Now I see another problem. The filenames 'stop' when there's a special character in the name of title. So "Büszke" now is "B". So I quess the problem is in the tables. Can you give me some advice in how to make them work right?
Logged

Brooklyn

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 33
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #7 on: December 12, 2010, 04:50:55 pm »

I had the exact issue when updating the same versions and was only able to display the pictures properly by manually editing the file names with "corrupt" characters. I also had to turn off reading of EXIF data to display pictures properly. And there's also the matter of "filmstrip overlap" that was another subsequent result of the update from 1.1d to 1.5.8.

In addition, there are member galleries that no longer display (including the admins 10001 member gallery), although all files are still contained on the server in their corresponding directories. And none of these contain "corrupt" file characters. No error message is present, simply an empty themed display.

Do you have these issues as well? If so, we can determine these are related and not isolated to a particular system setup.

Please note, I make reference to a gallery that is no longer available online but lives only on my hard drive -- though I do plan to someday soon republish it as part of a different, yet to be established website.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #8 on: December 12, 2010, 07:29:41 pm »

yvet, try to compare the settings of the two galleries (have a look at the config tables in the database). Maybe you find some related settings that are different. You could also try to import the data into the test gallery's database. Maybe it works. At this point I can just guess. I had a similar issue while upgrading from cpg1.3.x to cpg1.4.x. But that's long time ago and I solved it 'by accident' while trying a lot of things.
Logged

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #9 on: December 12, 2010, 10:14:57 pm »

At the moment working, testing and trying to make the database OK.

The question that comes up now is if it's normal that with a clean install the database also changes special characters? I tried it on two different websites and they both do something like this:

Code: [Select]
INSERT INTO `cpg15x_comments` VALUES (10329, 1, 'Catou', 'Büszke is alweer 16 maanden oud', '2010-12-12 21:05:49', '82.168.87.33', '82.168.87.33', '', 109, 'YES', 'NO');


Where 'Büszke' does show up correct in the gallery as "Büszke"
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #10 on: December 14, 2010, 04:20:12 pm »

That's maybe an encoding issue. To be honest, I'm no expert in such things, so I doubt I cannot help you with your issue at this point.
Logged

yvet

  • Coppermine newbie
  • Offline Offline
  • Posts: 6
Re: Upgrade from ancient 1.1: can't solve charset problem
« Reply #11 on: December 23, 2010, 07:41:18 pm »

André, The gallery is now working properly. I asked my host to look into it and they did. They found no faults in the database imports. The characters in the tables still do not look like the livesite. So "Büszke" looks like "Büszke". New uploads also show up like this in the database. But as long as everithing is the same, that's ok.

The host sugested to ad in /coppermine/include/config.inc.php
Code: [Select]
$CONFIG['dbcharset'] = 'utf8';

And that worked well because now I'll can sure that all data is the same.

Unfortunately the filenames did not show up correct. So by searching for special chararters in the filenames (for exampla search for *é*) en then manually changing them, all is working as it should now.

steps I took:
Backup old gallery
clean install of cpg1.5.10
compare sql old and new and manually cut and paste old data in 'new' tables
added to /coppermine/include/config.inc.php the line: $CONFIG['dbcharset'] = 'utf8';
manually changed filenames in directories (the ones with special characters)
manually changed filenames in database

It took a lot of time, but I got there.

@Brooklyn I'm sorry I can't help you with the problems you have. We did not experience problems with members galleries, filmstrip overlap or anything else.
Logged
Pages: [1]   Go Up
 

Page created in 0.024 seconds with 19 queries.