SQL errors after update from 4.2.2 to 4.4.1


Suddenly PDFs are not generated anymore and I get this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘background_image_id’.

Complete error:
[2018-05-01 07:06:59] production.ERROR: Illuminate\Database\QueryException [42S22] : /home/xxx/vendor/laravel/framework/src/Illuminate/Database/Connection.php [Line 647] => SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘background_image_id’ in ‘field list’ (SQL: updateaccountssetupdated_at= 2018-05-01 07:06:59,hide_quantity= 0,hide_paid_to_date= 1,invoice_labels= {“balance_due”:"",“credit_card”:"",“delivery_note”:"",“description”:"",“discount”:"",“due_date”:"",“gateway_fee_item”:"",“gateway_fee_description”:"",“hours”:"",“id_number”:"",“invoice”:"",“invoice_date”:"",“invoice_number”:"",“item”:"",“line_total”:"",“outstanding”:"",“paid_to_date”:"",“partial_due”:"",“po_number”:"",“quantity”:"",“quote”:"",“quote_date”:"",“quote_number”:"",“rate”:"",“service”:"",“subtotal”:"",“tax”:"",“terms”:"",“unit_cost”:“Stukprijs”,“valid_until”:"",“vat_number”:""},all_pages_footer= 0,all_pages_header= 0,invoice_fields= {“invoice_fields”:[“invoice.invoice_number”,“invoice.po_number”,“invoice.invoice_date”,“invoice.due_date”,“invoice.balance_due”,“invoice.partial_due”],“client_fields”:[“client.client_name”,“client.id_number”,“client.vat_number”,“client.address1”,“client.city_state_postal”,“client.email”],“account_fields1”:[“account.company_name”,“account.address1”,“account.city_state_postal”,“account.website”,“account.email”],“account_fields2”:[".blank",“account.vat_number”,“account.id_number”,“account.custom_value1”],“product_fields”:[“product.item”,“product.description”,“product.custom_value1”,“product.custom_value2”,“product.unit_cost”,“product.quantity”,“product.tax”,“product.line_total”],“task_fields”:[“product.service”,“product.description”,“product.custom_value1”,“product.custom_value2”,“product.rate”,“product.hours”,“product.tax”,“product.line_total”]},invoice_embed_documents= 1,background_image_id= whereid= 1) {"context":"PHP","user_id":1,"account_id":"1","user_name":"Lars Jansen","method":"POST","user_agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36","locale":"nl","ip":"84.241.xxx","count":11,"is_console":"no","is_api":"no","db_server":"mysql","url":"settings/invoice_design"} []

How to solve this?

Kind regards,
Lars Jansen

What do you see on /update

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (DBNAME_orders.#sql-1846_98ba54, CONSTRAINT activities_client_id_foreign FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE) (SQL: alter table activities add constraint activities_client_id_foreign foreign key (client_id) references clients (id) on delete cascade)

Have you manually deleted any clients from your database?

You can check with this SQL query:

select a.id, a.created_at, a.client_id
from activities a 
left join clients c on c.id = a.client_id
where c.id is null
and a.client_id is not null;

If there are matching records you’ll need to delete them and then load /update.

All my clients are matches…

And I didn’t do anything in the database. I just updated Invoice Ninja.

Have you manually deleted any clients in the past?

If you want to send a backup of your database from before the upgrade to contact@invoiceninja.com I can try to debug it.

Nope, also not the case… I sent the DB backup to your email address.

Thanks! From the database it looks like clients with an id less than 78 have been deleted.

The activities records left behind will need to be deleted to run the update.

Okay, so how do I do that?

Here’s the SQL query:

delete from activities where client_id < 78;

It does update now, thanks! But now another problem occured. In my dashboard there suddenly is an negative outstanding overall price. It says €-1.558,08, but I can’t find where that is set.

The update should not affect the totals on the dashboard, you may want to rollback to your backup.

The client balance and paid_to_date fields are used in the calculation.

I can’t find it in the DB, but I found it in the admin interface. For some reason the system generated some invoices which weren’t meant to be generated, so I deleted them manually from the admin interface. But because I did that, it now looks like I have to pay back that money to the customer. How do I restore this?

I’m not sure, marking invoices as deleted shouldn’t results in negative values.