Page 1 of 1

Deleted MySQL rows

Posted: 31 Jan 2016, 20:51
by NGPriest
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

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 20:53
by NGPriest
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)

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 20:56
by NGPriest
Deleted

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 21:23
by fox
>What can i do now? :S

think before you type in the future

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 21:30
by NGPriest
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

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 21:33
by fox
both mysql and php documentation are available on google

i suggest you go there instead of posting on this forum

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 21:36
by NGPriest
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

Re: Deleted MySQL rows

Posted: 31 Jan 2016, 23:05
by JustAMacUser
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.

Re: Deleted MySQL rows

Posted: 01 Feb 2016, 03:24
by sleeper_service
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. :)

Re: Deleted MySQL rows

Posted: 01 Feb 2016, 11:34
by NGPriest
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