SQL error updateTTL when importing tt-rss opml [BUG]

[marrco]

Describe the bug
Newly installed 1.80 via cPanel, imported my (less than 100) feeds i prevoiusly exported via an outdated (cPanel) tt-rss instance

feeds don't automatically update. Single feeds when forced update do show new items. Trying to force a complete feed refresh doesn't work

looking at logs there is an error:

2021-03-22 10:23:59
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-22 10:23:59
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-22 10:23:58
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-22 10:23:58
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value

Environment information (please complete the following information):
cPanel

Additional context
I suspect there's an issue with updating imported tt-rss exported feeds.

[Alkarex]

I will fix. Would you mind sharing the file (or a subset) you are trying to import?
Either here or by e-mail

[marrco]

Thx,
mail sent. Let me know if you need an account.

clicking on the update button it stalls, each time on a different feed.

https://i.imgur.com/gB3C50S.png

[Alkarex]

@marrco What database are you using? Which versions (OS, database, FreshRSS)?
Would you be able to try this branch? #3553
I recommend making a new FreshRSS user to test the import again, or restart from a fresh install + database

[marrco]

Your current version of FreshRSS is 1.18.0.
Your PHP version (7.4.15) is compatible with FreshRSS.
installation check is all green, but it's a cPanel account, so I have no access to the underlying system

cPanel Version	92.0 (build 12)
Apache Version	2.4.46
PHP Version	7.4.11
MySQL Version	10.1.48-MariaDB
Architecture	x86_64
Kernel Version	3.10.0-962.3.2.lve1.5.26.3.el7.x86_64
--	--

I'm quite new to FreshRSS so I don't know how to udate to that branch. In system configuration (as admin) there's an Auto-update server URL pointing to https://update.freshrss.org Is that the option to change to a different value to test the new branch?

I guess I can try to manually replace those 4 files you modified. Is that ok or I risk breaking future updates?

[Alkarex]

As our version 1.18.0 as been released so recently, it is OK to replace the 4 files.
(Those things are a bit easier when installed via git)

[marrco]

Manually updated the 4 files and created a new user i tried to import the opml i sent you yesterday. It's an old file with also a< few non working feeds

it stalls without any error https://i.imgur.com/UV3B4Ww.png sometimes at a different point

log is full of wanring and errors like:
2021-03-23 11:30:03
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:30:03
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:30:02
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:30:02
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:30:02
WebSub lease ends at 2021-03-20T18:03:12+00:00 and needs renewal: http://www.blogger.com/feeds/9100761888144266006/posts/default
2021-03-23 11:30:02
Error while WebSub unsubscribing from http://sanesecurity.blogspot.com/feeds/posts/default
2021-03-23 11:30:02
JSON not found for WebSub: http://sanesecurity.blogspot.com/feeds/posts/default
2021-03-23 11:30:00
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:30:00
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:30:00
Feed has invalid GUIDs: http://feeds.feedburner.com/nirsoft/voiG
2021-03-23 11:30:00
Error while WebSub unsubscribing from http://googlewebmastercentral.blogspot.com/feeds/posts/default
2021-03-23 11:30:00
JSON not found for WebSub: http://googlewebmastercentral.blogspot.com/feeds/posts/default
2021-03-23 11:30:00
Feed has invalid GUIDs: http://googlewebmastercentral.blogspot.com/feeds/posts/default
2021-03-23 11:30:00
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:30:00
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:29:58
Error while WebSub unsubscribing from https://www.spamresource.com/feeds/posts/default
2021-03-23 11:29:58
JSON not found for WebSub: https://www.spamresource.com/feeds/posts/default
2021-03-23 11:29:57
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:29:57
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:29:56
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:29:56
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:29:56
Error while WebSub unsubscribing from http://feedproxy.google.com/TheGeekStuff
2021-03-23 11:29:56
JSON not found for WebSub: http://feedproxy.google.com/TheGeekStuff
[...]

clearing the logs and trying to update (clicking the update button) gives lots more errors:

2021-03-23 11:34:45
Error while WebSub unsubscribing from https://kk.org/cooltools/index.xml
2021-03-23 11:34:45
JSON not found for WebSub: https://kk.org/cooltools/index.xml
2021-03-23 11:34:45
Feed has invalid GUIDs: https://www.corriere.it/rss/homepage.xml
2021-03-23 11:34:45
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:45
Feed has invalid GUIDs: http://xml2.corriereobjects.it/rss/homepage.xml
2021-03-23 11:34:45
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:34:44
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:44
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:34:44
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:44
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:34:44
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:44
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:34:44
Feed has invalid GUIDs: https://politepol.com/fd/O7J8KDqLEVZJ
2021-03-23 11:34:44
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:44
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting transaction UPDATE _feed SET ttl=:new_value WHERE ttl=:old_value
2021-03-23 11:34:44
A feed could not be found at http://feeds.engadget.com/weblogsinc/engadget; the status code is 200 and content-type is text/html; charset=utf-8 [http://feeds.engadget.com/weblogsinc/engadget]
2021-03-23 11:34:42
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE _feed ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-03-23 11:34:42
SQL warning updateTTL 1: Deadlock found when trying to get lock; try restarting
[...]

[Alkarex]

I will double-check with MariaDB tonight

[Alkarex]

Hum, errors like JSON not found for WebSub could indicate a partial write access problem.
Please make sure that your Web server has correct write permissions in ./FreshRSS/data/* and all sub-directories.
Something like:

cd ./FreshRSS/
sudo chown -R :www-data . && sudo chmod -R g+r . && sudo chmod -R g+w ./data/

[Alkarex]

And here is a fix for the dead-lock you see with MariaDB #3559

[marrco]

thx i'll do a few tests.

Keep in mind that i don't have full access to that server. It's a shared hosting with cPanel and softaculous so to setup FreshRSS I have a menu like this: https://www.softaculous.com/apps/rss/FreshRSS

[marrco]

Thanks for the fix #3559 - I updated that file too. One small issue remains when there are duplicate feeds (maybe because one is a redirect)

2021-03-24 07:40:54
SQL error updateFeed: Duplicate entry 'http://feeds.feedburner.com/blogspot/Tmnu' for key 'url' for feed 89

in my original tt-rss exported opml file the 2 entries are:

  <outline type="rss" text="PortableAppZ" xmlUrl="http://portableappz.blogspot.com/feeds/posts/default" ttrssSortOrder="0" ttrssUpdateInterval="0" htmlUrl="http://portableappz.blogspot.com/"/>
  <outline type="rss" text="PortableAppZ" xmlUrl="http://feeds2.feedburner.com/blogspot/Tmnu" ttrssSortOrder="0" ttrssUpdateInterval="0" htmlUrl="http://portableappz.blogspot.com/"/>

(i manually deleted one to fix the issue. I don't know if you can easily detect duplicates like theese)

[Alkarex]

@marrco Did you end up with one or two feeds in your FreshRSS? The error in the logs will remain, but the handling of such cases has already been implemented #3347
I have just given it a quick try: I do not get a problem, and I end-up with 2 feeds, because http://portableappz.blogspot.com/feeds/posts/default returns a temporary redirect 302. FreshRSS obeys to the standard and updates the address only in the case of a permanent redirection 301.
Can you confirm that the rest works for you now?

[marrco]

@Alkarex 2 feeds, but i'm trying to keep my error log clean of errors, so I decided to keep just one of those.

This it the tail of my php error log :

[24-Mar-2021 07:11:54 UTC] PHP Warning: include(): Failed opening '/home/[munged]/reader.[munged]e.biz/app/Models/CategoryDAO.php' for inclusion (include_path='.:/opt/cpanel/ea-php74/root/usr/share/pear') in /home/[munged]/reader.[munged]e.biz/lib/lib_rss.php on line 38
[24-Mar-2021 07:11:54 UTC] PHP Fatal error: Uncaught Error: Class 'FreshRSS_CategoryDAO' not found in /home/[munged]/reader.[munged]e.biz/app/Models/Factory.php:14
Stack trace:
#0 /home/[munged]/reader.[munged]e.biz/app/Controllers/javascriptController.php(17): FreshRSS_Factory::createCategoryDao()
#1 /home/[munged]/reader.[munged]e.biz/lib/Minz/Dispatcher.php(119): FreshRSS_javascript_Controller->nbUnreadsPerFeedAction()
#2 /home/[munged]/reader.[munged]e.biz/lib/Minz/Dispatcher.php(47): Minz_Dispatcher->launchAction('nbUnreadsPerFee...')
#3 /home/[munged]/reader.[munged]e.biz/lib/Minz/FrontController.php(81): Minz_Dispatcher->run()
#4 /home/[munged]/reader.[munged]e.biz/p/i/index.php(70): Minz_FrontController->run()
#5 {main}
thrown in /home/[munged]/reader.[munged]e.biz/app/Models/Factory.php on line 14
[24-Mar-2021 07:40:58 UTC] PHP Notice: A feed could not be found at http://www.retecivica.[mycity.domain]/; the status code is 200 and content-type is text/html;charset=UTF-8 in /home/[munged]/reader.[munged]e.biz/lib/SimplePie/SimplePie.php on line 1795

Can you confirm that the rest works for you now?
I'd say YES, thanks. But only time will tell. Let me check tomorrow if new errors show up

hth

[Alkarex]

@marrco It looks like you migth have deleted or changed the access rights of /home/[munged]/reader.[munged]e.biz/app/Models/CategoryDAO.php

[marrco]

well, I guess that happened this morning while I was updating that file.

so far I can confirm no residual issues or error in my logs. Fresh RSS is running fine with my imported feeds.

thanks a lot Alkarex !

[Alkarex]

Nice to hear :-)

[Alkarex]

@marrco You can also export your starred / labelled articles from TT-RSS and import them in FreshRSS.
I have just fixed a few things #3553 , so please use the latest 1.18.1-dev version if you want to give it a try - feedback welcome

[marrco]

@Alkarex I purged my old tt-rss setup last week. Sorry, I can't do that test

[posita]

Please consider reopening. I am still seeing these on 1.18.1, FYI:

2021-11-09 10:00:01
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE `_feed` ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-11-09 10:00:01
SQL warning updateTTL 1: Prepared statement needs to be re-prepared UPDATE `_feed` SET ttl=:new_value WHERE ttl=:old_value
2021-11-09 09:45:02
SQL error updateTTL 2: Duplicate column name 'ttl' ALTER TABLE `_feed` ADD COLUMN ttl INT NOT NULL DEFAULT 0
2021-11-09 09:45:02
SQL warning updateTTL 1: Prepared statement needs to be re-prepared UPDATE `_feed` SET ttl=:new_value WHERE ttl=:old_value

[Alkarex]

@posita Which database are you using? Are you importing on a newly installed FreshRSS instance? Could you please provide an example of OPML?

[posita]

I noticed this after trying to delete and re-add the feed https://www.theepochtimes.com/feed .

I think I had installed at 1.18.0 and upgraded (in-app) to 1.18.1.

SHOW VARIABLES LIKE '%version%'



admin_tls_version	TLSv1,TLSv1.1,TLSv1.2,TLSv1.3	
immediate_server_version	999999	
innodb_version	8.0.26	
original_server_version	999999	
protocol_version	10	
replica_type_conversions		
slave_type_conversions		
tls_version	TLSv1,TLSv1.1,TLSv1.2,TLSv1.3	
version	8.0.26-0ubuntu0.20.04.2	
version_comment	(Ubuntu)	
version_compile_machine	x86_64	
version_compile_os	Linux	
version_compile_zlib	1.2.11