Troubleshooting a Column 'group_id' cannot be null Error
When applying the 8.3.0 update, you may observe the following
Integrity constraint violation error:
SQLSTATE: 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))
This is caused by orphaned products in the database, specifically products which are not assigned to a valid Product Group.
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).
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.
orphaned_product_fixer.php from the server.
/install/install.php and follow the on-screen instructions to complete the software update process.
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
3. Go to the
/install/install.php file and follow the displayed instructions to run the update process again.