Freshrss: SQLite bug when TMP is too small

[Doshirae]

First, sorry if this issue already has been solved, maybe I didn't search enough

So, I have nginx configured with SQLite, and FreshRSS sees every feed as empty (asking me if still maintained), so I never have anything. I think the problem could only be of Nginx or fastCGI, because on localhost with a simple php -S localhost:8000 it works like a charm

Here is my server block from nginx, if that could help

server {
        server_name [who cares ?];
        root /path/to/FreshRSS/p; # documentation to only expose p/
        include https.conf;

        location ~ \.php$ {
            try_files $uri $document_root$fastcgi_script_name =404;
            fastcgi_pass unix:/run/php-fpm/php-fpm.sock; # don't worry, this is the right socket
            fastcgi_index index.php;
            # fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            include fastcgi.conf;
            fastcgi_param HTTP_PROXY "";
        }
    }

My 2 guesses are that could be the root that may mess up, or maybe I should uncomment that commented line ?

NEW : I tried to uncomment the line, did nothing
And I don't want to expose my server so I won't touch the root for now

Thanks in advance

[Alkarex]

Hello,
Did you have a look at the nginx example in the documentation?
https://freshrss.github.io/FreshRSS/en/admins/02_Installation.html

[Doshirae]

Oh no I didn't see it I didn't scroll down enough ><

I'll try that, thank you so much, and sorry for the disagreement
I'll come back to tell if it worked or not

On 4 March 2018 01:50:42 CET, Alexandre Alapetite [e-mail] wrote:

Hello,
Did you have a look at the nginx example in the documentation?
https://freshrss.github.io/FreshRSS/en/admins/02_Installation.html

--
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub:
https://github.com/FreshRSS/FreshRSS/issues/1816#issuecomment-370192502

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[Doshirae]

EDIT : Still not working, but I have to go

My error log is keeping saying that :

2018/03/04 12:24:10 [error] 17616#17616: *232 connect() to unix:/run/php-fpm/php-fpm.sock failed (11: Resource temporarily unavailable) while connecting to upstream, client: 192.168.1.1, server: rss.patate-douce.me, request: "GET /f.php?ea22b7c3 HTTP/2.0", upstream: "fastcgi://unix:/run/php-fpm/php-fpm.sock:", host: "rss.patate-douce.me"

[Alkarex]

Maybe you could try to run FreshRSS via Docker
https://github.com/FreshRSS/FreshRSS/tree/master/Docker

[Doshirae]

Ok so I investigated a bit more, and in fact, my FreshRSS works just fine at the begginning. But when I import my OPML list, nothing works afterwards (it tells me "This feed is empty. Please verify that it is still maintained." for every single feed, even if I add new ones after the import)

The only clue I have (but no clue how to resolve it) :
This is what happens when I try to add a new feed

2018-03-04 15:46:36SQL error addEntry: HY000: 21 bad parameter or other API misuse while adding entry in feed 224 with title: Founding Campaign 2017: Thank You, Everyone!
2018-03-04 15:46:36FreshRSS_EntryDAO::autoUpdateDb error: Array

for every article in the feed

[Alkarex]

Could you please try with the latest version of FreshRSS (1.10.1), which contains a fix for some database updates?

[Doshirae]

I am in 1.10.1 version, I git cloned the repo :/

[Alkarex]

All right. And using a database from an older version, or a new one?

[Doshirae]

I am using SQLite3 version 3.22.0

[Alkarex]

But was the database file created from an older version of FreshRSS, or with the current one?

[Doshirae]

The current, I began to try installing FreshRSS yesterday and tried to reinstall today

[Alkarex]

If you try with Docker, please use the freshrss/freshrss:dev tag, as I have just fixed a bug in OPML import (missing library for Docker).

[Doshirae]

I don't want to install it via Docker, I'm currently trying to test another database to see if that works or not

[Alkarex]

Ok. I have just tried an OPML import with 1.10.1 and SQLite without problem.

[Alkarex]

Could you please share your OPML file (or maybe a smaller one that shows the bug), so I can try to import it on my side?

[Doshirae]

I really don't know what causes the bug, so here is my entire OPML file : https://files.patate-douce.me/feedlist.opml

[Alkarex]

Except two feeds that are not working, the rest of the import is just fine on my side (FreshRSS 1.10.1, SQLite).

[Doshirae]

What distribution are you on ?
Can I see your /etc/php/php.ini maybe ?

That thing is really strange

[Alkarex]

Tested on Ubuntu 17.10 (native) and Alpine Linux 3.7 (Docker), in both cases with default php.ini, default packages, nothing special.

[Doshirae]

Do you know people that made it work with ArchLinux ARM ?

[Alkarex]

Not that I know of, but I can give a quick try on Rapbian / Raspberry Pi 3 / ARMv8 if it can help

[Doshirae]

I think Raspbian will be the same as Ubuntu
But don't worry, I'll try to work out what makes the bug, you've helped me really well, don't hassle yourself more ^^

[Alkarex]

Another idea: try a full setup and OPML import purely from command line https://github.com/FreshRSS/FreshRSS/tree/master/cli

[Frenzie]

Could be a PHP 7.2 issue if Arch has PHP 7.2 while Ubuntu 17.10 PHP 7.1.

NB This is just a shot in the dark.

[Doshirae]

This is getting weirder.
Everything works fine in CLI
I load my OPML, actualize, and it says that I have 848 unread articles
But when I connect to web, I have only the 20 of the 2 defaults feeds, and 0 of the imported ones

[Alkarex]

Are you sure you are looking at the same user / instance / database? :-)

[Doshirae]

What do you mean ?
My imported feeds are there in my user, but FreshRSS sees them as empty in the web
And when I go into my database and SELECT COUNT(*) FROM entry; (database is data/users/Doshirae/db.sqlite, the default user that I created), it also says 20

[Doshirae]

And I don't think it's caused by the version of PHP, because when running on my laptop with php -S 127.0.0.1:9000 (php version 7.2, too), it works normally

[Alkarex]

If you are willing to share a temporary guest SSH account, I could do some tests by myself, otherwise I can prepare a few test files.

[Doshirae]

I would rather prefer to have some test files, as I have many things that could play that are only accessible by root

On 5 March 2018 22:03:14 CET, Alexandre Alapetite [e-mail] wrote:

If you are willing to share a temporary guest SSH account, I could do
some tests by myself, otherwise I can prepare a few test files.

--
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub:
https://github.com/FreshRSS/FreshRSS/issues/1816#issuecomment-370564592

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[Doshirae]

Or maybe I can put your ssh key on my server with a user that can see those files I were talking about so that I can revoke it later ?

On 5 March 2018 22:03:14 CET, Alexandre Alapetite [e-mail] wrote:

If you are willing to share a temporary guest SSH account, I could do
some tests by myself, otherwise I can prepare a few test files.

--
You are receiving this because you authored the thread.
Reply to this email directly or view it on GitHub:
https://github.com/FreshRSS/FreshRSS/issues/1816#issuecomment-370564592

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

[Alkarex]

Sounds like a good plan. You can send me an e-mail and I will reply with my public key. https://alexandre.alapetite.fr/cv/

[Alkarex]

For the record, the problem seems to be at SQLite level with an Error: database or disk is full when making a SELECT on a temporary database, probably because SQLite is trying to make a temporary disk file that fails or lacks memory if swap is disabled.

Replacing CREATE TEMP TABLE tmp by CREATE TABLE tmp seems to solve the main problem:
https://github.com/FreshRSS/FreshRSS/blob/f632a346269100d6a93bef318ffa66c97f16f6fa/app/Models/EntryDAOSQLite.php#L30

The problem still requires some clarification
https://sqlite.org/tempfiles.html
https://sqlite.org/compile.html#temp_store

echo 'CREATE TEMP TABLE `tmp` AS SELECT id,guid,title,author,content,link,date,`lastSeen`,hash, is_read,is_favorite,id_feed,tags FROM entrytmp ORDER BY date; SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id,guid,title,author,content,link,date,`lastSeen`,hash,is_read,is_favorite,id_feed,tags FROM `tmp` ORDER BY date;' | sqlite3 test.sqlite
Error: near line 1: database or disk is full

[Alkarex]

Tested with the same OPML file on my own Raspberry Pi (using the new Docker image) without problem.

[Alkarex]

For future reference, debugging with strace shows that SQLite was writing in /var/tmp/, which had only 10MB of available space, which was apparently not enough (the test SQLite database was 18MB).

echo 'CREATE TEMP TABLE `tmp` AS SELECT id,guid,title,author,content,link,date,`lastSeen`,hash, is_read,is_favorite,id_feed,tags FROM entrytmp ORDER BY date; SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id,guid,title,author,content,link,date,`lastSeen`,hash,is_read,is_favorite,id_feed,tags FROM `tmp` ORDER BY date;' | strace -y sqlite3 db.sqlite
...
pwrite64(5</var/tmp/etilqs_b2d0f6c26e68466e (deleted)>, ... = -1 ENOSPC (No space left on device)
...
Error: near line 1: database or disk is full

df -h /var/tmp/                                           
Filesystem      Size  Used Avail Use% Mounted on                                   
tmpfs            10M     0   10M   0% /var/tmp

@Doshirae Could you try to increase the size of your /var/tmp ?

Maybe we should make an option to use either TEMP TABLE (faster) or TABLE (safer) in this SQLite request.

[Alkarex]

https://github.com/FreshRSS/FreshRSS/pull/1830