Database Upgrade Error

[Bruce]

Getting errors when applying database patches.

• This topic was modified 3 years, 11 months ago by Bruce.

Attachments:

1. 

   invoices-update.png
   

2. 

   invoices-update-error.png
   

[RRowley]

Check 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.

[Bruce]

Hi,

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, 11 months ago by Bruce.

[Bruce]

Zip file

Attachments:

1. php.zip

[RRowley]

These 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.

[Author]

Posts