Advanced search  

News:

CPG Release 1.6.26
Correct PHP8.2 issues with user and language managers.
Additional fixes for PHP 8.2
Correct PHP8 error with SMF 2.0 bridge.
Correct IPTC supplimental category parsing.
Download and info HERE

Pages: [1]   Go Down

Author Topic: [Fixed]: wrong url displayed in detailed statistics  (Read 34272 times)

0 Members and 1 Guest are viewing this topic.

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
[Fixed]: wrong url displayed in detailed statistics
« 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...
« Last Edit: March 03, 2009, 03:15:53 am by Paver »
Logged
Live long and prosper!
www.tripodart.net

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47843
  • aka "GauGau"
    • gaugau.de
Re: wrong url displayed in detailed statistics
« Reply #1 on: May 13, 2008, 08:39:19 am »

Known bug in cpg1.4.x
Logged

tinorebel

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 87
  • live long and prosper!
    • www.tripodart.net
Re: wrong url displayed in detailed statistics
« Reply #2 on: May 13, 2008, 12:55:54 pm »

Thanks.... :-\
Logged
Live long and prosper!
www.tripodart.net

Paver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1609
  • Paul V.
Re: [Fixed]: wrong url displayed in detailed statistics
« Reply #3 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.
« Last Edit: March 03, 2009, 04:23:06 am by Paver »
Logged

Paver

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1609
  • Paul V.
Re: [Fixed]: wrong url displayed in detailed statistics
« Reply #4 on: March 03, 2009, 05:04:57 am »

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

The function above comes from Lukas Oberhuber.
Logged
Pages: [1]   Go Up
 

Page created in 0.018 seconds with 19 queries.