Database Schema Migration Error Updating to NC 28.0.2

[theorangepotato]

IMPORTANT

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

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

Explain the Problem

I used the web updater to update Nextcloud from 27.x.x to 28.0.2. When I did this it gave me the following error:

Exception: Database error when running migration 28000Date20230906104802 for app core
Index name "news_items_starred_feed_id" for table "oc_news_items" collides with the constraint on table "oc_news_items_old".
Update failed

This same error shows up when I run php occ upgrade as well. Thus, it fails to upgrade Nextcloud, and I am now in a broken state. Can I safely remove either the oc_news_items_old or news_items_starred_feed_id tables?

Steps to Reproduce

Explain what you did to encounter the issue

1. Use the Nextcloud web updater to update from 27.x.x to 28.0.2.

System Information

• News app version: 24.0.0 (on disk)
• Nextcloud version: In between 27.x.x and 28.0.2
• Cron type: system cron
• PHP version: 8.1.2-1ubuntu2.14
• Database and version: MariaDB 10.6.16-MariaDB
• Browser and version: Firefox 122 (Current stable)
• OS and version: Ubuntu 22.04.3 LTS

[SMillerDev]

I don't think oc_news_items_old is a table managed by News, so I'm not sure about it.

[theorangepotato]

Hmm, the database descriptions look similar, but not identical:

`oc_news_items`

+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| guid_hash         | varchar(32)         | NO   | MUL | NULL    |                |
| fingerprint       | varchar(32)         | YES  | MUL | NULL    |                |
| content_hash      | varchar(32)         | YES  |     | NULL    |                |
| rtl               | tinyint(1)          | NO   |     | 0       |                |
| search_index      | longtext            | YES  |     | NULL    |                |
| guid              | longtext            | NO   |     | NULL    |                |
| url               | longtext            | YES  |     | NULL    |                |
| title             | longtext            | YES  |     | NULL    |                |
| author            | longtext            | YES  |     | NULL    |                |
| pub_date          | bigint(20)          | YES  |     | NULL    |                |
| body              | longtext            | YES  |     | NULL    |                |
| enclosure_mime    | longtext            | YES  |     | NULL    |                |
| enclosure_link    | longtext            | YES  |     | NULL    |                |
| feed_id           | bigint(20) unsigned | NO   | MUL | NULL    |                |
| last_modified     | bigint(20)          | YES  | MUL | 0       |                |
| unread            | tinyint(1)          | NO   | MUL | 0       |                |
| starred           | tinyint(1)          | NO   | MUL | 0       |                |
| media_thumbnail   | longtext            | YES  |     | NULL    |                |
| media_description | longtext            | YES  |     | NULL    |                |
| categories_json   | longtext            | YES  |     | NULL    |                |
| shared_by         | varchar(64)         | YES  |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+

`oc_news_items_old`

+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| guid_hash         | varchar(32)         | NO   | MUL | NULL    |                |
| fingerprint       | varchar(32)         | YES  | MUL | NULL    |                |
| content_hash      | varchar(32)         | YES  |     | NULL    |                |
| rtl               | tinyint(1)          | NO   |     | 0       |                |
| search_index      | longtext            | YES  |     | NULL    |                |
| guid              | longtext            | NO   |     | NULL    |                |
| url               | longtext            | YES  |     | NULL    |                |
| title             | longtext            | YES  |     | NULL    |                |
| author            | longtext            | YES  |     | NULL    |                |
| pub_date          | bigint(20) unsigned | YES  |     | NULL    |                |
| updated_date      | bigint(20) unsigned | YES  |     | NULL    |                |
| body              | longtext            | YES  |     | NULL    |                |
| enclosure_mime    | longtext            | YES  |     | NULL    |                |
| enclosure_link    | longtext            | YES  |     | NULL    |                |
| feed_id           | bigint(20)          | NO   |     | NULL    |                |
| status            | bigint(20)          | NO   |     | 0       |                |
| last_modified     | bigint(20) unsigned | YES  | MUL | 0       |                |
| unread            | tinyint(1)          | NO   | MUL | 0       |                |
| starred           | tinyint(1)          | NO   | MUL | 0       |                |
| media_thumbnail   | longtext            | YES  |     | NULL    |                |
| media_description | longtext            | YES  |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+

They also have similar indices:

`oc_news_items` and `oc_news_items_old` indices

+-------------------+----------------------------+
| TABLE_NAME        | INDEX_NAME                 |
+-------------------+----------------------------+
| oc_news_items_old | PRIMARY                    |
| oc_news_items_old | news_items_last_mod_idx    |
| oc_news_items_old | news_items_fingerprint_idx |
| oc_news_items_old | news_items_item_guid       |
| oc_news_items_old | news_items_unread_feed_id  |
| oc_news_items_old | news_items_starred_feed_id |
| oc_news_items_old | news_items_unread_id       |
| oc_news_items     | PRIMARY                    |
| oc_news_items     | news_items_last_mod_idx    |
| oc_news_items     | news_items_fingerprint_idx |
| oc_news_items     | news_items_item_guid       |
| oc_news_items     | news_items_unread_feed_id  |
| oc_news_items     | news_items_starred_feed_id |
| oc_news_items     | news_items_unread_id       |
| oc_news_items     | IDX_EA56D89651A5BC03       |
+-------------------+----------------------------+

Additionally, the MAX(last_modified) timestamp in oc_news_items is the day I performed the update, whereas MAX(last_modified) for oc_news_items_old was October 2020. Was there a database schema migration in 2020, perhaps?

[theorangepotato]

This seems to be a known issue with the upgrade to Nextcloud 28: nextcloud/server#41253.

Under the assumption that this table is very old, I will try dropping the index / table and seeing if it continues successfully.

[joshtrichards]

Was there a database schema migration in 2020, perhaps?

All the migrations can be found here:

https://github.com/nextcloud/news/tree/master/lib/Migration

I don't see any that would have created a table with _old appended to it:

https://github.com/search?q=repo%3Anextcloud%2Fnews+path%3A%2F%5Elib%5C%2FMigration%5C%2F%2F+old&type=code

[theorangepotato]

Hmm. The MAX(last_modified) date was October 9, 2020, which lines up with two of the migrations there, but you're right, there doesn't seem to be an _old table. (Unless that happens behind the scene in the ISchemaWrapper.)

It's also possible that I had an issue with an upgrade at that time and manually created that table, but have since forgotten about it.

Nevertheless, I deleted the oc_news_items_old, and Nextcloud seems to have upgraded without issue. And aside from the UI quirks from running News 24 with Nextcloud 28, News seems to be running fine and has all of my data. So, I am marking this as closed.

Feel free to reopen if others start to report the same issue.