Cospend update not working

[fauust]

Hi, I am trying to update the cospend app to the latest version (3.0.8) and there seem to be a DB error, see below:

Database error when running migration 030000Date20240911230019 for app cospend
An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'payment_mode_id' at row 164

NC running in docker, version 30.0.4.

[julien-nc]

Which database system are you using? Mysql/Mariadb, Postgres?
What's currently the exact type of the oc_cospend_bills.payment_mode_id column?

It is supposed to be an unsigned int with NotNull set to false and a default value set to 0.
Apparently, in your case, the migration system added an extra constraint on this column.

If you're using Postgres, could you try to run this database query? \d+ oc_cospend_bills;
This will show the structure of the table.

On my side, no such thing has ever happened with MariaDB or Sqlite.

[fauust]

Hi @julien-nc!
See below:

❯ sudo mycli
Connecting to socket /var/run/mysqld/mysqld.sock, owned by user mysql
MariaDB 10.11.6
mycli 1.26.1
Home: http://mycli.net
Bug tracker: https://github.com/dbcli/mycli/issues
Thanks to the contributor - QiaoHou Peng
MariaDB root@(none):(none)> use nextcloud
You are now connected to database "nextcloud" as user "root"
Time: 0.000s
MariaDB root@(none):nextcloud> show columns from oc_cospend_bills
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | int(11)             | NO   | PRI | <null>  | auto_increment |
| projectid         | varchar(64)         | NO   |     | <null>  |                |
| what              | varchar(300)        | NO   |     | <null>  |                |
| amount            | double              | NO   |     | <null>  |                |
| payerid           | int(11)             | NO   |     | <null>  |                |
| repeat            | varchar(1)          | NO   |     | n       |                |
| categoryid        | int(11)             | YES  |     | <null>  |                |
| paymentmode       | varchar(1)          | YES  |     | <null>  |                |
| lastchanged       | bigint(20) unsigned | NO   |     | 0       |                |
| repeatallactive   | int(11)             | NO   |     | 0       |                |
| repeatuntil       | varchar(20)         | YES  |     | <null>  |                |
| timestamp         | bigint(20) unsigned | NO   |     | 0       |                |
| comment           | varchar(300)        | YES  |     | <null>  |                |
| repeatfreq        | int(11)             | NO   |     | 1       |                |
| paymentmodeid     | int(11)             | NO   |     | 0       |                |
| deleted           | int(11)             | NO   |     | 0       |                |
| project_id        | varchar(64)         | YES  |     | <null>  |                |
| payer_id          | bigint(20) unsigned | YES  |     | <null>  |                |
| category_id       | bigint(20)          | YES  |     | <null>  |                |
| payment_mode_id   | bigint(20) unsigned | YES  |     | 0       |                |
| payment_mode      | varchar(1)          | YES  |     | <null>  |                |
| last_changed      | bigint(20) unsigned | NO   |     | 0       |                |
| repeat_all_active | int(11)             | NO   |     | 0       |                |
| repeat_until      | varchar(20)         | YES  |     | <null>  |                |
| repeat_frequency  | int(11)             | NO   |     | 1       |                |
+-------------------+---------------------+------+-----+---------+----------------+

Sorry for not giving details about the DB in the first place!

[prismplex]

Hi,
experiencing a similar issue, seems to be related. Had to disable the app cospend, otherwise my nextcloud ran into constant error. When trying to reenable I get the following error:
SQLSTATE[01000]: Warning: 1265 Data truncated for column 'project_id' at row 1
Using MariaDB 11.6.2
These are my columns of oc_cospend_bills:

#	Name	Type	Collation	Attributes	Null	Default	Comments	Extra
1	id	int(11)			No	None		AUTO_INCREMENT
2	what	varchar(300)	utf8mb4_bin		No	None
3	amount	double			No	None
4	repeat	varchar(1)	utf8mb4_bin		No	n
5	timestamp	bigint(20)		UNSIGNED	No	0
6	comment	varchar(300)	utf8mb4_bin		Yes	NULL
7	deleted	int(11)			No	0
8	project_id	varchar(64)	utf8mb4_bin		No	None
9	payer_id	bigint(20)		UNSIGNED	No	None
10	category_id	bigint(20)			Yes	NULL
11	payment_mode_id	bigint(20)		UNSIGNED	Yes	0
12	payment_mode	varchar(1)	utf8mb4_bin		Yes	NULL
13	last_changed	bigint(20)		UNSIGNED	No	0
14	repeat_all_active	int(11)			No	0
15	repeat_until	varchar(20)	utf8mb4_bin		Yes	NULL
16	repeat_frequency	int(11)			No	1

[fauust]

@julien-nc is there anything that I can test to try to investigate this pb? Do you have any suggestion on how we could rollback to the previous version (replacing the directory in apps does not seem to work, I know it's not supposed to anyway)...

[julien-nc]

@fauust The migration is trying to transfer the data from the paymentmodeid (which is an INT) column to the payment_mode_id (which is an unsigned BIGINT) one. So there seems to be a value that is either negative or more than the BIGINT limit (unlikely coming from an INT column).

So I guess you could check if there is a negative value in oc_cospend_bills.paymentmodeid. If there are some, just remove them (set them to 0) and try the Cospend upgrade again.

@prismplex About oc_cospend_bills.projectid, I have even less clue about what's happening. The old column (projectid) was a STRING(64) and the new one is the same. I don't see why the data could not be transferred. Can you list the project IDs?

SELECT projectid from oc_cospend_bills GROUP BY projectid;

Also, @prismplex On which migration step are you getting the warning? Look for something like 030000Date20240911230019 in the logs.

[fauust]

@fauust The migration is trying to transfer the data from the paymentmodeid (which is an INT) column to the payment_mode_id (which is an unsigned BIGINT) one. So there seems to be a value that is either negative or more than the BIGINT limit (unlikely coming from an INT column).

So I guess you could check if there is a negative value in oc_cospend_bills.paymentmodeid. If there are some, just remove them (set them to 0) and try the Cospend upgrade again.

Yep that was it, there was some negative value (-1) on old oc_cospend_bills.paymentmodeid, not sure why. Anyway, I could solve the problem with:

UPDATE oc_cospend_bills SET paymentmodeid = 0 WHERE paymentmodeid < 0;

And then I could proceed to the upgrade, thanks a lot for this great app!

[prismplex]

@prismplex About oc_cospend_bills.projectid, I have even less clue about what's happening. The old column (projectid) was a STRING(64) and the new one is the same. I don't see why the data could not be transferred. Can you list the project IDs?

After executing

SELECT projectid from oc_cospend_bills GROUP BY projectid;

I get:
#1054 - Unknown column 'projectid' in 'SELECT'

After executing
SELECT project_id from oc_cospend_bills GROUP BY project_id;
I get the following project IDs:
Bayrischzell, Dubai, London, Mallorca, budapest, dubai-dirham, jga-fabi, portugal, villach

Also, @prismplex On which migration step are you getting the warning? Look for something like 030000Date20240911230019 in the logs.

That is the error I get:

Database error when running migration 030007Date20241225163007 for app cospend
An exception occurred while executing a query: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'project_id' at row 1

[Oberlurch3000]

Thank you @fauust for sharing your solution! I had the exact same problem and was able to solve it the same way ;).
There was one row having a value < 0 in oc_cospend_bills.paymentmodeid.

[julien-nc]

@prismplex The problem is that we are trying to set NotNull to true for a column that contains NULL values (no clue why).
Apparently the oc_cospend_bills table is fine on your side. The project_id column type seems to be "Null: No" so I assume that part of the migration ran successfully for you.

Let's double check:

SELECT count(*) FROM oc_cospend_bills WHERE project_id IS NULL;

Let's check the others:

SELECT count(*) FROM oc_cospend_members WHERE project_id IS NULL;
SELECT count(*) FROM oc_cospend_shares WHERE project_id IS NULL;
SELECT count(*) FROM oc_cospend_categories WHERE project_id IS NULL;
SELECT count(*) FROM oc_cospend_paymentmodes WHERE project_id IS NULL;
SELECT count(*) FROM oc_cospend_currencies WHERE project_id IS NULL;

A fix would be to either set a project id for those broken rows or delete them if they make no sense.
I can do that in a migration step and make a release for you to try.

Worst case scenario would be that the data transfer has failed and a lot (or all) the rows of those tables have a NULL project_id. In this case you would need to manually set the project_id value everywhere.

[prismplex]

SELECT count(*) FROM oc_cospend_bills WHERE project_id IS NULL;

count: 0

the other commands return the following:

SELECT count(*) FROM oc_cospend_members WHERE project_id IS NULL;

#1054 - Unknown column 'project_id' in 'WHERE'

SELECT count(*) FROM oc_cospend_shares WHERE project_id IS NULL;

#1054 - Unknown column 'project_id' in 'WHERE'

SELECT count(*) FROM oc_cospend_categories WHERE project_id IS NULL;

count: 110

SELECT count(*) FROM oc_cospend_paymentmodes WHERE project_id IS NULL;

#1054 - Unknown column 'project_id' in 'WHERE'

SELECT count(*) FROM oc_cospend_currencies WHERE project_id IS NULL;

count: 0

Thank you for your help!

[julien-nc]

@prismplex Wow that is very unexpected.

• the project_id column is missing from oc_cospend_paymentmodes, oc_cospend_members and oc_cospend_shares but it should have been added by https://github.com/julien-nc/cospend-nc/blob/main/lib/Migration/Version030000Date20240921142937.php
• the project_id column was correctly added in oc_cospend_currencies and oc_cospend_categories in the same migration step (030000Date20240921142937)
• some categories have a NULL project_id
• oc_cospend_currencies and oc_cospend_bills are the only tables that seem fine

1. I would be curious to know if all the categories have a NULL project_id:

SELECT count(*) FROM oc_cospend_categories WHERE project_id IS NOT NULL;

2. Are the old columns still there in the problematic tables?

SELECT projectid FROM oc_cospend_members LIMIT 1;
SELECT projectid FROM oc_cospend_shares LIMIT 1;
SELECT projectid FROM oc_cospend_categories LIMIT 1;
SELECT projectid FROM oc_cospend_paymentmodes LIMIT 1;

or

SHOW COLUMNS FROM oc_cospend_members;
SHOW COLUMNS FROM oc_cospend_shares;
SHOW COLUMNS FROM oc_cospend_categories;
SHOW COLUMNS FROM oc_cospend_paymentmodes;

3. Which Cospend migration steps have run?

SELECT * FROM oc_migrations WHERE app="cospend";

4. Have you done anything else than upgrading Cospend like using a dev version from a git clone?
5. Do you know which upgrade path you took? From Cospend 2.x to what to 3.0.8?

Depending on the state of your DB it might be possible to run some migration steps again or manually fix the data to allow running the step that fails for you (030007Date20241225163007).