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 [2]   Go Down

Author Topic: how to update mysql for non existing images  (Read 17291 times)

0 Members and 2 Guests are viewing this topic.

hobert

  • Contributor
  • Coppermine novice
  • ***
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 29
Re: how to update mysql for non existing images
« Reply #20 on: April 16, 2015, 01:14:36 am »

I occasionally make changes to pictures via FTP, and wrote a little code snippet to make the delete check in the edit pics page autofill if the original pic is missing.  Then I just process each set of 100, starting from the last and move backwards through an album.  It takes about 10-15 secs per 100 (3-5 mins per 1000).

in editpics.php, after:
Code: [Select]
$isgalleryicon_selected = ($CURRENT_PIC['galleryicon']) ? 'checked="checked" ':'';
$isgalleryicon_disabled = (!isset($CURRENT_PIC['category']) || ($CURRENT_PIC['category'] < FIRST_USER_CAT)) ? ' style="display:none;" ':'';
I added a line:
Code: [Select]
$checked= (file_exists("albums/".$CURRENT_PIC['filepath'].$CURRENT_PIC['filename'])) ? '' : 'checked';//if file is missing, automatically check the delete box
I modified:
Code: [Select]
<input type="checkbox" name="delete{$CURRENT_PIC['pid']}" id="delete{$CURRENT_PIC['pid']}" value="1" class="checkbox" title="{$lang_editpics_php['del_pic']}" /><label for="delete{$CURRENT_PIC['pid']}" class="clickable_option">{$icon_array['delete']}</label>to (added $checked)
Code: [Select]
<input type="checkbox" name="delete{$CURRENT_PIC['pid']}" id="delete{$CURRENT_PIC['pid']}" value="1" class="checkbox" title="{$lang_editpics_php['del_pic']}" $checked /><label for="delete{$CURRENT_PIC['pid']}" class="clickable_option">{$icon_array['delete']}</label>
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: how to update mysql for non existing images
« Reply #21 on: November 21, 2017, 07:41:38 pm »

Not sure if to reply to this or start new topic - SORRY!

I'm trying to create a new gallery on a different host with 1 user from an existing gallery with several users. The gallery works but I cannot remove the "missing images" that appear. I obviously didn't include the userpics folders from the other users, as they were not needed, and that's what I can't delete. A couple of the users only had a few images and I was able to upload those userpic folders, then delete the users and files. But the remaining users have in one case over 1000 images, so rather a lot to upload, just to delete.

I've tried the check_files plugin and the editpics.php suggestion but neither seem to work. Any suggestions please.

Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #22 on: November 21, 2017, 07:46:50 pm »

So you cloned a complete gallery and now want to remove all pictures but from one user? If so, we can easily remove all pictures from the database where that user is not the owner. Please post a link to your new gallery and tell us which is that one user.
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #23 on: November 21, 2017, 10:26:54 pm »

So you cloned a complete gallery and now want to remove all pictures but from one user? If so, we can easily remove all pictures from the database where that user is not the owner. Please post a link to your new gallery and tell us which is that one user.
Thanks Αndré,

http://www.sharpo.uk/andrew/index.php?cat=1

The link is above, that page shows which user I wish to retain, the other two are to be deleted. Had it been just a few files for each user, I would've uploaded them and deleted via "users", as I did with a couple of other users.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #24 on: November 21, 2017, 10:31:32 pm »

What should happen with the pictures in the other categories? You need to say what exactly has to be deleted and what not.
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #25 on: November 21, 2017, 11:33:33 pm »

What should happen with the pictures in the other categories? You need to say what exactly has to be deleted and what not.
What I was attempting to do was just leave the 1 user. The categories could stay but be removed/deleted individually later if the remaining user had no images that linked to them.

My aim was to make Andrew Hadley administrator, then delete Richard Shaw and me (sharpo) which would leave 1 Admin and Andrew. I just deleted the other user (37 427 I think it was) as no images were assigned to him. Hope that explains my aims.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #26 on: November 22, 2017, 10:01:15 am »

So all other pictures, which doesn't reside in the albums of the following category should be deleted, right? http://www.sharpo.uk/andrew/index.php?cat=10007

This means all other user galleries and all pictures in all other categories and albums. Is this correct?
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #27 on: November 22, 2017, 10:38:01 am »

So all other pictures, which doesn't reside in the albums of the following category should be deleted, right? http://www.sharpo.uk/andrew/index.php?cat=10007

This means all other user galleries and all pictures in all other categories and albums. Is this correct?
That's correct, yes.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #28 on: November 22, 2017, 10:42:33 am »

Run this query with a tool like phpMyAdmin:
Code: [Select]
DELETE FROM cpg15x_pictures WHERE owner_id != 7
Please create a backup before you run the query. Maybe you need to adjust the table prefix (cpg15x_).
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #29 on: November 22, 2017, 11:38:17 am »

Run this query with a tool like phpMyAdmin:
Code: [Select]
DELETE FROM cpg15x_pictures WHERE owner_id != 7
Please create a backup before you run the query. Maybe you need to adjust the table prefix (cpg15x_).
Changed it to cpg14x and ran it, but nothing seems to have changed.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #30 on: November 22, 2017, 11:42:06 am »

Either the table prefix is still wrong (just have a look at the table names or at include/config.inc.php) or the MySQL user you used doesn't have sufficient permissions to delete from that database/table. What's the output when you run the query?
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #31 on: November 22, 2017, 11:52:49 am »

Either the table prefix is still wrong (just have a look at the table names or at include/config.inc.php) or the MySQL user you used doesn't have sufficient permissions to delete from that database/table. What's the output when you run the query?
I submit the query using phpmyadmin on the cpg14x_pictures table, it seems to finish doing whatever after a few seconds, but no indication of what it did or didn't do.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: how to update mysql for non existing images
« Reply #32 on: November 22, 2017, 11:59:05 am »

Create a new .php file in your gallery root and add this code to it:
Code: [Select]
<?php
define
('IN_COPPERMINE'true);
require(
'include/init.inc.php');
pageheader();
cpg_db_query("DELETE FROM {$CONFIG['TABLE_PICTURES']} WHERE owner_id != 7");
echo 
"Done.";
pagefooter();
//EOF

Now, open that file with your browser.
Logged

sharpo

  • Coppermine frequent poster
  • ***
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 332
Re: Re: how to update mysql for non existing images
« Reply #33 on: November 22, 2017, 12:12:07 pm »

Create a new .php file in your gallery root and add this code to it:
Code: [Select]
<?php
define
('IN_COPPERMINE'true);
require(
'include/init.inc.php');
pageheader();
cpg_db_query("DELETE FROM {$CONFIG['TABLE_PICTURES']} WHERE owner_id != 7");
echo 
"Done.";
pagefooter();
//EOF

Now, open that file with your browser.
Fantastic! Appears to have worked, now showing as 532 files in the gallery, which as far as I know is correct.

Thank you very much Αndré, I appreciate your help.
Logged
Sharpo (not an expert, just a Coppermine user)
3 live galleries, first started in 2006.
http://www.sharpos-world.co.uk/BB3cpg/ with over 8,000 images.
http://www.sharpos-world.co.uk/cpg/ with over 25,000 images. 1.6.25
http://www.sharpos-world.co.uk/kc/ with over 300 images. 1.6.25
Pages: 1 [2]   Go Up
 

Page created in 0.021 seconds with 20 queries.