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