Upgrade from ancient 1.1: can't solve charset problem

[yvet]

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 Expand

        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 Expand

$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 Expand

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


[Αndré]

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.

[yvet]

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 Expand

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 Expand

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, '');



[Αndré]

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

[yvet]

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

[Αndré]

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

[yvet]

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?

[Brooklyn]

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.

[Αndré]

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.

[yvet]

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 Expand

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"

[Αndré]

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.

[yvet]

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 Expand

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