Foreign Key Error - Upgrading from 2.8.0 to 2.9.1

Self-Hosted | v4/Legacy
1

Hi,

I’ve just gone to run an upgrade from 2.8.0 to 2.9.1 and I’ve received the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (dpidesig_ninja.payment_types, CONSTRAINTpayment_types_gateway_type_id_foreignFOREIGN KEY (gateway_type_id) REFERENCESgateway_types(id) ON DELETE CASCADE) (SQL: updatepayment_typessetgateway_type_id= 2 whereid= 2)

Any ideas?

Cheers

2

I resolved this by:

  1. creating a clean install of InvoiceNinja 2.9.1 in a separate DB
  2. Exporting the payment_types table to SQL
  3. Backing up my live invoiceninja instance that failed the update
  4. Clearing the payment_types table in my live instance
  5. Running:
set foreign_key_checks=0;
INSERT INTO <code>payment_types</code> (<code>id</code>, <code>name</code>, <code>gateway_type_id</code>) VALUES
(1, 'Apply Credit', NULL),
(2, 'Bank Transfer', 2),
(3, 'Cash', NULL),
(4, 'Debit', 1),
(5, 'ACH', 2),
(6, 'Visa Card', 1),
(7, 'MasterCard', 1),
(8, 'American Express', 1),
(9, 'Discover Card', 1),
(10, 'Diners Card', 1),
(11, 'EuroCard', 1),
(12, 'Nova', 1),
(13, 'Credit Card Other', 1),
(14, 'PayPal', 3),
(15, 'Google Wallet', NULL),
(16, 'Check', NULL),
(17, 'Carte Blanche', 1),
(18, 'UnionPay', 1),
(19, 'JCB', 1),
(20, 'Laser', 1),
(21, 'Maestro', 1),
(22, 'Solo', 1),
(23, 'Switch', 1);

But it’s still a concern that this occurred. Any idea what the issue would have been?

3

Can you run the following SQL query:

select * from payment_libraries;

You should see two records, from your error though it looks like there may only be one row.

4

Hi Hillel,

Unfortunately, I did not keep the backup of my SQL database before fixing it after I worked out the problem. I suppose this issue can be resolved now anyway being I worked out how to fix it.

Cheers
Harrison