[solved] Exception: Database error when running migration 010513Date20231203164157 for app cospend

[jgrete]

On upgrading severeal apps I got this error with cospend sudo -u wwwrun php /srv/www/htdocs/nextcloud/occ upgrade Nextcloud or one of the apps require upgrade - only a limited number of commands are available You may use your browser or the occ upgrade command to do the upgrade Setting log level to debug Updating database schema Updated database Updating ... Exception: Database error when running migration 010513Date20231203164157 for app cospend An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'timestamp' at row 667 Update failed Maintenance mode is kept active Resetting log level

cospend: 1.5.12 -> 1.5.13 System: Linux openSUSE 15.4 Browser: Firefox 120.0.1 PHP 8.0.29 DB: mariadb Ver 15.1 Distrib 10.10.2-MariaDB

I removed the app for now, hoping to reimport the backup later...

[urigg]

Same issue when updating Nextcloud from 27.1.4 to 27.1.5. Got stuck in an web interface update loop until I disabled the app on command line. When re-enabling later I get this error:

Database error when running migration 010513Date20231203164157 for app cospend An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'timestamp' at row 3

[julien-nc]

Could you try to edit cospend/lib/Migration/Version010513Date20231203164157.php line 52 and set it to:

    $column->setUnsigned(false);

I suspect you have negative timestamps. Enabling the app should then work.

Could you also run this query in your database?

SELECT timestamp FROM oc_cospend_bills WHERE timestamp < 1;

[jgrete]

Database query result:

` MariaDB [(none)]> use nc_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed MariaDB [nc_db]> SELECT timestamp FROM oc_cospend_bills WHERE timestamp < 1; +--------------+ | timestamp | +--------------+ | -61976464288 | | -61976377888 | +--------------+ 2 rows in set (0.020 sec) ` After changing the line as proposed the result still is:

Database error when running migration 010513Date20231203164157 for app cospend An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'timestamp' at row 667

Regards Jürgen

[urigg]

For me this worked, probably since after the workaround the app was in installed, updated but not enabled state.

And yes, I have one value of -3592 in the timestamp column.

[jgrete]

Any further hints? After reverting to the former version the app in nextcloud is still not usable. Is it save to remove the app and delete oc_cospend_bills from my nextcloud db? I would try to reinstall the app thereafter.

[julien-nc]

v1.5.14 should be fine. Cospend makes sure to set all negative timestamps to 0 before setting the DB column type to unsigned BIGINT. It should work fine now.

Still a mystery why timestamps could be negative in some cases, never seen that.

[jgrete]

I tried severeal things - no luck: I removed tables occospend... from the nc database, moved the export directory away, I deleted the app from nc and from both mobile phones. I checked the nextcloud.log (see below). I reinstalled cospend in nc and tried to start from scratch. On starting the app in nc as a normal user, I'm getting this (for about 10s, then only a circulating spinner top left). Sorry, it was in german. Translated but I don't know if that's orginal error message:

`Loading of project impossible: Nr17

This application requires JavaScript for proper operation. Please activate JavaScript and reload

Nr17

Internal server error The server was unable to complete the request. If this happens again, please send the following technical details to your server administrator. Further details can be found in the server log.

Technical details

Remote address: 89.xx.xx.177 Request ID: KUCxxxxxxxxxxZKBCWs `

No idea why for cospend javascript seems to be not acrivated while all other apps and nc itself have no problem with it...

Log grepped cospend entries cospend.log

[julien-nc]

@jgrete All your log entries are about missing DB tables. You probably didn't delete the Cospend-related entries in oc_migrations so the tables were not created when you reinstalled/reenabled Cospend.

DELETE FROM oc_migrations WHERE app=cospend;

[urigg]

Still a mystery why timestamps could be negative in some cases, never seen that.

In my case it looks like the timestamp was somehow invalid/zero, and got time zone converted afterwards.

[jgrete]

Ok, after deleting the remaining traces of cospend from the database, I could restart with a new project. The above given sql-command didn't work for me (sorry, I forgot to save the error message), so I used DBeaver to find and delete records from oc_migrations. Thank you for your help Regards Jürgen