forum.coppermine-gallery.net

Support => cpg1.5.x Support => cpg1.5 miscellaneous => Topic started by: babez on January 16, 2015, 06:17:25 pm

Title: how to update mysql for non existing images
Post by: babez on January 16, 2015, 06:17:25 pm
hi , i was accidentally delete lots of my images, and now on my album i glot lots of blank / trash can (maybe ..)  images only .
is there a way to update mysql so it will delete all rows that didnt link to any images ?

thank you
Title: Re: how to update mysql for non existing images
Post by: gmc on January 17, 2015, 02:46:28 am
My first suggestion would be to see if the files can be recovered from a backup you or your webhost might have...

if not need to understand what was deleted. Just thumbnails? CPG can recreate these via Admin Tools as an example.

If all sizes of pics are really deleted and not recoverable - you would need a script to validate all pics in the database, and remove those no longer existing...
But I would leave that as a last resort..

Greg
Title: Re: how to update mysql for non existing images
Post by: babez on January 17, 2015, 03:04:17 am
i dont have any backup files anympre , its on a vps not a shared hosting so its imposibble to recover the backup files ,

is there a way to get the scripts ? what do you suggest ?
Title: Re: how to update mysql for non existing images
Post by: gmc on January 17, 2015, 03:20:45 am
To clarify - all versions of the pictures (thumb_, normal_, orig_ (if used), and plain filename) were all deleted?
If so, sounds like we need to process the picture table and validate each file exists... Deleting from the database those that no longer do... I would have to write something to do that.

Ow large is your gallery (number of pics) so I have an idea how much processing it will do...
At least a VPS should have the resources to process.

Lets see if any of the other devs have a script already or an alternative - and go from there.
Greg
Title: Re: how to update mysql for non existing images
Post by: Phill Luckhurst on January 17, 2015, 01:05:50 pm
On any VPS if you have room try and install backup2l to automate backups. Not much help now but will in the future.

How many images have you accidentally deleted?

I've not had time to check, but maybe this little mod could be adjusted to work with 1.5 - http://forum.coppermine-gallery.net/index.php/topic,61925.msg307233.html#msg307233
Title: Re: how to update mysql for non existing images
Post by: babez on January 17, 2015, 01:35:47 pm
sorry for the late reply ,
maybe around 1000+ images
im not very familiar with sql stuff , so i hope someone will help me .
i've tried several images gallery script , and coppermine is the best choise i have so far.
it would be nice if there is a plugins that will sort out which images are not in databases anymore :D
Title: Re: how to update mysql for non existing images
Post by: gmc on January 18, 2015, 04:34:00 pm
Phill,
I like that mod - it identifies the missing files and allows normal coppermine code to process the deletes - doing all the needed cleanup...
The downside is if there are a lot of albums - or albums with more than 100 files (the max you can display in edit at once) - will need to repeat the process once or more on each... (maybe can adjust that max - at least on vps server shouldn't be a resource issue...)

babez,
Assuming it could be refit - would need you to 'Edit Files', set display to 100 files, and 'Apply Changes'... The pics with missing images will already be selected to delete... (If more than 100 files - I would start at end of album and work backwards...)
Would this work for you?
What version/release of CPG are you running?
Can you provide a link to the gallery so I can see what it looks like?

Plugins (or mods, code changes, etc) start with an idea...  :D

Greg
Title: Re: how to update mysql for non existing images
Post by: netb on January 18, 2015, 04:36:43 pm
Hello,

There is already a similar plugin which may help you here http://forum.coppermine-gallery.net/index.php/topic,77347.0.html

The UI is a little clumsy but it does find missing files for images in the database or additional files in the user dir that are not in the database.
Title: Re: how to update mysql for non existing images
Post by: gmc on January 18, 2015, 05:56:22 pm
Thanks netb... Didn't remember that one...
It does the identification - but not the fixing - and babez has over 1000 pics missing.

It does seem to save the output - so might be usable as input into an additional 'cleanup' step??
Title: Re: how to update mysql for non existing images
Post by: babez on January 18, 2015, 06:07:24 pm
gmc

sorry i dont have that link anymore , i have deleted the album . cant think of except i have to delete the album hehehehehe
if there is official plugins could do the scan -> and fixing ,i think its would be usefull for other CPG users then manually analyze and delete missing images one by one , dont u think ?
Title: Re: how to update mysql for non existing images
Post by: gmc on January 18, 2015, 08:45:31 pm
Thinking about the best way to do this...
Leaning towards an extension of André's plugin - an option to delete from database if the base and thumb don't exist (and normal too if applicable...)
The picture table entry will need to be deleted - along with any use of the picture (pid) in comments (delete), category or album thumbnails (set to 0), exif data (delete), hit_stats (delete), votes (delete), vote_stats (delete), and favpics (remove pid preserving other pics) references...

Not something I can do today - but I don't think it is that much work...  The plugin does the identification...

babez - I do need to know what release of CPG you are running... A link to the gallery will let me figure it out if you aren't sure.

Greg
Title: Re: how to update mysql for non existing images
Post by: babez on January 19, 2015, 04:20:50 am
hi Greg,

im running CPG 1.5 version

http://sexybabespic.com (NSFW!!!)
Title: Re: how to update mysql for non existing images
Post by: Phill Luckhurst on January 19, 2015, 09:27:09 pm
You are on the latest release, 1.5.34
Title: Re: how to update mysql for non existing images
Post by: Αndré on January 19, 2015, 10:28:49 pm
I'd also use the plugin as base and add some DELETE commands for the identified files. Maybe I find some time tomorrow to create a modified plugin. I need at least one reply, so I get a reminder in my inbox.
Title: Re: Re: how to update mysql for non existing images
Post by: gmc on January 19, 2015, 11:06:52 pm
I'd also use the plugin as base and add some DELETE commands for the identified files. Maybe I find some time tomorrow to create a modified plugin. I need at least one reply, so I get a reminder in my inbox.
Here's your reminder...
If you can't get to it - let me know - and I'll work on it during the week.

Greg

Title: Re: how to update mysql for non existing images
Post by: Αndré on January 28, 2015, 08:59:37 pm
Sorry for the delay. I just updated the plugin in SVN revision 8760. I added a button on the result page, to delete all files with missing full-sized picture from database (as you always can restore the intermediate-sized and thumbnail pictures via the admin tools, if the full-sized picture exists).

I haven't implemented the feature to remove missing pictures from the favorites, as it's not just a simple DELETE query, but you need to decode and re-encode each row separately (lack of time). Additionally, favorite pictures could also stored in cookie (if user is not logged in), so it won't work in all cases and probably need some more coding if it should work reliably.

Everyone feel free to test the update and implement missing features ;) Greg? 8)
Title: Re: how to update mysql for non existing images
Post by: babez on January 29, 2015, 02:33:29 am
hi there, where to download the plugins ?
Title: Re: how to update mysql for non existing images
Post by: gmc on January 29, 2015, 03:53:56 am
Everyone feel free to test the update and implement missing features ;) Greg? 8)
I can take a hint... :)  I'll refresh my svn copy and test...

hi there, where to download the plugins ?
if you aren't running svn software on your pc - you can download the 4 files making up the plugin at:
http://sourceforge.net/p/coppermine/code/HEAD/tree/branches/cpg1.5.x/plugins/check_files/
These should then be uploaded to folder plugins/check_files in your coppermine folder.
You can then install from plugin manager.
(Once tested - a zip file is typically made available to download...)
Title: Re: how to update mysql for non existing images
Post by: babez on January 29, 2015, 10:24:53 am
hi Greg!

thanks for the link ,downloading now ,
Title: Re: how to update mysql for non existing images
Post by: allvip on April 15, 2015, 11:21:55 pm
Is this topic solved because:

Board rules / Forum policies http://forum.coppermine-gallery.net/index.php?index.php/topic,55415.0.html (http://forum.coppermine-gallery.net/index.php?index.php/topic,55415.0.html)  Reply #17

Resolve your threads
If you have found an answer to your question, resolve your thread. Don't just post "I have found the answer", but tell others what you actually did to solve your issues. Posting a link to the page where you found the answer might help. Describing what you did might help as well. Finally, you can tag your thread as "solved" by clicking on the "Topic Solved" button on the bar at the left hand side at the bottom of your thread.

I think is solved so I will mark it as solved.
Title: Re: how to update mysql for non existing images
Post by: hobert 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>
Title: Re: how to update mysql for non existing images
Post by: sharpo 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.

Title: Re: how to update mysql for non existing images
Post by: Αndré 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.
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.
Title: Re: how to update mysql for non existing images
Post by: Αndré 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.
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.
Title: Re: how to update mysql for non existing images
Post by: Αndré 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?
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.
Title: Re: how to update mysql for non existing images
Post by: Αndré 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_).
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.
Title: Re: how to update mysql for non existing images
Post by: Αndré 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?
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.
Title: Re: how to update mysql for non existing images
Post by: Αndré 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.
Title: Re: Re: how to update mysql for non existing images
Post by: sharpo 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.