Unable to apply patch 318. Found foreign key table

[facer]

I looked in my php.log and saw that the following error is causing the problem:
Since I am have no clue of programming I tried the 2 suggestions without success.

Can anyone advise me please.

Unable to apply patch 318. Found foreign key table columns with values not in
the reference table column. The following list shows what values in foreign
key columns are missing from reference columns.

There two ways to fix this situation. Either change the row columns to reference
an existing record in the REFERENCE TABLE, or delete the rows that contain
the invalid columns.

To do this, the following example of the SQL statements to execute for the test
case where the ‘cron_log’ table contains invalid values ‘2’ and ‘3’ in the
‘cron_id’ column. The SQL statements to consider using are:

UPDATE si_cron_log SET cron_id = 6 WHERE cron_id IN (2,3);
—- or —-
DELETE FROM si_cron_log WHERE cron_id IN (2,3);

FOREIGN KEY TABLE COLUMN REFERENCE TABLE COLUMN INVALID VALUE
———————— —————— ———————– ——— ————-
invoice_items invoice_id invoices id 1

[facer]

after that I see an other error:

[0] => 42000
[1] => 1064
[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE sql_id = ‘454” at line 3

I have no clue any longer one after the other errors keep coming.

[RRowley]

The error is explicit. There are values in the cron_log table where the cron_id is set to 2 and to 3, but there are no entries in the cron table with an id of 2 or 3. Two options are provided. Change the cron_id in the those cron_log records to a value that does exist in the cron table or delete those records.

The update command:
UPDATE si_cron_log SET cron_id = 6 WHERE cron_id IN (2,3);
will set the cron_id to 6 for these records. Or the delete command:
DELETE FROM si_cron_log WHERE cron_id IN (2,3);
will delete these orphaned records.

I recommend the delete command. These records probably were associated with cron table entries that you deleted in the past and the history in the cron_log table was not cleaned up.

To either update or delete the records copy the command, go to the phpMyAdmin console and do:

1) Access the cron_log table
2) Select the SQL tab
3) Select the Clear button to clear the default content in the text box
4) Paste your copied command into the Run SQL query text box
5) Finally select the Go button to execute the command.

At this point, the orphaned records should not be orphaned/exist any more.

I recommend that you delete the content of the tmp/log/php.log file before accessing SI again in case there is another error, so it will be at the top of the file.

• This reply was modified 4 years, 4 months ago by RRowley.

[facer]

Thank you, it was a very good tip to delete the php.log as I was looking at the same error again and again, while I got it fixed already. I also used the Master 2019.2.1 BETA which was not a clever idea, I admit. Later on I used version: Master 2019 and I got it rolling.

Very happy with the result.
I am a long time user of SI, do not use it often just a couple of times a month.

Thank you for keeping SI alive!

• This reply was modified 4 years, 4 months ago by facer.

[kerryww]

Hi, thanks for the info and help, but eventhough I’ve followed your directions and attemped the proceedure numerous times I still keep getting the same error in the log file.

Do you have any other suggestions?

Thanks again for your help.

Log file error:

[14-Aug-2020 00:09:54 America/Los_Angeles] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[14-Aug-2020 00:15:48 America/Los_Angeles]
Unable to apply patch 318. Found foreign key table columns with values not in
the reference table column. The following list shows what values in foreign
key columns are missing from reference columns.

There two ways to fix this situation. Either change the row columns to reference
an existing record in the REFERENCE TABLE, or delete the rows that contain
the invalid columns.

To do this, the following example of the SQL statements to execute for the test
case where the ‘cron_log’ table contains invalid values ‘2’ and ‘3’ in the
‘cron_id’ column. The SQL statements to consider using are:

UPDATE si_cron_log SET cron_id = 6 WHERE cron_id IN (2,3);
—- or —-
DELETE FROM si_cron_log WHERE cron_id IN (2,3);

FOREIGN KEY TABLE COLUMN REFERENCE TABLE COLUMN INVALID VALUE
———————— —————— ———————– ——— ————-
invoices biller_id biller id 0
invoices customer_id customers id 0
invoices type_id invoice_type inv_ty_id 0
invoices preference_id preferences pref_id 0

[14-Aug-2020 00:15:48 America/Los_Angeles] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.

Attachments:

1. 

   Screenshot-from-2020-08-14-17-20-51.png
   

[kerryww]

Problem solved.

I’ve just now uploaded the SimpleInvoices Master files and connected to the database and it works. About the only thing not working is the logo doesn’t appear in the PDF invoice. But I see there’s a solution for that, so I’ll get onto that now.

For those who are having the same problem. A little more info about my situation, I’m on a shared host with Dreamhost and using PHP 7.2.

[mmmsabeel]

hello. im also stuck on this 318 foreign key patch. when clicked update i dont get any php error log.
but get 500 Server Error
A misconfiguration on the server caused a hiccup. Check the server logs, fix the problem, then try again.
URL: http://teknotrack.lk/inv1/inv1/index.php?case=run

[RRowley]

The 500 error is most likely due to you system not being properly set up. Did you have a previous version of SI running or is this a new set up? What is the webserver package you are running? Is this a Linux or Windows system?

If you were running a previous version, can you restore to it and run correctly?

[mmmsabeel]

i performed this update on top of previous version of si which was running perfectly on php7. after php 7+ update the pdf generation stopped working therefore installed this 2019 master file. this is running on justhost linux server. i tried restoring and reinstalling this but getting the same issue, till 318 update there is no issue. and the system freezes from there. there is also no error reported in the log file so i cannot troubleshoot it. i tried with a fresh install with new database, it worked.

[Author]

Posts