All categories and payment methods lost after upgrade to NC 22.2.2

[hcsihp]

After upgrading NC to version 22.2.2 (from version 21.0.4) all given categories and payment methods in my cospend-projects are lost (except "Keine Kategorie" / "Erstattung"). Creating and using new tags is possible.

Your Nextcloud version: 22.2.2 Your Cospend version : v1.4.3 Your database type: mysql 10.3.31 Your browser name and version: several, usually 78.15.0esr (64-Bit) A more or less precise protocol to reproduce the bug: After upgrading I realized the missing tags in cospend and the moneybuster-app. Given categories on entries are deleted.

[julien-nc]

Thanks for the bug report. I never saw that happen in my various test environments...

1. Could you check if the data is still there in the oc_cospend_categories and oc_cospend_paymentmodes tables?
2. Are the oc_cospend_project_categories and oc_cospend_project_paymentmodes tables still there?
3. Was there any error during Cospend or NC update?
4. Do you know if Cospend was updated during the NC 22.2.2 update process or did you update it before?

[roteiro]

Same problem here (NC 22.2.2, Cospend 1.4.3, 10.3.31-MariaDB-, running on Nexcloudpi with automatic updates)

1. Could you check if the data is still there in the `oc_cospend_categories` and `oc_cospend_paymentmodes` tables?

Tables are there but empty

2. Are the `oc_cospend_project_categories` and `oc_cospend_project_paymentmodes` tables still there?

Tables are not there anymore

3. Was there any error during Cospend or NC update?

Update went through automatically, I didn't notice any errors

4. Do you know if Cospend was updated during the NC 22.2.2 update process or did you update it before?

I think Cospend was updated before, but not sure.

Regarding 3 & 4 I could dig into the logs if you guide me to give more precise information.

I have SQL backups from before the update, whats the best way to restore the data?

[julien-nc]

@roteiro Thanks for the feedback.

The tables were renamed because the DB wrapper in recent Nextcloud releases considers oc_cospend_project_categories and oc_cospend_project_paymentmodes table names are too long. This would have break Cospend fresh install. So I implemented 2 migration steps to create the new tables, copy the data and delete the old ones. I tested those migration steps in multiple conditions and it never crashed... I wonder what could have happened on your side.

I have SQL backups from before the update, whats the best way to restore the data?

Well it's kind of straightforward. Just put all the rows of oc_cospend_project_categories in oc_cospend_categories and all the ones of oc_cospend_project_paymentmodes in oc_cospend_paymentmodes. The category/paymentMode IDs were not changed in the bills.

Regarding 3 & 4 I could dig into the logs if you guide me to give more precise information.

Well you can search for "cospend" in data/updater.log and data/nextcloud.log.

[roteiro]

Well it's kind of straightforward. Just put all the rows of oc_cospend_project_categories in oc_cospend_categories and all the ones of oc_cospend_project_paymentmodes in oc_cospend_paymentmodes. The category/paymentMode IDs were not changed in the bills.

Thanks! (Category restore was straightforwad, paymentmodes was a bit of a hassle, since i had no oc_cospend_project_paymentmodes table in my backup. Apparently my backup was still using the hardcoded paymentmodes only. I recreated the relevant paymentmodes and updated the paymentmodeid column to reflect the content of the paymentmode column)

Well you can search for "cospend" in data/updater.log and data/nextcloud.log.

Nothing at all in updater.log with grep -E 'cospend' updater.log and couldn't find errors related to cospend in nexctloud.log. I'm pretty sure that Cospend was updated to 1.4.3 before the update to NC22 was installed.

[hcsihp]

Thank for your quick replies! On my server the behavior is exactly as roteiro described. Unfortunately I have not done SQL Backups. Is it possible to migrate the tags from an usual nc-backup?

[julien-nc]

@hcsihp What do you mean by usual nc-backup?

[hcsihp]

I use the nc-backup-auto function for daily backups of my NC-Instance. That's not the data itself, but all the other stuff including the mysql-database, right? To my understanding there should also be the data of all cospend-projects. Can I now use this backup to restore my cospend categories?

[ThisIsMe-TIM]

As I know nc-backup-auto save the files, not the database (creates .tar ) For cospend the data is not in files but in databases (again "as I know")

THat means you can't get your cospend datas with nc-backup-auto (again and again "as I know")

I(ve got all my datas in these tables oc_cospend_bill_owers
oc_cospend_bills
oc_cospend_categories
oc_cospend_currencies
oc_cospend_members
oc_cospend_paymentmodes
oc_cospend_projects
oc_cospend_shares

I suppose that you still have the old named tables, insert into newtablename select * from oldtablename will do the job ?

[hcsihp]

@cestmoimeme Thanks for the hint. I found out that nc-backup also will save the database. But I only store it for the last four days... now the one with categories is gone, because it's six days old... *facepalm* The old tables are dropped, as mentioned above.

Regarding the standard categories and payment methods: are the primary keys the same every time? Does it make sense to get it from any of you as sql-files to insert them into the new tables?

[ThisIsMe-TIM]

Regarding the standard categories and payment methods: are the primary keys the same every time? Does it make sense to get it from any of you as sql-files to insert them into the new tables?

I don't know : I suggest it, but didn't do it ;) If I where in your case, I will try it, after keeping a save of actual tables of course !

:)

[hcsihp]

Found a workaround. I created new categories manually and assigned the categoryIDs from the bills-table. So for me this is fixed now. (That was my first dive into NC-Database...)

[zenlord]

I notice allmy categories are gone as well - probably the cause was the same. Additionally: I receive a 'enable javascript' error when I try to add new categories - but I'll add a new issue for that.

[julien-nc]

@zenlord Your additional error is most likely related. Some DB tables might be missing. No need to create another issue for the moment.

1. How did you upgrade Cospend? From Which version? Did you by any chance upgrade NC multiple times (20 -> 21 -> 22 for example) and then upgrade Cospend? I wonder why it only happens to some people.

2. Could you check if you have those DB tables :

   • oc_cospend_project_paymentmodes
   • oc_cospend_project_categories
   • oc_cospend_paymentmodes
   • oc_cospend_categories And check if each of them is empty or not?

DB migration steps were recently added in Cospend to rename the 2 first tables because the name constraints became stricter in NC. It seems those migrations have failed in your case. To solve your problem, you need to copy the content of oc_cospend_project_paymentmodes into oc_cospend_paymentmodes and the content of oc_cospend_project_categories into oc_cospend_categories. If oc_cospend_project_paymentmodes and oc_cospend_project_categories were deleted, you may restore them from a DB backup.

[julien-nc]

@zenlord It would also be interesting to see your server logs to understand what went wrong during the Cospend upgrade. If you recently upgraded, the logs will help. Could you provide the data/nextcloud.log file? If you don't want to send it here, you can send me a share link by email.

[zenlord]

Hi @eneiluj - thank you for your replies. 1 - I usually upgrade my apps pretty frequently and nextcloud itself pretty conservatively, so I don't think I updated nextcloud without updating cospend inbetween. 2 - I don't have the project tables in my database, and the other two are not empty: they indeed contain all categories that I ever used in any of my projects. They do seem to be linked to old projects, and in those projects I still have those categories and paymentmethods. In the new projects I don't have any of those categories (except for 'reimbursement') and 0 paymentmethods. Could it be that cospend is filtering the available categories/paymentmethods based on the criterion whether they have been used before in that same project? I would expect those data structures to be available for all projects, old and new, without being filtered at all... 3 - I don't have any relevant logs - I recently fixed logging entirely, as it was empty for many months (permission problem on filesystem level).

To be honest, I did have a big problem with files that did not pass the integrity check in my installation. Probably caused by running the occ command from outside of the nextcloud directoy, I had built up +- 470 files that should have been deleted (marked 'EXTRA_FILE' in the integrity checker). I have now removed all these EXTRA_FILES, but I have no idea if they caused any problem such as with your migration scripts... HTH!

[doc75]

For your information, I had a related issue (not the same, but might help analyze this one). One project was not having payment methods (but others have) => it raises javascript error when trying to add a payment method through the UI. When trying to insert in the DB the payment method for this project I ran into an error of primary key violation. After some search I discovered that the sequence id was below the max id.

This might explain why my project was not having payment methods

[JVAQUEROM]

My categories also disappeared some time ago, I think due to updating, but not sure when exactly. Is it possible to regenerate the categories?