forum.coppermine-gallery.net

Dev Board => cpg1.4 Testing/Bugs => cpg1.4 Testing/Bugs: FIXED/CLOSED => Topic started by: tinorebel on May 13, 2008, 01:33:44 am

Title: [Fixed]: wrong url displayed in detailed statistics
Post by: tinorebel on May 13, 2008, 01:33:44 am
Hallo, I hpe is the right place to post this.

I't sounds like a stupid problem ???, but if someone can help I would appreciate:

In my detailed stats I'm getting funny urls like:

%3A%2F%2Fwww.tripodart.net%2Fwebsite%2Fgal%2Fthumbnails.php%3Falbum%3Dlastup%26cat%3D0

Of curse seems a coding problem, but can't guess which.
I noticed these urls come up just since I upgraded MySQL to version 5.
On mysql I have set characters to  UTF-8 Unicode (utf8) (UTF8_unicode_ci), same as in my gallery coinfig.

Thanks for suggestions...
Title: Re: wrong url displayed in detailed statistics
Post by: Joachim Müller on May 13, 2008, 08:39:19 am
Known bug in cpg1.4.x
Title: Re: wrong url displayed in detailed statistics
Post by: tinorebel on May 13, 2008, 12:55:54 pm
Thanks.... :-\
Title: Re: [Fixed]: wrong url displayed in detailed statistics
Post by: Paver on March 03, 2009, 03:17:12 am
I couldn't find this bug on the bugs board so I moved this topic to mark the bug fixed.

Fixed and committed in 1.4 Subversion repository.  Was already fixed in 1.5. Fixed & committed to 1.5.

Will be in 1.4.21 and higher.
Title: Re: [Fixed]: wrong url displayed in detailed statistics
Post by: Paver on March 03, 2009, 05:04:57 am
Before running any of the queries here, I strongly recommend backing up your database (using phpMyAdmin (http://www.phpmyadmin.net) or the Coppermine plugin "backup/restore (http://forum.coppermine-gallery.net/index.php/topic,40439.0.html)").

To convert your current referer fields to proper characters, you can use the following MySQL query (using a tool like phpMyAdmin):
Code: [Select]
DELIMITER $$
DROP FUNCTION IF EXISTS `url_decode` $$
CREATE FUNCTION `url_decode`(original_text text) RETURNS text CHARSET utf8
BEGIN
declare new_text text default null;
declare pointer int default 1;
declare end_pointer int default 1;
declare encoded_text text default null;
declare result_text text default null;
SET new_text = replace(original_text,'+',' ');
SET pointer = LOCATE("%", new_text);
while pointer <> 0 && pointer < (char_length(new_text) - 2) do
SET end_pointer = pointer + 3;
while mid(new_text, end_pointer, 1) = "%" do
SET end_pointer = end_pointer+3;
end while;
SET encoded_text = mid(new_text, pointer, end_pointer - pointer);
SET result_text = convert(unhex(REPLACE(encoded_text, "%", "")) using utf8);
SET new_text = REPLACE(new_text, encoded_text, result_text);
SET pointer = LOCATE("%", new_text, pointer + char_length(result_text));
end while;
return new_text;
END $$
DELIMITER ;
UPDATE `cpg_hit_stats` SET referer = url_decode(referer)
where you replace in the last line cpg_hit_stats with your table name, e.g. cpg145_hit_stats.  Do the same for the _vote_stats table, e.g. cpg145_vote_stats.

The above code worked for me on one database with over 45,000 rows.

On another database (on a different web host), I got an error mentioning DELIMITER ; UPDATE . . ..  For this one, I used the following code in phpMyAdmin while also changing the Delimiter to $$ in phpMyAdmin's box just below the query box:
Code: [Select]
DROP FUNCTION IF EXISTS `url_decode` $$
CREATE FUNCTION `url_decode`(original_text text) RETURNS text CHARSET utf8
BEGIN
declare new_text text default null;
declare pointer int default 1;
declare end_pointer int default 1;
declare encoded_text text default null;
declare result_text text default null;
SET new_text = replace(original_text,'+',' ');
SET pointer = LOCATE("%", new_text);
while pointer <> 0 && pointer < (char_length(new_text) - 2) do
SET end_pointer = pointer + 3;
while mid(new_text, end_pointer, 1) = "%" do
SET end_pointer = end_pointer+3;
end while;
SET encoded_text = mid(new_text, pointer, end_pointer - pointer);
SET result_text = convert(unhex(REPLACE(encoded_text, "%", "")) using utf8);
SET new_text = REPLACE(new_text, encoded_text, result_text);
SET pointer = LOCATE("%", new_text, pointer + char_length(result_text));
end while;
return new_text;
END $$
UPDATE `cpg_hit_stats` SET referer = url_decode(referer) WHERE referer REGEXP '%'
replacing the table in the last line as before.  The number of rows affected here was over 185,000.  I ran the same script a second time and it affected about 400 rows, then a third with 1 row, then nothing after that.  I'm not sure why it didn't process all the rows the first time.  I wasn't generating new bad referer fields I don't think.  I didn't have enough time to investigate so I just mention it here in case it's useful for anyone.

As I said above, make sure to back up your database first.

If the scripts don't work, please don't ask here.  You'll need to consult the MySQL documentation (http://dev.mysql.com/doc/).

The function above comes from Lukas Oberhuber (http://forge.mysql.com/tools/tool.php?id=177).