[Fixed]: wrong url displayed in detailed statistics

[tinorebel]

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

[Joachim Müller]

Known bug in cpg1.4.x

[tinorebel]

Thanks.... :-\

[Paver]

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.

[Paver]

Before running any of the queries here, I strongly recommend backing up your database (using phpMyAdmin or the Coppermine plugin "backup/restore").

To convert your current referer fields to proper characters, you can use the following MySQL query (using a tool like phpMyAdmin):

Code Select Expand

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 Expand

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.

The function above comes from Lukas Oberhuber.