Problem #

You 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))

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);

2. Correct the records using the following query:

   UPDATE tblproductsSETgid= "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.

Last modified: October 29, 2024