Unable to register payments (500 server error)

Self-Hosted | v4/Legacy
1

Hello,

I recently had to recover my self hosted environment (create a new vm, install all components and restored a backup of the database. I’m currently on InvoiceNinja v4.5.13) all seemed well until I wanted to register a new payment. I throws an error (whatever way I try).
In the laraval error-log I find this:
production.ERROR: Illuminate\Database\QueryException [23000] : /var/www/html/invoiceninja/vendor/laravel/framework/src/Illuminate/Database/Connection.php [Line 647] => SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’ (SQL: insert into payments (user_id, account_id, public_id, payment_type_id, payment_date, transaction_reference, private_notes, exchange_currency_id, invoice_id, client_id, amount, updated_at, created_at) values (1, 1, 117, 2, 2019-07-02, , , , 122, 195, 145.2, 2019-07-09 10:58:06, 2019-07-09 10:58:06)) {“context”:“PHP”,“user_id”:1,“account_id”:1,“user_name”:"**********",“method”:“POST”,“user_agent”:“Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36”,“locale”:“nl”,“ip”:"192.168..",“count”:5,“is_console”:“no”,“is_api”:“no”,“db_server”:“mysql”,“url”:“payments”} []

When I check in the database it indeed registered the last payment with ID 0, but the status of the invoice is not updated (still == 2). The last succesful payment has an ID of 122. Did I forgot a step restoring the database in the new setup? Can anyone help getting this straightened out?

Thank you in advance

Guy

2

Is the table’s id field (the primary key) set to auto-increment?

3

No it isn’t, but I notice on invoices this id-field is also not autoincremented and adding new invoices still works. Is this then really necessary? And won’t it impact the existing id’s?

4

Here’s what I see in my database. Not sure why invoices are working but any changes to the database schema will cause problems in the app.


mysql> describe payments;
+-----------------------+----------------------+------+-----+---------+----------------+
| Field                 | Type                 | Null | Key | Default | Extra          |
+-----------------------+----------------------+------+-----+---------+----------------+
| id                    | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
5

Thank you for the input, my problem is solved. I changed all the id fields to auto increment and now everything works as expected.
It seems the backup of the database does not take the auto increment in account.