how to update mysql for non existing images

[babez]

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

[gmc]

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

[babez]

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 ?

[gmc]

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

[phill104]

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

[babez]

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

[gmc]

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

Greg

[netb]

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.

[gmc]

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

[babez]

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 ?

[gmc]

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

[babez]

hi Greg,

im running CPG 1.5 version

http://sexybabespic.com (NSFW!!!)

[phill104]

You are on the latest release, 1.5.34

[Αndré]

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.

[gmc]

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

[Αndré]

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?

[babez]

hi there, where to download the plugins ?

[gmc]

Everyone feel free to test the update and implement missing features Greg?

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

[babez]

hi Greg!

thanks for the link ,downloading now ,

[allvip]

Is this topic solved because:

Board rules / Forum policies 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.