News: Datatype mismatch on 15.1.0 migration

[ahyattdev]

IMPORTANT

Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)

• [x] I have read the CONTRIBUTING.md and followed the provided tips
• [x] I accept that the issue will be closed without comment if I do not check here
• [x] I accept that the issue will be closed without comment if I don not fill out all items in the issue template.

Explain the Problem

What problem did you encounter?

news app broken due to database issue

Steps to Reproduce

Explain what you did to encounter the issue

1. ubuntu 20.04 and postgres as database
2. try upgrading to news 1.15.1

System Information

• News app version: 15.0.6 (whatever previous latest was) -> 15.1.0
• Nextcloud version: 20.0.2
• Cron type: */5 * * * * php -f /var/nextcloud/cron.php
• PHP version: 7.4
• Database and version: postgresql 12+214ubuntu0.1
• Browser and version: not browser issue but firefox 83
• Distribution and version: ubuntu 20.04

Contents of nextcloud/data/nextcloud.log

{"reqId":"p007Ezc9SxYMyTamXJt5","level":3,"time":"2020-12-09T08:30:13+00:00","remoteAddr":"127.0.0.1","user":"andrew","app":"settings","method":"POST","url":"/index.php/settings/apps/enable","message":{"Exceptio
n":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing 'ALTER TABLE oc_news_feeds ADD CONSTRAINT folder FOREIGN KEY (folder_id) REFERENCES oc_news_folders (id) ON DELETE
 CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE':\n\nSQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint \"folder\" cannot be implemented\nDETAIL:  Key columns \"folder_id\" and \"id\" are of incomp
atible types: numeric and bigint.","Code":0,"Trace":[{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":169,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\
AbstractPostgreSQLDriver","type":"->"},{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":149,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::
"},{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1019,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::"},{"file":"/var/nextclou
d/lib/private/DB/Migrator.php","line":261,"function":"query","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Migrator.php","line":85,"function":"applySchema","class":"OC\
\DB\\Migrator","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Connection.php","line":466,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/nextcloud/lib/private/DB/MigrationService.
php","line":486,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\\DB\\MigrationS
ervice","type":"->"},{"file":"/var/nextcloud/lib/private/Installer.php","line":157,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/nextcloud/apps/settings/lib/Controller/AppSet
tingsController.php","line":448,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":169,"function":"enableApps","class":"OCA\
\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":100,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatc
her","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/App.php","line":152,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/nextcloud/lib/private/Route/Ro
uter.php","line":308,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/nextcloud/lib/base.php","line":1008,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/nex
tcloud/index.php","line":37,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php","Line":79,"Previous":{"Excep
tion":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint \"folder\" cannot be implemented\nDETAIL:  Key columns \"folder_id\" and \"id\" are of
 incompatible types: numeric and bigint.","Code":"42804","Trace":[{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1017,"function":"query","class":"Doctrine\\DBAL\\Driver\\
PDOConnection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Migrator.php","line":261,"function":"query","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Migrator.ph
p","line":85,"function":"applySchema","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Connection.php","line":466,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file
":"/var/nextcloud/lib/private/DB/MigrationService.php","line":486,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/MigrationService.php","line":414,"f
unction":"executeStep","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/nextcloud/lib/private/Installer.php","line":157,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"
/var/nextcloud/apps/settings/lib/Controller/AppSettingsController.php","line":448,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php
","line":169,"function":"enableApps","class":"OCA\\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":100,"function":"executeCo
ntroller","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/App.php","line":152,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":
"->"},{"file":"/var/nextcloud/lib/private/Route/Router.php","line":308,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/nextcloud/lib/base.php","line":1008,"function":"match","class":
"OC\\Route\\Router","type":"->"},{"file":"/var/nextcloud/index.php","line":37,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConne
ction.php","Line":83,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  foreign key constraint \"folder\" cannot be implemented\nDETAIL:  Key columns \"folder_id\" an
d \"id\" are of incompatible types: numeric and bigint.","Code":"42804","Trace":[{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php","line":78,"function":"query","class":"P
DO","type":"->"},{"file":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1017,"function":"query","class":"Doctrine\\DBAL\\Driver\\PDOConnection","type":"->"},{"file":"/var/nextclo
ud/lib/private/DB/Migrator.php","line":261,"function":"query","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Migrator.php","line":85,"function":"applySchema","class":"OC
\\DB\\Migrator","type":"->"},{"file":"/var/nextcloud/lib/private/DB/Connection.php","line":466,"function":"migrate","class":"OC\\DB\\Migrator","type":"->"},{"file":"/var/nextcloud/lib/private/DB/MigrationService
.php","line":486,"function":"migrateToSchema","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/nextcloud/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\\DB\\Migration
Service","type":"->"},{"file":"/var/nextcloud/lib/private/Installer.php","line":157,"function":"migrate","class":"OC\\DB\\MigrationService","type":"->"},{"file":"/var/nextcloud/apps/settings/lib/Controller/AppSe
ttingsController.php","line":448,"function":"installApp","class":"OC\\Installer","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":169,"function":"enableApps","class":"OCA
\\Settings\\Controller\\AppSettingsController","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":100,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispat
cher","type":"->"},{"file":"/var/nextcloud/lib/private/AppFramework/App.php","line":152,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/nextcloud/lib/private/Route/R
outer.php","line":308,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/nextcloud/lib/base.php","line":1008,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/ne
xtcloud/index.php","line":37,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php","Line":78}},"CustomMessage":"--"},"use
rAgent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:83.0) Gecko/20100101 Firefox/83.0","version":"20.0.2.2"}

Contents of Browser Error Console

Read http://ggnome.com/wiki/Using_The_Browser_Error_Console if you are unsure what to put here

 An exception occurred while executing 'ALTER TABLE oc_news_feeds ADD CONSTRAINT folder FOREIGN KEY (folder_id) REFERENCES oc_news_folders (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE': SQLSTATE[42804]: Datatype mismatch: 7 ERROR: foreign key constraint "folder" cannot be implemented DETAIL: Key columns "folder_id" and "id" are of incompatible types: numeric and bigint. 

[emtie]

I am on Ubuntu 20.04.1 as well but I am using mysql server version 8.0.22. My Errormessage is very simmilar:

An exception occurred while executing ALTER TABLE oc_news_feeds ADD CONSTRAINT folder FOREIGN KEY (folder_id) REFERENCES oc_news_folders (id) ON DELETE CASCADE:

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user ...

Please help, I am really happy with the news app and been using it for quiete some time now...

[SMillerDev]

That's a different issue, you're not allowed to perform the migration and news can't fix that for you.

[Grotax]

I tried to replicate this with a fresh install of 15.0.6 on the nextcloud docker image using sqlite.
I installed 15.0.6 manually, updating via occ and then running the upgrade command, no error.
My guess sqlite is as far as I know much more limited with its types so bigint and numeric are probably the same for sqlite

[ahyattdev]

@Grotax the issue is with postgres here? can you test with that?

[Grotax]

Not sure I checked the migration files and my guess is that you had an older installation of news because the current migrations files would create it as bigint.
https://github.com/nextcloud/news/blob/bf72440719905726bf1a7f7a6e7f9b28ce7e787f/lib/Migration/Version140200Date20200824201413.php#L37

Not sure how and if we can fix this in a smart way for your case.

[Grotax]

Because normally you are not supposed to take back migrations that you already published.
Maybe we could modify the existing one to check if it's bigint and if not migrate it?
Also adding @anoymouserver

[anoymouserver]

I've noticed some migration errors in other issues which are related to the switch from the old appinfo/database.xml to the migration scripts. The mentioned Version140200Date20200824201413.php took the latest version of the database.xml and made it the new initial step. This causes older migrations, like for the MediaRSS feature, not to be run (happens when updating from pre 14.1.4 to e.g. 14.2.0).

But this doesn't seem to be the case here, since the bigint change was made 7 years ago in 2f651f7. This issue is pretty sure not due to the new migration script, something else must have gone wrong.

[oatmealm]

I'm having a problem with 20.0.2 and 20.0.3. I've tried removing the app and reinstalling it.

sudo -u www-data php /var/www/nextcloud/occ app:install news
Error: An exception occurred while executing 'ALTER TABLE oc_news_feeds ADD CONSTRAINT folder FOREIGN KEY (folder_id) REFERENCES oc_news_folders (id) ON DELETE CASCADE':

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'nextcloud'@'localhost' for table 'oc_news_folders'

sudo -u www-data php /var/www/nextcloud/occ migration:status news

In MigrationService.php line 453:

  Migration step 'OCA\News\Migration\Version150005Date20201009192341' is unknown  


migrations:status <app>

[SMillerDev]

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'nextcloud'@'localhost' for table 'oc_news_folders'

This means you don't have permission to reference a table, news can't fix that.

[oatmealm]

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'nextcloud'@'localhost' for table 'oc_news_folders'

This means you don't have permission to reference a table, news can't fix that.

I can read that. The message appears in the web ui when trying to upgrade the app from the admin interface... are users expected to manage permission at the db/sql level to upgrade an installed app?

[Grotax]

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'nextcloud'@'localhost' for table 'oc_news_folders'

This means you don't have permission to reference a table, news can't fix that.

I can read that. The message appears in the web ui when trying to upgrade the app from the admin interface... are users expected to manage permission at the db/sql level to upgrade an installed app?

Well the administrator of the nextcloud instance is expected to do that for sure.
And there is no way that we can change the permissions of the SQL user of your nextcloud instance, that would be considered a major security incident.

[oatmealm]

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'nextcloud'@'localhost' for table 'oc_news_folders'

This means you don't have permission to reference a table, news can't fix that.

I can read that. The message appears in the web ui when trying to upgrade the app from the admin interface... are users expected to manage permission at the db/sql level to upgrade an installed app?

Well the administrator of the nextcloud instance is expected to do that for sure.
And there is no way that we can change the permissions of the SQL user of your nextcloud instance, that would be considered a major security incident.

Should I open an issue, if it's not realted? As I a user of an instance, to upgrade to a new release of the news app, I'm expected to issue sql commands to get this to work? Again, I'm upgrading an existing installtion of the news app...

[SMillerDev]

Should I open an issue, if it's not realted? As I a user of an instance, to upgrade to a new release of the news app, I'm expected to issue sql commands to get this to work? Again, I'm upgrading an existing installtion of the news app...

If your administrator locked your account to not allow some commands in an upgrade, that's not up to the news app to fix. There is nothing we can or will do here.

[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.

[ahyattdev]

still an issue, tried installing 15.1.1 and got the same error

An error occured during the request. Unable to proceed.
An exception occurred while executing 'ALTER TABLE oc_news_feeds ADD CONSTRAINT folder FOREIGN KEY (folder_id) REFERENCES oc_news_folders (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE': SQLSTATE[42804]: Datatype mismatch: 7 ERROR: foreign key constraint "folder" cannot be implemented DETAIL: Key columns "folder_id" and "id" are of incompatible types: numeric and bigint.✖

[Grotax]

I think you need to fix it manually or start with a fresh install of news without old DB tables of news.

[ahyattdev]

that could work

@Grotax do you know if there ever a code fix related to this?

i guess I'll have to learn a bit more about managing postgres dbs and convert whatever table myself.

don't want to reset theres like 200 RSS feeds in there

[Grotax]

Well I don't think so because your setup is a unicorn it shouldn't exist.

Your DB ended up in a state that is not intended by news.

[ahyattdev]

i've never touched the DB myself, so nextcloud news must have got it in that state through a botched upgrade. too bad we can't reproduce it.

not sure why you mean by my setup is a unicorn. it's just postgres.

[Grotax]

Well the oldest migration the current news repository has was not fully applied on your installation.

As we already discussed earlier in this post.

Now that means you either had a very old version and upgraded in a weird way or the migration failed but was ignored in your installation.

So your error was either caused by ignoring an error (Migration failed because of X) or its way to far in the past.

Whatever happens we can't fix that anymore because your installation is in a status before a required migration.

So it has nothing to do with postgress, it's the migration status that happened which makes it a unicorn.
But I bet it won't be a big problem to find a query that migrates your installation to the desired state so that you can jump back on the normal news upgrade cycle, but I can't help you with that.

[anoymouserver]

@ahyattdev If you feel up to it, you can try to manually change the type of oc_news_feed.folder_id to bigint, which will probably solve the problem (no guarantees).
Since the columns were all updated to bigint 7 years ago at the same time (2f651f709db1c41a6b1d4a3fc12df15d6b19e577), I suspect that something went wrong either during installation or possibly during a migration from SQLite.

[ahyattdev]

@anoymouserver cool, I converted all the numeric in news related tables to bigint and the upgrade went through. I do now have a problem where none of the feeds are updating. Any ideas where to start looking for that?

Like the latest posts are all from 2 months ago when my database broke and I don't see a way to manually trigger an update. Yes, cron jobs are working.

[ahyattdev]

I see it defaults to one hour, maybe i need to wait more

[anoymouserver]

Nice! 👍

You could lower the frequency if you want in the admin settings.
If it continues not to update, it could be the (unfortunately unsolved) #1141.

[ahyattdev]

good news, i just wasn't patient enough. looks to be working now! @anoymouserver