Troubleshooting a Column 'group_id' cannot be null Error

Symptom

When you update to WHMCS 8.3, you may see 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))
Click to copy

Cause

This indicates that there are orphaned products in the database (products which are not in a valid product group).

Solution

You can download a script to correct the integrity of product data in the WHMCS installation, which will allow the update process to complete successfully.  

To do this:

  1. Download the script.
  2. Unzip the script and upload it to the main WHMCS directory (the directory that contains the init.php file).
  3. In your preferred browser, go to the orphaned_product_fixer.php file to run the script.
    • The script will create a new hidden product group and assign the orphaned products to it.
    • When the script finishes, it will display Orphaned products have been reassigned to product group ID x.
  4. Delete the orphaned_product_fixer.php script from the server.
  5. Go to /install/install.php.
  6. Follow the displayed instructions to complete the update.

Workaround

You can also resolve this issue directly in the database.

To do this:

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

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

 2. Correct the records using the following query:

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

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.