Deleted MySQL rows

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Deleted MySQL rows

Postby NGPriest » 31 Jan 2016, 20:51

I was testing ddd, was wondering what the 2nd button did and marked all my feeds as read.
I've got the updater working and didn't want to purge all my unread feeds, so i changed the purge to 9999 days (later changed to 0)
I went into the database and saw a Last Read date, so thinking to mark everything as unread, delete all the rows with today's date.

So, I kinda did

Code: Select all

DELETE FROM ttrss_user_entires
WHERE last_read=2016-01-31 hh:mm:ss


And kinda deleted all the rows...
What can i do now? :S
Last edited by NGPriest on 31 Jan 2016, 20:55, edited 1 time in total.

NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Re: Deleted MySQL rows

Postby NGPriest » 31 Jan 2016, 20:53

I've spent half my night/morning trying to SELECT * FROM ttrss_entries LEFT JOIN ttrss_user_entries WHERE ttrss_user_entries.ref_id IS NULL

Code: Select all

SELECT *
FROM `ttrss_user_entries`
LEFT JOIN `ttrss_entries`
ON `ttrss_entries`.`id`=`ttrss_user_entries`.`ref_id`
WHERE `ttrss_user_entries`.`ref_id` IS NULL


Then i was planning on doing INSERT INTO, but i don't think i'm doing it correctly... :S

Code: Select all

INSERT INTO `ttrss_user_entries` (int_id,ref_id,owner_uid,unread) VALUES (1,1,1,1);


But i keep getting:

Code: Select all

#1452 - Cannot add or update a child row: a foreign key constraint fails (`ttrss_user_entries`, CONSTRAINT `ttrss_user_entries_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `ttrss_entries` (`id`) ON DELETE CASCADE)

NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Re: Deleted MySQL rows

Postby NGPriest » 31 Jan 2016, 20:56

Deleted

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Deleted MySQL rows

Postby fox » 31 Jan 2016, 21:23

>What can i do now? :S

think before you type in the future

NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Re: Deleted MySQL rows

Postby NGPriest » 31 Jan 2016, 21:30

fox wrote:>What can i do now? :S

think before you type in the future


Any way to reinsert those rows? :S
Or any PHP/MySQL script i can run to put all the entries from ttrss_entries into ttrss_user_entries? :S

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Deleted MySQL rows

Postby fox » 31 Jan 2016, 21:33

both mysql and php documentation are available on google

i suggest you go there instead of posting on this forum

NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Re: Deleted MySQL rows

Postby NGPriest » 31 Jan 2016, 21:36

fox wrote:both mysql and php documentation are available on google

i suggest you go there instead of posting on this forum


Sorry, i'm just in a panic trying to get all the entries back...

I can't even find the entry id's i need...

And even if i could, i'm not able to insert them into the database... :S

JustAMacUser
Bear Rating Overlord
Bear Rating Overlord
Posts: 373
Joined: 20 Aug 2013, 23:13

Re: Deleted MySQL rows

Postby JustAMacUser » 31 Jan 2016, 23:05

NGPriest wrote:I went into the database and saw a Last Read date, so thinking to mark everything as unread, delete all the rows with today's date.


For the benefit of those who find this thread in the future: Manually manipulating information in the database is a horrible idea.

If you want to revert an accidental marking of everything as read, you can just set the view mode to all articles or use the dedicated (virtual) feed "Recently read" to see what's been marked read, then select all and toggle the selection(s) back to unread.

All that being said, NGPriest, you literally deleted raw information from the database. You can't get that information back. If you have a backup you can restore it, otherwise you're out of luck. The ttrss_user_entries table contains meta data; it is linked to the ttrss_entries table by ID and reconstructing that data manually would be tedious to say the least.

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Deleted MySQL rows

Postby sleeper_service » 01 Feb 2016, 03:24

NGPriest wrote:So, I kinda did

Code: Select all

DELETE FROM ttrss_user_entires
WHERE last_read=2016-01-31 hh:mm:ss


And kinda deleted all the rows...
What can i do now? :S


here's where 'begin;' and 'rollback;' could have been your friends.

now, backups are your friends.

take heart, after you wipe everything and start over, you'll get some articles from each feed. :)

NGPriest
Bear Rating Trainee
Bear Rating Trainee
Posts: 12
Joined: 07 Oct 2015, 17:11

Re: Deleted MySQL rows

Postby NGPriest » 01 Feb 2016, 11:34

JustAMacUser wrote:
NGPriest wrote:I went into the database and saw a Last Read date, so thinking to mark everything as unread, delete all the rows with today's date.


For the benefit of those who find this thread in the future: Manually manipulating information in the database is a horrible idea.

If you want to revert an accidental marking of everything as read, you can just set the view mode to all articles or use the dedicated (virtual) feed "Recently read" to see what's been marked read, then select all and toggle the selection(s) back to unread.

All that being said, NGPriest, you literally deleted raw information from the database. You can't get that information back. If you have a backup you can restore it, otherwise you're out of luck. The ttrss_user_entries table contains meta data; it is linked to the ttrss_entries table by ID and reconstructing that data manually would be tedious to say the least.


sleeper_service wrote:
NGPriest wrote:So, I kinda did

Code: Select all

DELETE FROM ttrss_user_entires
WHERE last_read=2016-01-31 hh:mm:ss


And kinda deleted all the rows...
What can i do now? :S


here's where 'begin;' and 'rollback;' could have been your friends.

now, backups are your friends.

take heart, after you wipe everything and start over, you'll get some articles from each feed. :)


Yeah, i tried SELECT * FROM ttrss_entries LEFT JOIN ttrss_user_entries ON ttrss_entries.id=ttrss_user_entries.ref_id WHERE ttrss_user_entries.ref_id IS NULL and found nothing...

I guess i took too long and the rows got orphaned and i lost everything...

In future, i'll try not to panic and wipe most of my database... :S


Return to “Support”

Who is online

Users browsing this forum: Google [Bot] and 7 guests