News: COUNT(oc_news_items) shrunk from 350000 to 8000 during cron-run

[pille]

• [x] I have read the CONTRIBUTING.md and followed the provided tips

Explain the Problem

• lots of my feed items vanished (possibly during a cron run)
• backstory:
  
  
  
  • i've been using news app on owncloud in a sqlite DB for years
  
  
  • around 1.4GB / 350000 feed items
  
  
  • migrated to nextcloud
  
  
  • migrated to mysql backend
  
  
  • oc_news_items is about 2.1GB
  
  
  • during a longish (>30minutes) nextcloud-cron-run i discovered shrinking rows in table
  
  
  • now i have about 8000 rows left
  
  
  • not clear by which criteria rows were deleted
  
  
  • some items are marked read, though i don't remember reading them
  
  
  • all my starred items are lost
  
  
  • the set of the remaining 8000 contains old (up to 4years) and new items.
  
  

What problem did you encounter?
lost part of my data

Steps to Reproduce

not sure which part triggered the removal.
if you have a good hunch or a test scenario, i could replay the migration, as i still have my old DB.

System Information

• News app version: 13.1.6
• Nextcloud version: 16.0.4
• Cron type: cronjob
• PHP version: 7.2
• Database and version: sqlite -> mysql 5.7
• Browser and version: n/a
• Distribution and version: ubuntu18.04

Contents of nextcloud/data/nextcloud.log

• nothing related to cron
• lots related to news app. please specify which info you need, as it's mostly private information.

Contents of Browser Error Console

n/a

[Grotax]

My guess is, that the migration step didn't work how you expected it.

Don't know what do do about that.

[pille]

maybe a good first step would be if someone acknowledges, that the app or a cronjob deletes entries without user interaction. and describes the reasoning. there shouldn't be many occasions for that.

[Grotax]

I don't think we have that somone, that could do that :/

[SMillerDev]

The cronjob has always deleted old entries as far as I know. I don't know the rules though.

[pille]

this is what happened to each feed in numbers:

SELECT count(*) as count, feed_id
FROM `oc_news_items`
GROUP BY feed_id

after.csv.txt
before.csv.txt

[pille]

@SMillerDev: it never did on my system for four years and i don't remember a setting for such behavior. the only setting in this direction is "purge interval", which should remove deleted feeds. however - there were no complete feeds removed. only items of feeds. how are removed feeds marked for deletion? maybe the migration flagged something as a side-effect.

[pille]

based on @SMillerDev's comment, i found autoPurgeOld() in lib/Service/ItemService.php, which
"deletes all unread feeds that are not starred and over the count of $this->autoPurgeCount starting by the oldest." with autoPurgeCount = 200 hardcoded in lib/Config/Config.php.

this is the best lead currently, but all my starred items were removed.
also my old system had the same config, though it changed to 5000 for a short time in one commit.
where do i find my old config value in the sqlite dump?

[pille]

well, this went more into @Grotax direction. i've set autoPurgeCount = -1 and reimported oc_news_items. i noticed, that all items were read and not starred and found, that a97dd58e3b499b60ac8b37786d402d7f2e371a88 changed how those attributes are stored. this was the missing migration.

that combined with not correctly importing my disabled autoPurgeCount setting allowed the cronjob to remove the items.

i migrated them manually:

UPDATE oc_news_items SET unread = 1 WHERE status = '2';
UPDATE oc_news_items SET unread = 1 WHERE status = '6';
UPDATE oc_news_items SET starred = 1 WHERE status = '6';
UPDATE oc_news_items SET starred = 1 WHERE status = '4';

but no cheering too soon, as the app becomes completely unusable, because

SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` 
FROM `oc_news_feeds`
`feeds` LEFT OUTER JOIN `oc_news_folders` `folders` ON `feeds`.`folder_id` = `folders`.`id` 
        LEFT JOIN `oc_news_items` `items` ON `feeds`.`id` = `items`.`feed_id` AND unread = 1 
        WHERE `feeds`.`user_id` = 'user' AND (`feeds`.`folder_id` = 0 OR `folders`.`deleted_at` = 0)AND `feeds`.`deleted_at` = 0 
        GROUP BY `feeds`.`id`

is taking half an hour. i mean the sqlite DB was slow, but still usable.
can this be optimized? maybe cached, or limited?

[SMillerDev]

It's optimized by not having too many articles in the database by design. But if you find a way to make your edgecase work a pull request would be welcome.

[pille]

i can provide the _hack_, i've implemented:

--- lib/Db/FeedMapper.php~     2019-09-17 12:57:38.329585897 +0000
+++ lib/Db/FeedMapper.php      2019-09-19 11:56:15.143763883 +0000
@@ -49,24 +49,17 @@

     public function findAllFromUser($userId)
     {
-        $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' .
+        $sql = 'SELECT `feeds`.*, 0 AS `unread_count` ' .
             'FROM `*PREFIX*news_feeds` `feeds` ' .
             'LEFT OUTER JOIN `*PREFIX*news_folders` `folders` ' .
                 'ON `feeds`.`folder_id` = `folders`.`id` ' .
-            'LEFT JOIN `*PREFIX*news_items` `items` ' .
-                'ON `feeds`.`id` = `items`.`feed_id` ' .
-                // WARNING: this is a desperate attempt at making this query
-                // work because prepared statements dont work. This is a
-                // POSSIBLE SQL INJECTION RISK WHEN MODIFIED WITHOUT THOUGHT.
-                // think twice when changing this
-                'AND unread = ? ' .
             'WHERE `feeds`.`user_id` = ? ' .
             'AND (`feeds`.`folder_id` = 0 ' .
                 'OR `folders`.`deleted_at` = 0' .
             ')' .
             'AND `feeds`.`deleted_at` = 0 ' .
             'GROUP BY `feeds`.`id`';
-        $params = [true, $userId];
+        $params = [$userId];

         return $this->findEntities($sql, $params);
     }

this makes the query finish within a split second, _at the cost of not having a read-counter_ anymore.

[pille]

regarding the _performance issue_, i'd like to add, that migrating from MySQL to PostgreSQL solved the speed problem above for me, as it handles JOINs better. the original app is now usable again. another benefit is that on-disk-space-consumption of DB is roughly the same (1.2GB) as the SQL-dump (1.4GB). MySQL used 2.4GB. The original SQLite was 1.4GB.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.