Can't update the feeds (FreeBSD)

quad · August 15, 2018, 10:43am

I installed TT-RSS today and I’m also unable to update my feeds. Although I get a different error. When I first set up the cronjob I tried to run it manually:

$ sudo -u www /usr/local/bin/php /usr/local/www/site/ttrss/update.php --feeds --quiet
PHP Fatal error:  Uncaught PDOException: There is no active transaction in /usr/local/www/site/ttrss/classes/rssutils.php:1163
Stack trace:
#0 /usr/local/www/platinum.quad.moe/ttrss/classes/rssutils.php(1163): PDO->commit()
#1 /usr/local/www/platinum.quad.moe/ttrss/classes/rssutils.php(190): RSSUtils::update_rss_feed(1, true, false)
#2 /usr/local/www/platinum.quad.moe/ttrss/update.php(166): RSSUtils::update_daemon_common()
#3 {main}
  thrown in /usr/local/www/platinum.quad.moe/ttrss/classes/rssutils.php on line 1163

I’m running on FreeBSD with PHP 7.2 and Postgres. Although this issue seems to be due to PHP and not due to FreeBSD. I made sure to install both the php72-pdo_pgsql and the php72-pgsql packages.

System details:

# uname -sr
FreeBSD 11.1-RELEASE

# php --version
PHP 7.2.8 (cli) (built: Aug  7 2018 01:26:43) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.8, Copyright (c) 1999-2018, by Zend Technologies

# sudo -u postgres psql
postgres=# select version();
                                                                       version
-----------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on amd64-portbld-freebsd11.1, compiled by FreeBSD clang version 4.0.0 (tags/RELEASE_400/final 297347) (based on LLVM 4.0.0), 64-bit
(1 row)

Any help figuring this out would be much appreciated.

fox · August 15, 2018, 10:53am

great idea posting in an unrelated thread, well done

you will need to find previous error message (it would be SQL related, most likely) in tt-rss error log

e: feel free to edit new thread title if needed

quad · August 15, 2018, 11:06am

Sorry, I’m bad at forums. At least the title was halfway correct.

You were right, I found this:

Copy/Paste capable version:

PDOStatement::execute(): SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "ttrss_tags" violates foreign key constraint "ttrss_tags_post_int_id_fkey" DETAIL: Key (post_int_id)=(3) is not present in table "ttrss_user_entries".

1. classes/rssutils.php(1143): execute(Array)
2. classes/rssutils.php(190): update_rss_feed(4, 1, )
3. update.php(197): update_daemon_common(500)

fox · August 15, 2018, 11:09am

well, it’s a known issue which happened before to some people (search the forums) but so far i haven’t been able to replicate it.

if your current database state causes this all the time you can dump it and send it to me, i’ll take a look.

quad · August 15, 2018, 11:19am

Yes. It happens all the time. It has not managed a single update and claims the last update was performed during the unix epoch. aka in 1970.

Here’s a dump of the tt-rss db: https://a.uguu.se/iJyE8SUBEvmS_ttrss_dump.sql

I changed the admin password back to “password” before dumping the db for simplicity. Since I set up tt-rss today though, it’s pretty barren and only contains a couple random feeds I added to test it.

Edit: Link expires in 24 hours btw

fox · August 15, 2018, 11:21am

downloaded, thanks

/20char

e: i’m using postgresql 9.6 which doesn’t like this dump very much

fox · August 15, 2018, 12:00pm

well, this doesn’t look very good. i’ve installed postgresql 10.4 in docker, restored your dump there, and this is what i’m seeing:

naboo:tt-rss-mysql:$ psql -h localhost -p 5444 -U postgres postgres
Password for user postgres:
psql (9.6.10, server 10.4 (Debian 10.4-2.pgdg90+1))
WARNING: psql major version 9.6, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# \q

[12:34:07/16401] Lock: update.lock
[12:34:07/16401] marking all feeds as needing update...
[12:34:07/16401] Scheduled 4 feeds to update...
[12:34:07/16401] Base feed: http://feeds.arstechnica.com/arstechnica/technology-lab
[12:34:07/16401]  => 1970-01-01 00:00:00, 4 1
[12:34:07/16401]     0.0863 (sec)
[12:34:07/16401] Base feed: https://blog.quad.moe/index.xml
[12:34:07/16401]  => 1970-01-01 00:00:00, 2 1
[12:34:07/16401]     0.3288 (sec)
[12:34:07/16401] Base feed: https://www.tek.no/feeds/general.xml
[12:34:07/16401]  => 1970-01-01 00:00:00, 3 1
[12:34:08/16401]     0.8993 (sec)
[12:34:08/16401] Base feed: http://tt-rss.org/forum/rss.php
[12:34:08/16401]  => 1970-01-01 00:00:00, 1 1
[12:34:10/16401]     1.2878 (sec)
[12:34:10/16401] Processed 4 feeds in 2.6131 (sec), 0.6533 (sec/feed avg)
[12:34:10/16401] Running housekeeping tasks for user 1...
[12:34:10/16401] Sending digests, batch of max 15 users, headline limit = 1000
[12:34:10/16401] All done.
[12:34:10/16401] cache/simplepie: removed 0 files.
[12:34:10/16401] cache/feeds: removed 0 files.
[12:34:10/16401] cache/images: removed 0 files.
[12:34:10/16401] cache/export: removed 0 files.
[12:34:10/16401] cache/upload: removed 0 files.
[12:34:10/16401] Removed 0 old lock files.
[12:34:10/16401] Removing old error log entries...
[12:34:10/16401] Feedbrowser updated, 4 feeds processed.
[12:34:10/16401] Purged 0 orphaned posts.
[12:34:10/16401] Removed 0 (feeds) 0 (cats) orphaned counter cache entries.

it works just like it should.

this may be related to php 7.2, which i don’t have installed (debian ships 7.0) although i think its unlikely.

quad · August 15, 2018, 12:01pm

Maybe I should just give up and put tt-rss on one of my linux boxes instead then

fox · August 15, 2018, 12:02pm

can you at least downgrade php to 7.1 or 7.0 and see if it changes anything? so far i’ve had zero followup on this issue so i’m kinda interested.

e: also, i’ll keep this in mind, so if i’m particularly bored at some point, i’ll try to install freebsd in a VM and poke around. can’t promise this will be soon tho.

quad · August 15, 2018, 12:03pm

I’ll give it a try. Not much to lose.

Edit: well shit, works fine when i downgraded to the php70 packages.

fox · August 15, 2018, 12:11pm

interesting, i guess i’ll have to setup php 7.2 somewhere. thanks for investigating.

quad · August 15, 2018, 12:18pm

It could just be something that’s specifically broken on FreeBSD when you combine PHP 7.2 with a Postgres 10 server.

Looking at your earlier post it says:

WARNING: psql major version 9.6, server major version 10.
         Some psql features might not work.

I don’t think you’re running Postgres 9.6. Seems more like you’ve got a Postgres 9.6 client, while the Postgres server itself is version 10.

I believe FreeBSD uses the same kind of setup where the client-side of postgres often lags behind for some reason. No idea why Debian/FreeBSD would do this though.

fox · August 15, 2018, 12:20pm

yeah, it would be best to replicate your setup in a VM. it’s been years since i’ve installed freebsd.

like i said, i’ve installed the server in docker, but used debian stock client / libraries / php.
had to, because 10.x dump has slightly different syntax so it wouldn’t load on 9.6 and i didn’t want to mangle it manually.

e: i’ll probably begin with some low effort php 7.2 docker install and go from there.

xtaz · August 15, 2018, 3:57pm

FYI. I am happily running tt-rss (latest git version) with FreeBSD 11.2-RELEASE-p2, php-7.2.8, and postgresql 10.5 on both the client and the server. No problems whatsoever. Also I’ve never had similar problems on older versions either. It generally just works fine.

fox · August 15, 2018, 4:23pm

can you import the above dump to a temporary database and run tt-rss update.php on it?

xtaz · August 15, 2018, 4:41pm

Done. This is the result:

$ ./update.php --feeds
[17:12:54/77244] Lock: update.lock
[17:12:54/77244] Scheduled 4 feeds to update…
[17:12:55/77244] Base feed: Biz & IT – Ars Technica
[17:12:55/77244] => , 4 1
[17:12:57/77244] 2.8549 (sec)
[17:12:57/77244] Base feed: http://tt-rss.org/forum/rss.php
[17:12:57/77244] => 1970-01-01 00:00:00, 1 1
[17:13:02/77244] 5.0360 (sec)
[17:13:02/77244] Base feed: https://blog.quad.moe/index.xml
[17:13:02/77244] => 1970-01-01 00:00:00, 2 1
[17:13:05/77244] 2.6788 (sec)
[17:13:05/77244] Base feed: Tek.no
[17:13:05/77244] => 1970-01-01 00:00:00, 3 1
[17:13:09/77244] 3.7647 (sec)
[17:13:09/77244] Processed 4 feeds in 14.3596 (sec), 3.5899 (sec/feed avg)
[17:13:09/77244] Running housekeeping tasks for user 1…
[17:13:09/77244] Sending digests, batch of max 15 users, headline limit = 1000
[17:13:09/77244] All done.
[17:13:09/77244] cache/simplepie: removed 0 files.
[17:13:09/77244] cache/feeds: removed 0 files.
[17:13:09/77244] cache/images: removed 0 files.
[17:13:09/77244] cache/export: removed 0 files.
[17:13:09/77244] cache/upload: removed 0 files.
[17:13:09/77244] Removed 0 old lock files.
[17:13:09/77244] Removing old error log entries…
[17:13:09/77244] Feedbrowser updated, 4 feeds processed.
[17:13:09/77244] Purged 0 orphaned posts.
[17:13:09/77244] Removed 0 (feeds) 0 (cats) orphaned counter cache entries.

$ uname -a
FreeBSD host.example.com 11.2-RELEASE-p2 FreeBSD 11.2-RELEASE-p2 #0: Tue Aug 14 21:45:40 UTC 2018 [email protected]:/usr/obj/usr/src/sys/GENERIC amd64

$ pkg info | egrep ‘php|postgres’
php72-7.2.8 PHP Scripting Language
php72-ctype-7.2.8 The ctype shared extension for php
php72-curl-7.2.8 The curl shared extension for php
php72-dom-7.2.8 The dom shared extension for php
php72-fileinfo-7.2.8 The fileinfo shared extension for php
php72-hash-7.2.8 The hash shared extension for php
php72-json-7.2.8 The json shared extension for php
php72-mbstring-7.2.8 The mbstring shared extension for php
php72-opcache-7.2.8 The opcache shared extension for php
php72-pcntl-7.2.8 The pcntl shared extension for php
php72-pdo-7.2.8 The pdo shared extension for php
php72-pdo_pgsql-7.2.8 The pdo_pgsql shared extension for php
php72-pgsql-7.2.8 The pgsql shared extension for php
php72-posix-7.2.8 The posix shared extension for php
php72-session-7.2.8 The session shared extension for php
postgresql10-client-10.5 PostgreSQL database (client)
postgresql10-server-10.5 PostgreSQL is the most advanced open-source database available anywhere

fox · August 15, 2018, 4:43pm

well, I’m out of ideas

quad · August 16, 2018, 8:19am

I found time to upgrade my web server from FreeBSD 11.1 to 11.2 today.
After that I upgraded back up to PHP 7.2 instead of 7.0.
TT-RSS still works fine.

PHP 7.2 or its Postgres module might’ve simply been a bit broken on FreeBSD 11.1.

xtaz · August 16, 2018, 10:20am

Very weird. I had previously used php7.2 and postgresql 10.x on 11.1 for several months. I only upgraded mine to 11.2 last week.