SimpleInvoices Group Forum › Forums › Fearless359 SimpleInvoices Discussion Group › Database Upgrade Error
- This topic has 4 replies, 2 voices, and was last updated 3 years, 8 months ago by RRowley.
-
AuthorPosts
-
December 31, 2020 at 5:19 pm #1283BruceParticipant
Getting errors when applying database patches.
- This topic was modified 3 years, 9 months ago by Bruce.
Attachments:
January 1, 2021 at 6:04 am #1287RRowleyParticipantCheck the tmp/log/php.log file for error detail. You likely have orphaned records that prevent foreign keys from being added. The error information will tell you what they are as well as provide an example of how to correct them. If zip the error log file and attach it here, I can look at it an advise what to do. Make sure there is not secret info in the error log before attaching it.
January 20, 2021 at 12:38 am #1324BruceParticipantHi,
Thanks for taking the time to reply to my problem.
****************************************************
[07-Apr-2020 19:09:08 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[07-Apr-2020 19:09:08 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[07-Apr-2020 19:09:42 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[07-Apr-2020 19:09:42 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[07-Apr-2020 19:09:57 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[07-Apr-2020 19:09:57 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[07-Apr-2020 19:10:58 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[07-Apr-2020 19:10:58 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[07-Apr-2020 21:10:31 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[07-Apr-2020 21:10:31 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[08-Apr-2020 18:23:59 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[08-Apr-2020 18:23:59 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[01-Jan-2021 10:27:05 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[01-Jan-2021 10:27:05 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[01-Jan-2021 11:02:43 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[01-Jan-2021 11:02:43 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[01-Jan-2021 11:04:57 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[01-Jan-2021 11:04:57 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[01-Jan-2021 11:11:16 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[01-Jan-2021 11:11:16 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
[20-Jan-2021 18:15:15 Australia/Brisbane]
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
invoice_items invoice_id invoices id 2
invoice_items invoice_id invoices id 3
invoice_items invoice_id invoices id 4
invoice_items invoice_id invoices id 12
payment ac_inv_id invoices id 2
payment ac_inv_id invoices id 3[20-Jan-2021 18:15:15 Australia/Brisbane] SqlPatchManager::runSqlPatch() – SqlPatchManager::prePatch318() = Unable to set Foreign Keys.
- This reply was modified 3 years, 8 months ago by Bruce.
January 20, 2021 at 1:08 am #1326January 30, 2021 at 6:24 am #1332RRowleyParticipantThese are the records at issue (from the attached zip file):
FOREIGN KEY TABLE COLUMN REFERENCE TABLE COLUMN INVALID VALUE ------------------------ ------------------ ----------------------- --------- ------------- invoice_items invoice_id invoices id 1 invoice_items invoice_id invoices id 2 invoice_items invoice_id invoices id 3 invoice_items invoice_id invoices id 4 invoice_items invoice_id invoices id 12 payment ac_inv_id invoices id 2 payment ac_inv_id invoices id 3
These records are orphaned, they have no associated parent records. For the si_invoice_items records, you can check them in the database and verify the the index_id in each of these records does not exist in the si_invoices table. Similarly for the si_payments records you can verify that the ac_inv_id does not have a corresponding record in the si_invoices table.
Once verified, your choice is to delete the orphaned records or to manually create an si_invoice record with an id matching the orphaned records.
Once this is done, the patch process will complete.
-
AuthorPosts
- You must be logged in to reply to this topic.
Recent Comments