restoring image files accidentally deleted

alanpmcd · 2010-02-06 19:07:51

Let me start by saying I’m utterly inexperienced in sql. But I have the plug in rss_admin_db_manager

I recently discovered that some image files had been accidentally deleted last year from the site I help to run. I have found a backup of the lost image files and restored them via ftp to their rightful directory.

But I don’t know how to reinstate them in textpattern. I’ve got as far as understanding, I think, that the entries need to be in the table txp_image. And I have found an old backup of that table with data in it covering the lost images. How do I get that data into the current table? Restoring that old table will delete subsequent entries, so I need instead to edit the current table to insert some odl entries, as I see it

Any help gratefully received.

alanpmcd

jsoo · 2010-02-06 19:43:35

I don’t know rss_admin_db_manager, but if you have access to the db through phpMyAdmin or another db manager you can do this directly.

First thing to do is back up the current database, in case something goes wrong and you need to restore it.

Next I’d make a duplicate of your old db backup (being extra cautious here, but why not). Open the file in a text editor and look for the txp_image section. I’m assuming this is a MySQL dump. You’re looking for a block that begins something like this:

INSERT INTO `txp_image` (`id`,`name`,`category`,`ext`,`w`,`h`,`alt`,`caption`,`date`,`author`,`thumbnail`,`thumb_w`,`thumb_h`)
VALUES

followed by all the image records. (There might also be a LOCK TABLES line at the top, and UNLOCK TABLES; at the bottom of the image records; if so, you want those too.) Copy all that, and paste it into a new text file. Save it with the .sql file extension. There’s one change you’ll probably need to make: add the word IGNORE after the word INSERT.

Do not copy the DROP TABLE or CREATE TABLE stuff above this. Just the INSERT line and the data. (and LOCK TABLES if it’s there).

Import this file with your db manager.

alanpmcd · 2010-02-07 08:09:15

jsoo, Thanks. That has got me so near and yet so far. The devil is in that ‘Import this file with your db manager.’ I’ve tried doing that two ways via myphphadmin:

(a) adding the ‘missing’ data to a backup of the existing txp_image in my text editor, then importing that file – result, myphpadmin says ‘txp_image already exists’ and gives me an error;

(b) simply importing the file of the ‘missing’ data I’d created in my text editor with txp_image open – myphpadmin simply hangs.

It may be my error, I didn’t understand your reference to the word IGNORE in your response, and things didn’t seem to work with or without it.

If you or anyone else can suggest my next step I’d be very grateful.

alanpmcd

jsoo · 2010-02-07 12:33:15

alanpmcd wrote:

(a) adding the ‘missing’ data to a backup of the existing txp_image in my text editor, then importing that file – result, myphpadmin says ‘txp_image already exists’ and gives me an error;

Right, a MySQL dump typically includes a CREATE TABLE statement for each table. You can’t create a table that already exists.

(b) simply importing the file of the ‘missing’ data I’d created in my text editor with txp_image open – myphpadmin simply hangs.
…
It may be my error, I didn’t understand your reference to the word IGNORE in your response, and things didn’t seem to work with or without it.

The syntax is

INSERT IGNORE INTO `txp_image` (`id`,`name`,`category`,`ext`,`w`,`h`,`alt`,`caption`,`date`,`author`,`thumbnail`,`thumb_w`,`thumb_h`)
VALUES
	(1,'divider.gif','site-design','.gif',400,1,'---','','2005-07-22 16:37:11','me',0,0,0),
	(2,'txp_slug105x45.gif','site-design','.gif',105,45,'Textpattern CMS','','1977-12-24 13:20:42','me',0,0,0);

This example contains two data records. Each record except the last is followed by a comma. The last is followed by a semicolon. Note that this example is from a Txp 4.2.0 install, so it has thumb_w and thumb_h, added in this version.

alanpmcd · 2010-02-07 15:06:03

That worked a treat, thanks very much for your help.

alan

Textpattern CMS

Textpattern CMS support forum

#1 2010-02-06 19:07:51

restoring image files accidentally deleted

#2 2010-02-06 19:43:35

Re: restoring image files accidentally deleted

#3 2010-02-07 08:09:15

Re: restoring image files accidentally deleted

#4 2010-02-07 12:33:15

Re: restoring image files accidentally deleted

#5 2010-02-07 15:06:03

Re: restoring image files accidentally deleted

Board footer