Advanced search  

News:

cpg1.5.34 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.32 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

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

0 Members and 1 Guest are viewing this topic.

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
how to update mysql for non existing images
« on: January 16, 2015, 07: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
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #1 on: January 17, 2015, 03: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
Logged
Thanks!
Greg

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #2 on: January 17, 2015, 04: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 ?
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #3 on: January 17, 2015, 04: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
Logged
Thanks!
Greg

Phill Luckhurst

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4021
    • Windsurf.me
Re: how to update mysql for non existing images
« Reply #4 on: January 17, 2015, 02: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
« Last Edit: January 17, 2015, 02:12:57 PM by Phill Luckhurst »
Logged
It is a mistake to think you can solve any major problems just with potatoes.

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #5 on: January 17, 2015, 02: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
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #6 on: January 18, 2015, 05: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
Logged
Thanks!
Greg

netb

  • Coppermine newbie
  • Offline Offline
  • Posts: 18
Re: how to update mysql for non existing images
« Reply #7 on: January 18, 2015, 05: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.
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #8 on: January 18, 2015, 06: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??
Logged
Thanks!
Greg

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #9 on: January 18, 2015, 07: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 ?
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #10 on: January 18, 2015, 09: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
Logged
Thanks!
Greg

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #11 on: January 19, 2015, 05:20:50 AM »

hi Greg,

im running CPG 1.5 version

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

Phill Luckhurst

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 4021
    • Windsurf.me
Re: how to update mysql for non existing images
« Reply #12 on: January 19, 2015, 10:27:09 PM »

You are on the latest release, 1.5.34
Logged
It is a mistake to think you can solve any major problems just with potatoes.

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 14099
Re: how to update mysql for non existing images
« Reply #13 on: January 19, 2015, 11: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.
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: Re: how to update mysql for non existing images
« Reply #14 on: January 20, 2015, 12:06:52 AM »

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

Logged
Thanks!
Greg

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 14099
Re: how to update mysql for non existing images
« Reply #15 on: January 28, 2015, 09: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)
Logged

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #16 on: Yesterday at 03:33:29 AM »

hi there, where to download the plugins ?
Logged

gmc

  • Dev Team member
  • Coppermine frequent poster
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 391
    • GMC Design Photo Gallery
Re: how to update mysql for non existing images
« Reply #17 on: Yesterday at 04: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...)
Logged
Thanks!
Greg

babez

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: how to update mysql for non existing images
« Reply #18 on: Yesterday at 11:24:53 AM »

hi Greg!

thanks for the link ,downloading now ,
Logged
Pages: [1]   Go Up
 

Page created in 0.078 seconds with 21 queries.