Guides & Tutorials8.3 Update TroubleshootingTroubleshooting a Column 'group_id' cannot be null Error

Troubleshooting a Column 'group_id' cannot be null Error

Symptom

When applying the 8.3.0 update, you may observe the following Integrity constraint violation error:

 

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'group_id' cannot be null (SQL: insert into tblproducts_slugs (group_id, group_slug, slug, active, product_id, updated_at, created_at) values (?, ?, , 1, 1, 2021-09-30 10:24:30, 2021-09-30 10:24:30))

Cause

This is caused by orphaned products in the database, specifically products which are not assigned to a valid Product Group.

Solution

A fixer script is available to correct the integrity of product data in the WHMCS installation, which will allow the update process to complete successfully.  

Perform the following steps to fix this:

1. Download the fixer script from https://whmcs.community/files/file/170-core-17166-orphaned-products-fixer/

2. Unzip the fixer script and upload to the main WHMCS directory (the directory containing init.php).

3. Visit orphaned_product_fixer.php in your web browser.

4. The fixer completes successfully by displaying the output:

Orphaned products have been reassigned to product group ID x

5. A new hidden Product Group has been created and the orphaned products assigned to it.

6. Delete orphaned_product_fixer.php from the server.

7. Visit /install/install.php and follow the on-screen instructions to complete the software update process.

Workaround

Alternatively the same solution can be applied directly to the database. Use of the fixer script is recommended.

Perform the following steps to fix this manually:

1. Identify  the affected product records using the following MySQL® query:

SELECT * FROM tblproducts WHERE `gid` NOT IN (SELECT id FROM tblproductgroups);

 

2. You can then correct the records using the following query:

 

 UPDATE `tblproducts` SET `gid`= "x" WHERE `gid` NOT IN (SELECT id FROM tblproductgroups); 

In the above command, replace x with a valid group ID from tblproductgroups.id.

 

3. Go to the /install/install.php file and follow the displayed instructions to run the update process again.