SQL Error after 7.2.3 upgrade

[cosmin]

I upgrade my WHMCS with incremental patch 7.2.2>7.2.3 and after that I have this error when I try to access it:

 

Critical Error

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'category_id' cannot be null (SQL: insert into `tbltld_category_pivot` (`tld_id`, `category_id`, `created_at`, `updated_at`) values (1, , 2017-07-24 20:41:23, 2017-07-24 20:41:23))

 

I put back the files from backup and I ran upgrade with Automatic update and the same error.

 

Any ideea?

Thanks!

[rezetta]

I am having the same issue. Tried to insert the data manually in both the tables, but the same error occurs.

 

Do you have a fix for this issue?

[cosmin]

No solution until now...

[rezetta]

Okay, I submitted a ticket to support. Let's hope they find a solution.

[cosmin]

Any news from WHMCS tech?

[webio]

I'm experiencing similar issue. Any solution?

[cosmin]

Nothing until now...

[webio]

I've sent support ticket but for a quick hack I've removed not null from category_id column in tbltld_category_pivot table, refreshed WHMCS so it started working correctly after adding problematic row to MySQL DB, I've updated category_id manually changing it from null to some TLD category ID and reverted nullable to not null category_id column in tbltld_category_pivot table.

[webio]

Quick question to people who had this problem. Do you have maybe modified names of TLD categories in tbltld_categories table (using direct DB edit since AFAIK there is no way to localize them)?

[cosmin]

No, I didn't change anything.

[WHMCS John]

Hi,

The tbltld_category_pivot table does not contain any null fields, so if that is the case it suggest an issue with the table in your database.

 

Please find attached the stock table for v7.2 only.

tbltld_category_pivot.zip

[cosmin]

Thanks but I receive the same error:

 

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'category_id' cannot be null (SQL: insert into `tbltld_category_pivot` (`tld_id`, `category_id`, `created_at`, `updated_at`) values (1, , 2017-09-14 16:43:48, 2017-09-14 16:43:48))

[cosmin]

Finally the sollution:

 

Hello,

 

This error indicates that the tbltld_categories table is empty. To resolve this error you simply need to run the following SQL query, then re-attempt the upgrade. Also please ensure you haven't changed the category names from the defaults, as this isn't supported at this time and can/will cause unexpected issues:

 

INSERT INTO `tbltld_categories` (`id`, `category`, `is_primary`, `display_order`, `created_at`, `updated_at`) VALUES

(1, 'gTLD', 1, 1, '2016-08-11 22:44:57', '2016-08-11 22:44:57'),

(2, 'Specialty', 1, 3, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(3, 'Sponsored', 1, 4, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(4, 'ccTLD', 1, 2, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(5, 'Services', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(6, 'Geographic', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(7, 'Geography', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(8, 'Education', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(9, 'Popular', 1, 0, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(10, 'Money and Finance', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(11, 'Featured', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(12, 'Community', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(13, 'Identity and Lifestyle', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(14, 'Arts and Entertainment', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(15, 'Business', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(16, 'Adult', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(17, 'Novelty', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(18, 'Real Estate', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(19, 'Technology', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(20, 'Other', 1, 7, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(21, 'Shopping', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(22, 'Interest', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(23, 'Food and Drink', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(24, 'Sports', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(25, 'Leisure and Recreation', 0, NULL, '2016-08-11 22:44:58', '2016-08-11 22:44:58'),

(26, 'WatchList', 1, 6, '2016-08-11 22:45:01', '2016-08-11 22:45:01'),

(27, 'IDN', 1, 5, '2016-08-11 22:45:06', '2016-08-11 22:45:06');

[persolenk@gmail.com]

Hello, can you send me a tbtld_category_pivot please ? Because i don't download this.

[WHMCS John]

On 9/29/2017 at 8:32 AM, persolenk@gmail.com said:

Hello, can you send me a tbtld_category_pivot please ? Because i don't download this.

Please see the attachment to my post above: https://whmcs.community/topic/274549-sql-error-after-723-upgrade/?do=findComment&comment=1257793